Re: CallableStatements

Greg,

I understand your position. Consider my comments (about the nature of
open source and contributing to postgreSQL) as not directed to you
specifically, but to everyone on the mail list (or newsgroups) that may
read these messages. It is entirely possible that one of the people on
the sidelines might read this thread and think: "This is how I can make
my first contribution to the postgreSQL community". I will make sure
that CallableStatements are on the official todo list.

However, I do not know of anyone who is currently contributing code to
the postgres jdbc driver that considers this a high enough priority to
work on this feature (especially given the limited stored procedure
support in the underlying database).

With regards to your problem. The best way to determine if the problem
is in the jdbc driver or not is to run the same query you feel is
providing incorrect results through psql. If you see different behavior
in psql and jdbc, then it is likely a jdbc bug. However if you see the
same behavior in psql and jdbc then it is likely either a bug in your
code or in the backend server.

I personally have code that uses PreparedStatements to call stored
functions. So I know that the functionality works in general. So now
the task is to figure out why it isn't working for you.

If you could submit a test case that demonstrates the problem (i.e. a
script that creates the stored procedure and any necessary tables and
data and a java program that calls the stored procedure and shows the
error), preferably a stripped down version of the real code that clearly
shows the problem, then I am sure someone on this list would be
interested in looking at your problem further. (Of course, I would
recommend checking if this is really a jdbc problem first by trying the
sql through psql first).

thanks,
--Barry

gbittar(at)iqa(dot)cc wrote:

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