mORMot Open Source

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,237

Website

SQL parameters can now be prepared "on the fly"

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

For the upcoming version 1.12 of the framework, we added an internal SQL statement cache in the database access.
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.

In some cases, it can speed the SQLite3 process a lot.

In order to use this statement caching, any SQL statements must have the parameters to be surrounded with ':(' and '):'. That is, we enhanced the SQL format by adding an optional way of marking parameters inside the SQL request, to force statement caching.

Therefore, there are now two ways of writing the same SQL request:

1. Write the SQL statement as usual:

SELECT * FROM TABLE WHERE ID=10;

in this case, the SQL will be parsed by the SQLite3 engine, a statement will be compiled, then run.

2. Use the new optional markers to identify the changing parameter:

SELECT * FROM TABLE WHERE ID=:(10):;

in this case, any matching already prepared statement will be used for direct run.

In the last case, an internal pool of prepared TSQLRequest statements is used.

The generic SQL code used for the matching will be this one:

SELECT * FROM TABLE WHERE ID=?;

and the integer value 10 will be bounded to the prepared statement before execution.

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 your code, you should better use it, for instance:

 aName := OneFieldValue(TSQLMyRecord,'Name','ID=:(%):',[aID]);

instead of

 aName := OneFieldValue(TSQLMyRecord,'Name','ID=%',[aID]);

I found out that this SQL format enhancement is much more easy to use in the Delphi code than using parameters by name or by index, like in this code:

SQL.Text := 'SELECT Name FROM Table WHERE ID=:Index';
SQL.ParamByName('Index').AsInteger := aID;

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

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,237

Website

Re: SQL parameters can now be prepared "on the fly"

In the 1.15 release, a new overloaded method has been added, to bind inlined parameters more easily to your prepared requests.
You can use '?' to identify inline parameters, and it will be quoted and surrounded by :(..): on purpose.

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

aName := OneFieldValue(TSQLMyRecord,'Name','ID=:(%):',[aID]);

or even easier

aName := OneFieldValue(TSQLMyRecord,'Name','ID=?',[],[aID]);

instead of

aName := OneFieldValue(TSQLMyRecord,'Name','ID=%',[aID]);

In fact, from your client code, you may not use directly the :(...): expression in your request, but would rather use the overloaded TSQLRecord.Create, TSQLRecord.FillPrepare, TSQLRecord.CreateAndFillPrepare, TSQLRest.OneFieldValue, TSQLRest.MultiFieldValues, TQLRestClient.EngineExecuteFmt and TSQLRestClient.ListFmt methods, available since revision 1.15 of the framework, which will accept both '%' and '?' characters in the SQL WHERE format text, inlining '?' parameters with proper :(...): encoding and quoting the RawUTF8 / strings parameters on purpose.

At a lowest-level, inlining the bounds values inside the statement enabled better serialization in a Client-Server architecture, and made caching easier on the Server side: the whole SQL query contains all parameters within one unique RawUTF8 value, and can be therefore directly compared to the cached entries. As such, our framework is able to handle prepared statements without keeping bound parameters separated from the main SQL text.

It's also worth noting that external databases - see http://blog.synopse.info/post/2011/08/0 … e3-limited - will also benefit from this statement preparation. Inlined values will be bound separately to the external SQL statement, to achieve the best speed possible.