CHAPTER 3: SQL Precompiler Example Program

The following C program demonstrates the form of a program using embedded Empress SQL statements. It illustrates some of the commands and concepts involved. The program accesses the test database repairs and should be invoked from the directory containing this database.

Note

This program must be compiled with the -ptr option.

   # include <mscc.h>
   
   # define       DOLLSIZE    25
   
   extern         double      dollcvt ();
   
   EXEC SQL INCLUDE SQLCA;
   
   EXEC SQL BEGIN DECLARE SECTION;
        char      name[64], date[10], amount[10];
        char      newamount[10];
   EXEC SQL END DECLARE SECTION;
   
   main ()
   {
        double     sum, dollars, interest, total;
   
        EXEC SQL INIT;
        err_check( SQLCODE, "init" );
   
        EXEC SQL DATABASE IS "repairs";
        err_check( SQLCODE, "database is" );
   
        EXEC SQL OPEN_TABLE "loans" FOR UPDATE;
        err_check( SQLCODE, "open_table" );
   
        EXEC SQL DECLARE pers_name CURSOR
            FOR SELECT name FROM personnel;
        err_check( SQLCODE, "declare pers_name" );
   
        EXEC SQL OPEN pers_name;
        err_check( SQLCODE, "open pers_name" );
   
        EXEC SQL FETCH pers_name name INTO :name;
        err_check( SQLCODE, "fetch pers_name" );
   
        EXEC SQL DECLARE date_amount CURSOR
            FOR SELECT date, amount FROM loans
            WHERE name = :name FOR UPDATE;
        err_check( SQLCODE, "declare date_amount" );
   
        while (SQLCODE != 100)
        {
             printf ("()Monthly Statement for %s:()", name );
             printf (" Loan    Date Made    Interest Total()" );
   
             sum = -1;
   
             EXEC SQL OPEN date_amount;
             err_check( SQLCODE, "open date_amount" );
   
             EXEC SQL FETCH date_amount date, amount
                  INTO :date, :amount;
             err_check( SQLCODE, "fetch date_amount" );
   
             while ( SQLCODE != 100 )
             {
                  dollars = dollcvt(amount);
                  interest = dollars * 0.02;
                  total = dollars + interest;
                  sum += total;
   
                  sprintf (newamount, "%6.2f", total);
   
                  printf ("$%-6.2f     %s      $%-6.2f     $%s()",
                     dollars, date, interest, newamount);
   
                  EXEC SQL UPDATE loans
                  SET amount = :newamount
                  WHERE CURRENT OF date_amount;
   
                  if (SQLCODE == -8)
                       fprintf (stderr, "Conversion error:
                          cannot convert '%f'
                          for 'amount'()", newamount);
   
                  EXEC SQL FETCH date_amount date, amount
                  INTO :date, :amount;
                  err_check( SQLCODE, "fetch date_amount" );
             }
             EXEC SQL CLOSE date_amount;
             err_check( SQLCODE, "close date_amount" );
             if (sum > 0)
                 printf ("()Total now owing: $%-6.2f ()", sum);
             else
                 printf ("()No loans outstanding.()");
   
             EXEC SQL FETCH pers_name name
                 INTO :name;
             err_check( SQLCODE, "fetch pers_name" );
        }
        EXEC SQL CLOSE pers_name;
        err_check( SQLCODE, "close pers_name" );
   
        EXEC SQL CLOSE_TABLE loans, personnel;
        err_check( SQLCODE, "close_table" );
   
        EXEC SQL EXIT;
   }

   double dollcvt (string)
   
        char      *string;
   
   {
        extern      double     atof ();
        char     c, buffer [DOLLSIZE], *buf_ptr;
   
        for (buf_ptr = buffer; (c = *string++)
                                != '\0';)
             if (c != '$' &&
                c != '*' &&
                c != ' ' &&
                c != ',')
             *buf_ptr++ = c;
        *buf_ptr = '\0';
        return (atof (buffer));
   }
   
   err_check( sqlcode, sql_string )
   char  *sql_string;
   int     sqlcode;
   {
        if ( sqlcode != 0 && sqlcode != 100)
        {
             fprintf( stderr, "SQL error, code %d, in line
                  'exec sql ... %s ' n",sqlcode,
                  sql_string);
             EXEC SQL EXIT;
         }
   }