Firebird and 6.6.1 problem: 'Cannot retrieve Resultset data'

ste_ba · Post by **ste_ba** » 25.06.2007, 12:59

Hi,

there must be some obvious mistake, but somehow I'm blind, I don't see it. Every SELECT statement I send to the Firebird server is answered with an error message 'Cannot retrieve Resultset data'. Tried with Firebird 2 and Firebird Embedded Server 1.5. Same scripts worked fine with 6.5.1.

Details:
In Unit ZDbcInterbase6Statement, method TZInterbase6PreparedStatement.ExecuteQueryPrepared is called. Data is supposed to be stored in local variable SQLData of type IZResultSQLDA.

SQLData is then, among other variables, passed to the procedure PrepareResultSqlData, which is supposed to receive metadata like fieldcount.
However, SQLData.GetFieldCount is still = 0 afterwards, which then causes the method ExecuteQueryPrepared to raise the exception quoted above.

Within PrepareResultSqlData, the problem seems to occur in PlainDriver.isc_dsql_describe, for when SqlData.InitFields is called afterwards, there should be field data there already but are not.

If I may literally translate some German figures of speech, I seem to have tomatoes upon my eyes, or maybe I'm just standing on my hosepipe. You surely get the meaning :-) Can anybody make me see again?

ste_ba

Post by **mdaems** » 25.06.2007, 13:21

Hi ste_ba,

Can you tell us which component and script you are using? TZSQLProcessor or TZQuery? Open or Execute? The SQL property of the component, ...

Mark

ste_ba · Post by **ste_ba** » 25.06.2007, 13:39

Sorry ... since I was expecting a resultset, I thought it was obvious that I used TZQuery and Open :-)

The first SQL command I send is

Code: Select all

SELECT COUNT(*) FROM RDB$RELATIONS WHERE upper(RDB$RELATION_NAME)=upper(_ISO8859_1 't_mytablename' COLLATE DE_DE);

The ISO and collation specification come from a function I always call when sending strings to the Firebird server; I used to have some trouble with these, so just in case ... anyway it shouldn't do any damage. The table name is passed as a variable, which is the reason for writing "upper(...)" instead of "T_MYTABLENAME".

Talking about it, I notice that I have forgotten to mention why I started the query in the first place: because the TZConnection.GetTableNames method failed since the Zeos upgrade. It tells me

Dynamic SQL Error SQL error code = -804 Incorrect values within SQLDA structure. Error Code: -804. An error was found in the application program input parameters for the SQL statement. The SQL: SELECT DISTINCT a.RDB$RELATION_NAME, b.RDB$SYSTEM_FLAG, b.RDB$VIEW_CONTEXT, a.RDB$VIEW_SOURCE FROM RDB$RELATIONS a JOIN RDB$RELATION_FIELDS b ON a.RDB$RELATION_NAME=b.RDB$RELATION_NAME;

Maybe there is a hint in this?

The database itself is OK, I just created a new one and still get the same errors.

ste_ba

cipto_kh · Post by **cipto_kh** » 28.06.2007, 04:13

Perhaps you can add it in mantis bug with the database example. I haven't try FB with collation before (I use FB with its standart setting), but maybe we can look the problem together.

ste_ba · Post by **ste_ba** » 28.06.2007, 09:14

Hi cipto_kh,

it's not (only?) the specified collation, obviously, since TZConnection.GetTableNames won't work either, and I have worked with COLLATE before without any problems. I'm afraid I have no idea where it comes from.

ste_ba

ste_ba · Post by **ste_ba** » 30.07.2007, 15:25

Looks like trouble ... here's what happened: Since I had a few deadlines to keep with my DB apps, I "solved" the problem for now by deinstalling Zeos and reinstalling 6.5.1 (oddly, it only worked to reinstall Delphi completely, reinstalling Zeos wasn't enough). Worked fine. Until five minutes ago, when suddenly after a complete rebuild (i.e. compile all pas files rather than use dcu when appropriate) the same error hits me again:

