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 <[EMAIL PROTECTED]>:
>
>> BTW, the idea for our proposed CallableStatement extension was submitted
>> to us
>> by fellow list subscriber Brett Knights <[EMAIL PROTECTED]>.
>> We
>> inadvertantly omitted this acknowledgement in the original e-mail.
>>
>> Scott
>>
>>
>> ----- Original Message -----
>> From: "Scott Gammill" <[EMAIL PROTECTED]>
>> To: <[EMAIL PROTECTED]>
>> 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
>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
