BindArray* for select statement

mpv · 2014-02-18 14:37:15

I try to rewrite my query

Query := TSQLDBOracleConnectionProperties.NewThreadSafeStatementPrepared('select * from table where table.id in (1, 2, 3)');
Query.ExecutePrepared; // return 3 records

using parameters

var 
  arr: TInt64DynArray = [1, 2, 3];
Query := TSQLDBOracleConnectionProperties.NewThreadSafeStatementPrepared('select * from table where table.id in ?');
Query.BindArray(1, arr);
Query.ExecutePrepared;

But while using array parameters I got only one record in a result with id = 1;
I look to sources and seems such use of BindArray is not supported now. Is it possible to support such use of the BindArray method?

ab · 2014-02-18 15:00:30

BindArray() method is to bind not one array argument, but an array of arguments.
That is, to run the same statement several times with an array of arguments.

Its main use is about bulk/batch insert/update/delete statements.

But AFAIK you can use it with a temporary table, and a "select ... where .. in (select .. from temptable)":

bulk insert the ids in a temptable
select count(*) from all_objects where object_id in (select id from temptable);

I noted your request about adding BindArray*() support for ODBC.
Worth a feature request in http://synopse.info/fossil/tktnew
It won't be feasible with Zeos (no array binding possible with ZDBC), but it is already working with FireDac.
FireDAC is faster at insert when array DML binding is used - but FireDAC is slower than our own direct access layers, and even slower than Zeos/ZDBC, when retrieving one or a few rows of data (due to the TDataSet overhead).

mpv · 2014-09-15 11:59:32

AB, after long fight with Oracle we implement array binding for select statements via OCI_OBJECT support adding to SynDBOracle. So now we able to do:

var 
  arr: TInt64DynArray = [1, 2, 3];
Query := TSQLDBOracleConnectionProperties.NewThreadSafeStatementPrepared('select * from table where table.id in (select column_value from table(cast( ? as SYS.ODCINUMBERLIST)))');
Query.BindArray(1, arr);
Query.ExecutePrepared;

RawUTF8 array also supported (we use it to fallback in case Oracle10 since it not support Int64 arrays)

var 
  arr: TRawUTF8DynArray = ['123123423452345', '3124234454351324', '53567568578867867'];
Query := TSQLDBOracleConnectionProperties.NewThreadSafeStatementPrepared('select * from table where table.id in (select column_value from table(cast( ? as SYS.ODCIVARCHAR2LIST)))');
Query.BindArray(1, arr);
Query.ExecutePrepared;

From our tests this implementation is 2-100 times faster (depend of array & table size) and muсh simple, compared to temp table solution.
We merge implementation to current SynDBOracle version - I send merged version to you e-mail.
Please, add it to main repo...

Now we work on the same functionality for MSSQL & PostgreSQL and I send result to you after we finish.

ab · 2014-09-15 12:35:44

Great news!

ab · 2014-09-15 13:20:30

I just committed your patch.
See http://synopse.info/fossil/info/a040c1e2aa

Thanks a lot for sharing!
This is indeed a welcome performance enhancements in some rather common case.

mpv · 2014-09-15 14:51:30

After investigation we decide to add BindArray support to SynOleDB - almost everything is ready there. MS & Postgre OleDB drivers support arrays.

ab · 2014-09-15 15:57:00

You mean array of values, as needed to bind for an IN statement, not arrays as in batch/bulk mode?

mpv · 2014-09-16 06:43:17

I mean both - for SELECT statement & for bulk insert.

ab · 2014-09-16 07:35:26

So exciting!

mpv · 2015-10-26 17:33:34

We merge our implementation of array binding (for select IN [] clause ) for OleDb - see [81bc3f0dc5]
We have the near-the-same for SynDBZEOS, but need to merge from mORMot 1.18.282 to 1.18.2017

EMartin · 2015-10-26 17:56:56

@mpv, thanks for the modifications !!!

can you use stored procedures with SynOleDB ? or only CRUD statements ?

Thanks.

Esteban.

mpv · 2015-10-26 21:07:56

I try to avoid using stored procedures, but it is certainly possible with SynOleDB

CREATE FUNCTION dbo.test_table_func ( @iCode varchar)
RETURNS TABLE
AS
return select ID, code from my_table where code = @iCode

var 
  Query: ISQLDBStatement;
Query := Connection.NewThreadSafeStatementPrepared('select ID, code from dbo.test_table_func(?)', true, true);
Query.BindTextU(1, 'code1');
Query.ExecutePrepared

mpv · 2015-10-27 18:49:03

We just commit [a4c1803024] a SynDBZeos PostgreSQL array binding for select statements, like

     
SELECT usr.ID, usr.name FROM user usr WHERE usr.ID = ANY(?)

The main idea inside implementation is to transform Delphi array to string, representing PostgreSQL ARRAY and then bind parameter as string.

Usage sample:

