How to tell if the query returns resultset?

[Zoran]

Is there a way to determine if the sql command returns the resultset?

Testing if the word "select" is in command is not reliable. Especially because I don't even know which protocol (which dbms, with its SQL dialect) will be used.

[mdaems]

I can't think of a 'smart' way. You can always try to 'open' every query and handle the 'missing resultset' exeception raised in case of a non-select query.

Mark

[Zoran]

That would be a quite acceptable solution... if it worked. However, it does not.

For test I used Firebird2.1 protocol. SQL statement "alter table TABLE1 add column2 varchar(2)".
Then if I just call ZQuery1.ExecSQL, it works well.
But, if I try Open and then, in case it fails, ExecSQL, this code crashes:

Code: Select all

try
  ZQuery1.Open;
except
  on E: Exception do
    if (E is EZSQLException) and (E.Message = SCanNotRetrieveResultSetData) then begin
      ZQuery1.ExecSQL; // Here it crashes!
    end else
      raise;
end;

[mdaems]

Zoran,

If it crashes there : the trick already worked, I believe. Because the query has been executed and the Exception is caught. No need to do an other execsql.

Or is that incorrect? Use a TZSQLMonitor to check if the query has been sent to the server.

Mark

[Zoran]

Yes! The command had been already executed! I didn't notice it at first because I had put later in my code to roll back the transaction, because of raised exception.

Thank you, Mark!

Now, could you tell if I can rely on this? Is this behaviour of TZQuery consistent? I mean, will the Zeos Query always behave so, in any protocol?

[Zoran]

Now, could you tell if I can rely on this? Is this behaviour of TZQuery consistent? I mean, will the Zeos Query always behave so, in any protocol?

Eh... No. I tested today with Oracle... Zeos behaves very diferently comparing to Firebird...

1. EZSQLException is raised with "ORA-24333: zero iteration count" message. Shouldn't Zeos give uniformely SCanNotRetrieveResultSetData message?
2. The statement is not executed with Open. ExecSQL has to be called.

Hm... So, there seems to be no solution, except to test Zeos' behaviour for each protocol and to use ifs in order to respond differently depending on protocol...

[mdaems]

Zoran,
In my opinion zeoslib *SHOULD* behave consistent on the TZQuery level. And the way it works for FB seems to be the only one that's right when the exact nature of the query isn't predictable.
I'm afraid this is something which should be handled by the oracle zdbc driver. Unfortunately there are no real oracle OCI developers on the team for the moment. I suppose you're not really planning to start changing the zeoslib internal code to fix this?

Mark

[Zoran]

Zoran,
In my opinion zeoslib *SHOULD* behave consistent on the TZQuery level. And the way it works for FB seems to be the only one that's right when the exact nature of the query isn't predictable.

Yes, I found this in Delphi manual (bottom of this page):

Tip: When you do not know at design time whether the query returns a result set (for example, if the user supplies the query dynamically at runtime), you can code both types of query execution statements in a try...except block. Put a call to the Open method in the try clause. An action query is executed when the query is activated with the Open method, but an exception occurs in addition to that. Check the exception, and suppress it if it merely indicates the lack of a result set. (For example, TQuery indicates this by an ENoResultSet exception.)

I'm afraid this is something which should be handled by the oracle zdbc driver. Unfortunately there are no real oracle OCI developers on the team for the moment. I suppose you're not really planning to start changing the zeoslib internal code to fix this?

Mark

Doesn't sound like an easy task for me, I'm afraid...
Not only because of lack of ZDBC knowledge, but also because I'm not someone who has much experience with Oracle...

Anyway, I have just started to read the articles from ZEOS knowledge base ("Overview of the ZEOS DBO Architecture", "An Introduction to the ZDBC Api", "How To Write ZEOSLib Database Drivers").

If I ever manage to make any useful change in zdbc drivers, I will surely contribute the code.

[mdaems]

Well,
I just don't have the time nor any functional motivation to dive into it. However, I know where to find the OCI docs and the part of the zeoslib code where it is used.
The way the oracle OCI should be used can be read here : http://download.oracle.com/docs/cd/B193 ... i04sql.htm

The translation hereof inside zeoslib is in the ZDbcOracleStatement and ZDbcOracleUtils code.

Mark