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

Bug #504: multiple cursors cause transaction problems

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #504: multiple cursors cause transaction problems
Date: 2001-10-30 19:53:21
Message-ID: 200110301953.f9UJrLg23535@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Tony Griffiths (griffitt(at)cs(dot)man(dot)ac(dot)uk) reports a bug with a severity of 1
The lower the number the more severe it is.

Short Description
multiple cursors cause transaction problems

Long Description
I'm trying to query several tables with different cursors within a c++ program. I use ecpg -t to compile the code (see example below). When I run the code I can open anf fetch the first cursor,. However after the first cursor is exhausted (i.e.,sqlca.sqlcode == ECPG_NOT_FOUND) and the loop finished, any attempt to open a new cursor gets the following error:

-601: current transaction is aborted, queries ignored until end of transact

For each subsequent attempt to open another cursor the same error occurs.

I'm using postgreSQL version 7.1.3, built from source code using gcc3.0

I'm unsure whether this is a bug, but cannot find a solution.



Sample Code
  EXEC SQL BEGIN TRANSACTION;

  ::strcpy(my_defined_in, this->getOID()->toString());

  // Classes
  EXEC SQL DECLARE class_curs CURSOR FOR SELECT objectIdentifier, trim(both ' ' from owningObject), isowned, ispersisted, persistence_capable, has_extent, extent_name, name, comment FROM d_Class WHERE defined_in = :my_defined_in;
  EXEC SQL OPEN class_curs;
  for(;;) {
    EXEC SQL FETCH class_curs into :objectIdentifier, :owningObject :owningObject_ind, :isowned, :ispersisted, :persistence_capable, :has_extent, :extent_name :extent_name_ind, :name, :comment;

    if(sqlca.sqlcode == ECPG_NOT_FOUND) {
      cerr << sqlca.sqlcode << ": " << sqlca.sqlerrm.sqlerrmc << endl;
      EXEC SQL CLOSE class_curs;
      break;
    }
    // process information here
  }

  // Structures
  EXEC SQL DECLARE struct_curs CURSOR FOR SELECT objectIdentifier, trim(both ' ' from owningObject), isowned, ispersisted, name, comment FROM d_Structure_Type WHERE defined_in = :my_defined_in;
  EXEC SQL OPEN struct_curs;
  for(;;) {
    EXEC SQL FETCH struct_curs into :objectIdentifier, :owningObject :owningObject_ind, :isowned, :ispersisted, :name, :comment;
    if(sqlca.sqlcode == ECPG_NOT_FOUND){
      cerr << sqlca.sqlcode << ": " << sqlca.sqlerrm.sqlerrmc << endl;
      EXEC SQL CLOSE struct_curs;
      break;
    }
    // process information here
  }

  cerr << "2" << endl;

  // References
  EXEC SQL DECLARE ref_curs CURSOR FOR SELECT objectidentifier, trim(both ' ' from owningObject), isowned, ispersisted, ref_kind_, name, comment, references_collection_type_, references_keyed_coll_type_, references_ref_type_, references_class_type_, references_primitive_type_, references_alias_type_, references_structure_type_, references_enumeration_type_ FROM d_Ref_Type WHERE defined_in = :my_defined_in;
  EXEC SQL OPEN ref_curs;
  for(;;) {
    EXEC SQL FETCH refs_curs into :objectIdentifier, :owningObject :owningObject_ind, :isowned, :ispersisted, :ref_kind_, :name, :comment, :references_collection_type_ :references_collection_type_ind, :references_keyed_coll_type_ :references_keyed_coll_type_ind, :references_ref_type_ :references_ref_type_ind, :references_class_type_ :references_class_type_ind, :references_primitive_type_ :references_primitive_type_ind, :references_alias_type_ :references_alias_type_ind, :references_structure_type_ :references_structure_type_ind, :references_enumeration_type_ :references_enumeration_type_ind;

    if(sqlca.sqlcode == ECPG_NOT_FOUND){
      cerr << sqlca.sqlcode << ": " << sqlca.sqlerrm.sqlerrmc << endl;
      EXEC SQL CLOSE ref_curs;
      break;
    }
    // process information here
  }

  EXEC SQL END TRANSACTION;

No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: pgsql-bugsDate: 2001-10-30 20:00:21
Subject: Bug #505: egpg crashes when using indicator array variables
Previous:From: pgsql-bugsDate: 2001-10-30 19:00:15
Subject: Bug #503: case and LIMIT not working together

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