Thread: [ECPG] Multiple cursors in the same transaction

[ECPG] Multiple cursors in the same transaction

From

"Stergios Zissakis"

Date:

07 June 2004, 13:40:57

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
mytimeINTO :mydate; EXEC SQL CLOSE mytime; ... sprintf((char *) select_stmt, "select ... from ... where ...;\0", ...);
EXECSQL PREPARE ARCH FROM :select_stmt; EXEC SQL DECLARE SEL_CURS CURSOR FROM ARCH; EXEC SQL OPEN SEL_CURS; EXEC SQL
WHENEVERNOT 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
EXECUTEDEL2; 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;
EXECSQL 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
FROMARCH; 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; ...
EXECSQL WHENVER NOT FOUND goto somewhere; sprintf((char *) del2_stmt, "delete from %s where %s;\0", from, where); EXEC
SQLPREPARE 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

Re: [ECPG] Multiple cursors in the same transaction

From

Bruce Momjian

Date:

07 June 2004, 13:53:19

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,
Pennsylvania19073

Re: [ECPG] Multiple cursors in the same transaction

From

"Stergios Zissakis"

Date:

07 June 2004, 14:16:34

WITH HOLD worked just fine.

Thanks a lot.


-----Original Message-----
From: pgsql-interfaces-owner@postgresql.org
[mailto:pgsql-interfaces-owner@postgresql.org]On Behalf Of Bruce Momjian
Sent: Monday, June 07, 2004 4:50 PM
To: Stergios Zissakis
Cc: Interfaces; Kostas Lykiardopoulos; Dimitris Pantermalis
Subject: Re: [INTERFACES] [ECPG] Multiple cursors in the same transaction



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,
Pennsylvania19073
 

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command   (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)