Subject Re: "Invalid Request Handle" with stored procedures
Author pg_waspy
--- In, Helen Borrie <helebor@t...> wrote:
> At 07:05 PM 11/09/2003 +0000, you wrote:
> >Hey all.
> >I'm just getting started with this here Firebird, and I've setup a
> >bunch of stored procedures to do various searches, etc. When I use
> >these SPs in ISQL, they work normally.
> >
> >When I try and call the SPs from my VB application, I get an
> >error: "Message length error (encountered 0, expected 64)"
> >or "Invalid request handle", depending on how I word the query
> >(forget the ";") I'm accessing the database via the latest ODBC
> >driver at IBPhoenix. So, I figure it's the ODBC driver.
> >
> >Not so fast! IBOConsole also gives the same "Invalid request handle"
> >in its own ISQL tool, which is set to local - and therefore not over
> >odbc.
> >
> >Has anyone else had a problem with "Invalid request handle"
> >or "Message length error"? Any suggesstions?
> Sorry, I hit the Send button too soon.
> Do include the Firebird version in your questions. We currently have two
> distinct Firebird versions in the "hit-list".
> This error can occur with SPs when you call EXECUTE on a SP that returns a
> multi-row dataset. You need to make a SELECT call on such SPs, selecting
> the output parameters of the SP in the column list of the SELECT statement.
> The ODBC driver and the Delphi components used in IBOConsole can then
> prepare the appropriate client-side container, with objects of the right
> size and type to accept the columns. This preparation step has to
> happen; and if the structure of the return structure doesn't "fit" you
> will get one or both of the messages you report. One can only guess about
> why your queries succeed in isql; but isql uses an internal interface to
> the API, whereas ODBC and Delphi use the structures returned by the API call.
> We could help you better if you would supply the SQL and the method call
> that throws this exception.
> heLen

Ah, I see. I'm used to SQL Server where "EXECUTE PROCEDURE..." is good enough to return a daatset. So all I have to do is something like: "SELECT * FROM (EXECUTE PROCEDURE ...)" ?