Zeoslib Portal

Hi everyone--

I'm using Delphi 2009 to compile an app that uses ZEOS to access a Firebird DB through the Firebird 2.1 embedded client. I'm dynamically building a ZQuery and ZUpdateObject to read and write to a table whose name and fields are unknown until the user sets a few things. So far, the creation of the table is working great, and I can insert data using a DevExpress grid control.

However, when I close the form that has the grid, which closes the ZQuery, I always get an exception that says "Attempt to reclose a closed cursor. Error Code: -501. The cursor identified in a FETCH or CLOSE statement is not open."

I traced the problem down into the FreeStatement() routine in ZDbcInterbase6Utils, which tests that the Firebird statement handle is not nil, and if it isn't, it calls isc_dsql_free_statement() to free the statement handle and checks the status vector returned. Unfortunately, it then forgets to set the incoming statement handle to nil! Once I saw that, I felt pretty confident what the fix would be. I patched the routine and tested it out.

Unfortunately, even after setting the statement handle to nil, I'm still getting the exception! It seems like there is some place in the code where a statement handle is created, so that it's not nil, but it's never "opened," and is then "closed." This causes a call to isc_dsql_free_statement that Firebird doesn't like. This spurious freeing is causing the exception.

Does anybody else see this behavior? Is there a simple bug somewhere earlier in the code, or is this a design problem?

Which rev. are you using? Before rev. 780 there were some known problem between Zeos and DevExpress.

Moreover which Firebird dll version are you using? I mean FB 2.1.0 give some problem, so I suggest 2.1.2

I'm using the 7.0.0 alpha package downloaded from SourceForge, dated 12-30-2009. I'm also using an up-to-date Firebird embedded client. Should I be using the latest CVS/SVN branch instead?

OK, I've caught up to the front of the SVN trunk, but the behavior hasn't changed. There's definitely some statement handle being allocated, but not opened, so that the act of closing before freeing it causes the error. I'll try my best to trace things, but I could sure use some help .

More info: There's something fishy going on when a ZQuery is deactivated, and TZCachedResultSet.Close() is called. I am just learning the architecture of ZEOS, so things are a bit confusing... I have ZQuery.CachedUpdates = false, so why is there even a TZCachedResultSet present in the first place? Is a TZCachedResultSet always part of a TZQuery, and can it operate in some sort of "pass-through" mode? In any event, it seems as though the problem is with the Interbase implementation of IZResultSet, since the bug occurs when FResultSet.Close is called in TZCachedResultSet.

To clarify further: Simply opening the form and activating the ZQuery, and then immediately deactivating the ZQuery and closing the form is no problem. Only if rows are inserted or deleted does closing the form cause the -501 error. That's a pretty good indication that there's something fishy with caching of the results.

Here's a log file from the SQL monitor, showing the user connecting to the database, activating a query that is associated with an MDI parent form, activating a query that is associated with a new MDI child form, inserting a record by means of a DevExpress grid control on the child form, then closing the child form and the parent form. You can see from the timestamps that I'm stepping through the code, watching the statements as they are prepared, and the result sets get returned by the FireBird database. The two close operations cause the -501 error to occur. By using the debugger, I see that the prepared statements being closed correspond to the original SELECT SQL from the two forms' TZQuerys. There is some sort of problem with the preparation, storage, and/or use of the prepared SELECT statement and cached result set for TZQuery -- but only if the TZUpdateSQL for the TZQuery is used.



2010-05-07 22:28:38 cat: Connect, proto: firebirdd-2.1, msg: CONNECT TO "D:\vic3d\3.0 for Win32\src\interface\vic3d.fdb" AS USER "ehs"
2010-05-07 22:28:40 cat: Transaction, proto: firebirdd-2.1, msg: TRANSACTION STARTED.
2010-05-07 22:28:41 cat: Prepare, proto: firebirdd-2.1, msg: Statement 1 : select * from Config;

2010-05-07 22:28:41 cat: Execute prepared, proto: firebirdd-2.1, msg: Statement 1
2010-05-07 22:28:41 cat: Execute, proto: firebirdd-2.1, msg: SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION, a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE as RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE FROM RDB$RELATION_FIELDS a JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE) LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE and c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE') WHERE a.RDB$RELATION_NAME = 'CONFIG' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION
2010-05-07 22:28:45 cat: Execute, proto: firebirdd-2.1, msg: SELECT DISTINCT a.RDB$RELATION_NAME, a.RDB$SYSTEM_FLAG, a.RDB$VIEW_SOURCE, a.RDB$DESCRIPTION FROM RDB$RELATIONS a
2010-05-07 22:28:48 cat: Prepare, proto: firebirdd-2.1, msg: Statement 2 : SELECT DATAPOINTHASH, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, P14, P15, P16, P17, P18, P19, P20, P21 FROM "jjy9z2digff65j5pxh487ktwxf"

