INSERT with RETURNING

[0bscur3]

Firebird 2.X supports a query like:
INSERT INTO TABLE (FIELD2, FIELD3) VALUES (1,2) RETURNING FIELD0;

Thats a nice way to return an auto-generated field. How can i get that value using ZEOS?
If I do a ExecSQL, it doesn't return any field. If I try with Open() it give me the error "Invalid cursor reference Cursor is not open. Error Code: -504. Unknown cursor."

Does ZEOS support that?

[0bscur3]

(sorry for my bad english)

I have on machine with windows XP and Firebird 2.0 and one with windows vista and firebird 2.5 beta 2 - both with ZEOS 6.6.5-stable. I compiled a simple example similiar to:

Code: Select all

zquery.sql.add('INSERT INTO TPRODUTO (NMPRODUTO) VALUES ('TESTE') RETURNING ID');
zquery.Open();
ShowMessage(zquery.Fields[0].AsString);

And guess what, it worked fine on fb 2.0, but not on fb2.5 ... I even tried to use the fb 2.0 fbClient.dll on fb 2.5 machine, but doesn't work... ibExpert works fine, always give me the RETURNING value... but now I do not know if this is something on ZEOS, on FB or even on windows

And this was a compiled sample, so the version, dcus, etc from ZEOS don't interfere. I'm developing a framework and the idea was use ZEOS to do the DB-related "things"... but without this feature will be harder... can someone please help?

[0bscur3]

A little more information on this... I tried the same case with dbExpress and IBDAC from DevArt... with dbExpress I can get the "returning" value using

Active := true;
Fields[0].Asinteger; <- here is my returning field

BUT, as occurred with ZEOS, that do not work on FB2.5, only on 2.0.
One more detail, with ZEOS when this works, since i do not called EXEcSQL but Open(), if I'm using autocommit I have to manage the transaction myself since Open doesn't call commit for me (thas obvious but shows that use "returning" that way its a hack, it is not meant to work that way)

With IBDAC it worked flawlessly on both versions, the way someone would expect it to work:
I call ExecSQL on my query and get the RETURNING value from
qry.Params[0] <- this is an 'out' parameter.

OK, I could be using fb 2.0 and ZEOS would work (with the hacky "Open() and commit transaction" method)... but i'm afraid that with 2.5 all that stops to work and my framework would be broken
Can someone help me, at least with a patch since i do not have the "low-level" knowledge to implement this? I am willing to pay for that, I really need it

[seawolf]

Today I'm really busy, but tomorrow I give it a try

[seawolf]

I do some tests and this

zquery.sql.add('INSERT INTO TPRODUTO (NMPRODUTO) VALUES ('TESTE') RETURNING ID');
zquery.Open();
ShowMessage(zquery.Fields[0].AsString);

works well .. I mean until now Zeos works by this way

use ExecSQL if you use and INSERT or UPDATE
use Open if you use a SELECT

ExecSQL does not return any ResultSet, so using and INSERT + RETURNING
you will not receive any data.

Now I downloaded FB 2.5 and I do some tests

[mdaems]

Seems like the FB behaviour changed from returning a resultset with one row after 'insert...returning' to just setting some session variable.
Returning a resultset was handled correctly by 'open', but just setting a session variable isn't the same.
Can you do a insert returning using execsql and open a 'select id' query afterwards?

Mark

[0bscur3]

Seawolf, yes, with fb 2.0, that worked fine... it has the minor inconvenient of "Open()" do not trigger the autocommit but it makes sense, after all its just an "open"

Mark, thats the problem... maybe its because FB 2.5 is beta and thats just a bug, not a feature... but I can't afford to bet on the "open" workaround if that would stop working on future fb versions...
Probably that would never happen but i have some databases on wich multiple users are inserting records like crazy - maybe i didnt understand what you suggested but a separate select command after the insert would possibly return a wrong ID if multiple users are executing inserts, woudn't?

The best solution that i found for that is I control the ID field myself, geting the generator value before(and that increment the generator, so the next user gets a new value) and using it on my insert clause. It puts a little overhead on the process and ignores a nice fb feature (the "returning") that i would love to see ZEOS implementing - seriously guys, before ZEOS i was on dbExpress hell (with interbase drivers), and it was a terrible place

That "get the ID that you just inserted" thing its fundamental on the process i'm working and it cant go wrong... RETURNING looks like the best and most efficient way of doing this. It would be a nice feature on ZEOS 7 and I would be happy to test it

leo

[mdaems]

maybe i didnt understand what you suggested but a separate select command after the insert would possibly return a wrong ID if multiple users are executing inserts, woudn't?

Yes, indeed, that would if you can only do a select against the table you inserted in. I might have guessed wrong what the 'returning ID' is actually doing. If it only creates a resultset as before and nothing else I suppose it's a FB bug. But I was thinking it might also set some variable in your session that you can select from.

