title: Oracle Pro*C -- using a cursor in nested loop
body: <p>I'm teaching myself Pro*C and have a program that goes (supposedly) through records in a database with a cursor, and it compiles and runs.  The problem is that I'm getting whatever junk was in memory when the variables print (those read in using the cursor).</p>
  
  <p>I tried splitting up the <em>sql exec</em> statements several different ways but this didn't help.  Also tried opening and closing the sql at different places, also unhelpful.  I'm really at the end of a long process of debugging and I'm quite sure at this point that I'm making an extremely newbie mistake.  If any Oracle programmers here wouldn't mind taking a second, I'd really like a little feedback on how to get back on track here.</p>
  
  <p>It's supposed to print:</p>
  
  <pre><code>Enter a Guest_ID(type 0 to terminate)&gt;&gt;
  
  1
  
  Charge Summary for: Firstname Lastname Guest-ID: 1
  
  Sales_Item: 1 – Room (Taxable)
  
  Hotel-Id Hotel-Name Trans-Date Quantity Unit-Price Extended-Price
  
  Hotel-Id Hotel-Name Trans-Date Quantity Unit-Price Extended-Price
  
  Hotel-Id Hotel-Name Trans-Date Quantity Unit-Price Extended-Price
  
  Sales Item Total Quantity Extended-Price
  </code></pre>
  
  <p>It actually prints:</p>
  
  <pre><code>Enter a Guest_ID(type 0 to terminate)&gt;&gt;
  
  3
  
  Charge Summary for: l▒   Guest_ID: 3
  </code></pre>
  
  <p>I feel like I'm completely messing up the cursor, but I can't pinpoint exactly where the problem is because I'm still getting used to how the variables are declared and used in Pro*C.  Also, C programs are usually debugged but this is run on a remote server and debugging is very limited, don't even have dbx commands.</p>
  
  <p>The code:</p>
  
  <pre><code>#include&lt;stdio.h&gt;
  #include&lt;string.h&gt;
  #include&lt;stdlib.h&gt;
  exec sql include sqlca;
  
  // OK - Here we GO
  void main()
  {
      // First, create all the variables that we will need to communicate between 
      // the "C" program and the database
      exec sql begin declare section;
          //VARCHAR sLastName[51], sFirstName[51], sHotelName[51], sCheckInDate[12], sRoom[11];
          VARCHAR sLastName[51], sFirstName[51], sHotelName[51], sTransDate[11];
          //int nDays, nGuest_ID, nCount;
          int nGuest_ID, nQuantity, nUnitPrice, nCount, nHotelID, nItemID;
          //VARCHAR sInCity[11];
          VARCHAR sItemName[31], sTaxable[11];
          VARCHAR sUserID[21], sPassword[21];
      exec sql end declare section;
  
          // Now define the cursor we will use to get all of the charges that the guest incurred at all hotels
      exec sql declare dbGuest cursor for
          Select G.Guest_ID, G.Last_Name, G.First_Name, C.Item_ID, C.Item_Name, C.Quantity, C.Unit_Price, C.Trans_Date, H.Hotel_Name, H.Hotel_ID, SI.Taxable
          From Hotel H, Charge C, Stay S, Guest G, Sales_Item SI Where
          C.Stay_ID=S.Stay_ID And H.Hotel_ID=S.Hotel_ID And G.Guest_ID=S.Guest_ID
              And SI.Item_ID=C.Item_ID
          Group By S.Guest_ID;
  
      // Set up the user-id and password to access my database
      // Because we are using the local database on this server
      // we don't need to use any database location or SID
      strcpy(sUserID.arr,"myuserid"); 
      strcpy(sPassword.arr,"mypassword");  
      sUserID.len=strlen(sUserID.arr);
      sPassword.len=strlen(sPassword.arr);
      exec sql connect :sUserID identified by :sPassword;
  
      // sqlca.sqlcode is a variable that is set based on the last command sent in to the database
      // a value anything other than zero for what we just did (connect to the database) indicates
      // a error.
      if(sqlca.sqlcode !=0)
         {
          //printf("Sorry, cannot connect to server, pgm aborted %s\n",sqlca.sqlcode); //correction 2/5/14
          printf("Sorry, cannot connect to server, pgm aborted %d\n",sqlca.sqlcode); //change to %d
          exit(1);
         }
      //we made it here, so we were able to open the database correctly
      exec sql SELECT COUNT(*) INTO :nCount FROM Guest;
      printf ("There are %d Guests.\n",nCount);
      for(;;){
          // Read in through stdio the Guest we want to query, then set it up do we can use it
          printf("Enter a Guest_ID(type 0 to terminate)&gt;&gt;\n");
          scanf("%d",&amp;nGuest_ID);
          //Guest_ID.len= strlen(Guest_ID.arr);
          if(nGuest_ID==0)
          {
              printf("BYE\n");
              exit(0);
          }
          printf("%s %s %s %s %d\n","Charge Summary for:", sFirstName.arr, sLastName.arr, " Guest_ID:", nGuest_ID);
          //printf("I do not work yet (type exit to terminate)&gt;&gt;\n");
                  // Open our cursor and begin reading records
          exec sql open dbGuest;
          for(;;)
          {
              //exec sql fetch dbGuest into :nGuest_ID, :sLastName, :sFirstName, :sHotelName, :sCheckInDate, :nDays, :sRoom;
              exec sql fetch dbGuest into :sLastName, :sFirstName, :nItemID, :sItemName, :nQuantity, :nUnitPrice, :sTransDate, :sHotelName, :nHotelID;
              if(sqlca.sqlcode !=0)  // If anything went wrong or we read past eof, stop the loop
              {
                  break;
              }
              printf("%s %s %s %s %d\n","Charge Summary for:", sFirstName.arr, sLastName.arr, " Guest_ID:", nGuest_ID);
              // Do the crazy stuff to end the C-Strings
              sLastName.arr[sLastName.len] = 0;
              sFirstName.arr[sFirstName.len] = 0;
              sItemName.arr[sItemName.len] = 0;
              sTransDate.arr[sTransDate.len] = 0;
              sHotelName.arr[sHotelName.len] = 0;
  
              // Print out the information for this guest
              printf("%s %d %s %s \n", "Sales_Item: ", nItemID, " - ", sItemName.arr);
  
              printf("%d %s %s %d %d \n", nHotelID, " ", sHotelName.arr, " ",sTransDate.arr, " ", nQuantity, " ", nUnitPrice);
          }
          // close the cursor and end the program
          exec sql close dbGuest ;
      }
      exit(0);
  }
  </code></pre>
  
  <p>Normally C programs would be run in debuggers but this is Pro<em>C and I'm kind of lost with the whole Oracle Pro</em>C debugging thing (since it's running on a remote database).</p>
  
  <p>Went through these but not helpful:</p>
  
  <p><a href="https://stackoverflow.com/questions/12384100/strange-behaviours-with-oracle-nested-cursors">Strange behaviours with oracle nested cursors</a></p>
  
  <p><a href="https://stackoverflow.com/questions/12711271/oracle-proc-insert-into-values-select">Oracle ProC INSERT INTO VALUES ( (select ...) )</a></p>
  
  <p><a href="https://stackoverflow.com/questions/10962482/oracle-proc-updating-table-with-cursor-failed?rq=1">Oracle Pro*C updating table with cursor failed</a></p>
  
  <p>I've been told that the VARCHAR variables should be declared differently, but other ways seem to throw errors.</p>