home *** CD-ROM | disk | FTP | other *** search
/ OS/2 Shareware BBS: Product / Product.zip / DBDEMO.ZIP / DEMODB.ZIP / D30ACBA4.SQC < prev    next >
Text File  |  1991-06-03  |  11KB  |  320 lines
  1.  
  2. /*  **********************************************************************   */
  3. /*                                                                           */
  4. /*  APPLICATION D30ACBA4.SQC - TEMPORARY FIX FOR SYMPHONY FILE IMPORT        */
  5. /*       CREATED WITH DEFAULT DATABASE PARAMETERS IN SYMPHONY                */
  6. /*                                                                           */
  7. /*  THIS PROGRAM IS BEING USED ON A TEMPORARY BASIS TO DELETE 9 ROWS FROM    */
  8. /*  THE BOX TABLE AND 5 ROWS FROM THE CUSTOMER TABLE.  THESE ROWS WERE       */
  9. /*  IMPORTED ALONG WITH ALL THE CORRECT DATA FROM SYMPHONY FILES. THE ROWS   */
  10. /*  BEING DELETED ARE NOT DATA ROWS BUT HEADER ROWS AND TRASH ROWS.          */
  11.  
  12. /*               W. BROWN  A10/814     10/15/87                              */
  13.  
  14. /*  ***********************************************************************  */
  15. /*                                                                           */
  16. /*   MODIFICATIONS:                                                          */
  17. /*       Date       Author                Change                             */
  18. /*                                                                           */
  19. /*     12/10/87     D.J.Grant          Added SQLEISIG function call, and     */
  20. /*                                     enhances exit_error to also print     */
  21. /*                                     SQLERRD[0] - [5] and SQL_ERRP.        */
  22. /*     02/03/89     W.B.Brown          upgraded to release 3.0               */
  23. /*                                                                           */
  24. /*  ***********************************************************************  */
  25.  
  26.  
  27. /*  Include library functions  ****       ********   PAGE 3     */
  28.  
  29.  
  30. #include <stdio.h>
  31. #include <string.h>
  32. #include <sql.h>
  33. #include <sqlenv.h>
  34. #include <sqlca.h>
  35. #include <stdlib.h>
  36.  
  37. struct sqlca sqlca;
  38.  
  39. unsigned char msg_buf[512];          /* SQL error message buffer             */
  40. unsigned char dbase[] = "BANK";
  41. unsigned char password[] = "";
  42. unsigned char errfile[] = "D30ACBA4.ERR";
  43.  
  44. /*   Declare rte_label - progress marker */
  45.  
  46.     unsigned char rte_label[16] = "               ";
  47.  
  48.  
  49. unsigned char  sqlerrp_value[9] = "        ";             /* error value  */
  50.  
  51. int errlevel = 0;
  52.  
  53.  
  54. /*   ***********  TABLES USED IN THIS PROGRAM    ******************          */
  55.  
  56. /*       CUSTOMER TABLE                         BOX TABLE
  57.  
  58.   CU_SSN       CHAR(11)   NOT NULL         B_NUMB     CHAR(3)  NOT NULL
  59.   CU_NAME      CHAR(30)   NOT NULL         B_SIZE     CHAR(1)  NOT NULL
  60.   CU_ADDR1     CHAR(25)   NOT NULL         B_RENT     DEC(3,2) NOT NULL
  61.   CU_ADDR2     CHAR(25)   NOT NULL         B_SSN      CHAR(11) NULLABLE
  62.   CU_ZIP       CHAR(5)    NOT NULL         B_PAYUP    CHAR(3)  NULLABLE
  63.  
  64. */
  65.  
  66. /*  SQL DECLARE SECTION - Host variables                                     */
  67.  
  68. EXEC SQL BEGIN DECLARE SECTION;
  69.  
  70.   unsigned char ssn[12];                     /*   Customer Table variables   */
  71.   short custcnt;
  72.  
  73.   unsigned char bnumb[4];                    /*   Box Table variables        */
  74.   short boxcnt;
  75.  
  76. EXEC SQL END DECLARE SECTION;
  77.  
  78. /*   END OF SQL DECLARE SECTION - Host variables                             */
  79.  
  80.  
  81.  
  82. /*  ** BEGIN MAIN FUNCTION  *******       ********              */
  83.  
  84. int main (void);
  85. main ()
  86. {
  87.  
  88.         FILE *ptr_errfile;
  89.  
  90.  
  91. /*   Declare a short integer to receive the return code message length-SQL   */
  92. /*   Declare a short integer to indicate dbase open(0) or failed (-1).       */
  93. /*   Declare a short integer to indicate error file open (-1) or closed (0)  */
  94.  
  95.     short rc = 0;                    /*  From sqlaintp - initialize to 0     */
  96.     short dbas_cls = 0;              /*  Initialize to open.                 */
  97.     short err_cls = 0;
  98.  
  99. /*                 *************************************                     */
  100. /*                                                                           */
  101. /*                         SQL EXCEPTION HANDLING                            */
  102. /*                                                                           */
  103. /*                 *************************************                     */
  104.  
  105. EXEC SQL
  106.      WHENEVER SQLERROR GOTO ret_code;
  107.  
  108. EXEC SQL
  109.      WHENEVER SQLWARNING CONTINUE;
  110.  
  111. EXEC SQL
  112.      WHENEVER NOT FOUND CONTINUE;
  113.  
  114. /*            ****************************************                       */
  115.  
  116.   strcpy(rte_label,"instal sqleisig");
  117.  
  118.   sqleisig(&sqlca);                             /* ctrl-break processing     */
  119.  
  120.   if (sqlca.sqlcode != 0)
  121.     {
  122.      goto ret_code;
  123.     }
  124.  
  125. /*            ****************************************                       */
  126.  
  127.  
  128. /*   *******************************************************************     */
  129. /*                                                                           */
  130. /*  Store the name of the routine in rte_label for error tracking            */
  131. /*  Start the database using the function "sqlestrd"; check for error on     */
  132. /*  start-database function.                                                 */
  133. /*                                                                           */
  134. /*                  ******************************                           */
  135. /*                                                                           */
  136.  
  137.     strcpy(rte_label,"start database ");
  138.  
  139. start_dbres:
  140.  
  141.     sqlestrd (dbase,password,'S',&sqlca);
  142.  
  143.     if (sqlca.sqlcode == -1015)                       /* If start db func    */
  144.     {                                                 /* fails, call restart */
  145.        strcpy(rte_label,"restart databas");           /* func.               */
  146.        sqlerest(dbase,password,&sqlca);               /* Check for failed    */
  147.          if (sqlca.sqlcode != 0)                      /* restart; on error   */
  148.             {                                         /* set dbase closed,   */
  149.             dbas_cls = -1;                            /* go to error exit.   */
  150.             goto ret_code;
  151.             }
  152.        strcpy(rte_label,"start database2");
  153.        sqlestrd (dbase,password,'S',&sqlca);          /* Good restart-start  */
  154.     }                                                 /* db again.           */
  155.  
  156.     if (sqlca.sqlcode != 0)                           /* Check 2nd start for */
  157.       {                                               /* failure; if failed, */
  158.       dbas_cls = -1;                                  /* set flag to closed. */
  159.       goto ret_code;                                  /* Also checks for any */
  160.       }                                               /* other error code on */
  161.                                                       /* 1st sqlestrd.       */
  162.  
  163. /*                  *****************************                            */
  164.  
  165.  
  166. /*                DELETE TRASH ROWS FROM CUSTOMER AND BOX TABLES             */
  167.  
  168.  
  169. strcpy (rte_label,"delt cust name ");
  170.  
  171.   EXEC SQL
  172.         DELETE FROM CUSTOMER
  173.         WHERE CU_SSN = 'Name       ';
  174.  
  175. strcpy (rte_label,"delt cust ssn  ");
  176.  
  177.   EXEC SQL
  178.         DELETE FROM CUSTOMER
  179.         WHERE CU_SSN = 'ssn        ';
  180.  
  181. strcpy (rte_label,"delete box Nam ");
  182.  
  183.   EXEC SQL
  184.         DELETE FROM BOX
  185.         WHERE B_NUMB = 'Nam';
  186.  
  187. strcpy (rte_label,"delete box BNU ");
  188.  
  189.   EXEC SQL
  190.         DELETE FROM BOX
  191.         WHERE B_NUMB = 'BNU';
  192.  
  193. strcpy (rte_label,"delete box BRE ");
  194.  
  195.   EXEC SQL
  196.         DELETE FROM BOX
  197.         WHERE B_NUMB = 'BSS';
  198.  
  199. strcpy (rte_label,"delete box BRE ");
  200.  
  201.   EXEC SQL
  202.         DELETE FROM BOX
  203.         WHERE B_NUMB = 'BRE';
  204.  
  205. strcpy (rte_label,"delete box BSI ");
  206.  
  207.   EXEC SQL
  208.         DELETE FROM BOX
  209.         WHERE B_NUMB = 'BSI';
  210.  
  211. strcpy (rte_label,"count cust rows");
  212.  
  213.   EXEC SQL
  214.         SELECT COUNT (*)
  215.         INTO :custcnt
  216.         FROM CUSTOMER;
  217.  
  218.   printf ("\nNUMBER OF CUSTOMER ROWS AFTER DELETE:   %d\n",custcnt);
  219.  
  220. strcpy (rte_label,"count box  rows");
  221.  
  222.   EXEC SQL
  223.         SELECT COUNT (*)
  224.         INTO :boxcnt
  225.         FROM BOX;
  226.  
  227.   printf ("\nNUMBER OF BOX ROWS AFTER DELETE:   %d\n",boxcnt);
  228.  
  229.   goto end;
  230.  
  231.  
  232.  
  233. /*                      *******************************                      */
  234. /*                                                                           */
  235. /*    EXIT ERROR ROUTINE - Log error information to screen or print file.    */
  236. /*                                                                           */
  237. /*                      *******************************                      */
  238.  
  239. ret_code:
  240.  
  241.     if ((ptr_errfile = fopen(errfile, "w")) == NULL)
  242.      {
  243.       printf ("\nUNABLE TO OPEN D30ACBA4.ERR\n");
  244.       printf ("%s\n",rte_label);
  245.       printf ("SQLCODE IS %ld\n",sqlca.sqlcode);
  246.       errlevel = 2;
  247.       goto continue_err;
  248.      }
  249.     fprintf(ptr_errfile,"%15s\n",rte_label);
  250.     fprintf(ptr_errfile,"SQLCODE IS %ld\n",sqlca.sqlcode);
  251.     errlevel = 2;
  252.  
  253.     fprintf(ptr_errfile,"\nSQLERRD[0] is %ld",sqlca.sqlerrd[0]);
  254.     fprintf(ptr_errfile,"\nSQLERRD[1] is %ld",sqlca.sqlerrd[1]);
  255.     fprintf(ptr_errfile,"\nSQLERRD[2] is %ld",sqlca.sqlerrd[2]);
  256.     fprintf(ptr_errfile,"\nSQLERRD[3] is %ld",sqlca.sqlerrd[3]);
  257.     fprintf(ptr_errfile,"\nSQLERRD[4] is %ld",sqlca.sqlerrd[4]);
  258.     fprintf(ptr_errfile,"\nSQLERRD[5] is %ld",sqlca.sqlerrd[5]);
  259.  
  260.     memcpy (sqlerrp_value, sqlca.sqlerrp, 8);
  261.     fprintf(ptr_errfile,"\nSQLERRP is %s",sqlerrp_value);
  262.  
  263.     rc = sqlaintp(msg_buf,512,0,&sqlca);
  264.     if (rc < 0)
  265.      {
  266.       fprintf (ptr_errfile, "SQLAINTP ERROR.  Return Code = %d",rc);
  267.      }
  268.     else if (rc > 0)
  269.      {
  270.       fprintf (ptr_errfile,"\n");
  271.       fprintf (ptr_errfile,msg_buf);
  272.      }
  273.     err_cls = -1;
  274.  
  275. continue_err:
  276.  
  277.     EXEC SQL
  278.             WHENEVER SQLERROR CONTINUE;
  279.  
  280.     EXEC SQL
  281.             ROLLBACK WORK;
  282.  
  283.     if (sqlca.sqlcode < 0)
  284.      {
  285.       if ((ptr_errfile = fopen(errfile,"a")) == NULL)
  286.        {
  287.         printf ("\nUNABLE TO OPEN D30ACBA4.ERR -- ROLLBACK FAILED");
  288.         goto end;
  289.        }
  290.       fprintf (ptr_errfile,"\nSQLCODE IS %ld,  ROLLBACK FAILED.",sqlca.sqlcode);
  291.      }
  292.  
  293.  
  294.  
  295. /*       **************************       *******************   */
  296. /*                                                                           */
  297. /*                  END OF PROGRAM - ROUTINE LABEL "END"                     */
  298. /*                                                                           */
  299. /*                 *************************************                     */
  300.  
  301. end:
  302.  
  303.  
  304.     if (dbas_cls == 0)
  305.     {                                                 /* if database is open */
  306.       strcpy(rte_label,"stop database  ");
  307.       sqlestpd (&sqlca);                              /* Stop using database */
  308.     }
  309.  
  310.     if (err_cls == -1)                                /* If an SQL or 'C'    */
  311.       {                                               /* error has occurred  */
  312.       fclose(ptr_errfile);                            /* close error file.   */
  313.       }
  314.  
  315.  
  316.    return(errlevel);
  317.  
  318. }                                                     /* END MAIN FUNCTION   */
  319.  
  320.