Hi Marko.
> BEGIN
> Stmt 1: SAVEPOINT svp
> Stmt 1: CREATE CURSOR CC AS SELECT values from TEST LIMIT 2 -- OK
> Stmt 1: FETCH 1 -- fetched one row
> Stmt 2: INSERT INTO VALUES -- ERROR.
> Stmt 2: ROLLBACK TO svp:
> Inside PSQLODBC:
> 1. PSQLDODBC calls CLOSE CURSOR CC. Error, because transaction is in
> ERROR condition.
> 2. ROLLBACK TO svp: this won't be done, because (1) failed already.
> Correct way here is to NOT CLOSE DEAD CURSORS!
> Stmt 1: FETCH 1 -- fetched one row -- NOT DONE
> CLOSE CURSOR CC -- NOT DONE
> END
Umm, Actually, the cursor name will not be found when it ROLLBACK out of cursor.
It is a phenomenon which happens even if it is not psqlodbc....
I consider the following scenario.
CREATE TABLE svtest (k1 varchar(19) PRIMARY KEY,d1 varchar(10));
INSERT INTO svtest VALUES('X','1');
INSERT INTO svtest VALUES('Y','2');
INSERT INTO svtest VALUES('Z','3');
BEGIN;
SAVEPOINT svp1;
DECLARE CC CURSOR FOR SELECT * from svtest LIMIT 2;
FETCH FIRST FROM CC;
SAVEPOINT svp2;
INSERT INTO svtest values('X','99');
ROLLBACK TO svp2;
INSERT INTO svtest values('A','0');
FETCH FIRST FROM CC;
FETCH NEXT FROM CC;
CLOSE CC;
END;
SELECT * FROM svtest;
DROP TABLE svtest CASCADE;
The above works with psqlodbc. However, Updating cursor is not considered carefully.
Regards,
Hiroshi Saito
