mORMot Open Source

peak.tibor

Member

Registered: 2013-02-22

Posts: 19

BindNull may have an issue when used with OCI

I'm experiencing problems when trying to pass null values to a stored procedure using the OCI interface: when the last param is NULL, all the params become NULL!

Here is my example:

I create a simple table:

CREATE TABLE nullch1 (
  FILL char(13 CHAR) NOT NULL
, INFO char(20 CHAR) NULL
, SZOVEG char(10 CHAR) NULL  )

Here is the stored procedure:

CREATE OR REPLACE PROCEDURE nullch1_Proc(FILL   in char default null,
                                         INFO   in char default null,
                                         SZOVEG in char default null) AS
BEGIN
  INSERT INTO nullch1 VALUES (FILL, INFO, SZOVEG);
END;

Here is the Statement I prepare, bind values then execute:

call nullch1_Proc(FILL=> ? ,INFO=> ? ,SZOVEG=> ? )

Here is how I use it:

   
    SQL_SP := 'call nullch1_Proc(FILL=> ? ,INFO=> ? ,SZOVEG=> ? )';
    Statement := TSQLDBOracleStatement(con.NewStatement);
    Statement.Prepare(SQL_SP,False);
    FILL_value := 'Demo1'; INFO_value := 'Demo2'; SZOVEG_value := 'Demo3';
    Statement.BindTextS(1,FILL_value);
    Statement.BindNull(2);
    Statement.BindTextS(3,SZOVEG_value);
    Statement.ExecutePrepared; // This will work

    Statement.BindTextS(1,FILL_value);
    Statement.BindTextS(2,INFO_value);
    Statement.BindNull(3);
    Statement.ExecutePrepared; // This will fail, because the stored procedure will get ALL the params as NULL values!

Do I make any mistake?
Or does really have the Mormot Library some bug?

As a workaround currently I always define an 'dummy' parameter for the stored parameter as the last one. This last parameter is not used for anything, but is there to avoid the above issue.

Thanks in advance.

Tibor

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,007

Website

Re: BindNull may have an issue when used with OCI

Perhaps a Statement.Reset is needed between the two ExecutePrepared() method calls:

    {/ Reset the previous prepared statement
     - some drivers expect an explicit reset before binding parameters and
       executing the statement another time
     - this default implementation will just do nothing }
    procedure Reset; virtual;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,007

Website

Re: BindNull may have an issue when used with OCI

I've created and fixed a new ticket http://synopse.info/fossil/tktview?name=5df9d39858 about NULL bindings in TSQLDBOracleStatement.

In fact, the same "indicator" flag was used for all parameters: this may explain why all parameters are NULLed in your case.

It has been fixed for both one parameter row binding and array binding modes.

peak.tibor

Member

Registered: 2013-02-22

Posts: 19

Re: BindNull may have an issue when used with OCI

Thanks a lot, this solved my issue.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 15,007

Website

Re: BindNull may have an issue when used with OCI

Thanks for the feedback.

Therefore, I've closed the ticket.