mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,651

Website

SQLite3: SQL parameters can now be prepared

Up to now, most SQL statements were parsed, then prepared before execution.
Only individual TSQLRecord content retrieval was using prepared statements.

We added an internal SQL statement cache in the framework. That is, if a previous SQL statement is run with some parameters, a prepared version, available in cache, is used, and new parameters are bounded to it before the execution by SQLite3.
It can speed the SQLite3 process a lot, since all SQLite3 calls are pipelined for security purposes.

In order to use this statement caching, any SQL statements must have the parameters to be surrounded with ':(' and '):'.
In this case, an internal pool of prepared TSQLRequest statements is used.
Example of possible inlined values are:

:(1234):  :(12.34): :(12E-34): :("text"): :('text'): (with double quoting inside the text, just like any SQL statement)

All internal SQL statement generated by the ORM are now using this new parameter syntax.
For instance, here is how an object deletion is handled:

function TSQLRestServerDB.EngineDelete(Table: TSQLRecordClass; ID: integer): boolean;
begin
  if Assigned(OnUpdateEvent) then
     OnUpdateEvent(self,seDelete,Table,ID); // notify BEFORE deletion
  result := EngineExecuteFmt(
    'DELETE FROM % WHERE RowID=:(%):;',[Table.SQLTableName,ID]);
end;

In you code, you should better use it, for instance:

aName := OneFieldValue(TSQLRecord,'Name','ID=:(23):');

instead of

aName := OneFieldValue(TSQLRecord,'Name','ID=23');

The mORMot framework will also take benefit of this new feature.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,651

Website

Re: SQLite3: SQL parameters can now be prepared

See http://synopse.info/forum/viewtopic.php?id=180