Re: 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

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

Scott

----- Original Message -----
From: "Scott Gammill" <scottgammill(at)yahoo(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org

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

Quoting Scott Gammill <scottgammill(at)yahoo(dot)com>:

> BTW, the idea for our proposed CallableStatement extension was submitted
> to us
> by fellow list subscriber Brett Knights <brett(at)knightsofthenet(dot)com>.
> We
> inadvertantly omitted this acknowledgement in the original e-mail.
>
> Scott
>
>
> ----- Original Message -----
> From: "Scott Gammill" <scottgammill(at)yahoo(dot)com>
> To: <pgsql-jdbc(at)postgresql(dot)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(at)retep(dot)org(dot)uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/

My comments on this thread are:

1) Having java as an available stored procedure language would be
great. I am not sure if that is what is being proposed or not, but it
seems to be a necesary step if you are talking about running java on the
database server.

2) The idea of extending the CallableStatement object seems to be
going down the same path as EJBs. I don't think it makes sense to
reinvent the wheel if EJBs already provide the functionality you are
trying to achieve. (while it may be true that EJBs are overkill, simple
alternatives have a way over time of getting more and more feature rich
and thus ending up as complex).

thanks,
--Barry

Peter T Mount wrote:

> Quoting Scott Gammill <scottgammill(at)yahoo(dot)com>:
>
>> BTW, the idea for our proposed CallableStatement extension was submitted
>> to us
>> by fellow list subscriber Brett Knights <brett(at)knightsofthenet(dot)com>.
>> We
>> inadvertantly omitted this acknowledgement in the original e-mail.
>>
>> Scott
>>
>>
>> ----- Original Message -----
>> From: "Scott Gammill" <scottgammill(at)yahoo(dot)com>
>> To: <pgsql-jdbc(at)postgresql(dot)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
>