Re: CallableStatements

Hi Barry,

First of all, I want to dispel any notion that I am unaware or unappreciative
of Postgres' function as a contributory, self-sustaining, user-driven software
venture. I have always prefaced my correspondences to this board with
statements of appreciation, and if it was doubted, let it no longer be so. My
way of contribution at this time is not through code, but rather through an
extraordinary web application which I have poured my own time and money into
for several years now, and which is near come to fruition. The success of
this site would benefit the Postgres community, as it would strengthen its
base of successful implementations at client sites. I hope you can see that.
It's not enough, and I have it in the foremost of my mind that when and if I
am given a chance to give back to the community in one way or another more
directly, I will, because Postgres is an amazing resource, democratizing the
software industry and, in my mind, improving it.

That said, I see it as a useful part of my function right now to report what I
see to be significant gaps in the functionality. I do so not just for me, but
for the community as a whole. If stored procedures are not an integral part
of a Java programmer's workshop, then the Postgres solution inevitably brings
with it certain drawbacks, flaws which I would personally be happy to fix, but
which unfortunately I am not presently in a position to do so. Nevertheless,
if the flaws do in fact exist, and I am in a position to explain those flaws
as I see them, then please pardon me. It is of course possible, anyway, that
I am wrong.

I was hesitant to use PreparedStatements as a substitute for
CallableStatements because it was my impression that CallableStatements are
designed for stored procedure invocation, and that much of the work that goes
into 'preparing' PreparedStatements would presuppose a select statement on
tables/views, and would optimize its query on that basis.

What I have seen of this technique you mention, using a 'select
procedure_name;' statement with an 'executeQuery' invocation is that the JDBC
driver misbehaves. By way of example, I have a stored procedure, well-tested
and often used, which
1) accepts parameters,
2) performs a sequence of tests, each one of which will return an integer
value signifying an error if a condition is met. One of these tests checks a
value in the database foo_table.foo_field_boolean_value. If that boolean
value is true, then the stored procedure returns an error code.
3) If the boolean value is false, then the stored procedure process the
update which sets foo_table.foo_field_boolean_value to true, and then returns
a 0 integer value signifying successful completion of the procedure.

Now, normally, as I said, that procedure works. I use insert triggers on a
log table to indirectly execute that procedure and it functions flawlessly,
and has so for over a year. However, when I do what you advise, call the
stored procedure directly from my Java code using a select statement, I get
ambiguous results if the foo_table.foo_field_boolean_value mentioned above is
false prior to the invocation.

In this case, which should process the update and return 0, something else
happens. The procedure occurs, the update happens, but instead of returning
0, it returns the error code! How does this happen? I am going to continue
investigating this to make sure I am not dreaming, but what appears to happen
is that the Postgres JDBC PreparedStatement is indeed not optimized for stored
procedure invocations. It appears to re-execute the procedure when the
underlying data on which it is operating has changed. So it appears to run
through once, perform the update, and then revert back and re-process before
ever returning a 0 integer. Re-starting at the top, it now sees that
foo_table.foo_field_boolean_value is true, and returns the error code. So my
application sees the update and the error message!

So if this is in fact a bug, then it would support my contention that
rudimentary stored procedure functionality is not available to the Java
programmer. Personally, I don't think it's a bug per se, because I have never
read anywhere prior to this discussion that PreparedStatements were designed
for stored procedure invocations. PreparedStatements are supposed to prepare
queries, and be available for reuse, with some optimization and preparation
having already been taken for subsequent calls. That is my assumption. I have
not evaluated the underlying JDBC code at this point, so what it is actually
doing is more guesswork than analysis on my part.

Please pardon the appearance that I am unaware or unappreciate of what
Postgres is and has to offer to the software community, for such is surely not
the case. I use it and espouse it.. Frankly, I am a little surprised that
you appeared upset, because I often see posts to this board where people ask
trivial questions, the answers to which are clearly specified in download and
installation instructions, whereas I am asking, I believe, a reasonable
question about undocumented functionality. But, I am willing to chalk it up
to miscommunication, which is all too common on the Internet.

Greg

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(at)postgresql(dot)org
> > [mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of
> > email(at)gregorybittar(dot)com
> > Sent: Friday, November 23, 2001 6:47 PM
> > To: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org)
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
> >