From: | "Hiroshi Saito" <saito(at)inetrt(dot)skcapi(dot)co(dot)jp> |
---|---|
To: | "Marko Ristola" <marko(dot)ristola(at)kolumbus(dot)fi>, "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> |
Cc: | <pgsql-odbc(at)postgresql(dot)org> |
Subject: | Re: savepoint |
Date: | 2005-09-06 09:01:31 |
Message-ID: | 048501c5b2c1$92d17540$01324d80@hiroshi5jz7dqj |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-odbc |
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
From | Date | Subject | |
---|---|---|---|
Next Message | zuschlag2 | 2005-09-06 11:46:51 | Re: Continuing encoding fun.... |
Previous Message | Anoop Kumar | 2005-09-06 08:53:16 | Re: Continuing encoding fun.... |