Roman Rokytskyy wrote:

>
>
>
>>There is a method called setSelectableProcedure(boolean f) in
>>org.firebirdsql.jdbc.FBCallableStatement
>>
>>
>
>In fact that method is declared in
>org.firebirdsql.jdbc.FirebirdCallableStatement interface.
>
>
>
>>Is there any other way to set callable statments selectable?
>>E.g. some System.property to set or perhaps some connection property
>>that makes CallableStatements selectable
>>by default as you create them?
>>
>>
>
>No, there isn't.
>
>
>
>>The reason I ask is that many other databases use procedures that
>>commonly returns multirow resultsets.
>>It would be easier to write multi dbplatform code if you hadn't have
>>to resort to ugly things like this:
>>
>>
>
>Problem is that the Firebird uses two different calls for the selectable
>procedures and normal procedures that return some parameters. For normal
>procedures you have to use EXECUTE PROCEDURE statement, but if you use it
>for selectable procedures, you will get only first row. However if you use
>SELECT * FROM myProc that is not selectable, you get an empty result set.
>And the problem is that server does not provide an hint to tell whether
>procedure is selectable or not.
>
>So, I'm, afraid, if we will use SELECT * FROM <proc name> as default
>construct (as you suggest) is not the solution to your problem. It must be
>solved in the server somehow.
>
>

So, is there any way to get a multirow ResultSet from a non selectable
procedure?
E.g. could I rewrite the procedure below to make it non selectable and
still have it return
multiple rows from a CallableStatement Or am I missing something?

CREATE PROCEDURE getnames
RETURNS (
fname VARCHAR(30),
lname VARCHAR(30)
) AS
BEGIN
FOR SELECT fname, lname
FROM name
INTO :fname, :lname
DO
SUSPEND;
END

Posts on the firbird-support list indicates that this is not possible.
I have tried to return a cursor but whatever I do I can't get that to compile.

Or did you perhaps mean that the SQL syntax in the server had to be changed?

Regards
Uno Engborg


[Non-text portions of this message have been removed]