Subject Re: [Firebird-Java] setSelectableProcedure(true)
Author Uno Engborg
Roman Rokytskyy wrote:

>>There is a method called setSelectableProcedure(boolean f) in
>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 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
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?

fname VARCHAR(30),
lname VARCHAR(30)
) AS
FOR SELECT fname, lname
FROM name
INTO :fname, :lname

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?

Uno Engborg

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