Skip site navigation (1) Skip section navigation (2)

[ECPG] Multiple cursors in the same transaction

From: "Stergios Zissakis" <szis(at)intranet(dot)gr>
To: "Interfaces" <pgsql-interfaces(at)postgresql(dot)org>
Cc: "Kostas Lykiardopoulos" <klyk(at)intranet(dot)gr>,"Dimitris Pantermalis" <dpant(at)intranet(dot)gr>
Subject: [ECPG] Multiple cursors in the same transaction
Date: 2004-06-07 13:36:29
Message-ID: NGBBJHBHDKPNOODGDMMAOECMCIAA.szis@intranet.gr (view raw or flat)
Thread:
Lists: pgsql-interfaces
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


Responses

pgsql-interfaces by date

Next:From: Bruce MomjianDate: 2004-06-07 13:50:29
Subject: Re: [ECPG] Multiple cursors in the same transaction
Previous:From: SenthilDate: 2004-06-07 09:56:15
Subject: A doubt in pgtcl

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group