Re: CallableStatements - Mailing list pgsql-jdbc

Hi jdbc:ers

I have been following the discussion about Callable Statements and just felt
that there are arguments for api compliance on this matter that hasn´t been
exposed.
The issue is protability. Almost everything our company writes needs to be
reused
on both Oracle, PosgreSQL and SQLServer. We promote PostgreSQL as our first
choice and would really appriciate this functionality as the other two has
Callable Statements.
If I recall correctly, there is lack of support in the backend for returning
resultsets from functions,
which Callable Statements in its api purest form should, right?
But isn´t it possible to make a simple wrapper? I saw there was a little
project
referenced in the conformance/issues file found on applinet
(http://lab.applinet.nl/postgresql-jdbc)?

Regards
Per-Olof

----- Original Message -----
From: "Barry Lind" <barry@xythos.com>
To: "Stuart Robinson" <stuart@zapata.org>
Cc: <pgsql-jdbc@postgresql.org>
Sent: Monday, November 26, 2001 8:54 PM
Subject: Re: [JDBC] CallableStatements


> Stuart,
>
> You are issuing a select statement, so you would use executeQuery() and
> ignore the returned ResultSet.
>
> thanks,
> --Barry
>
> Stuart Robinson wrote:
>
> > But if you use the executeUpdate method, you'll get an error, because it
> > isn't expecting a result, no? So, how do you call a stored procedure
using
> > executeUpdate?
> >
> > -Stuart
> >
> > On Mon, 26 Nov 2001, Barry Lind wrote:
> >
> >
> >>Stuart,
> >>
> >>All stored procedures in postgres return a result.  You can however
> >>ignore the result.
> >>
> >>--Barry
> >>
> >>
> >>Stuart Robinson wrote:
> >>
> >>
> >>>But what do you do if you want to call a stored procedure and NOT get a
> >>>result?
> >>>
> >>>On Mon, 26 Nov 2001, Barry Lind wrote:
> >>>
> >>>
> >>>
> >>>>As Dave has said, since stored procedures in Postgres can only return
a
> >>>>single value, there is little to be gained from CallableStatements
that
> >>>>you can't already do with regular Statements or PreparedStatements.
> >>>>
> >>>>The way to call stored procedures in postgres is via a select
statement.
> >>>> Thus to call procedure foo(), you would issue the query 'select
> >>>>foo()'.  Since this is a standard select statement, you can use either
a
> >>>>regular Statement or PreparedStatement to get the result of this
stored
> >>>>procedure.
> >>>>
> >>>>Having said that, if you wanted to contribute a CallableStatement
> >>>>implementation for postgres we would be glad to accept it.  Remember
> >>>>that this is an open source project, features get added by people who
> >>>>want or need them.  If you need CallableStatements implement them an
> >>>>submit a patch.
> >>>>
> >>>>thanks,
> >>>>--Barry
> >>>>
> >>>>
> >>>>
> >>>>Dave Cramer wrote:
> >>>>
> >>>>
> >>>>
> >>>>>Well, given that postgres doesn't support the notion of returning a
> >>>>>result set from a stored procedure; I'm not sure what benefit this
would
> >>>>>be.
> >>>>>
> >>>>>Regards,
> >>>>>
> >>>>>Dave
> >>>>>
> >>>>>-----Original Message-----
> >>>>>From: pgsql-jdbc-owner@postgresql.org
> >>>>>[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of
> >>>>>email@gregorybittar.com
> >>>>>Sent: Friday, November 23, 2001 6:47 PM
> >>>>>To: pgsql-jdbc@postgresql.org
> >>>>>Subject: [JDBC] CallableStatements
> >>>>>
> >>>>>
> >>>>>CallableStatements weren't in Postgres as of the last time I checked,
> >>>>>version 7.1.
> >>>>>
> >>>>>The JDBC specification has lots of goodies in it, such as examining a
> >>>>>server's metadata and sending cursors backwards and forwards over
result
> >>>>>sets.  However, from the perspective of a Java programmer,
> >>>>>CallableStatements are essential tools for communicating with a
database
> >>>>>server.
> >>>>>
> >>>>>Without the benefit of CallableStatements, all efforts at efficiency
are
> >>>>>wasted.  The hallmark of any robust system is distributed processing,
> >>>>>which requires invoking stored procedures on foreign machines.  Doing
so
> >>>>>through CallableStatements would
> >>>>>(a) accomplish work and (b) retrieve a result code in one logical
> >>>>>network transmission.  Without CallableStatements, retrieving the
result
> >>>>>code not only requires more programming infrastructure, but also
taxes
> >>>>>the application at runtime as the Java application tries to discover
> >>>>>what the result of the stored procedure was. This method requires an
> >>>>>additional deletion to purge the logged result code record, lest the
log
> >>>>>grow, slowing searches. Therefore, we are looking at considerably
more
> >>>>>processing done, 2 or 3 transmissions, where 1 should suffice.
> >>>>>
> >>>>>Consequently, I would hope that CallableStatements are recognized as
a
> >>>>>very important part of the JDBC puzzle.
> >>>>>
> >>>>>
> >>>>>---------------------------(end of
broadcast)---------------------------
> >>>>>TIP 2: you can get off all lists at once with the unregister command
> >>>>>   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
> >>>>>
> >>>>>
> >>>>>
> >>>>>---------------------------(end of
broadcast)---------------------------
> >>>>>TIP 3: if posting/reading through Usenet, please send an appropriate
> >>>>>subscribe-nomail command to majordomo@postgresql.org so that your
> >>>>>message can get through to the mailing list cleanly
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>---------------------------(end of
broadcast)---------------------------
> >>>>TIP 5: Have you checked our extensive FAQ?
> >>>>
> >>>>http://www.postgresql.org/users-lounge/docs/faq.html
> >>>>
> >>>>
> >>>>
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 2: you can get off all lists at once with the unregister command
> >>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >>
> >>
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>