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).
I tried splitting up the sql exec 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.
It's supposed to print:
Enter a Guest_ID(type 0 to terminate)>>
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
It actually prints:
Enter a Guest_ID(type 0 to terminate)>>
3
Charge Summary for: l▒ Guest_ID: 3
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.
The code:
#include<stdio.h>
#include<string.h>
#include<stdlib.h>
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)>>\n");
scanf("%d",&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)>>\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);
}
Normally C programs would be run in debuggers but this is ProC and I'm kind of lost with the whole Oracle ProC debugging thing (since it's running on a remote database).
Went through these but not helpful:
Strange behaviours with oracle nested cursors
Oracle ProC INSERT INTO VALUES ( (select ...) )
Oracle Pro*C updating table with cursor failed
I've been told that the VARCHAR variables should be declared differently, but other ways seem to throw errors.
