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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

BindArray* for select statement

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: BindArray* for select statement

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 smile
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).

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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.

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: BindArray* for select statement

Great news!
smile

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: BindArray* for select statement

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.

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: BindArray* for select statement

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

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: BindArray* for select statement

So exciting!

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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

Offline

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

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: BindArray* for select statement

@mpv, thanks for the modifications !!!

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

Thanks.

Esteban.


Esteban

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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)

Offline

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

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: BindArray* for select statement

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.


Esteban

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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

Offline

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

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: BindArray* for select statement

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


Esteban

Offline

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

mpv
Member
From: Ukraine
Registered: 2012-03-24
Posts: 1,534
Website

Re: BindArray* for select statement

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

Offline

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

EMartin
Member
From: Buenos Aires - Argentina
Registered: 2013-01-09
Posts: 332

Re: BindArray* for select statement

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.


Esteban

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,182
Website

Re: BindArray* for select statement

@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

Offline

Board footer

Powered by FluxBB