mORMot Open Source

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

AddArrayOfConst + Values by reference

I use AddArrayOfConst to create the parameters.

When I add values of type double (returned by a method), the last value always prevails.

I assume it's being added by reference ...

Is there any way to avoid this?

PS: I am posting this session because the database-related methods also use FormatUTF8 internally, and the same occurs.

Sample code (Delphi 7):

procedure TForm8.Button1Click(Sender: TObject);
var
  AParams : TTVarRecDynArray;
  AWhere : RawUTF8;
begin
  AWhere := '';

  SQLAddWhereAnd(AWhere, 'ative = %');
  AddArrayOfConst(AParams, [Ord(True)]);

  SQLAddWhereAnd(AWhere, 'inative = %');
  AddArrayOfConst(AParams, [Ord(False)]);

  SQLAddWhereAnd(AWhere, 'date_ini >= %');
  AddArrayOfConst(AParams, [IncDay(Date(),-2)]);

  SQLAddWhereAnd(AWhere, 'date_end <= %');
  AddArrayOfConst(AParams, [Date]);

  Memo1.Lines.Add(FormatUTF8(AWhere,AParams));

  //Produces: ative = 1 and inative = 0 and date_ini >= 43168 and date_end <= 43168
end;

Using doubles directly does not occur.

procedure TForm8.Button1Click(Sender: TObject);
var
  AParams : TTVarRecDynArray;
  AWhere : RawUTF8;
begin
  AWhere := '';

  SQLAddWhereAnd(AWhere, 'ative = %');
  AddArrayOfConst(AParams, [Ord(True)]);

  SQLAddWhereAnd(AWhere, 'inative = %');
  AddArrayOfConst(AParams, [Ord(False)]);

  SQLAddWhereAnd(AWhere, 'my_float1 = %');
  AddArrayOfConst(AParams, [4.99]);

  SQLAddWhereAnd(AWhere, 'my_float2 = %');
  AddArrayOfConst(AParams, [5.99]);

  Memo1.Lines.Add(FormatUTF8(AWhere,AParams));

  //Produces: ative = 1 and inative = 0 and my_float1 = 4.99 and my_float2 = 5.99

end;

Last edited by macfly (2018-03-09 18:23:26)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,236

Website

Re: AddArrayOfConst + Values by reference

If it is by reference, make a single copy in a local variable.

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: AddArrayOfConst + Values by reference

First... thanks for reply.

Sorry, I did not report this in the post.
My question would be if there is a way to add the value without creating - one local variable for each param -, because in some cases there are many parameters, and pass directly leaves the code cleaner.

But I imagine it's not possible ...

  AWhere := '';

  AMyValue := 4.99;
  SQLAddWhereAnd(AWhere, 'my_value1 = %');
  AddArrayOfConst(AParams, [AMyValue]);

  AMyValue := 5.99;
  SQLAddWhereAnd(AWhere, 'my_value2 = %');
  AddArrayOfConst(AParams, [AMyValue]);

  AMyValue := 6.99;
  SQLAddWhereAnd(AWhere, 'my_value3 = %');
  AddArrayOfConst(AParams, [AMyValue]);

//produces:  my_value1 = 6.99 and my_value2 = 6.99 and my_value3 = 6.99

One var for each param works:

  AWhere := '';

  AMyValue1 := 4.99;
  SQLAddWhereAnd(AWhere, 'my_value1 = %');
  AddArrayOfConst(AParams, [AMyValue1]);

  AMyValue2 := 5.99;
  SQLAddWhereAnd(AWhere, 'my_value2 = %');
  AddArrayOfConst(AParams, [AMyValue2]);

  AMyValue3 := 6.99;
  SQLAddWhereAnd(AWhere, 'my_value3 = %');
  AddArrayOfConst(AParams, [AMyValue3]);

//produces:  my_value1 = 4.99 and my_value2 = 5.99 and my_value3 = 6.99

Last edited by macfly (2018-03-09 19:21:32)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,236

Website

Re: AddArrayOfConst + Values by reference

This is how the compiler works...
TTVarRec contains references to the supplied parameters.
There is no way to change this behavior.

Also note that your where request will NOT be efficient, since parameters won't be prepared.
It will generate a SQL query like "SELECT * FROM TABLE WHERE actice=1 and inactive=0..." so with no prepared statement.
This is unsafe (SQL injection) and slow.
You should use ? instead.

But in your case, using TTVarRec and AddArrayOfConst is overcomplicated, and not efficient.
Just define your WHERE clause as plain RawUTF8 text, adding values by using the :(....): inlining syntax.
See https://synopse.info/files/html/Synopse … ml#TITL_61
This is how it works behind the scene, and how TVarRec are in fact transmitted to the DB engine.
You can easily add inlined :(...): parameters in some text using the overloaded FormatUTF8() function, which handle ? as expected.

Something like this:

var where: RawUTF8;
begin
  where := FormatUTF8('active=?', [], [ord(true)]);
  where := FormatUTF8('% and active=?', [where], [ord(true)]);
  where := FormatUTF8('% and date_ini>=?', [where], [DateToSQL(IncDay(Date(),-2))]);
 ...

I've updated the documentation to make it clearer.

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: AddArrayOfConst + Values by reference

I understood about the compiler and thank you for the explanation.

I used '%' only in this examples.

For real queryes i use '?' and do not use formatUT8 directly.

The parameters are passed to the Retrieve* methods.

  AWhere := '';

  AMyValue := 4.99;
  SQLAddWhereAnd(AWhere, 'my_value1 = ?');
  AddArrayOfConst(AParams, [AMyValue]);

  AMyValue := 5.99;
  SQLAddWhereAnd(AWhere, 'my_value2 = ?');
  AddArrayOfConst(AParams, [AMyValue]);

  AMyValue := 6.99;
  SQLAddWhereAnd(AWhere, 'my_value3 = ?');
  AddArrayOfConst(AParams, [AMyValue]);

  ARecord.RetrieveList(TSQLBaby, AWhere,[AParams]); 

 //Query produced:  my_value1 = :(6.99): and my_value2 = :(6.99): and my_value3 = :(6.99):

My concern is not to pass the query already formatted with the parameters, it is because I imagine ORM can handle formatting differently according to the database.
And a change in the database could break the query.

However as the ORM also uses FormatUTF8 internally, it seems that this is an unnecessary concern.

Thanks for the tips, really makes the code more secure and clear.

I'll try to use it this way

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,236

Website

Re: AddArrayOfConst + Values by reference

As you stated, the ORM uses plain UTF8 during the process, and inline of the parameters.
It ensures easy statement value cache, and efficient transmission as REST/remote request as JSON.
Then to manage the format according to the database, it un-serialize the "inlined" parameters into the proper SynDB values.

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: AddArrayOfConst + Values by reference

Thank you for the valuable information