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:
- 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.
- 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.
