INSERT with RETURNING

[guidoaerts]

seawolf,
with lazarus and fb 2.1.2 and zeos 6.6.5 I couldn't open a resultset without the change, so insert with returning did not work, just because the insert statement is executed with
FRowsAffected := Statement.ExecuteUpdatePrepared;
which does not fetch a resultset.
ZQuery.Open does not work either with INSERT.
(the INSERT...RETURNING clause was introduced in FB 2.0.5, in FB 2.1 the RETURNING clause was extended to INSERT, INSERT OR UPDATE, UPDATE and DELETE clauses. the main advantage is that the returned values are not vulnerable to concurrency)
http://www.firebirdsql.org/rlsnotesh/rl ... b210-rtrng
guido

[capt_kirk]

Hello there! This is my first post here. I am a Zeos user since 2004. A very good and solid project. Congratulations people!

Now the bad news...

I'm using Lazarus 0.9.28.2 + Zeos 6.6.5-stable + FireBird 2.1.
I have tried to use the new FB insert/update..returning feature. The query executed in the SQL Manager tool works just fine, however, when it is executed using the ZQuery Component, I've got this:

"SQL Error: request synchronization error. Error code: -901. Unsuccessful execution caused by system error that does not preclude successful execution of subsequent statments."

So, what is happening? An update to Zeos 6.6.6 would fix the problem?

Here is my code:

Code: Select all

  DM1.QryAgendas.Close;
  DM1.QryAgendas.SQL.Clear;
  DM1.QryAgendas.SQL.Add('update agendas set agenda_sequencia = (agenda_sequencia + 1) where senha_id = ' + inttostr(senha_id) + ' and agenda_data = CURRENT_DATE RETURNING agenda_id, agenda_sequencia');
  DM1.QryAgendas.Open;

I thanks for any assistance that you may provide.

[guidoaerts]

capt_kirk,
The returning clause is not supported by zeos. However, you can make the change I proposed higher in this thread and use the command ExecSQL, in stead of Open.

Guido

[capt_kirk]

Hey Guido!

Thanks for the advice. But, how can I return more than one field? Your sample shows only one. Can I return more than one field in the RETURNING clause?
An what is this memory leak that you were talking about? Does it may cause the application crashes?

Your code: "myNewId := ZQuery.DbcResultSet.GetLong(1);" Should I just exchange the (1) for (2)?

That's bad! :-( No support for RETURNING's? Is this going to be fixed in future versions of Zeos?

Thanks again.

[guidoaerts]

capt_kirk,
Your sql is ok, it will return a row with two values. Normally, when you use 'open' zeos will fetch the resultset and define the fieldtypes. Now you will have to do it yourself. But you know what you asked for, isn't it? Use the appropriate GetFunction (GetInt, GetString, Getlong...) with the index of the value in the returned row. (in your case agenda_id will be (1), agenda_sequencia will be (2))
Afaik, the resultset needs not be freed, so the memory leak isn't there. But I didn't test. If there is one, it could cause the app to hang after an amount of cycles.
Up until now, there seems to be no plans to incorporate it in future versions, because it conflicts with the idea that only SELECT statements return resultsets. INSERT and UPDATE are not supposed to return anything .
Guido

[marcov]

Note that 2.2.2 is nearly 2 years old (in august). If you suspect a problem in FPC, better test with a recent version first, before spending too much time on it.

[andrevanzuydam]

I'm on the way to solving this problem as it is something that I need to have pretty urgently. There is a new constant available to free statements and keep the cursors open - DSQL_UNPREPARE which I have been playing with on the Free Statement procedures.

On one of my projects a blank SQL gets generated for some or other reason and I have built in the necessary checking so that a statement is not prepared unless there is actual SQL.

I cannot see why the change in the describing of the type of statement is messing up, my thoughts are leaning towards something straight forward like a statement handle which is out (I have checked this though and it seems correct).

