home *** CD-ROM | disk | FTP | other *** search
/ OS/2 Shareware BBS: Product / Product.zip / DBDEMO.ZIP / DEMODB.ZIP / D30ACBC1.SQC < prev    next >
Text File  |  1991-06-03  |  28KB  |  688 lines
  1. /*************************************      *******************/
  2. /*                                                                         */
  3. /*  APPLICATION PROGRAM NAME:  D30ACBC1.SQC of D30SCB01                    */
  4. /*                                                                         */
  5. /*  PROGRAM LANGUAGE:  'C' Language                                        */
  6. /*                                                                         */
  7. /*  PROGRAM DESCRIPTION:  D30ACBC1.SQC is the import/create view pgm       */
  8. /*                                                                         */
  9. /*                        This program will create the necessary views     */
  10. /*                        that will be used throughout the BANKING         */
  11. /*                        scenario.                                        */
  12. /*                                                                         */
  13. /*                        In addition, 5 of the 7 tables that form the     */
  14. /*                        the BANK database,  will be filled with actual   */
  15. /*                        data via the Import function.                    */
  16. /*                                                                         */
  17. /*                        The remaining two tables will have their         */
  18. /*                        data filled during D30ACB03.SQC.                 */
  19. /*                                                                         */
  20. /*   AUTHOR:  D. J. Grant                    DATE: 04/17/87                */
  21. /*                                                                         */
  22. /*   DATABASE:   SHELBY 3 BANK database                                    */
  23. /*               Tables:  Customer/Box/Loan/Debits/Credits/Savings/Checking*/
  24. /*                                                                         */
  25. /*   INPUT FILES:  Import files:                                           */
  26. /*                                                                         */
  27. /*                      CUSTOMER.WRK                                       */
  28. /*                      BOX.WR1                                            */
  29. /*                      LOAN.WKS                                           */
  30. /*                      SAVINGS.WKS                                        */
  31. /*                      CHECKING.WK1                                       */
  32. /*                                                                         */
  33. /*   OUTPUT FILES:  D30ACBC1.ERR - this is a file, written to disk, only   */
  34. /*                  when some type of error condition is returned from     */
  35. /*                  SQL.  It serves as an error log, recording the error   */
  36. /*                  code, its corresponding error message, and any other   */
  37. /*                  useful information which the program might be able     */
  38. /*                  to give.                                               */
  39. /*                                                                         */
  40. /*                  CUSTERR   -  is a file that will be created as a       */
  41. /*                  result of an error on the import function for the      */
  42. /*                  CUSTOMER table.  If this file does not exist, it       */
  43. /*                  will be created as a result of the error condition.    */
  44. /*                                                                         */
  45. /*                  Similarly, the following files will be created if      */
  46. /*                  an error occurs during the import function with        */
  47. /*                  regard to the respective tables:  BOXERR, LOANERR      */
  48. /*                  SAVERR, and CHCKERR.                                   */
  49. /*                                                                         */
  50. /* *********************** PROLOGUE CONTINUED NEXT PAGE  ***************** */
  51.  
  52. /* ******************************************     *********** */
  53. /* *********************** PROLOGUE CONTINUED **************************** */
  54. /*                                                                         */
  55. /*                                                                         */
  56. /*   DEPENDENCIES:  INVICTA, SHELBY 3 DATABASE KERNEL,                     */
  57. /*                  creation of SHELBY 3 BANK database,                    */
  58. /*                  pre-compilation with SQLPREP,                          */
  59. /*                  binding to data base with SQLPREP or SQLBIND,          */
  60. /*                  compilation with IBM C/2, using                        */
  61. /*                     compiler options:                                   */
  62. /*                     "/AL /DLINT_ARGS /DSQL_REL_10 /W2 /Od /FPa"         */
  63. /*                                                                         */
  64. /*   FUNCTION CALLS:  'C' COMPILER LIBRARY                                 */
  65. /*                       stdio.h - fopen,fclose,fgets,fprintf              */
  66. /*                       malloc.h - malloc                                 */
  67. /*                       string.h - strcpy                                 */
  68. /*                       memory.h - memcpy                                 */
  69. /*                                                                         */
  70. /*                    SHELBY 3 LIBRARY                                     */
  71. /*                       sql.h - sqlaintp                                  */
  72. /*                       sqlenv.h - sqlestrd,sqlestpd                      */
  73. /*                                - sqleisig (ctrl-break processing)       */
  74. /*                       sqlutil.h - import utility                        */
  75. /*                                                                         */
  76. /*                    OTHER                                                */
  77. /*                       none                                              */
  78. /*                                                                         */
  79. /*  ERROR CONDITIONS:                                                      */
  80. /*            NORMAL - specifically tested for and handled by the ap-      */
  81. /*                     plication program:  (exit_normal)                   */
  82. /*                     DOS (errorlvl) = 0.                                 */
  83. /*                                                                         */
  84. /*            ABNORMAL - unexpected and resulting in program termination:  */
  85. /*                       (exit_error)                                      */
  86. /*                     DOS (errorlvl) = 2.                                 */
  87. /*                                                                         */
  88. /*  MODIFICATIONS:                                                         */
  89. /*            Date      Author         Description                         */
  90. /*                                                                         */
  91. /*            10/05/87  D.J. Grant     - added enhanced exit_error code    */
  92. /*                                         to handle < or > sqlaintp error.*/
  93. /*                                     - added file open error handling    */
  94. /*                                         to display error message on     */
  95. /*                                         screen.                         */
  96. /*                                     - add     to each page.*/
  97. /*                                     - added code to restart database.   */
  98. /*                                     - changed each table import process */
  99. /*                                         to use memcpy to specify the    */
  100. /*                                         tcolumn list, instead of using  */
  101. /*                                         a strcpy.                       */
  102. /*                                     - added DOS return code processing. */
  103. /*                                     - changed all program references to */
  104. /*                                         use new naming conventions.     */
  105. /*          10/26/87    D.J.Grant      Added SQLEISIG function call, and   */
  106. /*                                     enhances exit_error to also print   */
  107. /*                                     SQLERRD[0] - [5] and SQL_ERRP.      */
  108. /*          03/08/88    D.J.Grant      - deleted the ZP compiler option.   */
  109. /*                                     - created new table - NEWCUST.      */
  110. /*                                     - created views - VIEWBOX & VIEWCUST*/
  111. /*          07/28/88    W.B.Brown      - added two views using SET ops     */
  112. /*                                       views will be exported in D30ACB05*/
  113. /*          02/03/89    W.B.Brown      - upgraded to release 3.0           */
  114. /** END OF PROLOGUE ********************************************************/
  115.  
  116. /********************************************     ***************/
  117. /*****************************************************************************/
  118. /*  SET UP INCLUDES                                                          */
  119. /*****************************************************************************/
  120.  
  121. #include <stdio.h>
  122. #include <string.h>
  123. #include <memory.h>
  124. #include <malloc.h>
  125. #include <sql.h>
  126. #include <sqlenv.h>
  127. #include <sqlutil.h>
  128. #include <sqlca.h>
  129.  
  130. /*****************************************************************************/
  131. /*  DEFINITIONS AND DECLARATIONS                                             */
  132. /*****************************************************************************/
  133.  
  134. struct sqlca sqlca;
  135.  
  136. /* Set up error message buffer */
  137.  
  138.         unsigned char msgbuf[512];
  139.  
  140. /* Declarations for D30ACBC1.ERR error file */
  141.  
  142.         unsigned char  errorfle[]="D30ACBC1.err";
  143.         unsigned char   tcaseid[]="Test Case Id : D30ACBC1.SQC";
  144.         unsigned char  descript[]="BANK:  create views & import";
  145.  
  146. /* Declarations for BANK data base */
  147.  
  148.         unsigned char  database[]="bank";   /* establish database name       */
  149.         unsigned char  password[]="";       /* set up null password          */
  150.  
  151. /* Declarations for import filnames */
  152.  
  153.         unsigned char  imp_cust[]="customer.wrk";
  154.         unsigned char  imp_box []="box.wr1";
  155.         unsigned char  imp_loan[]="loan.wks";
  156.         unsigned char  imp_sav []="savings.wks";
  157.         unsigned char  imp_chck[]="checking.wk1";
  158.  
  159.         unsigned char  imptype []="WSF";
  160.  
  161. /* Declarations for import Insert string for tclostrg */
  162.  
  163.         unsigned char  ins_cust[]="INSERT INTO CUSTOMER";
  164.         unsigned char  ins_box []="INSERT INTO BOX     ";
  165.         unsigned char  ins_loan[]="INSERT INTO LOAN    ";
  166.         unsigned char  ins_sav []="INSERT INTO SAVINGS ";
  167.         unsigned char  ins_chck[]="INSERT INTO CHECKING";
  168.         unsigned char  custerr[] = "custerr.msg";
  169.         unsigned char  boxerr[] = "boxerr.msg";
  170.         unsigned char  loanerr[] = "loanerr.msg";
  171.         unsigned char  saverr[] = "saverr.msg";
  172.         unsigned char  chckerr[] = "chckerr.msg";
  173.  
  174. /*****************************************************************************/
  175.  
  176.     unsigned char rte_label[16] = "               ";    /* progress marker   */
  177.     unsigned char  sqlerrp_value[9] = "        ";         /* error value  */
  178.  
  179. /*****************************************************************************/
  180.  
  181. /********************************************     ***************/
  182. int main (void);
  183. main ()
  184. {
  185.  
  186.    FILE *ptr_errorfle;                      /* declare ptr to error fle */
  187.  
  188. /*****************************************************************************/
  189. /* Import information:  The following section sets up the environment for    */
  190. /*      the 5 imports that will be performed during this program.  Maximum   */
  191. /*      pointers and memory allocations will be set up inorder to allow      */
  192. /*      one set up for all 5 imports.                                        */
  193. /*                                                                           */
  194. /*****************************************************************************/
  195.  
  196.         struct sqlchar *tcolstrg;         /* pointer to tcolstrg */
  197.         struct sqlchar *filetmod;          /* declare import structure       */
  198.         struct sqldcol *dcoldata;         /* pointer to dcolumn list */
  199.  
  200.         char   *ptr1, *ptr2, *ptr3;       /* pointers for memory allocation  */
  201.  
  202.         int callerac = 0;
  203.  
  204. /* Program local variables */
  205.  
  206.  
  207.     int errlevel = 0;                        /* return code to DOS call pgm  */
  208.  
  209.     short dbas_cls = 0;                      /* database open/close flag     */
  210.  
  211.     short rc = 0;                            /* variable for return code */
  212.  
  213.  
  214. /*  Allocate storage for sqldcol; the number used in the assignment should   */
  215. /*  be the maximum number of columns for any table.                          */
  216.  
  217.   ptr1 = malloc((sizeof(char))+(sizeof(short))+(10*sizeof(struct sqldcoln)));
  218.  
  219.   dcoldata = (struct sqldcol *)ptr1;
  220.   dcoldata->dcolmeth = 'D';
  221.  
  222.   ptr2 = malloc(22);
  223.  
  224.   tcolstrg = (struct sqlchar *)ptr2;
  225.   tcolstrg->length = 20;
  226.  
  227.   ptr3 = malloc(3);
  228.   filetmod = (struct sqlchar *)ptr3;
  229.  
  230.   filetmod->length = 0;
  231.   filetmod->data[0] = '\0';
  232.  
  233.  
  234. /********************************************     ***************/
  235.  
  236. /*****************************************************************************/
  237. /*  ERROR HANDLING FOR SQL STATEMENTS                                        */
  238. /*****************************************************************************/
  239.  
  240.         EXEC SQL
  241.              WHENEVER SQLERROR GOTO exit_error;
  242.         EXEC SQL
  243.              WHENEVER SQLWARNING CONTINUE;
  244.         EXEC SQL
  245.              WHENEVER NOT FOUND CONTINUE;
  246. /*****************************************************************************/
  247.  
  248.   strcpy(rte_label,"instal sqleisig");
  249.  
  250.   sqleisig(&sqlca);                             /* ctrl-break processing     */
  251.  
  252.   if (sqlca.sqlcode != 0)
  253.     {
  254.      goto exit_error;
  255.     }
  256.  
  257. /***************************************************************************/
  258.  
  259. /*****************************************************************************/
  260. /*  START USING DATABASE                                                     */
  261. /*****************************************************************************/
  262.  
  263.    strcpy(rte_label,"start database ");
  264.  
  265.    start_dbres:
  266.  
  267.     sqlestrd (database,'S',&sqlca);
  268.  
  269.     if (sqlca.sqlcode == -1015)                       /* If start db func    */
  270.     {                                                 /* fails, call restart */
  271.        strcpy(rte_label,"restart databas");           /* func.               */
  272.        sqlerest(database,&sqlca);            /* Check for failed    */
  273.          if (sqlca.sqlcode != 0)                      /* restart; on error   */
  274.             {                                         /* set dbase closed,   */
  275.             dbas_cls = -1;                            /* go to error exit.   */
  276.             goto exit_error;
  277.             }
  278.        strcpy(rte_label,"start database2");
  279.        sqlestrd (database,'S',&sqlca);       /* Good restart-start  */
  280.     }                                                 /* db again.           */
  281.  
  282.     if (sqlca.sqlcode != 0)                           /* Check 2nd start for */
  283.       {                                               /* failure; if failed, */
  284.       dbas_cls = -1;                                  /* set flag to closed. */
  285.       goto exit_error;                                /* Also checks for any */
  286.       }                                               /* other error code on */
  287.                                                       /* 1st sqlestrd.       */
  288.  
  289.  
  290. /********************************************     ***************/
  291.  
  292. /* *****************    ADD FOREIGN KEYS TO TABLES   ********************** */
  293.  
  294.    strcpy(rte_label,"ADD FOREIGN KEY");
  295.  
  296.   EXEC SQL
  297.      ALTER TABLE SAVINGS FOREIGN KEY SSSN (S_SSN) REFERENCES CUSTOMER
  298.      ON DELETE RESTRICT;
  299.  
  300.   EXEC SQL
  301.      ALTER TABLE LOAN FOREIGN KEY LSSN (L_SSN) REFERENCES CUSTOMER
  302.      ON DELETE RESTRICT;
  303.  
  304.   EXEC SQL
  305.      ALTER TABLE CHECKING FOREIGN KEY CSSN (C_SSN1) REFERENCES CUSTOMER
  306.      ON DELETE RESTRICT;
  307.  
  308.   EXEC SQL
  309.      ALTER TABLE DEBITS FOREIGN KEY DACCT (DB_ACCNUMB) REFERENCES CHECKING
  310.      ON DELETE CASCADE;
  311.  
  312.   EXEC SQL
  313.      ALTER TABLE CREDITS FOREIGN KEY CACCT (CR_ACCNUMB) REFERENCES CHECKING
  314.      ON DELETE CASCADE;
  315.  
  316.   EXEC SQL COMMIT;
  317.  
  318. /*   ***********************  GRANT PRIVILEGES  ***************************  */
  319.  
  320.    strcpy(rte_label,"REVOKE PUBLIC  ");
  321.  
  322.    EXEC SQL
  323.       REVOKE CREATETAB ON DATABASE FROM PUBLIC;
  324.  
  325.    EXEC SQL
  326.       REVOKE BINDADD ON DATABASE FROM PUBLIC;
  327.  
  328.    EXEC SQL
  329.       REVOKE CONNECT ON DATABASE FROM PUBLIC;
  330.  
  331.    strcpy(rte_label,"GRANT PRIVILEGE");
  332.  
  333.    EXEC SQL
  334.       GRANT DBADM ON DATABASE TO GDBADM, BADMIN;
  335.  
  336.    EXEC SQL
  337.       GRANT CONNECT, BINDADD ON DATABASE TO GPREP, PREP;
  338.  
  339.    EXEC SQL
  340.       GRANT CONNECT, CREATETAB ON DATABASE TO GCREATE, TCREATE;
  341.  
  342.  
  343.    EXEC SQL
  344.       GRANT CONNECT ON DATABASE TO GQRYSEL, QRYSEL, GEDIT, TEDIT, GTVCON,
  345.       TVCON, GSELINS, SELINS, REFER, TDELETE, TUPDATE, SELUPDEL, TEXECUTE,
  346.       EDITALT;
  347.  
  348.    EXEC SQL
  349.       GRANT SELECT ON TABLE CUSTOMER TO GQRYSEL, QRYSEL;
  350.  
  351.    EXEC SQL
  352.       GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE CUSTOMER TO GEDIT, TEDIT;
  353.  
  354.    EXEC SQL
  355.       GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE CHECKING TO GEDIT, TEDIT;
  356.  
  357.    EXEC SQL
  358.       GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE SAVINGS TO GEDIT, TEDIT;
  359.  
  360.    EXEC SQL COMMIT;
  361. /*****************************************************************************/
  362. /*      Create View 1   -   for D30ACB02  (year end loan report)             */
  363. /*****************************************************************************/
  364.  
  365.      strcpy (rte_label,"create vloan   ");
  366.  
  367.      EXEC SQL
  368.           CREATE VIEW VLOAN AS
  369.  
  370.           SELECT CU_SSN,CU_NAME,CU_ADDR1,CU_ADDR2,CU_ZIP,L_NUMB,L_TYPE,L_RATE,
  371.                  L_PAYMT,L_BAL,L_INTODAT,L_DESCRIP,L_DATE,L_SSN,L_TERM
  372.  
  373.           FROM   CUSTOMER, LOAN
  374.  
  375.           WHERE  CUSTOMER.CU_SSN = LOAN.L_SSN;
  376.  
  377. /*****************************************************************************/
  378. /*      Create View 2   -   for D30ACB02  (savings report)                   */
  379. /*****************************************************************************/
  380.  
  381.      strcpy (rte_label,"create vsave   ");
  382.  
  383.      EXEC SQL
  384.           CREATE VIEW VSAVE AS
  385.  
  386.           SELECT CU_SSN,CU_NAME,S_INTTOD,S_ACCNUMB,S_BAL,S_SSN
  387.  
  388.           FROM   CUSTOMER, SAVINGS
  389.  
  390.           WHERE  CUSTOMER.CU_SSN = SAVINGS.S_SSN;
  391.  
  392.  
  393.      EXEC SQL  COMMIT;
  394.  
  395.  
  396. /********************************************     ***************/
  397. /*      Create View 3   -   for D30ACB06  (view import)                      */
  398. /*****************************************************************************/
  399.  
  400.      strcpy (rte_label,"create viewbox ");
  401.  
  402.      EXEC SQL
  403.           CREATE VIEW VIEWBOX AS
  404.  
  405.           SELECT B_NUMB, B_SIZE, B_RENT
  406.  
  407.           FROM   BOX
  408.  
  409.           WHERE  B_SIZE = 'M';
  410.  
  411.  
  412.      EXEC SQL  COMMIT;
  413. /*****************************************************************************/
  414. /*      Create View 4   -   for D30ACB06  (view import)                      */
  415. /*****************************************************************************/
  416.  
  417.      strcpy (rte_label,"create viewcust");
  418.  
  419.      EXEC SQL
  420.           CREATE VIEW VIEWCUST AS
  421.  
  422.           SELECT *
  423.  
  424.           FROM   NEWCUST;
  425.  
  426.      EXEC SQL  COMMIT;
  427.  
  428. /*****************************************************************************/
  429. /*      Create View 5   -   for D30ACB05  (view export)                      */
  430. /*****************************************************************************/
  431.  
  432.      strcpy (rte_label,"create solloan ");
  433.  
  434.      EXEC SQL
  435.           CREATE VIEW SOLLOAN (SSN, SSNSC) AS
  436.  
  437.           ((SELECT CU_SSN, S_SSN
  438.             FROM CUSTOMER, SAVINGS
  439.             WHERE (CU_SSN = S_SSN) AND (S_BAL > 1000.00))
  440.  
  441.                       INTERSECT
  442.  
  443.            (SELECT CU_SSN, C_SSN1
  444.             FROM CUSTOMER, CHECKING
  445.             WHERE (CU_SSN = C_SSN1) AND (C_ENDBAL > 1000.00))
  446.  
  447.                       EXCEPT
  448.  
  449.            (SELECT CU_SSN, L_SSN
  450.             FROM CUSTOMER, LOAN
  451.             WHERE CU_SSN = L_SSN));
  452.  
  453.        EXEC SQL COMMIT;
  454.  
  455.  
  456. /*****************************************************************************/
  457. /*      Create View 6   -   for D30ACB05  (view export)                      */
  458. /*****************************************************************************/
  459.  
  460.      strcpy (rte_label,"create chkact  ");
  461.  
  462.      EXEC SQL
  463.      CREATE VIEW CHKACT (ACCNUMB) AS
  464.        ((SELECT C_ACCNUMB
  465.          FROM CHECKING
  466.          WHERE C_ACCNUMB NOT IN (SELECT DB_ACCNUMB FROM DEBITS))
  467.  
  468.                  INTERSECT
  469.  
  470.         (SELECT C_ACCNUMB
  471.          FROM CHECKING
  472.          WHERE C_ACCNUMB NOT IN (SELECT CR_ACCNUMB FROM CREDITS)));
  473.  
  474.       EXEC SQL COMMIT;
  475.  
  476.  
  477. /*****************************************************************************/
  478. /*      Create View 7   -   for D30ACB05  (view export)                      */
  479. /*****************************************************************************/
  480.  
  481.      strcpy (rte_label,"create inactcus");
  482.  
  483.      EXEC SQL
  484.         CREATE VIEW INACTCUS (SSN) AS
  485.          (SELECT CU_SSN FROM CUSTOMER
  486.           WHERE CU_SSN NOT IN ((SELECT C_SSN1 FROM CHECKING)
  487.                                         UNION
  488.                               (SELECT S_SSN FROM SAVINGS)
  489.                                         UNION
  490.                               (SELECT L_SSN FROM LOAN)
  491.                                         UNION
  492.                               (SELECT B_SSN FROM BOX)));
  493.  
  494.     EXEC SQL COMMIT;
  495.  
  496. /****************************************     *******************/
  497.  
  498. /* Customer table import */
  499.  
  500.    strcpy (rte_label,"import customer");
  501.  
  502.         tcolstrg->length = 20;
  503.         memcpy(tcolstrg->data,ins_cust,20);      /* default the tcolumn list */
  504.  
  505.         sqluimp (database,imp_cust,dcoldata,tcolstrg,
  506.                  imptype,filetmod,custerr,callerac,&sqlca);
  507.  
  508.         if (sqlca.sqlcode < 0)
  509.          {
  510.           goto exit_error;
  511.          }
  512.  
  513.    EXEC SQL COMMIT;
  514.  
  515. /* Box table import */
  516.  
  517.    strcpy (rte_label,"import box data");
  518.  
  519.         memcpy(tcolstrg->data,ins_box,20);       /* default the tcolumn list */
  520.  
  521.         sqluimp (database,imp_box,dcoldata,tcolstrg,
  522.                  imptype,filetmod,boxerr,callerac,&sqlca);
  523.  
  524.         if (sqlca.sqlcode < 0)
  525.          {
  526.           goto exit_error;
  527.          }
  528.  
  529.     EXEC SQL COMMIT;
  530.  
  531. /* Loan table import */
  532.  
  533.    strcpy (rte_label,"import loan dat");
  534.  
  535.         memcpy(tcolstrg->data,ins_loan,20);  /* default the tcolumn list     */
  536.  
  537.         sqluimp (database,imp_loan,dcoldata,tcolstrg,
  538.                  imptype,filetmod,loanerr,callerac,&sqlca);
  539.  
  540.         if (sqlca.sqlcode < 0)
  541.          {
  542.           goto exit_error;
  543.          }
  544.  
  545.      EXEC SQL COMMIT;
  546.  
  547.  
  548. /********************************************     ***************/
  549.  
  550.  
  551. /* Savings table import */
  552.  
  553.    strcpy (rte_label,"import savings ");
  554.  
  555.         memcpy(tcolstrg->data,ins_sav,20);    /* default the tcolumn list    */
  556.  
  557.         sqluimp (database,imp_sav,dcoldata,tcolstrg,
  558.                  imptype,filetmod,saverr,callerac,&sqlca);
  559.  
  560.         if (sqlca.sqlcode < 0)
  561.          {
  562.           goto exit_error;
  563.          }
  564.  
  565.     EXEC SQL COMMIT;
  566.  
  567. /* Checking table import */
  568.  
  569.    strcpy (rte_label,"import checking");
  570.  
  571.         memcpy(tcolstrg->data,ins_chck,20);    /* default the tcolumn list   */
  572.  
  573.         sqluimp (database,imp_chck,dcoldata,tcolstrg,
  574.                  imptype,filetmod,chckerr,callerac,&sqlca);
  575.  
  576.  
  577.  
  578.    printf("\n inserting data into table PERFDAT3 \n");
  579.    EXEC SQL
  580. INSERT INTO  PERFDAT3 SELECT
  581. CU_SSN , CU_NAME, CU_ADDR1 , CU_ADDR2 ,CU_ZIP , L_NUMB , L_TYPE, L_RATE ,
  582. L_TERM ,L_PAYMT, L_BAL, L_INTODAT,L_DESCRIP, L_DATE, S_ACCNUMB , S_IRATE,
  583. S_INTTOD, S_BAL, B_NUMB , B_SIZE,B_RENT ,  B_PAYUP, C_ACCNUMB , C_STARTBAL, C_ENDBAL
  584. FROM CUSTOMER, LOAN, SAVINGS, BOX, CHECKING
  585. WHERE CU_SSN = L_SSN AND CU_SSN = S_SSN AND CU_SSN = B_SSN AND CU_SSN = C_SSN1;
  586.  
  587.    printf("\n inserting data into table PERFDAt4 \n");
  588.    EXEC SQL
  589. INSERT INTO  PERFDAt4 SELECT
  590. CU_SSN , CU_NAME, CU_ADDR1 , CU_ADDR2 ,CU_ZIP , L_NUMB , L_TYPE, L_RATE ,
  591. L_TERM ,L_PAYMT, L_BAL, L_INTODAT,L_DESCRIP, L_DATE, S_ACCNUMB , S_IRATE,
  592. S_INTTOD, S_BAL, B_NUMB , B_SIZE,B_RENT ,  B_PAYUP, C_ACCNUMB , C_STARTBAL, C_ENDBAL
  593. FROM CUSTOMER, LOAN, SAVINGS, BOX, CHECKING
  594. WHERE CU_SSN = L_SSN AND CU_SSN = S_SSN AND CU_SSN = B_SSN AND CU_SSN = C_SSN1;
  595.  
  596. EXEC SQL CREATE INDEX  D3INDEX ON PERFDAT3  (CU_SSN ASC);
  597.  
  598.  
  599.         if (sqlca.sqlcode >= 0)
  600.         {
  601.              goto exit_normal;            /* exit the program due to error   */
  602.         }
  603.  
  604.    EXEC SQL COMMIT;
  605.  
  606.  
  607. /********************************************     ***************/
  608. /*****************************************************************************/
  609. exit_error:
  610.                                             /* set up error file processing  */
  611.     errlevel = 2;                              /* set DOS return code -error */
  612.  
  613.     if ((ptr_errorfle = fopen(errorfle, "w")) == NULL)
  614.  
  615.      {
  616.       printf ("\nUNABLE TO OPEN D30ACBC1.ERR\n");     /* can't open errorfle */
  617.       printf ("%s\n",rte_label);                      /* display msg - screen*/
  618.       printf ("SQLCODE IS %ld\n",sqlca.sqlcode);
  619.       goto continue_err;
  620.      }                                                /* print to error file */
  621.  
  622.     fprintf (ptr_errorfle,"%s\n%s\n",tcaseid,descript);
  623.     fprintf (ptr_errorfle,"%s\n",rte_label);
  624.     fprintf (ptr_errorfle,"SQLCODE IS %ld\n",sqlca.sqlcode);
  625.  
  626.                                               /* SQL miscellaneous error info*/
  627.  
  628.     fprintf(ptr_errorfle,"\nSQLERRD[0] is %ld",sqlca.sqlerrd[0]);
  629.     fprintf(ptr_errorfle,"\nSQLERRD[1] is %ld",sqlca.sqlerrd[1]);
  630.     fprintf(ptr_errorfle,"\nSQLERRD[2] is %ld",sqlca.sqlerrd[2]);
  631.     fprintf(ptr_errorfle,"\nSQLERRD[3] is %ld",sqlca.sqlerrd[3]);
  632.     fprintf(ptr_errorfle,"\nSQLERRD[4] is %ld",sqlca.sqlerrd[4]);
  633.     fprintf(ptr_errorfle,"\nSQLERRD[5] is %ld",sqlca.sqlerrd[5]);
  634.  
  635.     memcpy (sqlerrp_value, sqlca.sqlerrp,8);
  636.     fprintf(ptr_errorfle,"\nSQLERRP is %s",sqlerrp_value);
  637.  
  638.     rc = sqlaintp(msgbuf,512,0,&sqlca);
  639.  
  640.     if (rc < 0)                                      /* message retrieval err*/
  641.  
  642.     {
  643.       fprintf (ptr_errorfle, "SQLAINTP ERROR.  Return Code = %d",rc);
  644.     }
  645.  
  646.     else if (rc > 0)
  647.      {                                              /* print error message   */
  648.       fprintf (ptr_errorfle,"\n");
  649.       fprintf (ptr_errorfle,msgbuf);
  650.      }
  651.     fclose(ptr_errorfle);                           /* close the error file  */
  652.  
  653.  
  654. continue_err:                                  /* reset sqlerror to eliminate*/
  655.                                                /*  looping if Rollback fails */
  656.     EXEC SQL
  657.             WHENEVER SQLERROR CONTINUE;
  658.  
  659.     EXEC SQL
  660.             ROLLBACK WORK;                     /* Rollback any work          */
  661.  
  662.     if (sqlca.sqlcode < 0)                     /* Rollback failed            */
  663.      {
  664.       if ((ptr_errorfle = fopen(errorfle,"a")) == NULL)  /* open errorfle    */
  665.  
  666.        {                                       /* open failed - display msg. */
  667.         printf ("\nUNABLE TO OPEN D30ACBC1.ERR -- ROLLBACK FAILED");
  668.         goto exit_normal;
  669.        }
  670.  
  671.    fprintf (ptr_errorfle,"\nSQLCODE IS %ld,  ROLLBACK FAILED.",sqlca.sqlcode);
  672.  
  673.       fclose(ptr_errorfle);                    /* close the error file       */
  674.      }
  675.  
  676. /*****************************************************************************/
  677.  
  678. /********************************************     ***************/
  679. exit_normal:
  680.  
  681.    if (dbas_cls == 0)
  682.     {
  683.      sqlestpd (&sqlca);                   /* stop using the database         */
  684.     }
  685.  
  686.    return(errlevel);                      /* DOS error return code           */
  687.  }
  688.