I like very much user participation (SCRUM / Agile is my moto) - I never
believe to be always right nor write perfect code, and I'm convinced Open
Source projects are also about sharing ideas among people of good will.
So when an active member of the forum reported his confusion /
concern about some of the ORM methods of our framework, it appeared that
some re-factoring was necessary.
There was a breaking change about the TSQLRecord.Create /
FillPrepare / CreateAndFillPrepare and TSQLRest.OneFieldValue /
MultiFieldValues methods: for historical reasons, they expected
parameters to be marked as % in the SQL WHERE clause, and inlined
via :(...):.
Since revision 1.17 of the framework, those methods expect parameters marked as
? and with no :(...):.
For instance, instead of writing:
aRec.CreateAndFillPrepare(Client,'Datum=?',[],[DateToSQL(EncodeDate(2012,5,4))]);
you should write now:
aRec.CreateAndFillPrepare(Client,'Datum=?',[DateToSQL(EncodeDate(2012,5,4))]);
The void [], array (used for replacing %
characters) is not to be written any more, since the default is to use bound
parameters via ? and not textual replacement via
%.
Due to this breaking change, user code review is necessary if you
want to upgrade the engine from 1.16 or previous.
In all cases, using ? is less confusing for new users, and more
close to the usual way of preparing database queries - e.g. as used in
SynDB.pas units.
Both TSQLRestClient.EngineExecuteFmt / ListFmt methods are not
affected by this change, since they are just wrappers to the
FormatUTF8() function.
Query parameters
For safer and faster database process, the WHERE clause of the request
expects some parameters to be specified. They are bound in the ?
appearance order in the WHERE clause of the [CreateAnd]FillPrepare
query method.
Standard simple kind of parameters (RawUTF8, integer, double..)
can be bound directly - as in the sample code above for Name or
Sex properties. The first parameter will be bound as 'A%'
RawUTF8 TEXT, and the second as the 1 INTEGER value.
Any TDateTime bound parameter shall better be specified using
DateToSQL() or DateTimeToSQL() functions, as
such:
aRec.CreateAndFillPrepare(Client,'Datum=?',[DateToSQL(EncodeDate(2012,5,4))]);
aRec.CreateAndFillPrepare(Client,'Datum<=?',[DateTimeToSQL(Now)]);
For TTimeLog / TModTime / TCreateTime kind of properties,
please use the underlying Int64 value as bound parameter.
Any sftBlob property should better be handled separately, via
dedicated RetrieveBlob and UpdateBlob method calls,
if the data is expected to be big (more than a few MB). But you can specify a
small BLOB content using an explicit conversion to the corresponding TEXT
format, by calling BinToBase64WithMagic() overloaded functions
when preparing such a query.
Feedback is welcome in our
forum.