Query := Connection.NewThreadSafeStatementPrepared('SELECT usr.ID, usr.name FROM user usr WHERE usr.ID = ANY(?)', true, true);
Query.Query.BindArray(1, [1, 2, 3]);
Query.ExecutePrepared;

Work with Int64/RawUTF8 array and any version of ZEOS

Last edited by mpv (2015-10-27 18:51:03)

EMartin · 2015-10-28 11:08:58

Thanks @mpv works for MS SQL Server. I'll try to implement cursors in SynOleDb because the integrations with customers are with stored procedure and in some cases returning data with cursors, this is not easy for my I have not knowledge Ole Db API.

On the other hand you or @ab they had problems with Oracle stored procedures returning output parameter VARCHAR2 ? I tried with no luck, exception in ParamToVariant is raised, SynDBOracle works fine but SynOleDB not.

Thanks again.

Esteban.

mpv · 2015-10-28 11:36:20

I strongly recommend use SynDBOracle for communication with Oracle. OleDb Oracle provider always buggy for lats 15 years..

EMartin · 2015-10-28 13:36:22

I know, I'll use SynDBOracle but for the other engines I need SynOleDB or SynDBODBC but any have support for cursors.

mpv · 2015-10-28 13:47:30

For what kind of database? Please give an example of the procedure to be executd

EMartin · 2015-10-28 16:36:01

One example: a stored procedure returning a dataset using Informix, a big customer uses Oracle and Informix, another use AS400, another MySQL and so on.

I did make a small modification to SynOleDb for compatibility with other engines that do not support ISSCommandWithParametersSupport:

procedure TOleDBStatement.ExecutePrepared;
var i: integer;
    ...
    ISSCommandWithParametersSupport: ISSCommandWithParameters;
    ICommandWithParametersSupport: ICommandWithParameters;
begin
  ...
        fCommand.QueryInterface(ISSCommandWithParameters,ISSCommandWithParametersSupport);
        if Assigned(ISSCommandWithParametersSupport) then
        begin
          OleDBConnection.OleDBCheck(self,
            ISSCommandWithParametersSupport.SetParameterInfo(
              fParamCount, pointer(fParamOrdinals), pointer(fParamBindInfo)));

          if ssParamPropsCount>0 then
            OleDBConnection.OleDBCheck(self,
              ISSCommandWithParametersSupport.SetParameterProperties(
                ssParamPropsCount, pointer(ssParamProps)));
        end
        else
        begin
          fCommand.QueryInterface(ICommandWithParameters,ICommandWithParametersSupport);
          if Assigned(ICommandWithParametersSupport) then
            OleDBConnection.OleDBCheck(self,
              ICommandWithParametersSupport.SetParameterInfo(
                fParamCount, pointer(fParamOrdinals), pointer(fParamBindInfo)));
        end;
        
        SetLength(ParamsStatus,fParamCount);
        OleDBConnection.OleDBCheck(self,
          (fCommand as IAccessor).CreateAccessor(
            DBACCESSOR_PARAMETERDATA,fParamCount,Pointer(fParamBindings),0,
            fDBParams.HACCESSOR,pointer(ParamsStatus)),ParamsStatus);        
  ...
end;

Maybe you want check this.

Best regards.

Esteban.

ab · 2015-11-02 10:23:18

@mpv

I've fixed and integrated your patches.

It was breaking OleDB outside MSSQL (e.g. with Jet/MSAccess).
See http://synopse.info/fossil/info/bb7e33b0aa

mORMot Open Source

#1 2014-02-18 14:37:15

BindArray* for select statement

#2 2014-02-18 15:00:30

Re: BindArray* for select statement

#3 2014-09-15 11:59:32

Re: BindArray* for select statement

#4 2014-09-15 12:35:44

Re: BindArray* for select statement

#5 2014-09-15 13:20:30

Re: BindArray* for select statement

#6 2014-09-15 14:51:30

Re: BindArray* for select statement

#7 2014-09-15 15:57:00

Re: BindArray* for select statement

#8 2014-09-16 06:43:17

Re: BindArray* for select statement

#9 2014-09-16 07:35:26

Re: BindArray* for select statement

#10 2015-10-26 17:33:34

Re: BindArray* for select statement

#11 2015-10-26 17:56:56

Re: BindArray* for select statement

#12 2015-10-26 21:07:56

Re: BindArray* for select statement

#13 2015-10-27 18:49:03

Re: BindArray* for select statement

#14 2015-10-28 11:08:58

Re: BindArray* for select statement

#15 2015-10-28 11:36:20

Re: BindArray* for select statement

#16 2015-10-28 13:36:22

Re: BindArray* for select statement

#17 2015-10-28 13:47:30

Re: BindArray* for select statement

#18 2015-10-28 16:36:01

Re: BindArray* for select statement

#19 2015-11-02 10:23:18

Re: BindArray* for select statement

Board footer