invalid cursor problem with Pro*C++

From: Paul Wagner <wagner_at_mega.cs.umn.edu>
Date: 14 Apr 2000 13:45:41 GMT
Message-ID: <8d77e5$rvm$1_at_news1.tc.umn.edu>

[Quoted] I'm having a problem with transactions and cursors. I'm using Pro*C/C++ in a C++ program under Oracle 8.1.5.0.1 on Linux. I'm testing a program from Raj Sunderraman's book on Oracle 8 programming, which basically has a double nested while loop with fetches from two different tables at the two while loop levels, and then does an insert into another table at the inner level based on the row combinations from the two levels. I can get it to work if I allow the whole program to be one transaction, but if I try to make each insert be its own transaction, I get a -1001 - invalid cursor error. Any ideas on what the problem might be is much appreciated.

I've put the major parts of the code below, and show which lines are causing the invalid cursor error.

Thanks for any and all help,

Paul

---
(works with two commented code lines removed, generates invalid cursor -1001
 error with these lines uncommented)
...


   // --- Declare cursors


   EXEC SQL declare analyst_cur cursor for
            select aid
            from   analyst;

   EXEC SQL declare security_cur cursor for
            select symbol
            from   security;



   // --- Open and process cursors


   EXEC SQL open analyst_cur;
   EXEC SQL fetch analyst_cur
            into  :analyst_id indicator :analyst_id_ind;

   while (sqlca.sqlcode == 0)
   {
      total_analysts++;
      EXEC SQL open security_cur;
      EXEC SQL fetch security_cur
               into  :ssymbol indicator :ssymbol_ind;

      while (sqlca.sqlcode == 0)
      {
         total_securities++;
//         EXEC SQL set transaction read write;
         rating = rand() % 5 + 1;       // generated rating is 1 to 5
         EXEC SQL insert into rating
                  values (:analyst_id, :ssymbol, :rating);
         if (sqlca.sqlcode != 0)
         {
            cout << " Error while inserting rating for "
                 << analyst_id.arr << " symbol " << ssymbol.arr << endl;
            EXEC SQL rollback release;
         }
         else     // success
         {
            cout << " Analyst " << analyst_id.arr
                 << " rated " << ssymbol.arr
                 << " as " << rating << endl;
            total_ratings_ins++;
//            EXEC SQL commit work;
         }
         EXEC SQL fetch security_cur
                  into  :ssymbol indicator :ssymbol_ind;
      }   // end - while - securities
      EXEC SQL close security_cur;
      EXEC SQL fetch analyst_cur
               into :analyst_id indicator :analyst_id_ind;
   }
   cout << "sqlca.sqlcode is: " << sqlca.sqlcode << endl;



   // --- Cursor cleanup


   EXEC SQL close analyst_cur;



   // --- Disconnect from Oracle


   EXEC SQL commit work release;
   ...
--
*    *    *    *    *    *    *    *    *    *    *    *    *    *    *    *
* Paul J. Wagner                School   - wagner_at_cs.umn.edu               *
* Computer Science Department   Work     - wagnerp_at_uwstout.edu             *
* University of Minnesota                                                  *

Received on Fri Apr 14 2000 - 15:45:41 CEST