> I still don't see why it is a problem. The Java programmer should
> know whether he is dealing with a SSP or an ESP. He should present
> the correct statement to the interface. The driver should be taught
> to do the right thing and programmers should expect to handle
> exceptions when they don't.

The problem is that Java does not distinguish selectable and
non-selectable procedures. JDBC provides a single interface called
CallableStatement, that can be used to execute procedures that return
result sets as well as procedures that do not return result sets.

> It seems to me that the JDBC standard itself has problems with an
> SQL syntax that doesn't conform to *its* expectations. Isn't the
> problem really that the Jaybird driver needs to be be refined to
> permit stored procedure names and arguments in SELECT calls?

The problem is that all developers implement against JDBC
specification, and it is pretty legitimate to use CallableStatement to
call strored procedures.

> That way, those who are writing applications with more sophisticated
> data access interfaces can continue to use EXECUTE PROCEDURE and
> SELECT in the most optimal way. Preparing a multi-row buffer and
> forcing looped fetches on an ESP that can *only* return a singleton
> might be nice and tidy way to lower the bar for Java programmers but
> for programmers used to a fully-implemented API it's a horror.

Then please let me SELECT * FROM from non-selectable procedures, or
throw an exception that I cannot do this. But please do not return me
empty result set pretending everything is ok.

> I've been watching the chit-chat on the java list about this over
> the weeks and I can't fathom out why Jaybird can't sub-class the
> dumbed-down JDBC interface to handle stuff like selectable SPs,
> multiple transactions per connection and multiple cursors per
> statement. It seems no better than ADO or .NET.

That's how this is implemented now, but this requires people to link
against our interfaces. And the worser is that there is no change to
ask Firebird whether procedure is selectable or not.

> I choke when I see Roman telling people that it's a *deficiency*
> that Firebird's SP implementation isn't like MSSQL's, or an IT
> teacher complaining that it's too hard to get students to
> understand what they're doing. What the heck's happening to
> education?

I don't tell that it's deficiency, but a specifics. And I tell that
I'm not able to map that into JDBC specification.

> When returning a multi-row dataset from a SP, Firebird elegantly
> spits a tuple into a buffer, waits for a fetch from the client and
> then spits out the next tuple - just like a regular SELECT (which
> from the client's POV, it is).

Agree - that's very elegant solution.

> If this so-called "unified execution" has to be introduced as a
> second-string *option* to stuff Firebird into this warpy mould, just
> don't make it a one-size-fits-all engine re-implementation that
> drags everyone else down to the lowest common denominator.
> Notwithstanding, I'd rather see the Jaybird developers find a way to
> make it work properly, and show the rest what they're missing.
>
> How hard is it to teach the driver that SELECT BLAH FROM APROC(?,
> ...) means it has to prepare a multi-row buffer,
> call EXECUTE PROCEDURE and start a fetch loop?

It does this already. But it requires from developer to give us a hint
that I should use SELECT instead of EXECUTE. This hint is the problem,
because it requires linking to the JDBC extension.

What I ask, is either possibility to execute procedure in a unified
form or provide me a hint that procedure is selectable. I fail to
understand that here it is told that it is pretty legitimate to return
an empty result set when non-selectable procedure is used with SELECT
statement. Either it is allowed and delivers correct results, or it is
not allowed and throws an error. Then I can handle this in JayBird. Or
add new field into the RDB$PROCEDURES telling me what should be used.

Roman