Thread: Bug with binding query parameters hangs entire connection instance. (Possibly redundant notice)

Hello.
This may be an issue that you are all aware of, sorry for not digging
through archives and such,
but I simply don't have the time.

Noting that support for CallableStatement is not yet implemented I
elected to use
the following format for calling user defined SQL Functions and
dynamically bind
parameters to them.

PreparedStatement procedure = conn.prepareStatement("select
someFunction(?,?,?)");

and then binding params with
procedure.setObject(index, aStringOrIntegerOrSomeSuch);

which mostly works fine. However, I discovered that in some cases, when
the bound
parameter is totatlly screwed, ie. Float when the function expects
Integer, the
PostgreSQL jdbc driver will throw an exception:

java.sql.SQLException: ERROR:  pg_atoi: error in "140.0": can't parse ".0"

which is fine and all, except that any subsequent statements made to the
connection instance in question will fail with:

java.sql.SQLException: The query returned no rows

which is not too great. For my application I made a workaround that
runs a query I know should work before every query to determine if
the connection instance is jammed and has to be re-instantiated...
This introduced surprisingly little overhead with a pre prepared statement,
but it is a kludgy annoyance nonetheless.

Any ideas or comments?

Summery greetings from Finland!
 Peter Bäck

--
-=[ Peter Bäck                        +358-400-564050 ]=-
-=[ Oy IT Mill Ltd                   Ruukinkatu 2 - 4 ]=-
-=[ http://www.itmill.com       20540 Turku , Finland ]=-

Peter,

After getting an error during a transaction the postgresql database
aborts the transaction and all subsequent statements issued in the
transaction will error.  After an error you will need to rollback the
transaction and begin a new one.  This means that in java code using the
jdbc driver you can't just trap a SQLException and continue processing,
you need to rollback inside the exception handler and start a new
transaction.

thanks,
--Barry

Peter Bäck wrote:

> Hello.
> This may be an issue that you are all aware of, sorry for not digging
> through archives and such,
> but I simply don't have the time.
>
> Noting that support for CallableStatement is not yet implemented I
> elected to use
> the following format for calling user defined SQL Functions and
> dynamically bind
> parameters to them.
>
> PreparedStatement procedure = conn.prepareStatement("select
> someFunction(?,?,?)");
>
> and then binding params with
> procedure.setObject(index, aStringOrIntegerOrSomeSuch);
>
> which mostly works fine. However, I discovered that in some cases,
> when the bound
> parameter is totatlly screwed, ie. Float when the function expects
> Integer, the
> PostgreSQL jdbc driver will throw an exception:
>
> java.sql.SQLException: ERROR:  pg_atoi: error in "140.0": can't parse
> ".0"
>
> which is fine and all, except that any subsequent statements made to the
> connection instance in question will fail with:
>
> java.sql.SQLException: The query returned no rows
>
> which is not too great. For my application I made a workaround that
> runs a query I know should work before every query to determine if
> the connection instance is jammed and has to be re-instantiated...
> This introduced surprisingly little overhead with a pre prepared
> statement,
> but it is a kludgy annoyance nonetheless.
>
> Any ideas or comments?
>
> Summery greetings from Finland!
> Peter Bäck
>