Subject Re: [Firebird-Architect] Re: RFC: Please unify stored procedure execution
Author Nando Dessena
Martijn, All,

>> >> Does it work (i.e. does it output the parameters) without a suspend?
>> R> Unfortunately(?) - yes. :))
>> I meant: does it output the parameters when called through select?

M> No, not without a suspend. Welcome to the problem ;-)

Thanks. ;-)

Here's the line of thinking I have developed in these hours based on
all that has been said here. Just MHO of course.
The problem stems from trying to have Firebird behave like other
RDBMSs that (unfortunately) support returning a result set from a
SP call (meaning with "SP call" the EXECUTE style *only*). It might
appear logical (so it appers to Roman, at least) to switch to a
different call syntax (SELECT) when needed, but the truth is that a
CallableStatement (if I have got the JDBC model right) should have no
business messing around with SELECT statements that incidentally
support selectable stored procedures (SSPs). Firebird's SSPs are not
like returning a result set from a MSSQL's SP, the model is completely
different as we know and there are things that Firebird cannot emulate
even with SSPs (like the ability to return more than one result set,
return a result set *and* output parameters, the RESULT parameter, and
the ability to return a result set without having to define an output
parameter for each column). There are more differences than
similarities between the two models and thus I think that trying to
coerce a SELECT * FROM SSP into a CallableStatement in the name of
database agnosticism is forcing the model to be what it isn't.
One day Firebird might support returning a result set from a SP call
(the "table" datatype that was discussed not long ago in a thread
initially about temp tables would help there), but until then IMHO the
need is not unifying EXECUTE and SELECT because they're two different

I can see the value in having the feature "result set returned from a
stored procedure" in a database independent way, I just have the
feeling that going the SELECT route is not the right way.

About detecting the kind of call by looking at the presence of output
parameters, I still think it's "good enough" for FlameRobin for the
time being (yet I'll make a note of the bound cases for future
enhancement). It obviously isn't good enough for Jaybird.

Nando Dessena