Re: Soliciting CallableStatement ideas - Mailing list pgsql-jdbc

BTW, the idea for our proposed CallableStatement extension was submitted to us
by fellow list subscriber Brett Knights <brett@knightsofthenet.com>.  We
inadvertantly omitted this acknowledgement in the original e-mail.

Scott


----- Original Message -----
From: "Scott Gammill" <scottgammill@yahoo.com>
To: <pgsql-jdbc@postgresql.org>
Sent: Friday, April 20, 2001 10:36 PM
Subject: [JDBC] Soliciting CallableStatement ideas


We're implementing the CallableStatement interface for the JDBC driver.  The
problem is that Postgres doesn't support real stored procedures, but only
functions that take arguments and return a value, a column of values, or in the
future, a ResultSet.  No OUT parameters.  You call functions with a select
statement.

For example, function f takes a float parameter and returns an int.  Call
function f with the following query:
     select f(3.4);
The ResultSet returned by the select will contain the integer which was
returned by f.
That's the way all postgres functions, even user-defined functions, are called.
Thus, CallableStatement would just be a wrapper around PreparedStatement, for
it would parse a procedure call and mush it into a select statement and then
pass it off to PreparedStatement.

If the user knows this, it doesn't seem likely that he/she would go to the
trouble to use CallableStatement but would instead just use PreparedStatement.
(Correct me if I'm wrong about this assessment.)

So, we're thinking of extending CallableStatement to be more powerful:

We're proposing to have stored procedures which are really Java classes that
use the JDBC driver to make any kinds of queries to the database and return
values and ResultSets.  Thus, such a class would export an interface that would
mimic the CallableStatement interface (but somewhat simpler).  The user would
use prepareCall, and the appropriate Java class would be loaded, and then the
user could do setXXX, registerOutParameter, execute, and getXXX as if he/she
were using a bona fide stored procedure.  In this way, a Java class acting like
a stored procedure could indeed have OUT parameters and IN/OUT parameters, and
even return multiple ResultSets.

The work involved in implementing such a "stored procedure" via a Java class
would not make it worthwhile if the user is the only one using a database,
perhaps for academic research, etc.  It would be useful for a company with
multiple db users, for example -- the dba would implement a bunch of Java
"stored procedures," and database users throughout the company would use
CallableStatements to access these procedures, and they wouldn't even have to
know whether they were using postgres stored functions or these Java "stored
procedures."

The question is:  Would this kind of extension to CallableStatement be useful
to anybody?  It seems like a cool idea to us, but we don't need to put forth
the effort if it won't be of much use to people.  Please let me know what you
think.  If you have other ideas for what we can do, we welcome those as well.
You can post suggestions to the mailing list instead of sending them to me, so
others can see the ideas that are out there.

Sincerely,
Scott


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com