home *** CD-ROM | disk | FTP | other *** search
/ OS/2 Shareware BBS: Product / Product.zip / DBDEMO.ZIP / DEMODB.ZIP / D30ACB03.SQC < prev    next >
Text File  |  1991-06-03  |  38KB  |  805 lines
  1. /**********************************       ********   PAGE 1   */
  2. /*                                                                         */
  3. /*  APPLICATION PROGRAM NAME:  D30ACB03.SQC                                */
  4. /*                                                                         */
  5. /*  PROGRAM LANGUAGE: 'C' Language with Embedded SQL                       */
  6. /*                                                                         */
  7. /*  PROGRAM DESCRIPTION:                                                   */
  8. /*            D30ACB03 uses 'C' language to read two ASCII files           */
  9. /*            created under PE2 which contain credit and debit             */
  10. /*            transactions and uses imbedded SQL to insert these           */
  11. /*            rows of transactions into the database tables CREDITS        */
  12. /*            and DEBITS. The purpose of this program is to test the       */
  13. /*            following functions of SHELBY:                               */
  14. /*                                                                         */
  15. /*                 START DB  (SHARED)                 (3.2.10)             */
  16. /*                 RESTART DB                                              */
  17. /*                 STOP DB                            (3.2.11)             */
  18. /*                 DATA TYPES: NUMERICAL              (3.3.5.1)            */
  19. /*                             DECIMAL                                     */
  20. /*                 DATA TYPES: CHARACTER              (3.3.5.2)            */
  21. /*                 HOST VARIABLES                     (3.3.7.3)            */
  22. /*                 IMBEDDED SQL                       (3.9.1)              */
  23. /*                       EXEC SQL                     (3.9.1.1)            */
  24. /*                       BEGIN/END DECLARE            (3.9.1.2)            */
  25. /*                       INCLUDE                      (3.9.1.3)            */
  26. /*                       WHENEVER                     (3.9.1.5)            */
  27. /*                       SELECT                       (3.6.1)              */
  28. /*                         SUM,AVG,MIN,MAX,COUNT      (3.3.8.2)            */
  29. /*                       INSERT                       (3.6.5)              */
  30. /*                       LOCK - EXCLUSIVE             (3.6.8)              */
  31. /*                       COMMIT - with WORK option    (3.6.9)              */
  32. /*                       ROLLBACK                     (3.6.10)             */
  33. /*                 'C' LANGUAGE INTERFACE             (3.11.1)             */
  34. /*                       HOST VARIABLES               (3.11.1.1)           */
  35. /*                       LANGUAGE RESTRICTIONS        (3.11.1.2)           */
  36. /*                       SPECIAL COMMANDS             (3.11.1.3)           */
  37. /*                       PRECOMPILER                  (3.11.1.4)           */
  38. /*                       DB ENVIRONMENT               (3.11.1.4)           */
  39. /*                       ERROR MSG                    (3.11.1.4)           */
  40. /*                                                                         */
  41. /*  AUTHOR:   W.B.Brown                      DATE: 06/16/87                */
  42. /*                                                                         */
  43. /*  DATA BASE:                                                             */
  44. /*            SHELBY 3 BANK database                                       */
  45. /*                  - CREDITS table                                        */
  46. /*                  - DEBITS table                                         */
  47. /*                                                                         */
  48. /*  INPUT FILES:                                                           */
  49. /*            CREDITS.ASC - an ASCII file containing the data to be        */
  50. /*            inserted into each row of the CREDITS table. The file was    */
  51. /*            created using Personal Editor and saved in ASCII format by   */
  52. /*            specifying "file credits.asc notabs".                        */
  53. /*                                                                         */
  54. /*            DEBITS.ASC  - an ASCII file containing the data to be        */
  55. /*            inserted into each row of the DEBITS table.  The file was    */
  56. /*            created using Personal Editor and saved in ASCII format by   */
  57. /*            specifying "file debits.asc  notabs".                        */
  58. /*                                                                         */
  59. /* ***************    PROLOG CONTINUED NEXT PAGE    ********************** */
  60.  
  61. /* ** PROLOG CONTINUED ************       ********   PAGE 2   */
  62. /*                                                                         */
  63. /*                                                                         */
  64. /*  OUTPUT FILES:                                                          */
  65. /*            D30ACB03.ERR - This is a file, written to disk, only when    */
  66. /*            some unexpected error condition is returned from SQL.  It    */
  67. /*            serves as an error log, recording the error code, its cor-   */
  68. /*            responding error message, and any other useful information   */
  69. /*            which the program might be able to give.                     */
  70. /*                                                                         */
  71. /*            D30ACB03.SUM - This is a file, written to disk with records  */
  72. /*            containing summary data from both the input files and the    */
  73. /*            tables (after the data has been inserted).   Summary data    */
  74. /*            will consist of the count of records read in/count of the    */
  75. /*            rows in the tables; average check amount; average deposit    */
  76. /*            amount; minimum and maximum on the same two columns; and the */
  77. /*            sum of those columns.  Comparisons between input data and    */
  78. /*            TABLE data will be recorded as matched or unmatched.         */
  79. /*                                                                         */
  80. /*  DEPENDENCIES:                                                          */
  81. /*            INVICTA, SHELBY 3 DATABASE KERNEL,                           */
  82. /*            database manager started                                     */
  83. /*            creation of SHELBY 3 BANK database,                          */
  84. /*            creation of CREDITS and DEBITS tables,                       */
  85. /*            precompiling and binding with SQLPREP                        */
  86. /*            compilation with IBM C Compiler 2.0, using compiler options: */
  87. /*                     "/AL /Gs /DLINT_ARGS /DSQL_REL_10 /W3 /Od "         */
  88. /*                                                                         */
  89. /*  FUNCTION CALLS:                                                        */
  90. /*            COMPILER LIBRARY                                             */
  91. /*                 stdio.h - fopen,fclose,fgets,fprintf                    */
  92. /*                 memory.h - memcpy                                       */
  93. /*                 string.h - strcpy                                       */
  94. /*                 stdlib.h - data conversion                              */
  95. /*                 math.h - math conversion                                */
  96. /*            SHELBY 3 LIBRARY                                             */
  97. /*                 sql.h - sqlaintp; sqlenv - sqlestrd,sqlestpd,sqlerest   */
  98. /*                 sqlutil.h - insert                                      */
  99. /*                 sqlca.h - sqlca.sqlcode                                 */
  100. /*                 sqlenv.h - sqleisig (ctrl-break processing)             */
  101. /*            OTHER                                                        */
  102. /*                 none                                                    */
  103. /*                                                                         */
  104. /*  ERROR CONDITIONS:                                                      */
  105. /*        'C' NORMAL - specifically tested for and handled by the ap-      */
  106. /*                     plication program:                                  */
  107. /*                        log error, exit thru "end" routine.              */
  108. /*                                                                         */
  109. /*       SQL  ABNORMAL - unexpected and resulting in program termination:  */
  110. /*                        log error, exit thru "ret_code" & "end" routine  */
  111. /*                       -1015   Restart Database                          */
  112. /*                                                                         */
  113. /*  MODIFICATIONS:                                                         */
  114. /*            Date      Author         Description                         */
  115. /*                                                                         */
  116. /*          09/02/87    W.B.Brown      Debug & add screen print routine if */
  117. /*                                     error file fails to open.           */
  118. /*                                                                         */
  119. /*          10/26/87    D.J.Grant      Added SQLEISIG function call, and   */
  120. /*                                     enhances exit_error to also print   */
  121. /*                                     SQLERRD[0] - [5] and SQL_ERRP.      */
  122. /*                                                                         */
  123. /*           2/09/88    W.B.Brown      Removed COMMIT HOLD - DCR 214       */
  124. /*                                     after INSERTing.                    */
  125. /*           8/02/88    W.B.Brown      Added a flag to determine if summary*/
  126. /*                                     file has been opened                */
  127. /*           2/03/89    W.B.Brown      Upgraded to release 3.0             */
  128. /***************************************************************************/
  129.  
  130.  
  131. /*  Include library functions  ****       ********   PAGE 3     */
  132.  
  133.  
  134. #include <stdio.h>
  135. #include <memory.h>
  136. #include <string.h>
  137. #include <sql.h>
  138. #include <sqlenv.h>
  139. #include <sqlutil.h>
  140. #include <sqlca.h>
  141. #include <stdlib.h>
  142. #include <math.h>
  143.  
  144. /*  Define lengths for input records and for routine labels                  */
  145.  
  146. #define CRECLEN     27              /* Credit input record - 25 bytes, \n,\0 */
  147. #define DEBLEN      32              /* Debit input record - 30 bytes, \n.\0  */
  148. #define RTELBLEN    16              /* Labels for routines - error tracking  */
  149.  
  150. /* The following field definitions are for the length of each field in input */
  151. /* These lengths will be used to move fields from input record to host vars  */
  152.  
  153. #define CRACNOLN     8              /* Checking acct. no. for a credit       */
  154. #define CRDATELN     8              /* Transaction date (mm-dd-yy)           */
  155. #define CRAMTLN      9              /* Deposit amount - decimal (8,2)        */
  156.  
  157. #define DBACNOLN     8              /* Checking acct. no. for a debit        */
  158. #define DBDATELN     8              /* Transaction date (mm-dd-yy)           */
  159. #define DBAMTLN      9              /* Check amount - decimal (8,2)          */
  160. #define DBCKNOLN     5              /* Check number                          */
  161.  
  162. struct sqlca sqlca;
  163.  
  164. /* The following definitions are for input/output/error tracking purposes:   */
  165. /*     an input buffer to hold the ASCII record                              */
  166. /*     a message buffer for use with SQL error messages                      */
  167. /*     a field for tracking progress thru the program                        */
  168. /*     database name, error file, and summary file                           */
  169.  
  170. unsigned char crdt_buff[CRECLEN];    /* Input buffer for credits             */
  171. unsigned char debt_buff[DEBLEN];     /* Input buffer for debits              */
  172. unsigned char msg_buf[512];          /* SQL error message buffer             */
  173. unsigned char rte_label[RTELBLEN];   /* Routine label for error tracking     */
  174. unsigned char dbase[] = "BANK";
  175. unsigned char password[] = "";
  176. unsigned char errfile[] = "D30ACB03.ERR";
  177. unsigned char sumdata[] = "D30ACB03.SUM";
  178. unsigned char credfile[] = "credits.asc";
  179. unsigned char debfile[] = "debits.asc";
  180.  
  181. unsigned char  sqlerrp_value[9] = "        ";             /* error value  */
  182.  
  183. int errlevel = 0;
  184.  
  185.  
  186. /*     ****************************       ********   PAGE 4     */
  187. /*                                                                           */
  188. /*   THIS COMMENT BOX SHOWS THE DEFINITION OF THE TABLES ON THE DATABASE.    */
  189. /*                                                                           */
  190. /*           CREDITS TABLE                     DEBITS TABLE                  */
  191. /*     CR_ACCNUMB    CHAR(8)   NOT NULL   DB_ACCNUMB   CHAR(8)   NOT NULL    */
  192. /*     CR_TRANDATE   CHAR(8)   NOT NULL   DB_TRANDATE  CHAR(8)   NOT NULL    */
  193. /*     CR_DEPAMT     DEC(8,2)  NOT NULL   DB_AMT       DEC(8,2)  NOT NULL    */
  194. /*                                        DB_CHECKNO   CHAR(5)   NOT NULL    */
  195. /*                                                                           */
  196. /*     ****************************************************************      */
  197.  
  198. /*  **********************************************************************   */
  199. /*  SQL DECLARE SECTION - Host variables - used to insert data from the      */
  200. /*                        input buffers to the CREDITS and DEBITS tables     */
  201. /*  **********************************************************************   */
  202.  
  203. EXEC SQL BEGIN DECLARE SECTION;
  204.  
  205. /*    Credit transaction fields                                              */
  206.  
  207.         unsigned char craccno[8];
  208.         unsigned char crdate[8];
  209.         double cramt;
  210.  
  211. /*    Debit transaction fields                                               */
  212.  
  213.         unsigned char dbaccno[8];
  214.         unsigned char dbdate[8];
  215.         double dbamt;
  216.         unsigned char dbchkno[5];
  217.  
  218. /*   Host variables for summary data from CREDITS and DEBITS tables after    */
  219. /*   data has been inserted into these tables.                               */
  220.  
  221.         long crd_cnt;
  222.         long deb_cnt;
  223.         double crd_sum;
  224.         double deb_sum;
  225.         double crd_min;
  226.         double deb_min;
  227.         double crd_max;
  228.         double deb_max;
  229.         double crd_avg;
  230.         double deb_avg;
  231.  
  232. EXEC SQL END DECLARE SECTION;
  233.  
  234. /*  ***********************************************************************  */
  235. /*   END OF SQL DECLARE SECTION - Host variables                             */
  236. /*  ***********************************************************************  */
  237.  
  238.  
  239.  
  240. /*  ** BEGIN MAIN FUNCTION  *******       ********   PAGE 5     */
  241.  
  242. int main (void);
  243. main ()
  244. {
  245. /*   Declare input files, output error file, and output summary data file    */
  246.  
  247.     FILE *ptr_crinput;               /*  Credit input file pointer           */
  248.     FILE *ptr_dbinput;               /*  Debit input file pointer            */
  249.     FILE *ptr_errfile;               /*  Error file pointer                  */
  250.     FILE *ptr_sumdata;               /*  Summary file pointer                */
  251.  
  252. /*   Declare a short integer to receive the return code message length-SQL   */
  253. /*   Declare a short integer to indicate credits fle open(0), failed(-1).    */
  254. /*   Declare a short integer to indicate error file open(-1) or closed(0).   */
  255. /*   (Error file is open for error, therefore (-1) is used for open.)        */
  256. /*   Declare a short integer to indicate dbase open(0) or failed (-1).       */
  257.  
  258.     short rc = 0;                    /*  From sqlaintp - initialize to 0     */
  259.     short crd_opn = 0;               /*  Initialize to open.                 */
  260.     short err_cls = 0;               /*  Initialize to closed.               */
  261.     short sum_cls = 0;               /*  Initialize to closed.               */
  262.     short dbas_cls = 0;              /*  Initialize to open.                 */
  263.  
  264. /*   Declare and initialize counters for summary data on records read        */
  265. /*   from the ASCII files.                                                   */
  266.  
  267.     long cr_count = 0;
  268.     long db_count = 0;
  269.     double cr_sum = 0.0;
  270.     double db_sum = 0.0;
  271.     double cr_min = 999999.99;
  272.     double db_min = 999999.99;
  273.     double cr_max = 0.0;
  274.     double db_max = 0.0;
  275.     double cr_avg = 0.0;
  276.     double db_avg = 0.0;
  277.     short crsum_match = 0;
  278.     short crcnt_match = 0;
  279.     short crmin_match = 0;
  280.     short crmax_match = 0;
  281.     short cravg_match = 0;
  282.     short dbsum_match = 0;
  283.     short dbcnt_match = 0;
  284.     short dbmin_match = 0;
  285.     short dbmax_match = 0;
  286.     short dbavg_match = 0;
  287.  
  288. /* Declare a char variable for the decimal fields read in and use them later */
  289. /* to convert to double type.                                                */
  290.  
  291.     unsigned char ch_cramt[10];
  292.     unsigned char ch_dbamt[10];
  293.  
  294.                                                            /*     PAGE 6     */
  295.  
  296.  
  297. /*  In order to access individual fields in the input ASCII file, each field */
  298. /*  will have an assigned pointer corresponding to the position offset in    */
  299. /*  the input buffer; each field will be referenced by its pointer.          */
  300.  
  301.     unsigned char *ptr_craccno;                       /* Credit trans. ptrs  */
  302.     unsigned char *ptr_crdate;
  303.     unsigned char *ptr_cramt;
  304.  
  305.     unsigned char *ptr_dbaccno;                       /* Debit trans. ptrs   */
  306.     unsigned char *ptr_dbdate;
  307.     unsigned char *ptr_dbamt;
  308.     unsigned char *ptr_dbchkno;
  309.  
  310.     ch_cramt[9] = '\0';                               /* Set last char in    */
  311.     ch_dbamt[9] = '\0';                               /* interim var to null */
  312.  
  313.     ptr_craccno = crdt_buff;                          /* Credit trans fields */
  314.     ptr_crdate = crdt_buff + 8;
  315.     ptr_cramt = crdt_buff + 16;
  316.  
  317.     ptr_dbaccno = debt_buff;                          /* Debit trans fields  */
  318.     ptr_dbdate = debt_buff + 8;
  319.     ptr_dbamt = debt_buff + 16;
  320.     ptr_dbchkno = debt_buff + 25;
  321.  
  322. /*    *****************************       *******    PAGE 7     */
  323. /*                                                                           */
  324. /*                         SQL EXCEPTION HANDLING                            */
  325. /*  Whenever an error occurs during the execution of an SQL statement,       */
  326. /*  (sqlcode = negative number), go to error routine.                        */
  327. /*  If a warning occurs (sqlcode = positive number), continue with the next  */
  328. /*  executable statement in the program.                                     */
  329. /*  If a row is not found or a selected table is empty, go to error routine. */
  330. /*                                                                           */
  331. /*                 *************************************                     */
  332.  
  333. EXEC SQL
  334.      WHENEVER SQLERROR GOTO ret_code;
  335.  
  336. EXEC SQL
  337.      WHENEVER SQLWARNING CONTINUE;
  338.  
  339. EXEC SQL
  340.      WHENEVER NOT FOUND GOTO ret_code;
  341. /*                 *************************************                     */
  342.  
  343.   strcpy(rte_label,"instal sqleisig");
  344.  
  345.   sqleisig(&sqlca);                             /* ctrl-break processing     */
  346.  
  347.   if (sqlca.sqlcode != 0)
  348.     {
  349.      goto ret_code;
  350.     }
  351.  
  352. /*            ****************************************                       */
  353. /*                                                                           */
  354. /*   Store routine name "open credits" in rte_label for progress tracking;   */
  355. /*   Open Credit file with appropriate pointer;  check for error on open;    */
  356. /*   in case of error, open the error file and write a message on errfile    */
  357. /*                                                                           */
  358. /*            *****************************************                      */
  359.  
  360. strcpy(rte_label,"open credit asc");
  361. if ((ptr_crinput = fopen(credfile,"r")) == NULL)
  362.     {
  363.     crd_opn = -1;
  364.     goto ret_code;
  365.     }
  366.  
  367.  
  368. /*   ******************************       *********  PAGE 8     */
  369. /*                                                                           */
  370. /*  Store the name of the routine in rte_label for error tracking            */
  371. /*  Start the database using the function "sqlestrd"; check for error on     */
  372. /*  start-database function.                                                 */
  373. /*                                                                           */
  374. /*                  ******************************                           */
  375. /*                                                                           */
  376.  
  377.     strcpy(rte_label,"start database ");
  378.  
  379. start_dbres:
  380.  
  381.     sqlestrd (dbase,password,'S',&sqlca);
  382.  
  383.     if (sqlca.sqlcode == -1015)                       /* If start db func    */
  384.     {                                                 /* fails, call restart */
  385.        strcpy(rte_label,"restart databas");           /* func.               */
  386.        sqlerest(dbase,password,&sqlca);               /* Check for failed    */
  387.          if (sqlca.sqlcode != 0)                      /* restart; on error   */
  388.             {                                         /* set dbase closed,   */
  389.             dbas_cls = -1;                            /* go to error exit.   */
  390.             goto ret_code;
  391.             }
  392.        strcpy(rte_label,"start database2");
  393.        sqlestrd (dbase,password,'S',&sqlca);          /* Good restart-start  */
  394.     }                                                 /* db again.           */
  395.  
  396.     if (sqlca.sqlcode != 0)                           /* Check 2nd start for */
  397.       {                                               /* failure; if failed, */
  398.       dbas_cls = -1;                                  /* set flag to closed. */
  399.       goto ret_code;                                  /* Also checks for any */
  400.       }                                               /* other error code on */
  401.                                                       /* 1st sqlestrd.       */
  402.  
  403. /*                  *****************************                            */
  404. /*                                                                           */
  405. /*  Store the name of the routine in rte_label for error tracking.           */
  406. /*  LOCK THE CREDITS TABLE IN EXCLUSIVE MODE TO INSERT ROWS OF DATA.         */
  407. /*  Check for unsuccessful LOCK; on error go to log error and exit.          */
  408. /*                                                                           */
  409. /*                  ******************************                           */
  410.  
  411.     strcpy(rte_label,"lock credit exm");
  412.  
  413.     EXEC SQL
  414.          LOCK TABLE CREDITS IN EXCLUSIVE MODE;
  415.  
  416.  
  417. /*      ***************************       ********   PAGE 9     */
  418. /*                                                                           */
  419. /*    Until end of file is reached on the CREDITS.ASC file, read each row,   */
  420. /*    calculate summary data, move each field from input buffer to the SQL   */
  421. /*    host variable, insert from the host variables to the CREDITS table.    */
  422. /*                                                                           */
  423. /*                 *******************************                           */
  424.  
  425. cred_loop:
  426.  
  427. while ((fgets(crdt_buff,CRECLEN,ptr_crinput)) != NULL)
  428.  {
  429.  
  430. /*         Move fields from input buffer to host variables.                  */
  431.  
  432.       memcpy(craccno,ptr_craccno,CRACNOLN);           /* CR Acct. No.        */
  433.       memcpy(crdate,ptr_crdate,CRDATELN);             /* CR Trans. Date      */
  434.       memcpy(ch_cramt,ptr_cramt,CRAMTLN);             /* CR Deposit Amt.     */
  435.       cramt = atof(ch_cramt);                         /* Convert to double   */
  436.  
  437. /*         Calculate summary data from input record.                         */
  438.  
  439.       cr_count = cr_count + 1;                        /* Count of creds      */
  440.       cr_sum = cramt + cr_sum;                        /* Total deposit amts  */
  441.       if (cramt < cr_min)
  442.         {
  443.         cr_min = cramt;                               /* Find/assign MIN amt */
  444.         }
  445.       if (cramt > cr_max)
  446.         {
  447.         cr_max = cramt;                               /* Find/assign MAX amt */
  448.         }
  449.  
  450. /*          Insert data from host variables to columns in CREDITS table      */
  451.  
  452.     strcpy(rte_label, "insert ccommand");
  453.  
  454. EXEC SQL
  455.      INSERT INTO CREDITS (CR_ACCNUMB, CR_TRANDATE, CR_DEPAMT)
  456.      VALUES (:craccno, :crdate, :cramt);
  457.  
  458.  
  459.  }                                                    /* End While Loop      */
  460.  
  461. /*       **************************       ********   PAGE 10    */
  462. /*                                                                           */
  463. /*    Check for no credit transactions - log on error file, end pgm; else:   */
  464. /*    When end of input file is reached, calculate the average for "cramt",  */
  465. /*    and using SELECT and SUM, MIN, MAX, AVG, COUNT, calculate summary data */
  466. /*    from the data inserted into the CREDITS table.                         */
  467. /*    Compare the input summary data with the table inserted data and set    */
  468. /*    flags (0 for unequal, 1 for equal) to be printed out with the summary  */
  469. /*    data to file G20ACB03.SUM.                                             */
  470. /*                                                                           */
  471. /*                    *********************************                      */
  472.  
  473.     strcpy(rte_label, "no credit input");
  474.  
  475.     if (cr_count == 0)
  476.     {
  477.       fclose (ptr_crinput);
  478.       goto ret_code;
  479.     }
  480.  
  481. /*    Calculate the average for credit amount field from input field.        */
  482.  
  483.     cr_avg = (cr_sum / cr_count);
  484.  
  485. /*    Select summary data from columns in CREDITS table.                     */
  486.  
  487.     strcpy(rte_label, "select ccommand");
  488.  
  489. EXEC SQL
  490.      SELECT SUM(CR_DEPAMT), AVG(CR_DEPAMT), MIN(CR_DEPAMT), MAX(CR_DEPAMT),
  491.             COUNT(*)
  492.      INTO :crd_sum, :crd_avg, :crd_min, :crd_max, :crd_cnt
  493.      FROM CREDITS;
  494.  
  495. /*   Compare input summary data with table summary data for match.           */
  496.  
  497.     if (cr_sum == crd_sum)
  498.      {
  499.      crsum_match = 1;
  500.      }
  501.     if (cr_count == crd_cnt)
  502.      {
  503.      crcnt_match = 1;
  504.      }
  505.     if (cr_min == crd_min)
  506.      {
  507.      crmin_match = 1;
  508.      }
  509.     if (cr_max == crd_max)
  510.      {
  511.      crmax_match = 1;
  512.      }
  513.     if (cr_avg == crd_avg)
  514.      {
  515.      cravg_match = 1;
  516.      }
  517.  
  518.  
  519. /*      ***************************       *********  PAGE 11    */
  520. /*                                                                           */
  521. /*        Print summary data & match data for credits to summary file.       */
  522. /*                                                                           */
  523. /*      ************************************************************         */
  524.     strcpy(rte_label,"open sumry file");
  525.  
  526.     if ((ptr_sumdata = fopen(sumdata, "w")) == NULL)
  527.      {
  528.       goto ret_code;
  529.      }
  530.     sum_cls = 1;
  531.     fprintf(ptr_sumdata,"CREDIT TABLE ROW COUNT:  %ld\n",crd_cnt);
  532.     fprintf(ptr_sumdata,"CREDIT.ASC ROW COUNT:    %ld\n",cr_count);
  533.     fprintf(ptr_sumdata,"CREDIT TABLE DEPAMT SUM: %.2f\n",crd_sum);
  534.     fprintf(ptr_sumdata,"CREDIT.ASC DEPAMT SUM:   %.2f\n",cr_sum);
  535.     fprintf(ptr_sumdata,"CREDIT TABLE DEPAMT MIN: %.2f\n",crd_min);
  536.     fprintf(ptr_sumdata,"CREDIT.ASC DEPAMT MIN:   %.2f\n",cr_min);
  537.     fprintf(ptr_sumdata,"CREDIT TABLE DEPAMT MAX: %.2f\n",crd_max);
  538.     fprintf(ptr_sumdata,"CREDIT.ASC DEPAMT MAX:   %.2f\n",cr_max);
  539.     fprintf(ptr_sumdata,"CREDIT TABLE DEPAMT AVG: %.2f\n",crd_avg);
  540.     fprintf(ptr_sumdata,"CREDIT.ASC DEPAMT AVG:   %.2f\n",cr_avg);
  541.     fprintf(ptr_sumdata,"%d%d%d%d%d\n",
  542.             crcnt_match, crsum_match, crmin_match, crmax_match, cravg_match);
  543.  
  544. /*   UNLOCK, RELEASE CREDITS TABLE. CLOSE CREDITS.ASC FILE.                  */
  545.  
  546.         fclose(ptr_crinput);
  547.         EXEC SQL   COMMIT WORK;
  548.  
  549. /* ************************************************************************* */
  550. /*                                                                           */
  551. /*      DEBIT INPUT AND INSERTION INTO DEBITS TABLE SECTION                  */
  552. /*                                                                           */
  553. /* ************************************************************************* */
  554.  
  555. /*   Open Debit  file with appropriate pointer;  check for error on open;    */
  556. /*   in case of error, open the error file and write a message on errfile    */
  557.  
  558. strcpy(rte_label,"open debits asc");
  559.  
  560. if ((ptr_dbinput = fopen(debfile,"r")) == NULL)
  561.     {
  562.     goto ret_code;
  563.     }
  564.  
  565. /*                  *****************************                            */
  566. /*                                                                           */
  567. /*  Store the name of the routine in rte_label for error tracking.           */
  568. /*  Lock the DEBITS table in exclusive mode for insertion.                   */
  569. /*                                                                           */
  570. /*                  ******************************                           */
  571.  
  572.     strcpy(rte_label,"lock debits tbl");
  573.  
  574.     EXEC SQL
  575.          LOCK TABLE DEBITS IN EXCLUSIVE MODE;
  576.  
  577.  
  578. /*    *****************************       ********   PAGE 12    */
  579. /*                                                                           */
  580. /*    Until end of file is reached on the DEBITS.ASC file, read each row,    */
  581. /*    calculate summary data, move each field from input buffer to the SQL   */
  582. /*    host variable, insert from the host variables to the DEBITS  table.    */
  583. /*                                                                           */
  584. /*                 *******************************                           */
  585.  
  586. dbet_loop:
  587.  
  588. while ((fgets(debt_buff,DEBLEN,ptr_dbinput)) != NULL)
  589.  {
  590.  
  591. /*         Move fields from input buffer to host variables.                  */
  592.  
  593.       memcpy(dbaccno,ptr_dbaccno,DBACNOLN);           /* DB Acct. No.        */
  594.       memcpy(dbdate,ptr_dbdate,DBDATELN);             /* DB Trans. Date      */
  595.       memcpy(ch_dbamt,ptr_dbamt,DBAMTLN);             /* DB Check Amt.       */
  596.       dbamt = atof(ch_dbamt);                         /* Convert to double   */
  597.       memcpy(dbchkno,ptr_dbchkno,DBCKNOLN);           /* DB Check No.        */
  598.  
  599. /*         Calculate summary data from input record.                         */
  600.  
  601.       db_count = db_count + 1;                        /* Count of debits     */
  602.       db_sum = dbamt + db_sum;                        /* Total check amts    */
  603.       if (dbamt < db_min)
  604.         {
  605.         db_min = dbamt;                               /* Find/assign MIN amt */
  606.         }
  607.       if (dbamt > db_max)
  608.         {
  609.         db_max = dbamt;                               /* Find/assign MAX amt */
  610.         }
  611.  
  612. /*          Insert data from host variables to columns in DEBITS  table      */
  613.  
  614.     strcpy(rte_label, "insert dcommand");
  615.  
  616. EXEC SQL
  617.      INSERT INTO DEBITS (DB_ACCNUMB, DB_TRANDATE, DB_AMT, DB_CHECKNO)
  618.      VALUES (:dbaccno, :dbdate, :dbamt, :dbchkno);
  619.  
  620.  
  621.  }                                                    /* End While Loop      */
  622.  
  623. /*       **************************       ********   PAGE 13    */
  624. /*                                                                           */
  625. /*    Check for no debits on input file, log error, end pgm; else:           */
  626. /*    When end of input file is reached, calculate the average for "dbamt",  */
  627. /*    and using SELECT and SUM, MIN, MAX, AVG, COUNT, calculate summary data */
  628. /*    from the data inserted into the DEBITS  table.                         */
  629. /*    Compare the input summary data with the table inserted data and set    */
  630. /*    flags (0 for unequal, 1 for equal) to be printed out with the summary  */
  631. /*    data to file D30ACB03.SUM.                                             */
  632. /*                                                                           */
  633. /*                    *********************************                      */
  634.  
  635.     strcpy (rte_label, "no debit input ");
  636.  
  637.     if (db_count == 0)
  638.     {
  639.       fclose (ptr_dbinput);
  640.       goto ret_code;
  641.     }
  642.  
  643. /*    Calculate the average for debit (check) amount field from input.       */
  644.  
  645.     db_avg = (db_sum / db_count);
  646.  
  647. /*    Calculate summary data from columns in DEBITS table.                  */
  648.  
  649.     strcpy(rte_label, "select dcommand");
  650.  
  651. EXEC SQL
  652.      SELECT SUM(DB_AMT), AVG(DB_AMT), MIN(DB_AMT), MAX(DB_AMT), COUNT(*)
  653.      INTO :deb_sum, :deb_avg, :deb_min, :deb_max, :deb_cnt
  654.      FROM DEBITS;
  655.  
  656. /*   Compare input summary data with table summary data for match.           */
  657.  
  658.     if (db_sum == deb_sum)
  659.      {
  660.      dbsum_match = 1;
  661.      }
  662.     if (db_count == deb_cnt)
  663.      {
  664.      dbcnt_match = 1;
  665.      }
  666.     if (db_min == deb_min)
  667.      {
  668.      dbmin_match = 1;
  669.      }
  670.     if (db_max == deb_max)
  671.      {
  672.      dbmax_match = 1;
  673.      }
  674.     if (db_avg == deb_avg)
  675.      {
  676.      dbavg_match = 1;
  677.      }
  678.  
  679.  
  680. /*      ***************************       ***********  PAGE 14  */
  681. /*                                                                           */
  682. /*       Print summary data & match data for debits to summary file.         */
  683. /*                                                                           */
  684. /*      ***********************************************************          */
  685.  
  686.     fprintf(ptr_sumdata,"DEBIT  TABLE ROW COUNT:  %ld\n",deb_cnt);
  687.     fprintf(ptr_sumdata,"DEBIT.ASC  ROW COUNT:    %ld\n",db_count);
  688.     fprintf(ptr_sumdata,"DEBIT  TABLE DEPAMT SUM: %.2f\n",deb_sum);
  689.     fprintf(ptr_sumdata,"DEBIT.ASC  DEPAMT SUM:   %.2f\n",db_sum);
  690.     fprintf(ptr_sumdata,"DEBIT  TABLE DEPAMT MIN: %.2f\n",deb_min);
  691.     fprintf(ptr_sumdata,"DEBIT.ASC  DEPAMT MIN:   %.2f\n",db_min);
  692.     fprintf(ptr_sumdata,"DEBIT  TABLE DEPAMT MAX: %.2f\n",deb_max);
  693.     fprintf(ptr_sumdata,"DEBIT.ASC  DEPAMT MAX:   %.2f\n",db_max);
  694.     fprintf(ptr_sumdata,"DEBIT  TABLE DEPAMT AVG: %.2f\n",deb_avg);
  695.     fprintf(ptr_sumdata,"DEBIT.ASC  DEPAMT AVG:   %.2f\n",db_avg);
  696.     fprintf(ptr_sumdata,"%d%d%d%d%d\n",
  697.             dbcnt_match, dbsum_match, dbmin_match, dbmax_match, dbavg_match);
  698.  
  699. /*      CLOSE DEBITS.ASC FILE.   UNLOCK, RELEASE DEBITS TABLE.               */
  700.  
  701.         fclose(ptr_dbinput);
  702.         EXEC SQL COMMIT WORK;
  703. /*                                                                           */
  704.     goto end;
  705.  
  706. /*                      *******************************                      */
  707. /*                                                                           */
  708. /*    EXIT ERROR ROUTINE - Log error information to screen or print file.    */
  709. /*                                                                           */
  710. /*                      *******************************                      */
  711.  
  712. ret_code:
  713.  
  714.     errlevel = 2;
  715.     if ((ptr_errfile = fopen(errfile, "w")) == NULL)
  716.      {
  717.       printf ("\nUNABLE TO OPEN D30ACB03.ERR\n");
  718.       printf ("%s\n",rte_label);
  719.       printf ("SQLCODE IS %ld\n",sqlca.sqlcode);
  720.       printf("Number of CREDIT inserts: %ld\n", cr_count);
  721.       printf("Number of DEBIT inserts: %ld\n", db_count);
  722.       goto continue_err;
  723.      }
  724.     err_cls = 1;
  725.     fprintf(ptr_errfile,"%15s\n",rte_label);
  726.     fprintf(ptr_errfile,"SQLCODE IS %ld\n",sqlca.sqlcode);
  727.  
  728.                                               /* SQL miscellaneous error info*/
  729.  
  730.     fprintf(ptr_errfile,"\nSQLERRD[0] is %ld",sqlca.sqlerrd[0]);
  731.     fprintf(ptr_errfile,"\nSQLERRD[1] is %ld",sqlca.sqlerrd[1]);
  732.     fprintf(ptr_errfile,"\nSQLERRD[2] is %ld",sqlca.sqlerrd[2]);
  733.     fprintf(ptr_errfile,"\nSQLERRD[3] is %ld",sqlca.sqlerrd[3]);
  734.     fprintf(ptr_errfile,"\nSQLERRD[4] is %ld",sqlca.sqlerrd[4]);
  735.     fprintf(ptr_errfile,"\nSQLERRD[5] is %ld",sqlca.sqlerrd[5]);
  736.  
  737.     memcpy (sqlerrp_value, sqlca.sqlerrp, 8);
  738.     fprintf(ptr_errfile,"\nSQLERRP is %s",sqlerrp_value);
  739.  
  740.     rc = sqlaintp(msg_buf,512,0,&sqlca);
  741.     if (rc < 0)
  742.      {
  743.       fprintf (ptr_errfile, "SQLAINTP ERROR.  Return Code = %d",rc);
  744.      }
  745.     else if (rc > 0)
  746.      {
  747.       fprintf (ptr_errfile,"\n");
  748.       fprintf (ptr_errfile,msg_buf);
  749.      }
  750.     fprintf(ptr_errfile,"\nNumber of CREDIT inserts: %ld\n", cr_count);
  751.     fprintf(ptr_errfile,"Number of DEBIT inserts: %ld\n", db_count);
  752.  
  753.  
  754. /*    ***************************      **********  PAGE 15 ***  */
  755.  
  756. continue_err:
  757.  
  758.     EXEC SQL
  759.             WHENEVER SQLERROR CONTINUE;
  760.  
  761.     EXEC SQL
  762.             ROLLBACK WORK;
  763.  
  764.     if (sqlca.sqlcode < 0)
  765.      {
  766.       if ((ptr_errfile = fopen(errfile,"a")) == NULL)
  767.        {
  768.         printf ("\nUNABLE TO OPEN D30ACB03.ERR -- ROLLBACK FAILED");
  769.         goto end;
  770.        }
  771.   fprintf (ptr_errfile,"\nSQLCODE IS %ld,  ROLLBACK FAILED.",sqlca.sqlcode);
  772.        }
  773.  
  774.  
  775.  
  776. /*       **************************       ********   PAGE 16    */
  777. /*                                                                           */
  778. /*                  END OF PROGRAM - ROUTINE LABEL "END"                     */
  779. /*                                                                           */
  780. /*                 *************************************                     */
  781.  
  782. end:
  783.  
  784.     if (sum_cls != 0)
  785.       {                                               /* Summary file was    */
  786.       fclose(ptr_sumdata);                            /* opened if there     */
  787.       }                                               /* were any credits.   */
  788.  
  789.     if ((dbas_cls == 0) && (crd_opn == 0))            /* Database was opened */
  790.     {                                                 /* if credit file open */
  791.       strcpy(rte_label,"stop database  ");
  792.       sqlestpd (&sqlca);                              /* Stop using database */
  793.     }
  794.  
  795.     if (err_cls == 1)                                 /* If an SQL or 'C'    */
  796.       {                                               /* error has occurred  */
  797.       fclose(ptr_errfile);                            /* close error file.   */
  798.       }
  799.  
  800.  
  801.    return(errlevel);
  802.  
  803. }                                                     /* END MAIN FUNCTION   */
  804.  
  805.