At 11:48 AM 21/12/2004 +0100, Martijn Tonies wrote:

>No, you're wrong. Let me tell you why...
>
>In IB/Fb, there is only 1 type of call to a procedure. But, in Java speak,
>when you "call" a procedure, it can be a procedure on any database engine.
>Next, this procedure can return output parameters OR it can return a
>resultset. (eg: a MS SQL procedure). So, Java has a facility to return a
>resultset from a stored procedure call.
>
>To convert this generic Java-calling mechanism to Firebird, you should use
>either EXEC or SELECT (when you want to return a resultset). The problem
>is, that with Firebird, you cannot really know if it returns a resultset.
>That is
>the problem.

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.

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?

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.

>Don't think "I know how to use my stored procedure", but think "inside a
>driver, with a specification from someone else, how do I know to return
>a resultset and not a single set of output parameters".

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.

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?

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). Sheesh, MSSQL has to materialize a table to return a multi-row set
from a SP; if 20 transactions are running the same SP, it has to
materialize 20 tables. No wonder MSSQL SYSDBAs have to keep bedrolls and
toothbrushes in the server room.

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?

Helen