---------------
Exception der Klasse EZSQLException aufgetreten. Meldung: 'SQL Error: Dynamic SQL Error SQL error code = -804 Incorrect values within SQLDA structure. Error Code: -804. An error was found in the application program input parameters for the SQL statement. The SQL: SELECT DISTINCT a.RDB$RELATION_NAME, b.RDB$SYSTEM_FLAG, b.RDB$VIEW_CONTEXT, a.RDB$VIEW_SOURCE FROM RDB$RELATIONS a JOIN RDB$RELATION_FIELDS b ON a.RDB$RELATION_NAME=b.RDB$RELATION_NAME; '.
----------------

The SQL Code comes from TZConnection.GetTableNames, but I can't send ANY other SQL command either - I'm back where I started from: "Cannot retrieve resultset data".

Any suggestions? I thought it was a 6.6.x problem, but I have deleted anything that had to do with 6.6.x. It's all 6.5.1 now. Firebird 1.5 Embedded (but 2.0 won't work either). WinXP. Help!

ste_ba

swm · Post by **swm** » 11.08.2007, 17:28

Have the same problem! Don't know why, none of my projects (four different applications) using Zeos with Delphi 2005 (WinXP Prof.) and Firebird 2 Embedded is working because of 'Cannot retrieve Resultset data'! I've tried finished projects compiled last maybe three weeks later and their executables works (so itsn't probably problem with Firebird). When I compile them now, I've got error above on first simple SQL SELECT. I didn't change anyting in Zeos and I'm not sure whether I've changed anything in Delphi because I work with VS .NET primary. Have tried different PCs, old executable works, the new one doesn't.
The problem is in the code as ste_ba already shows. I've tried reinstall 6.5.1, then delete and install 6.6.x, then install latest Firebird 2 embedded, nothing works. Please help!

Edit: Opening sql query in design time is without error, data are succesfully fetched to dbgrid.

ste_ba · Post by **ste_ba** » 20.08.2007, 11:42

@swm:

Have you tried reinstalling Delphi? That's what worked for me in the end. Uninstall Delphi (I deleted all Delphi Registry entries, just to be sure), delete Zeos folders, reboot, install Delphi, unzip and install Zeos 6.5.1. At least that made it work for some time - until it started all over again. Right now, I use my notebook for development, as for some mysterious reason it still works with the same program code.

ste_ba

kamilozo · Post by **kamilozo** » 17.09.2007, 10:23

Hi,

I am using TZConnection to receive all DB tables during the design time

List: TStrings
TZConnection.GetTableNames('*',List);

but the List is always empty.

this is bug. Was it fixed in some release? I use currently ZEOSDBO-6.6.1-beta.

Thanx
Kamil

ste_ba · Post by **ste_ba** » 17.09.2007, 10:30

Hi kamilozo,

looks like a completely different piece of cake. Maybe you should better start a new thread for that.

ste_ba

Post by **mdaems** » 17.09.2007, 10:34

What if you just use an empty string instead of '*'?

Can you please tell us what database you are using? The implementation of getting all tables depends on the database used.

Mark

kamilozo · Post by **kamilozo** » 19.09.2007, 22:48

Hi,

thank you for answer and excuse for my late reply. I am using Firebird Database Server 2.0.1. Leaving the mask initial doesn't help. The result is still empty list.
Is there at least any workaround how to get list of all tables from database using TZConnection?

Kamilozo

kamilozo · Post by **kamilozo** » 21.09.2007, 22:03

Hi,

The table list retrieval works now. After 2 days of debuging I found wrong cast command in my source. :-(

K.

Working example
===========

procedure Tfrm_cumain.Button6Click(Sender: TObject);
var
database: TZConnection;
XList: TStrings;
tableName: string;
List: TStrings;
i: integer;
begin
List:=TStringList.Create;
List.Clear;
XList := TStringList.Create;
disconnect := False;

database := FireBirdSQL.Connection;
if not database.Connected then
begin
database.Connect;
end;
if database.Connected then
begin
database.GetTableNames('', XList);
end;
if XList.Count > 0 then
begin
for i := 0 to XList.Count - 1 do
begin
tableName := XList.Strings;
if copy(tableName, 1, 4) <> 'RDB$' then
List.Add(tableName);
end;
end;

Memo2.Lines := List;
end;