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.
Even before fetching the values into sFirstName, you print them. To start with, as you didn't initialise it, it prints garbage value. Also if you feel the cursor fetch is interrupted, before *break*ing the LOOP, use sqlca's sqlerrm to print the error message. Like sqlca.sqlerrm.sqlerrmc
Then, your OPEN CURSOR call would fail, since the query has syntax errors.
So, you need to either modify the cursor as below, or modify the query correctly.
We have to check the status of the OPEN cursor, before proceeding, else FETCH would again fail, and the results may be unpredictable. So, please check the sqlca.sqlcode after every EXEC SQL call.
Also, we need to handle NULL, we can use NVL() in queries, if Indicator variables are not used
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;
Non aggregated columns can be used only with aggregate functions. So either Remove the Grouping or add MAX() to other columns.
Add the below to your declarations
int temp_sales_id = -999;
int first_iter = 1;
int total_nQuantity = 0;
float total_nUnitPrice = 0.0;
Then,
exec sql open dbGuest;
/* Lets check the status of the OPEN statement before proceeding , else exceptions would be suppressed */
if(sqlca.sqlcode !=0) // If anything went wrong or we read past eof, stop the loop
{
printf("Error while opening Cursor <%d><%s>\n",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
break;
}
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;
/* Check for No DATA FOUND */
if(sqlca.sqlcode == 100 || sqlca.sqlcode == 1403) // If anything went wrong or we read past eof, stop the loop
{
printf("CURSOR is empty after all fetch");
break;
}
/* Check for other errors */
else if(sqlca.sqlcode != 0)
{
printf("Error while fetching from Cursor <%d><%s>\n",sqlca.sqlcode,sqlca.sqlerrm.sqlerrmc);
break;
}
if(first_iter) {
printf("%s %s %s %s %d\n","Charge Summary for:", sFirstName.arr, sLastName.arr, " Guest_ID:", nGuest_ID);
first_iter = 0;
}
// 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;
if(temp_sales_id == -999 || temp_sales_id != nItemID)
{
/* First Item or Sales Item has Changed (next sales id)*/
temp_sales_id = nItemID;
// 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);
total_nQuantity += nQuantity;
total_nUnitPrice += nUnitPrice;
}
if (temp_sales_id != nItemID) {
/* Printing total for Current Sale id */
/* If you want to Sum all the sale id together take this finally */
printf("Total Quantity <%d> Total Extended Price <%g>\n",total_nQuantity,total_nUnitPrice);
total_nUnitPrice = 0;
total_nQuantity = 0;
}
if(temp_sales_id == -999 || temp_sales_id == nItemID) {
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 ;