Re: Soliciting CallableStatement ideas - Mailing list pgsql-jdbc

Quoting Scott Gammill <scottgammill@yahoo.com>:

> 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.)

No, that sounds correct. Also the user may work out that it would be more
efficient to use PreparedStatement as it's removing the extra parsing stage, ie:

   CallableStatement parse -> PreparedStatement parse -> query...

>
> 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.

This sounds similar to a conversation made about 2 months ago where we were
discussing how we could link a JVM directly into the backend, effectively
giving us a Java/Pl capability, which this sounds like.

This also opens up nice little things like Corba/RMI, JMS etc.

Peter

--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/