Bug #504: multiple cursors cause transaction problems - Mailing list pgsql-bugs

Tony Griffiths (griffitt@cs.man.ac.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
examplebelow). 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