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





---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly