Re: transaction problem using cursors

Hello

I thing so problem is there

                    AND Cast("CUSTOMERS"."ZIP" as integer) < 20000

You cannot cast 'A1234' to integer

Regards
Pavel Stehule


2007/6/11, Pit M. <fmi-soft@gmx.de>:
> We use PG 8.2.4 with as cursors over libpq and get an error:
>
> ERROR:  current transaction is aborted, commands ignored until end of
> transaction block
>
> How can we avoid this error or make the cursor still working afterwards?
>
>
> Assume following contents of table CUSTOMERS:
>
> ID     |  ZIP  (varchar(5))
> ------ | -----------------------
> 1       |  12345
> 2       |  12346
> 3       |  99999
> 4       |  A1234
>
>
> Assume a user doing a query which shall select all customers having a
> ZIP in [10000;20000[ by using a CAST. If the cursor now fetches the row
> with ID 4 we get an error (invalid input syntax) as "A1234" cannot be
> casted as integer. This is ok, BUT now all further FETCH commands fail
> with "current transaction is aborted"!
>
> How can we resume from there with FETCH commands (e.g. at least FETCH
> FIRST)? We cannot avoid the (first) error itself, as the user can enter
> any WHERE condition at runtime. Therefore we must handle the error - if
> any - and resume from there properly.
>
> Refer to following log:
>
> SAVEPOINT tsp_020DE240
>
> DECLARE c020DE860 SCROLL CURSOR FOR
>     SELECT "CUSTOMERS"."ID","CUSTOMERS"."ZIP"
>     FROM "CUSTOMERS"
>     WHERE Cast("CUSTOMERS"."ZIP" as integer) >= 10000
>                      AND Cast("CUSTOMERS"."ZIP" as integer) < 20000
>
> FETCH  FROM c020DE860
>
> RELEASE SAVEPOINT tsp_020DE240
>
> FETCH FIRST FROM c020DE860
>
> FETCH FORWARD  FROM c020DE860 -> OK
>
> FETCH FORWARD  FROM c020DE860 -> ERROR:  invalid input syntax for
> integer: "A1234"
>
> FETCH FORWARD  FROM c020DE860 -> ERROR:  current transaction is aborted,
> commands ignored until end of transaction block
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>