Let's have a look at some FB API docs.
-> didn't really find them for FB. Somebody knows where to find them?
Some googling -> http://ibexpert.info/ibe/index.php?n=Do ... onLanguage

In DSQL, values are being returned within the same protocol roundtrip as the INSERT itself is executed.
If the RETURNING clause is present, then the statement is described as isc_info_sql_stmt_exec_procedure by the API (instead of isc_info_sql_stmt_insert), so the existing connectivity drivers should support this feature automatically.

So it seems like the old behaviour was intentional and changed somehow in fb2.5. If this was intentional we can only ask the FB people.

Mark

[seawolf]

Today I installed fb 2.5 and, running a tests, it gives me the same error you watched .. tomorrow i will do a deep check.

Library knows you statement is a "normal" insert or an insert + returing, so problem is not there .. it looks like problem is related to the fetch, but i'm not so sure

[mdaems]

seawolf,

Did you find some time to check this?

Mark

[guidoaerts]

Hi, with lazarus 0.9.26.2 FPC 2.2.2 (svn18980) i386-win32 and zeos 6.6.5-stable and firebird 2.1.2 (windows build):
this works :

in unit ZAbstractRODataset line 1426:

FRowsAffected := Statement.ExecuteUpdatePrepared;
//returns an integer

replace with:

if Statement.ExecutePrepared then
//returns true if there is a valid resultset
FResultset := Statement.GetResultSet;
FRowsAffected := Statement.GetUpdateCount;



in my unit :

ZQuery.Close;
ZQuery.Sql.Clear;
ZQuery.Sql.Add('insert into personen (naam, voornaam) values (:naamparam, :voornaamparam) returning idnummer');
ZQuery.ParamByName('naamparam').Value:= PrivenaamEdit.Text;
ZQuery.ParamByName('voornaamparam').Value:= PrivevoornaamEdit.Text;
ZQuery.ExecSQL;

ZQuery.DbcResultSet.First;
myNewId := ZQuery.DbcResultSet.GetLong(1);

I don't know if this is ok to do in unit ZAbstractRODataset, but at least it works for me... But maybe this was already fixed in a later version of Zeos?

Guido

[mdaems]

I'm not sure either, but when this doesn't create a memory leak with FResultset it seems acceptable.

However, I did run the test suite on Zeoslib 7 before and after the change and I got a weird issue when Firebird is used:
Message: TZGenericTestDbcResultSet.TestQueryLocate: SQL Error: Dynamic SQL Error SQL error code = -501 Attempt to reclose a closed cursor. Error Code: -501. The cursor identified in a FETCH or CLOSE statement is not open.

When doing the patch to zeoslib 6.6 I also get a new error popping up, but there the text is different:

1) TestStoredProc: ETestFailure
at
"firebird20/firebirdd-2.0: expected: <12346> but was: <0>"

Have you ever tried to run the test suite?

Mark

[guidoaerts]

You are probably right about the memory leak, FResultset isn't freed nor nilled in TZAbstractRODataset, but maybe that might be solved in the destructor with an if<>nil.

On the other hand, in ZDbcInterbase6ResultSet.pas, line 139
[quote]
{ TZInterbase6ResultSet }

{**
Releases this <code>ResultSet</code> object's database and
JDBC resources immediately instead of waiting for
this to happen when it is automatically closed.

<P><B>Note:</B> A <code>ResultSet</code> object
is automatically closed by the
<code>Statement</code> object that generated it when
that <code>Statement</code> object is closed,
re-executed, or is used to retrieve the next result from a
sequence of multiple results. A <code>ResultSet</code> object
is also automatically closed when it is garbage collected.
}
procedure TZInterbase6ResultSet.Close;
begin
if FStmtHandle <> nil then
begin
{ Free output allocated memory }
FSqlData := nil;
FParamsSqlData := nil;
{ Free allocate sql statement }
FreeStatement(FIBConnection.GetPlainDriver, FStmtHandle);
end;
inherited Close;
end;
[end quote]
Does this mean we don't have to worry about memory?

I had to put in the 'dbcResultSet.First' line, to be able to get the value. I suppose this relates to the cursor error?


I don't know about the test suite. Where can I find the how-to?
Guido

[guidoaerts]

Mark,
concerning the test suite : I found the buildingtests.pdf in the doc subdir of zeos665. I have no delphi license (only delphi 2), so I suppose I cannot set up the test suite. Unless there is a port to Lazarus?
Guido

[seawolf]

Hi, I don't understand why you need to do this change .. I mean Did you encounter the same problem with Lazarus and FB 2.1.2?

Because using Delphi the problem is raised only using FB 2.5 RC1 and RC2

I wrote A message to the FB dev team but they told me is a Zeos problem