Re: Bug with binding query parameters hangs entire connection

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
>