Anyway I'll be keeping a close eye on this forum and will post my code as soon as I get a break through on the debugging.

[andrevanzuydam]

Some feedback on this problem, Apparently the Result set is passed back in this instance with the call to isc_dsql_execute2 which allows for two params - currently in the code base the second parameter is not being used and is set to null.

The current code in the engine calls the execute which opens and closes a cursor and passes back a result. Zeos then tries to fetch the record again, which is generally how Firebird works with queries but not anymore for stored procedures or the returning clause on the insert or update statements.

The solution I am looking into is as follows:

If the SQL has returning in it or is stored proc do not allow Zeos to fire the traditional next row but build a result set from the param returned on execute2 statement. This will ensure that the cursor error is not fired.

Some thoughts on the above would be appreciated.

[mdaems]

guidoaerts,

There is a port. Just compile the ZTestAll lazarus Project.

Mark

[guidoaerts]

Mark,
It seems to be not that easy. I set up lazarus on another xp pc, installed zeos 6.6.6 and tried to run th ZTestAll project. Failed because it needs presence of all the testunits :

ZTestSysUtils, ZTestVariant, ZTestTokenizer,
ZTestList, ZTestFramework, ZTestExprToken, ZTestExpression,
//parsesql
ZTestSybaseToken, ZTestSqLiteToken,
ZTestSqlAnalyser, ZTestScriptParser, ZTestPostgreSqlToken, ZTestOracleToken,
ZTestMySqlToken, ZTestInterbaseToken,
//dbc
ZTestDbcResultSet, ZTestDbcUtils, ZTestDbcCache,
ZTestDbcCachedResultSet, ZTestDbcMetadata,ZTestDbcResultSetMetadata, ZTestDbcResolver,
ZTestDbcSqLite, ZTestDbcPostgreSqlMetadata, ZTestDbcPostgreSql, ZTestDbcOracle,
ZTestDbcMySqlMetadata, ZTestDbcMySql, ZTestDbcMsSql, ZTestDbcInterbaseMetadata,
ZTestDbcInterbase, ZTestDbcASA, ZTestDbcASAMetadata,
//component
ZTestSqlTypes, ZTestSqlStrings, ZTestSqlProcessor,
ZTestSqlMetadata, ZTestSorting, ZTestMasterDetail, ZTestExecuteSql,
ZTestDataSetGeneric, ZTestData, ZTestConnection,
//bugreport
ZTestBugDbcCore,
ZTestBugDbcMySql, ZTestBugCompMySql,
ZTestBugDbcPostgreSql, //ZTestBugCompPostgreSql,
//ZTestBugDbcInterbase, //ZTestBugCompInterbase,
ZTestBugDbcDbLib, ZTestBugCompDbLib,
ZTestBugCompMSSql
;
where can i find this stuff? Or do I still have to set up a test suite as for delphi?
guido

[mdaems]

The ZTest* units are in the zeoslib/test directories. For Delphi you need to install the test suite packages.
For Lazarus I hoped all files were in the project file so it don't need the right pads to be set

Mark.

[guidoaerts]

Mark, I really want to contribute. But there is no /test directory. I feel like annoying. Maybe we should first of all set up a clear HOW-TO for newbies like me to get into the testing. Once you got me that far, I am willing to write that how-to. i.e. : windows, lazarus-fpc, firebird. (I could, for this, extend to other free rdbm's).
I have upgraded to 6.6.6-stable.
Maybe this is off-topic. Can we start a separate thread about this?
Guido

[mdaems]

Yes we can. See http://zeos.firmos.at/viewtopic.php?t=2951

[andrevanzuydam]

I have fixed this problem, where can I post the code ?

[andrevanzuydam]

Hi Everyone

I've just got it together to release the patch for the INSERT ... RETURNING problem on Firebird. You'll have to get it from the SVN repository and give it a spin. Please post up your problems if you get any.

Perhaps a new forum for the testing of this may be in order.