2010-05-07 22:28:48 cat: Execute prepared, proto: firebirdd-2.1, msg: Statement 2
2010-05-07 22:28:49 cat: Execute, proto: firebirdd-2.1, msg: SELECT a.RDB$RELATION_NAME, a.RDB$FIELD_NAME, a.RDB$FIELD_POSITION, a.RDB$NULL_FLAG, a.RDB$DEFAULT_VALUE, b. RDB$FIELD_LENGTH, b.RDB$FIELD_SCALE, c.RDB$TYPE_NAME, b.RDB$FIELD_TYPE, b.RDB$FIELD_SUB_TYPE, b.RDB$DESCRIPTION, b.RDB$CHARACTER_LENGTH, b.RDB$FIELD_PRECISION, a.RDB$DEFAULT_SOURCE, b.RDB$DEFAULT_SOURCE as RDB$DEFAULT_SOURCE_DOMAIN,b.RDB$COMPUTED_SOURCE as RDB$COMPUTED_SOURCE FROM RDB$RELATION_FIELDS a JOIN RDB$FIELDS b ON (b.RDB$FIELD_NAME = a.RDB$FIELD_SOURCE) LEFT JOIN RDB$TYPES c ON (b.RDB$FIELD_TYPE = c.RDB$TYPE and c.RDB$FIELD_NAME = 'RDB$FIELD_TYPE') WHERE a.RDB$RELATION_NAME = 'jjy9z2digff65j5pxh487ktwxf' ORDER BY a.RDB$RELATION_NAME, a.RDB$FIELD_POSITION
2010-05-07 22:28:58 cat: Prepare, proto: firebirdd-2.1, msg: Statement 3 : INSERT INTO "jjy9z2digff65j5pxh487ktwxf" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2010-05-07 22:28:58 cat: Bind prepared, proto: firebirdd-2.1, msg: Statement 3 : 'd3fcacf6c04dfab2e3c1ae4b0b2efbb64bd7252a0e4a18d304a8a8f3a5f42646',4,0,1,1,0,0,1,1,0,0,1,1,0,0,0,0,0,0,200,0.01,1000,
2010-05-07 22:28:58 cat: Transaction, proto: firebirdd-2.1, msg: TRANSACTION COMMIT
2010-05-07 22:28:58 cat: Execute prepared, proto: firebirdd-2.1, msg: Statement 3
2010-05-07 22:28:59 cat: Transaction, proto: firebirdd-2.1, msg: TRANSACTION STARTED.
2010-05-07 22:28:59 cat: Prepare, proto: firebirdd-2.1, msg: Statement 4 : SELECT DATAPOINTHASH, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13, P14, P15, P16, P17, P18, P19, P20, P21 FROM "jjy9z2digff65j5pxh487ktwxf" WHERE DATAPOINTHASH=?
2010-05-07 22:28:59 cat: Bind prepared, proto: firebirdd-2.1, msg: Statement 4 : 'd3fcacf6c04dfab2e3c1ae4b0b2efbb64bd7252a0e4a18d304a8a8f3a5f42646',
2010-05-07 22:28:59 cat: Execute prepared, proto: firebirdd-2.1, msg: Statement 4
2010-05-07 22:29:05 cat: Other, proto: firebirdd-2.1, msg: Dynamic SQL Error SQL error code = -501 Attempt to reclose a closed cursor, errcode: -501, error: The cursor identified in a FETCH or CLOSE statement is not open.
2010-05-07 22:29:12 cat: Other, proto: firebirdd-2.1, msg: Dynamic SQL Error SQL error code = -501 Attempt to reclose a closed cursor, errcode: -501, error: The cursor identified in a FETCH or CLOSE statement is not open.

After doing a search on this forum for both "autocommit" and "FireBird," I think I see the source of my problem. ZEOS is doing soft commits to keep cursors open "across transactions," but I'm specifying hard commits to keep FireBird happy. Oh well. I guess the behavior I'm seeing could be classified as a design choice, after all.

OK, we're getting near the end of this issue: I have been using the hard_commit=yes optional parameter in the ZConnection. Removing it seems to have removed the behavior! I'll just have to encourage the user to click on a "Garbage Collect" button occasionally to do a quick disconnect/reconnect and thereby remove old transaction garbage.

I think that obviously the hard_commit parameter needs to be looked at a bit more, and I'm going to investigate...

Well, I think that hard_commit has been labeled as very experimental all over the place, isn't it?
The reason why they aren't used by the zeoslib components is exactly because they silently close things that are still needed by open query components.
I'd say : use them only when you're not using resultsets. And when using resultsets only commit manually when you know resultsets are closed.

Mark