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