mORMot Open Source

p.s.t.

Member

Registered: 2012-12-20

Posts: 9

In-memory SQLite3

I need to have direct access to in-memory database and use TSQLDBSQLite3ConnectionProperties to access it.
I create my table and then insert data.
When I call

  props.Execute( 'insert into Assortment(id, plu) values (222,222);', []);

no insert is executed. But

 props.ExecuteNoResult( 'insert into Assortment(id, plu) values (?,?);', [111, 111])

works ok.
I also tried to insert data in loop:

dbStatement := props.NewThreadSafeStatementPrepared('insert into Assortment(id, plu) values (?,?);', false);
  for I := 1 to 2 do
  begin
      dbStatement.Bind(1,i);
      dbStatement.Bind(2, i+55);
      dbStatement.ExecutePrepared;
  end

First insert is ok, but second time I get error: 'library routine called out of sequence'; I have to call dbStatement.Prepare() but i don't understand why, maybe I do something wrong?

Here is my sample application

program InMemoryDB;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  mORMotSQLite3,
  SynCommons,
  SynDB,
  synDBSqLite3,
  mORMotHttpServer,
  System.Actions,
  synSqLite3;

var
  Props: TSQLDBSQLite3ConnectionProperties;
  dbStatement: TSQLDBStatement;
  createQuery, fJsonBuffer: rawUTF8;
  i: integer;
begin
  try
  createQuery :=  'CREATE TABLE Assortment (ID integer primary key, plu integer);';
  Props := TSQLDBSQLite3ConnectionProperties.Create(':memory:','','','');
  props.ExecuteNoResult(createQuery, []);
  props.ExecuteNoResult( 'insert into Assortment(id, plu) values (?,?);', [111, 111]);  //ok
  props.Execute( 'insert into Assortment(id, plu) values (222,222);', []); //
  fJSONBuffer := props.Execute('Select id from Assortment', []).FetchAllAsJSON(true);
  Writeln('Select 1: ' + fJsonBuffer);;
  dbStatement := props.NewThreadSafeStatementPrepared('insert into Assortment(id, plu) values (?,?);', false);
  for I := 1 to 2 do
  begin
      if i > 1 then
        dbStatement.Prepare('insert into Assortment(id, plu) values (?,?)', false); // without this I get error 'library routine called out of sequence'
      dbStatement.Bind(1,i);
      dbStatement.Bind(2, i+55);
      dbStatement.ExecutePrepared;
  end;

  fJSONBuffer := props.Execute('Select id from Assortment', []).FetchAllAsJSON(true);
  Writeln('Select 2: ' + fJsonBuffer);;
  Readln;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,255

Website

Re: In-memory SQLite3

Use the Reset method, as stated by the documentation:

    {/ Reset the previous prepared statement
     - some drivers expect an explicit reset before binding parameters and
       executing the statement another time
     - this default implementation will just do nothing }
    procedure Reset; virtual;

For TSQLDBSQLite3Statement, this is mandatory.

p.s.t.

Member

Registered: 2012-12-20

Posts: 9

Re: In-memory SQLite3

Thank you. It works very well.