Adam Leinss
asked on
Pro*C - Oracle 8i - EXEC SQL SELECT fun and formating
I have the following Pro*C code here: http://stealth.kirenet.com/~aleinss/menu.pc My database tables and data are here: http://stealth.kirenet.com/~aleinss/create.sql I'm trying to implement a function (print_table()) that prints out a table, that's it. Simple right? Wrong! Well, it works, sort of:
Name Address ID Class # of Overdue Materials
------ --------- ---------- --- -----
Betty White 555 N. Old Fart St. 999067540
Adam Leinss 4971 N. 107th St. 999967260
Paul McNally 557 N. Database Dr. 999008940
...
The problem is that the class and # over overdue materials get completely dropped (i.e. not printed to the screen) and I have no idea why??? I thought it was printing out the class earlier (which is either the letters O, F or S), but now it doesn't print either the class or the # of overdue materials.
Also, is there an easy(ier) of formatting the titles on this report? I'm having a dickens of a time matching it up with the data.
Thanks.
Name Address ID Class # of Overdue Materials
------ --------- ---------- --- -----
Betty White 555 N. Old Fart St. 999067540
Adam Leinss 4971 N. 107th St. 999967260
Paul McNally 557 N. Database Dr. 999008940
...
The problem is that the class and # over overdue materials get completely dropped (i.e. not printed to the screen) and I have no idea why??? I thought it was printing out the class earlier (which is either the letters O, F or S), but now it doesn't print either the class or the # of overdue materials.
Also, is there an easy(ier) of formatting the titles on this report? I'm having a dickens of a time matching it up with the data.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
aleinss
One more point, normally for Pro*C, I use VARCHAR datatypes to map to character datatypes. So if the above does not work, try this also. Below, I've changed the char to varchar. Note that with varchar you have to set the length of the variable after you fetch the value into it.
Try below:
void print_table() {
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR db_name[21];
VARCHAR db_address[31];
VARCHAR db_id[10];
VARCHAR db_class[2];
int db_overdue=0;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE member_cursor CURSOR FOR
SELECT *
FROM MEMBER;
EXEC SQL OPEN member_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
printf("\nLibrary Member List\n");
printf("\n\nName\t\tAddres s\t\t\tID\ tClass\t# of Overdue Materials\n");
printf("------\t---------\ t\t------- ---\t---\t -----\n");
for (;;) {
EXEC SQL FETCH member_cursor INTO :db_name, :db_address, :db_id, :db_class,
:db_overdue;
/**sora has added this bit here***/
db_name.len = strlen((char *)db_name.arr);
db_id.len = strlen((char *)db_id.arr);
db_address.len = strlen((char *)db_address.arr);
db_class.len = strlen((char *)db_class.arr);
/***also added some type casting here*****/
printf("%s%s%s%s%i\n",(cha r *)db_name.arr,(char *)db_address.arr ,(char *)db_id.arr , (char *)db_class.arr , db_overdue.arr );
}
EXEC SQL CLOSE member_cursor;
EXEC SQL COMMIT WORK RELEASE;
return;
}
sora
One more point, normally for Pro*C, I use VARCHAR datatypes to map to character datatypes. So if the above does not work, try this also. Below, I've changed the char to varchar. Note that with varchar you have to set the length of the variable after you fetch the value into it.
Try below:
void print_table() {
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR db_name[21];
VARCHAR db_address[31];
VARCHAR db_id[10];
VARCHAR db_class[2];
int db_overdue=0;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE member_cursor CURSOR FOR
SELECT *
FROM MEMBER;
EXEC SQL OPEN member_cursor;
EXEC SQL WHENEVER NOT FOUND DO break;
printf("\nLibrary Member List\n");
printf("\n\nName\t\tAddres
printf("------\t---------\
for (;;) {
EXEC SQL FETCH member_cursor INTO :db_name, :db_address, :db_id, :db_class,
:db_overdue;
/**sora has added this bit here***/
db_name.len = strlen((char *)db_name.arr);
db_id.len = strlen((char *)db_id.arr);
db_address.len = strlen((char *)db_address.arr);
db_class.len = strlen((char *)db_class.arr);
/***also added some type casting here*****/
printf("%s%s%s%s%i\n",(cha
}
EXEC SQL CLOSE member_cursor;
EXEC SQL COMMIT WORK RELEASE;
return;
}
sora
The following examples should correct the problem,
meanwhile providing a reference how to handle string
format: such as %m.ns, %-n.ms, %+n.ms, %0n.ms, ...
where m, n are numbers for width and precision. Find a C book (for example C&R) and read the section on printf.
printf("%20.20s %30.30s %9.9s %1.1s %i\n",db_name,db_address,d b_id,db_cl ass,db_ove rdue);
printf("%-20s %30.30s %9.9s %1.1s %i\n",db_name,db_address,d b_id,db_cl ass,db_ove rdue);
.....
Good luck.
PS. sora's sugguestion should be also work.
meanwhile providing a reference how to handle string
format: such as %m.ns, %-n.ms, %+n.ms, %0n.ms, ...
where m, n are numbers for width and precision. Find a C book (for example C&R) and read the section on printf.
printf("%20.20s %30.30s %9.9s %1.1s %i\n",db_name,db_address,d
printf("%-20s %30.30s %9.9s %1.1s %i\n",db_name,db_address,d
.....
Good luck.
PS. sora's sugguestion should be also work.
For proper titling you can do the following
right pad all your field values to the maximum length of the column and do the same for the headings as well.
Alternatively, you can put tabs in between the field values when you printed them. I noticed that you have tabs in the printf statement for the title/header but no tabs for the field values.
In the above code (from my previous post), replace the printf statement for the field values with this one and see if it looks any better
printf("%s\t\t%s\t\t\t%s\t %s\t%i\n", (char *)db_name.arr,(char *)db_address.arr ,(char *)db_id.arr , (char
*)db_class.arr , db_overdue.arr );
sora
right pad all your field values to the maximum length of the column and do the same for the headings as well.
Alternatively, you can put tabs in between the field values when you printed them. I noticed that you have tabs in the printf statement for the title/header but no tabs for the field values.
In the above code (from my previous post), replace the printf statement for the field values with this one and see if it looks any better
printf("%s\t\t%s\t\t\t%s\t
*)db_class.arr , db_overdue.arr );
sora
The example below may be helpful for title format:
printf("%-10.10s%s\n", "Employee", "Salary");
printf("%-10.10s%s\n", "--------", "------");
printf("%-10.10s%s\n", "Employee", "Salary");
printf("%-10.10s%s\n", "--------", "------");
ASKER
Thanks guys, I'll give these a shot in a day or two.
ASKER
Duh...that was too simple...it was the null thing...geese, I'm dense.
In terms of the titling, I just spaced it out by hand.
I was putting " " in the printf statement instead of just using regular spaces.
Thank you to everyone for you help.
In terms of the titling, I just spaced it out by hand.
I was putting " " in the printf statement instead of just using regular spaces.
Thank you to everyone for you help.
ASKER