Function problem <TSQLRequest.Prepare>

eraldo · 2012-02-03 04:53:57

Dear administrator,

When I have multiple commands within a Begin, End, and one of the commands is wrong, I would have it generated an exception regarding the error.

Example:
When you run the lines below, the function: "function TSQLRequest.Prepare (" does not return the error on the line "DELETE fron cod where test = 1;"

BEGIN;
CREATE TEMP TABLE test (cod INTEGER, name VARCHAR(50));
INSERT INTO test values (1, 'alex');
DELETE fron test where cod = 1;
INSERT INTO test values (1, 'alex');
END;

I believe that there can be a change in function

function TSQLRequest.Prepare(DB: TSQLite3DB; const SQL: RawUTF8): integer;
begin
  fDB := DB;
  fRequest := 0;
  if DB=0 then
    raise ESQLException.Create(DB,SQLITE_CANTOPEN);
  result := sqlite3_prepare_v2(RequestDB, pointer(SQL), length(SQL)+1, fRequest, fNextSQL);
  while (result=SQLITE_OK) and (Request=0) do // comment or white-space
    result := sqlite3_prepare_v2(RequestDB, fNextSQL, -1, fRequest, fNextSQL);
  fFieldCount := sqlite3_column_count(fRequest);
  sqlite3_check(RequestDB,result);
end;

If I run the commands without the "Begin, End," is not returned any errors.

I hope you understood what I wanted to show.

ab · 2012-02-03 09:05:49

How to you run the lines?

You should provide only one SQL statement at once withTSQLRequest.Prepare. Then call PrepareNext if there is remaining SQL commands.

eraldo · 2012-02-03 12:09:11

How would I do to properly execute the code below?
So that I received the error regarding the "frontier" what is wrong

var
st: TStringList.
r: TSQLRequest;
Begin;
st: = TStringList.Create;
begin with the st
Add ('BEGIN')
Add ('CREATE TEMP TABLE test (cod INTEGER, name VARCHAR (50));');
Add ('INSERT INTO test values (1, "alex");');
Add ('DELETE fron cod where test = 1;');
Add ('INSERT INTO test values (1, "alex");');
Add ('END');
end;

ab · 2012-02-03 13:03:39

Call TSQLRequest.Execute(DB,SQL) for every line:

var r: TSQLRequest;
begin
     r.Execute(DB,'CREATE TEMP TABLE test (cod INTEGER, name VARCHAR (50));');
     r.Execute(DB,'INSERT INTO test values (1, "alex");');
     r.Execute(DB,'DELETE fron cod where test = 1;');
     r.Execute(DB,'INSERT INTO test values (1, "alex");');
end;

eraldo · 2012-02-03 13:21:09

But for SQLite that will be only one transaction?
In other words: No need to start a transaction in order to run all or not run anything if there is a problem?

eraldo · 2012-02-03 21:24:43

How do I run the script below as a transaction

  s: = TStringList.Create;
   s.Add ('BEGIN;');
   s.Add ('INSERT INTO title (name) VALUES (' TEST ');');
   s.Add ('INSERT1 INTO title (name) VALUES (' TEST ');');
   s.Add ('END');
   SQLite3DB.ExecuteNoResult (s.Text, []);

I think it should return an exception but it does not.

eraldo · 2012-02-04 05:57:48

Dear administrator,
I think I found a bug in the "TSQLRequest.PrepareNext"
I think the right way is:

function TSQLRequest.PrepareNext: integer;
begin
  if (Request=0) or (fNextSQL^=#0) then
    result := SQLITE_DONE else begin
    Close; // free previous statement
    result := sqlite3_prepare_v2(RequestDB, fNextSQL, -1, fRequest, fNextSQL);
    while (result=SQLITE_OK) and (Request=0) and (fNextSQL^<>#0) do // comment or white-space
      result := sqlite3_prepare_v2(RequestDB, fNextSQL, -1, fRequest, fNextSQL);
    fFieldCount := sqlite3_column_count(fRequest);
    sqlite3_check(RequestDB,result); //This function should do the checking before changing the value of result
                                                  //To be generated an exception if there is any error in the script
    if Request=0 then
      result := SQLITE_DONE; // nothing more to add
  end;
end;

My code that throws an exception correctly.

I would like you to tell me if I'm doing right this way

  ...
  s := TStringList.Create;
  s.Add('BEGIN;');
  s.Add('INSERT INTO XXX(nome) VALUES("TEST1");');
  s.Add('INSERT ERR XXX(nome) VALUES("TEST2");');  // <<<ERROR
  s.Add('INSERT INTO XXX(nome) VALUES("TEST3");');
  s.Add('COMMIT;');
 try
  r.ExecuteAll(db.DB, s.Text);
      except
         on E : Exception do begin
         r.Execute(db.DB, 'rollback;') ;
         ShowMessage( E.Message);
        end;
 end;
  r.Close;
  s.Free;
end;

mORMot Open Source

#1 2012-02-03 04:53:57

Function problem <TSQLRequest.Prepare>

#2 2012-02-03 09:05:49

Re: Function problem <TSQLRequest.Prepare>

#3 2012-02-03 12:09:11

Re: Function problem <TSQLRequest.Prepare>

#4 2012-02-03 13:03:39

Re: Function problem <TSQLRequest.Prepare>

#5 2012-02-03 13:21:09

Re: Function problem <TSQLRequest.Prepare>

#6 2012-02-03 21:24:43

Re: Function problem <TSQLRequest.Prepare>

#7 2012-02-04 05:57:48

Re: Function problem <TSQLRequest.Prepare>

Board footer