SQLDB "INSERT ... RETURNING" Firebird 2.5.x
lazarus / 16-Databases

----------------------------------------------------------------------
patyi · 2011-12-01 14:38

Hi all !

I upgrade my Firebird database from 2.1 to 2.5.1 and I cant get
working anymore INSERT ... RETURNING statements.
Gogling a bit and find that the Firebird client protocol is changed :
for isc_info_sql_stmt_exec_procedure in case of RETURNING clause,
instead of isc_dsql_fetch must call isc_dsql_execute2 api command.
I try to find in SQLDB source a place where can modify the code, and
find procedure Fetch witch include isc_dsql_fetch api call, but not
shore is the right place to
make changes ...

Pleas, if someone familiar with this code help to solve this !
I currently use ZeosDB 7.0 it's support and work with RETURNING, but
in some new projects I like to use SQLDB  to bee more generic in
database level with Lazarus.

Thanks. (sorry for my english)

----------------------------------------------------------------------
patyi · 2011-12-01 14:49

In SQLDB source IBConnection.pp :

Code sequence from PrepareStatement procedure :

    // If the statementtype is isc_info_sql_stmt_exec_procedure then
    // override the statement type derrived by parsing the query.
    // This to recognize statements like 'insert into .. returning'
correctly
    if IBStatementType = isc_info_sql_stmt_exec_procedure then
       FStatementType := stExecProcedure;

The Fetch procedure :

function TIBConnection.Fetch(cursor : TSQLCursor) : boolean;
var
  retcode : integer;
begin
  with cursor as TIBCursor do
    begin
    if FStatementType = stExecProcedure then                         
               // added by patyi
      retcode := isc_dsql_execute2(@Status[0], @Statement, 1,
SQLDA);// added by patyi
    else                                                             
                                                    // added by patyi
      retcode := isc_dsql_fetch(@Status[0], @Statement, 1, SQLDA);   
 // this is the original code
    if (retcode <> 0) and (retcode <> 100) then
      CheckError('Fetch', Status);
    end;
  Result := (retcode <> 100);
end;

----------------------------------------------------------------------
BigChimp · 2011-12-01 15:04

Patyi, nice find.

It would really help (if you have not already done so) to write a
simple FPC test program calling INSERT...RETURNING, create a bug in
the bug tracker and upload it to the bug tracker, indicating your fix
as well...

This will help the developers fix this issue...

Thanks!

----------------------------------------------------------------------
Lacak2 · 2011-12-02 07:28

I think this bug is already fixed see:
http://bugs.freepascal.org/view.php?id=16236#0052294
and:
http://svn.freepascal.org/cgi-bin/viewvc.cgi/trunk/packages/fcl-
db/src/sqldb/interbase/ibconnection.pp?r1=19239&r2=19303

----------------------------------------------------------------------
patyi · 2011-12-02 16:47

Hi all Lazarus good people !

Thanks Lacak2 ! You is my live saver ! 

Yes  this bug is fixed on 10/25/2011, Lacak2 is point me to the patch.
I'm apply the patch an can confirm that is work as expected !!! 

I love Lazarus - I love this game ...  Thanks again, Patyi.