Using '?' parameters in CreateAndFillPrepare() going wrong

Bascy · 2012-08-30 09:48:41

Using an Oracle database, somethings strange happens when i execute the following code:

  // Select a single record by Name
  lName := Format(cPPName, [cResultID]);
  Rec := TSQLUnitTestSampleRecord.CreateAndFillPrepare(Client,'Name=?', [lname], '');

Executing this results in an error stating: Executeprepared expected 1 bound parameters, got 0

Looking at the callstack, it seems that the supplied parameter is not formatted into the SQLwhere clause by the FormatUTF8(FormatSQLWhere,ParamsSQLWhere) call

SQLite3Commons.TSQLRest.InternalListRecordsJSON(TSQLUnitTestSampleRecord,'Name=?')
SQLite3Commons.TSQLRecord.CreateAndFillPrepare($2470890,'Name=?','')
SQLite3Commons.TSQLRecord.CreateAndFillPrepare($2470890,'Name=?',(...),'')
uTestMormotDBBaseCommon.TMormotDBBaseCommonSelectTest.TestSelectSingleRecordByString

[Edit]
Extra info: If i add another parameter to the CreateAndFillPrepare, it works

  Rec := TSQLUnitTestSampleRecord.CreateAndFillPrepare(Client,'Name=?', [], [lname], '');

I see now that two different const arrays should be supplied to substitute the % and the ? parameters separately .. mmmmm :-(
IMHO its a very confusing construction

Last edited by Bascy (2012-08-30 10:08:04)

ab · 2012-08-30 14:42:07

The first construction expect % tokens and a list of parameters.
The 2nd construction expect % tokens and ? tokens, and two list of parameters.

The % tokens are substituted WITHIN the SQL statement: it is to be used e.g. for a field name or a fixed value.
The ? tokens are bound parameters.

The docs may be confusing, but it is how it works.

Bascy · 2012-08-30 15:10:04

Ok, fog is clearing up a bit now
What i also would expect is a CreateFillAndPrepare that will only substitute '?' with bound parameters

ab · 2012-08-30 15:48:56

Take a look at the code, the two list of parameters, with % and ? mixed tokens, are used often in the mORMot units.

Could sound confusing, but just a matter of taste.
I found it very convenient to have the ability to use % within the SQL statement.

Edit:
On 2nd thoughts, it sounds that the first overloaded version (with one array of const for % parameters) was as such since the beginning.
Then we added two array of consts, and % + ? tokens.

You are right, it could make sense to have the first version be changed from % to ? kind of parameters.
It would be a breaking change, but may sound less confusing to new users.
The 2nd version with both % and ? would stay available.

I'll see what I can do.
Thanks for the feedback.

Bascy · 2012-08-31 06:20:28

Hi Ab, thanks for your replies!
I really appreciate the fact that you are always open to suggestions from users, not afraid to acknowledge that somethings things could be made clearer or more convenient from a user point of view. Makes for a very serious user-participation feeling

Thanks

ab · 2012-08-31 14:50:42

Following your remark and feedback, I've made a huge code refactoring, and refreshed some methods syntax.

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

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. in SynDB.pas unit.

Both TSQLRestClient.EngineExecuteFmt / ListFmt methods are not affected by this change, since they are just wrappers to the FormatUTF8() function.

See http://synopse.info/fossil/info/82b9e305dd

Documentation has been updated, and I have made a blog article about this modification.

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 thanks for your feedback.

mORMot Open Source

#1 2012-08-30 09:48:41

Using '?' parameters in CreateAndFillPrepare() going wrong

#2 2012-08-30 14:42:07

Re: Using '?' parameters in CreateAndFillPrepare() going wrong

#3 2012-08-30 15:10:04

Re: Using '?' parameters in CreateAndFillPrepare() going wrong

#4 2012-08-30 15:48:56

Re: Using '?' parameters in CreateAndFillPrepare() going wrong

#5 2012-08-31 06:20:28

Re: Using '?' parameters in CreateAndFillPrepare() going wrong

#6 2012-08-31 14:50:42

Re: Using '?' parameters in CreateAndFillPrepare() going wrong

Board footer