Problems with mixing cursors... (sample code included)

From: Garry Taylor <spidey_at_metronet.com>
Date: Tue, 28 Feb 1995 21:47:01 GMT
Message-ID: <D4qCIE.27p_at_metronet.com>

I am new to Pro*C (though not SQL) and am trying to write code which will allow me to have more than one cursor active. (The code will be used in a library which allows calling programs to access the DB without having to know the dirty details.) For Watcom, DB2, and Sybase this has not been a problem, but I cannot seem to get it going in Oracle.

I have a sample piece of code below which I thought should work, but whenever it runs, the fetch against cursor 1 returns data from cursor 2. An Oracle support person has been looking at this for several days now, but has yet to provide me with an answer. I'm hoping that perhaps one of you can.

Any help at all is greatly appreciated!

                                   Thanks,
                                   Garry Taylor
                                   spidey_at_metronet.com
------------------

Version levels:

   Oracle V6.0.36.2.1
   Pro*C V 1.4.8.2.2

---

Source code:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL include sqlca;

 EXEC SQL BEGIN DECLARE SECTION;

 char *      current_db="T:ncr2:SID";
 char        name[30];
 char *      db_name;
 char *      sql_statement;
 char *      userid="bob";
 char *      password="hope";

 EXEC SQL END DECLARE SECTION; int do_sql( char *user_sql);
void do_fetch( int cursor);
void do_close( int cursor);
void whoops();

main()
{

 int hstmt1, hstmt2;

 EXEC SQL DECLARE db1 DATABASE;
 EXEC SQL DECLARE db2 DATABASE;

 EXEC SQL WHENEVER SQLERROR DO whoops();

 EXEC SQL CONNECT :userid IDENTIFIED by :password AT db1 USING :current_db;  EXEC SQL CONNECT :userid IDENTIFIED by :password AT db2 USING :current_db;

 db_name = malloc( 10);

 sql_statement = malloc( 200);

 strcpy( db_name, "db1");
 hstmt1 = do_sql( "Select TABLE_NAME from ALL_TABLES");

 strcpy( db_name, "db2");
 hstmt2 = do_sql( "Select OWNER from ALL_TABLES");

 do_fetch( hstmt1);

 do_fetch( hstmt2);

 do_close( hstmt1);
 do_close( hstmt2);
 free( sql_statement);
}

int do_sql( char *user_sql)
{

 static int cursor_number = 1;

 strcpy( sql_statement, user_sql);
 printf( "Defining cursor%d\n", cursor_number);  switch (cursor_number)

     {
     case 1:
         EXEC SQL at :db_name  DECLARE STATEMENT1 STATEMENT; 
         EXEC SQL PREPARE STATEMENT1 from :sql_statement; 

         printf( "Declaring cursor 1 at '%s'\n", db_name);
         EXEC SQL DECLARE CURSOR1 CURSOR For STATEMENT1;
         EXEC SQL OPEN CURSOR1;
         break;

     case 2:
         EXEC SQL at :db_name  DECLARE STATEMENT2 STATEMENT; 
         EXEC SQL PREPARE STATEMENT2 from :sql_statement; 

         printf( "Declaring cursor 2 at '%s'\n", db_name);
         EXEC SQL DECLARE CURSOR2 CURSOR For STATEMENT2;
         EXEC SQL OPEN CURSOR2;
         break;

    }

 return( cursor_number++);
}

void do_fetch( int cursor_number)
{

 memset( name, 0, sizeof(name));

 switch( cursor_number)

     {
     case 1:
         printf( "Fetching cursor 1\n");
         EXEC SQL FETCH CURSOR1 INTO :name;
         break;

     case 2:
         printf( "Fetching cursor 2\n");
         EXEC SQL FETCH CURSOR2 INTO :name;
         break;

    }

 printf( "The name read was '%s'\n", name); }

void do_close( int cursor_number)
{

 printf( "Closing cursor %d\n", cursor);  switch( cursor_number)

     {
     case 1:
         EXEC SQL CLOSE CURSOR1;
         break;

     case 2:
         EXEC SQL CLOSE CURSOR2;
         break;

    }

 EXEC SQL COMMIT WORK RELEASE;
}  

void whoops()
{

 char error_message[200];
 int size = sizeof(error_message);

 sqlglm( error_message, &size, &size);
 printf( " W H O O P S !!!: '%s'\n", error_message);

}

---

Output from program:

Defining cursor1
Declaring cursor 1 at 'db1'
Defining cursor2
Declaring cursor 2 at 'db2'
Fetching cursor 1

The name read was 'SYS                           '
Fetching cursor 2
The name read was 'SYS                           '

Closing cursor 1
 W H O O P S !!!: 'ORA-01012: Unable to open message file (SQL-02113).' Closing cursor 2
 W H O O P S !!!: 'ORA-01012: Unable to open message file (SQL-02113).' Received on Tue Feb 28 1995 - 22:47:01 CET