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

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)

If you are offloading a lot of processing onto your server, then really all
you want to do is send it some parameters, wait while work gets done, and
get a result code to find out how things went.

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

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

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(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
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Stuart Robinson [stuart(at)zapata(dot)org]

And you can easily do that using a regular Statement or
PreparedStatement by issuing the 'select foo()' select (where foo is the
function you want to call). You don't need to have CallableStatements
to call stored procedures in postgres.

--Barry

gbittar(at)iqa(dot)cc wrote:

> If you are offloading a lot of processing onto your server, then really all
> you want to do is send it some parameters, wait while work gets done, and
> get a result code to find out how things went.
>
> 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
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>
>

Stuart,

That's pretty straight forward, just put it in a select statement

i.e. nextval('sequence') is really a stored procedure

select nextval('seqname') returns the next value of the sequence

Dave

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Stuart Robinson
Sent: Monday, November 26, 2001 12:48 PM
To: Barry Lind
Cc: email(at)gregorybittar(dot)com; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] CallableStatements

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(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
> >
> >
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Stuart Robinson [stuart(at)zapata(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

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(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
>>>
>>>
>>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>>http://www.postgresql.org/users-lounge/docs/faq.html
>>
>>
>

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(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
> >>>
> >>>
> >>>
> >>
> >>
> >>---------------------------(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(at)postgresql(dot)org)
>

--
Stuart Robinson [stuart(at)zapata(dot)org]
http://www.nerdindustries.com
http://www.tzeltal.org

You don't call it with executeUpdate, you call it with executeQuery.

Dave

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Stuart Robinson
Sent: Monday, November 26, 2001 1:41 PM
To: Barry Lind
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] CallableStatements

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(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
> >>>
> >>>
> >>>
> >>
> >>
> >>---------------------------(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(at)postgresql(dot)org)
>

--
Stuart Robinson [stuart(at)zapata(dot)org] http://www.nerdindustries.com
http://www.tzeltal.org

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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(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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>---------------------------(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(at)postgresql(dot)org)
>>
>>
>

On Mon, 26 Nov 2001 10:40:52 -0800 (PST), you 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?

You don't. In the current implementation you need to use a
SELECT statement. Why is that a problem?

Regards,
René Pijlman <rene(at)lab(dot)applinet(dot)nl>

There are various circumstances where you might want to call a stored
procedure with an executeUpdate method. For example, let's suppose you
have a view that combines a couple of tables and you want an application
you're building to be able to write to it. Since views are read-only, you
would create a rule that intercepts the inserts and updates and fires off
a stored procedure instead. Since the application is doing an insert or an
update, it will use executeUpdate, but the stored procedure will have to
use select and return a result, causing the application to error out.

-Stuart

On Mon, 26 Nov 2001, Rene Pijlman wrote:

> On Mon, 26 Nov 2001 10:40:52 -0800 (PST), you 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?
>
> You don't. In the current implementation you need to use a
> SELECT statement. Why is that a problem?
>
> Regards,
> René Pijlman <rene(at)lab(dot)applinet(dot)nl>
>

--
Stuart Robinson [stuart(at)zapata(dot)org]
http://www.nerdindustries.com
http://www.tzeltal.org

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(at)xythos(dot)com>
To: "Stuart Robinson" <stuart(at)zapata(dot)org>
Cc: <pgsql-jdbc(at)postgresql(dot)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(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
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>
> >>>>---------------------------(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(at)postgresql(dot)org)
> >>
> >>
> >
>
>
>
> ---------------------------(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)
>

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

Barry,

I want to add that, although I have observed the problem I stated with the
PreparedStatement being used as a stored procedure execution medium, it may be
that I can practice re-structuring the procedures so that tests are performed
by secondary procedures which complete and return values to the primary
procedure, which would in turn process the update on successful completion of
the subsidiary procedures This would be an odd, but entirely satisfactory,
workaround.

Greg

Barry Lind wrote:

> As Dave has said, ....

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