Solved

Pro*C - Oracle 8i - EXEC SQL SELECT fun and formating

Posted on 2002-04-30
8
596 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:Adam Leinss
  • 3
  • 3
  • 2
8 Comments
 
LVL 22

Author Comment

by:Adam Leinss
ID: 6982387
Another thing, it seems that the break commands in the above code is causing problems such as compiling errors such as "break" outside of for or switch statement whenever I try to add additional code to other functions...grrr
0
 
LVL 5

Accepted Solution

by:
sora earned 200 total points
ID: 6982584
Try this. I just increased the sizes of all the char variables by 1 to accomodate the null character \0 as well. see if this works...


void print_table() {

EXEC SQL BEGIN DECLARE SECTION;

char db_name[21];
char db_address[31];
char db_id[10];
char 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\tAddress\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;
          printf("%s%s%s%s%i\n",db_name,db_address,db_id,db_class,db_overdue);
     }

     EXEC SQL CLOSE member_cursor;
     EXEC SQL COMMIT WORK RELEASE;
     return;
}




sora
0
 
LVL 5

Expert Comment

by:sora
ID: 6982599
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\tAddress\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",(char *)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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 7

Expert Comment

by:waynezhu
ID: 6982617
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,db_id,db_class,db_overdue);

printf("%-20s %30.30s %9.9s %1.1s %i\n",db_name,db_address,db_id,db_class,db_overdue);

.....

Good luck.

PS. sora's sugguestion should be also work.
0
 
LVL 5

Expert Comment

by:sora
ID: 6982618
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
0
 
LVL 7

Expert Comment

by:waynezhu
ID: 6982623
The example below may be helpful for title format:

printf("%-10.10s%s\n", "Employee", "Salary");
printf("%-10.10s%s\n", "--------", "------");
0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 6982661
Thanks guys, I'll give these a shot in a day or two.
0
 
LVL 22

Author Comment

by:Adam Leinss
ID: 6984990
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question