From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Stergios Zissakis <szis@intranet.gr>
Cc: Interfaces <pgsql-interfaces@postgresql.org>
Cc: Kostas Lykiardopoulos <klyk@intranet.gr>
Cc: Dimitris Pantermalis <dpant@intranet.gr>
Subject: Re: [ECPG] Multiple cursors in the same transaction
Date: Mon, 7 Jun 2004 09:50:29 -0400 (EDT)
Message-ID: <200406071350.i57DoTH27589@candle.pha.pa.us> (raw)
In-Reply-To: <NGBBJHBHDKPNOODGDMMAOECMCIAA.szis@intranet.gr>
Cursors are automatically closed at the end of a transaction. You can
use WITH HOLD on the cursor to bypass that if you are running 7.4.X.
---------------------------------------------------------------------------
Stergios Zissakis wrote:
> Dear ECPG support,
>
> During a port from Oracle I came accross a problem where a cursor is lost
> when a commit is performed. Consider the following piece of code:
>
> EXEC SQL WHENEVER SQLERROR DO call_sql_error_function(...);
>
> EXEC SQL CONNECT TO :connect_str USER :username IDENTIFIED BY :password;
>
> sprintf((char *) del_stmt.arr, "select columns from delete_table;\0");
> EXEC SQL PREPARE Delete FROM del_stmt;
> EXEC SQL DECLARE del1 CURSOR FOR Delete;
> EXEC SQL OPEN del1;
>
> EXEC SQL WHENEVER NOT FOUND DO break;
>
> while( true )
> {
> EXEC SQL FETCH FROM del1 INTO :dlt;
> ...
> sprintf((char *) stmt.arr, "select to_char((date_trunc('month',
> localtimestamp) - interval '%d month'), 'MM/DD/YYYY HH24:MI:SS');\0",
> months);
> EXEC SQL PREPARE S1 FROM :stmt;
> EXEC SQL DECLARE mytime CURSOR FOR S1;
> EXEC SQL OPEN mytime;
> EXEC SQL FETCH mytime INTO :mydate;
> EXEC SQL CLOSE mytime;
> ...
> sprintf((char *) select_stmt, "select ... from ... where ...;\0", ...);
> EXEC SQL PREPARE ARCH FROM :select_stmt;
> EXEC SQL DECLARE SEL_CURS CURSOR FROM ARCH;
> EXEC SQL OPEN SEL_CURS;
> EXEC SQL WHENEVER NOT FOUND DO break;
>
> while( true )
> {
> EXEC SQL FETCH :rows_to_fetch FROM SEL_CURS INTO :alarm_records;
> for( int i = 0; i < sqlca.sqlerrd[2]; i++ )
> {
> ...
> }
> }
> EXEC SQL CLOSE SEL_CURS;
> ...
> EXEC SQL WHENVER NOT FOUND goto somewhere;
> sprintf((char *) del2_stmt, "delete from %s where %s;\0", from, where);
> EXEC SQL PREPARE DEL2 FROM :del2_stmt;
> EXEC SQL EXECUTE DEL2;
> EXEC SQL COMMIT; // I think this commit causes the problem... see the
> comments further down.
>
> somewhere:
> ...
> }
>
> EXEC SQL CLOSE del1;
> EXEC SQL COMMIT WORK RELEASE;
>
> The problem is that fetching the second time round from the del1 cursor
> cause the app to bomb out. The postmaster log displays the following
> message: "... ERROR: cursor del1 does not exist". Any ideas why this is
> happening? Who come del1 dissapears whithout closing it? Notice that my
> delete_table (del_stmt statement at the beginning) contains a single row; so
> the second time a fetch is performed it should simply not found anything and
> do a break to terminate the loop.
>
> The work-around I found was to split the work in two different connections
> (contexts); one for selecting from the delete_table and the other for the
> rest of the statements. This way everything works fine but I cannot really
> afford an extra connection for this (my app has already 20 connections by
> design). Here is the code that solved the problem:
>
> EXEC SQL WHENEVER SQLERROR DO call_sql_error_function(...);
>
> EXEC SQL CONNECT TO :connect_str AS :ctx1 USER :username IDENTIFIED BY
> :password;
> EXEC SQL CONNECT TO :connect_str AS :ctx2USER :username IDENTIFIED BY
> :password;
>
> sprintf((char *) del_stmt.arr, "select columns from delete_table;\0");
> EXEC SQL PREPARE Delete FROM del_stmt;
> EXEC SQL AT :ctx1 DECLARE del1 CURSOR FOR Delete;
> EXEC SQL AT :ctx1 OPEN del1;
>
> EXEC SQL WHENEVER NOT FOUND DO break;
>
> while( true )
> {
> EXEC SQL AT :ctx1 FETCH FROM del1 INTO :dlt;
> ...
> sprintf((char *) stmt.arr, "select to_char((date_trunc('month',
> localtimestamp) - interval '%d month'), 'MM/DD/YYYY HH24:MI:SS');\0",
> months);
> EXEC SQL PREPARE S1 FROM :stmt;
> EXEC SQL AT :ctx2 DECLARE mytime CURSOR FOR S1;
> EXEC SQL AT :ctx2 OPEN mytime;
> EXEC SQL AT :ctx2 FETCH mytime INTO :mydate;
> EXEC SQL AT :ctx2 CLOSE mytime;
> ...
> sprintf((char *) select_stmt, "select ... from ... where ...;\0", ...);
> EXEC SQL PREPARE ARCH FROM :select_stmt;
> EXEC SQL AT :ctx2 DECLARE SEL_CURS CURSOR FROM ARCH;
> EXEC SQL AT :ctx2 OPEN SEL_CURS;
> EXEC SQL WHENEVER NOT FOUND DO break;
>
> while( true )
> {
> EXEC SQL AT :ctx2 FETCH :rows_to_fetch FROM SEL_CURS INTO
> :alarm_records;
> for( int i = 0; i < sqlca.sqlerrd[2]; i++ )
> {
> ...
> }
> }
> EXEC SQL AT :ctx2 CLOSE SEL_CURS;
> ...
> EXEC SQL WHENVER NOT FOUND goto somewhere;
> sprintf((char *) del2_stmt, "delete from %s where %s;\0", from, where);
> EXEC SQL PREPARE DEL2 FROM :del2_stmt;
> EXEC SQL AT :ctx2 EXECUTE DEL2;
> EXEC SQL AT :ctx2 COMMIT;
>
> somewhere:
> ...
> }
>
> EXEC SQL AT :ctx1 CLOSE del1;
> EXEC SQL COMMIT WORK RELEASE;
>
>
>
> Is there any other solution to this problem?
>
> Thanks in advance.
>
> Stergios Zissakis
>
> Intracom S.A.
> Network Management Systems Dept.
> Paiania
> Athens
> Greece
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: pgman@candle.pha.pa.us, szis@intranet.gr, klyk@intranet.gr, dpant@intranet.gr
Subject: Re: [ECPG] Multiple cursors in the same transaction
In-Reply-To: <200406071350.i57DoTH27589@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox