mORMot Open Source

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

TSQLRecord and BindArray

Hello,

is it possible to bind an array in a TSQLRecord-class?

Or what is the best way to do the following task?

I have a class and I would like to select all cars which IDs I pass in a list.

  TCar = class;
  TSQLCar = class(TSQLRecord);

  function GetCars(const AIDList: TList<Integer>): TList<TCar>;
  begin
    // My question here is how can I pass in the best way the IDs from the list?
    SQLCar := SQLCar.CreateAndFillPrepare(ClientDB, 'ID = ?');
    ...
  end;

The most obvious solution would be to iterate the list, create a comma separated ID-string and do something like 'ID IN (1,2,3,4)'. Is it a good way to do it?

What about the strings which should be escaped? E.g. 'Name IN (...)'

How can I parameterize a TSQLRecord?

Thank you for your help!

Greets CC

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: TSQLRecord and BindArray

There is no standard way of parametrizing such a query in SQL.
So the ORM has no way of running a ID=? with an array.

What you can do is indeed create a ID IN (1,2,3,4) string.
You can use the Int64DynArrayToCSV() function to generate the IN() clause very efficiently.
For instance:

 Int64DynArrayToCSV([1,2,3,4],4,'ID in (',')')

cypriotcalm

Member

Registered: 2015-02-18

Posts: 122

Re: TSQLRecord and BindArray

Thank you for your quick answer! :-)