home *** CD-ROM | disk | FTP | other *** search
/ OS/2 Shareware BBS: Product / Product.zip / DBDEMO.ZIP / DEMODB.ZIP / D30ACBD1.SQC < prev    next >
Text File  |  1991-06-03  |  16KB  |  386 lines
  1.  
  2. /**********************************       ********   PAGE 1   */
  3. /*                                                                         */
  4. /*  APPLICATION PROGRAM NAME:  D30ACBD1.SQC                                */
  5. /*                                                                         */
  6. /*  PROGRAM LANGUAGE: 'C' Language with Embedded SQL                       */
  7. /*                                                                         */
  8. /*  PROGRAM DESCRIPTION:                                                   */
  9. /*            D30ACBD1 uses 'C' language to create indexes on all          */
  10. /*            tables in the BANK database, and to do runstats              */
  11. /*            using the newly created indexes. The following functions     */
  12. /*            of SHELBY  are tested:                                       */
  13. /*                                                                         */
  14. /*                 START DB  (SHARED)                 (3.2.10)             */
  15. /*                 RESTART DB                                              */
  16. /*                 STOP DB                            (3.2.11)             */
  17. /*                 DATA TYPES: NUMERICAL              (3.3.5.1)            */
  18. /*                 DATA TYPES: CHARACTER              (3.3.5.2)            */
  19. /*                 IMBEDDED SQL                       (3.9.1)              */
  20. /*                       EXEC SQL                     (3.9.1.1)            */
  21. /*                       BEGIN/END DECLARE            (3.9.1.2)            */
  22. /*                       INCLUDE                      (3.9.1.3)            */
  23. /*                       WHENEVER                     (3.9.1.5)            */
  24. /*                       SELECT                       (3.6.1)              */
  25. /*                       ROLLBACK                     (3.6.10)             */
  26. /*                       CREATE INDEXEX                                    */
  27. /*                       RUNSTATS                                          */
  28. /*                 'C' LANGUAGE INTERFACE             (3.11.1)             */
  29. /*                       LANGUAGE RESTRICTIONS        (3.11.1.2)           */
  30. /*                       SPECIAL COMMANDS             (3.11.1.3)           */
  31. /*                       PRECOMPILER                  (3.11.1.4)           */
  32. /*                       DB ENVIRONMENT               (3.11.1.4)           */
  33. /*                       ERROR MSG                    (3.11.1.4)           */
  34. /*                                                                         */
  35. /*  AUTHOR:   S. L. Pettit                   DATE: 11/01/87                */
  36. /*                                                                         */
  37. /*  DATA BASE:                                                             */
  38. /*            SHELBY 3 BANK database                                       */
  39. /*                  - CREDITS table     - LOAN table                       */
  40. /*                  - DEBITS table      - SAVINGS table                    */
  41. /*                  - CUSTOMER table    - BOX table                        */
  42. /*                  - CHECKING table                                       */
  43. /*                                                                         */
  44. /*  INPUT FILES: NONE                                                      */
  45. /*                                                                         */
  46. /* ***************    PROLOG CONTINUED NEXT PAGE    ********************** */
  47.  
  48. /* ** PROLOG CONTINUED ************       ********   PAGE 2   */
  49. /*                                                                         */
  50. /*                                                                         */
  51. /*  OUTPUT FILES:                                                          */
  52. /*            D30ACBD1.ERR - This is a file, written to disk, only when    */
  53. /*            some unexpected error condition is returned from SQL.  It    */
  54. /*            serves as an error log, recording the error code, its cor-   */
  55. /*            responding error message, and any other useful information   */
  56. /*            which the program might be able to give.                     */
  57. /*                                                                         */
  58. /*  DEPENDENCIES:                                                          */
  59. /*            INVICTA, SHELBY 3 DATABASE KERNEL,                           */
  60. /*            database manager started                                     */
  61. /*            creation of SHELBY 3 BANK database,                          */
  62. /*            creation of all tables in BANK database,                     */
  63. /*            precompiling and binding with SQLPREP                        */
  64. /*            compilation with IBM C Compiler 2.0, using compiler options: */
  65. /*                     "/AL /Gs /DLINT_ARGS /DSQL_REL_10 /W3 /Od "         */
  66. /*                                                                         */
  67. /*  FUNCTION CALLS:                                                        */
  68. /*            COMPILER LIBRARY                                             */
  69. /*                 stdio.h - fopen,fclose,fgets,fprintf                    */
  70. /*                 string.h - strcpy                                       */
  71. /*            SHELBY 3 LIBRARY                                             */
  72. /*                 sql.h - sqlaintp; sqlenv - sqlestrd,sqlestpd,sqlerest   */
  73. /*                 sqlenv.h - sqleisig (ctrl-break processing)             */
  74. /*            OTHER                                                        */
  75. /*                 none                                                    */
  76. /*                                                                         */
  77. /*  ERROR CONDITIONS:                                                      */
  78. /*        'C' NORMAL - specifically tested for and handled by the ap-      */
  79. /*                     plication program:                                  */
  80. /*                        log error, exit thru "end" routine.              */
  81. /*                                                                         */
  82. /*       SQL  ABNORMAL - unexpected and resulting in program termination:  */
  83. /*                        log error, exit thru "ret_code" & "end" routine  */
  84. /*                       -1015   Restart Database                          */
  85. /*                                                                         */
  86. /*  MODIFICATIONS:                                                         */
  87. /*            Date      Author         Description                         */
  88. /*                                                                         */
  89. /*          10/26/87    D.J.Grant      Added SQLEISIG function call, and   */
  90. /*                                     enhances exit_error to also print   */
  91. /*                                     SQLERRD[0] - [5] and SQL_ERRP.      */
  92. /*          11/30/87    W.B.Brown      Add prolog                          */
  93. /*          02/03/89    W.B.Brown      Upgrade for release 3.0             */
  94. /***************************************************************************/
  95.  
  96. #include <stdio.h>
  97. #include <string.h>
  98. #include <sql.h>
  99. #include <sqlenv.h>
  100. #include <sqlutil.h>
  101.  
  102. EXEC SQL INCLUDE sqlca;
  103.  
  104. #define LABELLEN   16       /* statement label length for error logging    */
  105.                             /*    15 characters + string null              */
  106.  
  107. unsigned char msgbuf[512];
  108. unsigned char s_label[LABELLEN];
  109. unsigned char database[] = "BANK";             /* database name            */
  110. unsigned char password[] = "";                 /* password to database     */
  111. unsigned char errfile[] = "D30ACBD1.ERR";      /* error file DOS name      */
  112. unsigned char errpterm[9] = "        ";        /* Null terminated string   */
  113.                                                /* for printing sqlerrp     */
  114. unsigned char customer[] = "DBSYSADM.CUSTOMER";
  115. unsigned char box[] = "DBSYSADM.BOX";
  116. unsigned char checking[] = "DBSYSADM.CHECKING";
  117. unsigned char savings[] = "DBSYSADM.SAVINGS";
  118. unsigned char loan[] = "DBSYSADM.LOAN";
  119. unsigned char credits[] = "DBSYSADM.CREDITS";
  120. unsigned char debits[] = "DBSYSADM.DEBITS";
  121.  
  122. /***************************************************************************/
  123. /* SQL DECLARE SECTION -  For this program none are declared.              */
  124. /***************************************************************************/
  125.  
  126. EXEC SQL BEGIN DECLARE SECTION;
  127.  
  128. EXEC SQL END DECLARE SECTION;
  129.  
  130. /***************************************************************************/
  131.  
  132.  
  133. int main (void);
  134. main ()
  135. {
  136.    FILE *ptr_error;              /* declare error file                     */
  137.    int errorlevel = 0;           /* to be returned to DOS                  */
  138.    short rc = 0;                 /* return code from sqlaintp (error mes-  */
  139.                                  /*    sage retrieval function)            */
  140.    unsigned char *ptr_idxlist[2];
  141.    int numidx;
  142.  
  143. install_sig:
  144.    strcpy (s_label, "install_sig:");
  145.    sqleisig (&sqlca);                       /* Install DBM supplied signal */
  146.                                             /* handling routine.           */
  147.    if (sqlca.sqlcode != 0)
  148.    {
  149.       goto exit_error;
  150.    }
  151.  
  152.    EXEC SQL
  153.         WHENEVER SQLERROR GOTO exit_error;
  154.    EXEC SQL
  155.         WHENEVER SQLWARNING CONTINUE;
  156.    EXEC SQL
  157.         WHENEVER NOT FOUND CONTINUE;
  158.  
  159. start_db:
  160.    sqlestrd (database,password,'S',&sqlca); /* Start using specified DB,   */
  161.                                             /* with specified password, in */
  162.                                             /* shared mode.                */
  163.    if (sqlca.sqlcode == -1015)              /* If the database needs to be */
  164.    {                                        /*   restarted, due to previ-  */
  165.       strcpy (s_label,"restart:");          /*   ous system failure, issue */
  166.       sqlerest (database,password,&sqlca);  /*   restart, followed by      */
  167.       if (sqlca.sqlcode != 0)               /*   another start using.      */
  168.       {
  169.          goto exit_error;
  170.       }
  171.       sqlestrd (database,password,'S',&sqlca);
  172.    }
  173.    strcpy(s_label,"start_db:   ");
  174.    if (sqlca.sqlcode != 0)
  175.    {
  176.       goto exit_error;
  177.    }
  178.  
  179. create_index1:
  180.    strcpy(s_label,"create_index1:");
  181.  
  182.    printf ("\n CREATING BOX INDEX ON BOX NUMBER \n");
  183.  
  184.  
  185.    EXEC SQL
  186.         CREATE UNIQUE INDEX IDX_B_NUMB ON BOX (B_NUMB);
  187.  
  188. create_index2:
  189.    strcpy(s_label,"create_index2:");
  190.  
  191.    printf ("\n CREATING LOAN INDEX ON LOAN NUMBER \n");
  192.  
  193.    EXEC SQL
  194.         CREATE UNIQUE INDEX IDX_L_NUMB ON LOAN (L_NUMB);
  195.  
  196. create_index3:
  197.    strcpy(s_label,"create_index3:");
  198.  
  199.    printf ("\n CREATING LOAN INDEX ON LOAN SSN \n");
  200.  
  201.    EXEC SQL
  202.         CREATE INDEX IDX_L_SSN ON LOAN (L_SSN);
  203.  
  204. create_index4:
  205.    strcpy(s_label,"create_index4:");
  206.  
  207.    printf ("\n CREATING SAVINGS INDEX ON SAVINGS NUMBER \n");
  208.  
  209.    EXEC SQL
  210.         CREATE UNIQUE INDEX IDX_S_ACCNUMB ON SAVINGS (S_ACCNUMB);
  211.  
  212. create_index5:
  213.    strcpy(s_label,"create_index5:");
  214.  
  215.    printf ("\n CREATING SAVINGS INDEX ON SAVINGS SSN \n");
  216.  
  217.    EXEC SQL
  218.         CREATE INDEX IDX_S_SSN ON SAVINGS (S_SSN);
  219.  
  220. create_index6:
  221.    strcpy(s_label,"create_index7:");
  222.  
  223.    printf ("\n CREATING CHECKING INDEX ON CHECKING SSN1 \n");
  224.  
  225.    EXEC SQL
  226.         CREATE INDEX IDX_C_SSN1 ON CHECKING (C_SSN1);
  227.  
  228. create_index7:
  229.    strcpy(s_label,"create_index7:");
  230.  
  231.    printf ("\n CREATING CREDITS INDEX ON CREDITS NUMBER \n");
  232.  
  233.    EXEC SQL
  234.         CREATE INDEX IDX_CR_ACCNUMB ON CREDITS (CR_ACCNUMB);
  235.  
  236. create_index8:
  237.    strcpy(s_label,"create_index8:");
  238.  
  239.    printf ("\n CREATING DEBITS INDEX ON DEBITS NUMBER \n");
  240.  
  241.    EXEC SQL
  242.         CREATE INDEX IDX_DB_ACCNUMB ON DEBITS (DB_ACCNUMB);
  243.  
  244.  
  245. runstat1:
  246.    strcpy(s_label,"runstat1:");
  247.    ptr_idxlist[0] = "DBSYSADM.IDX_B_NUMB";
  248.    numidx = 1;
  249.  
  250. // printf ("\n RUNSTATS ON BOX NUMBER INDEX \n");
  251.  
  252. //   sqlustat (box, numidx, ptr_idxlist, 'B', 'R', &sqlca);
  253. //   if (sqlca.sqlcode != 0)
  254. //   {
  255. //      goto exit_error;
  256. //   }
  257.  
  258. runstat2:
  259.    strcpy(s_label,"runstat2:");
  260.    ptr_idxlist[0] = "DBSYSADM.IDX_L_NUMB";
  261.    ptr_idxlist[1] = "DBSYSADM.IDX_L_SSN";
  262.    numidx = 2;
  263.  
  264. // printf ("\n RUNSTATS ON LOAN NUMBER AND SSN \n");
  265.  
  266. //   sqlustat (loan, numidx, ptr_idxlist, 'B', 'R', &sqlca);
  267. //   if (sqlca.sqlcode != 0)
  268. //   {
  269. //      goto exit_error;
  270. //   }
  271.  
  272. runstat3:
  273.    strcpy(s_label,"runstat3:");
  274.    ptr_idxlist[0] = "DBSYSADM.IDX_S_ACCNUMB";
  275.    ptr_idxlist[1] = "DBSYSADM.IDX_S_SSN";
  276.    numidx = 2;
  277.  
  278. // printf ("\n RUNSTATS ON SAVINGS NUMBER AND SSN \n");
  279.  
  280. // sqlustat (savings, numidx, ptr_idxlist, 'B', 'R', &sqlca);
  281. // if (sqlca.sqlcode != 0)
  282. // {
  283. //    goto exit_error;
  284. // }
  285.  
  286. runstat4:
  287.    strcpy(s_label,"runstat4:");
  288.    ptr_idxlist[0] = "DBSYSADM.IDX_C_SSN1";
  289.    numidx = 1;
  290.  
  291. // printf ("\n RUNSTATS ON CHECKING NUMBER AND SSN1 \n");
  292.  
  293. // sqlustat (checking, numidx, ptr_idxlist, 'B', 'R', &sqlca);
  294. // if (sqlca.sqlcode != 0)
  295. // {
  296. //    goto exit_error;
  297. // }
  298.  
  299. runstat5:
  300.    strcpy(s_label,"runstat5:");
  301.    ptr_idxlist[0] = "DBSYSADM.IDX_CR_ACCNUMB";
  302.    numidx = 1;
  303.  
  304. // printf ("\n RUNSTATS ON CREDITS NUMBER INDEX \n");
  305.  
  306. //   sqlustat (credits, numidx, ptr_idxlist, 'B', 'R', &sqlca);
  307. //   if (sqlca.sqlcode != 0)
  308. //   {
  309. //      goto exit_error;
  310. //   }
  311.  
  312. runstat6:
  313.    strcpy(s_label,"runstat6:");
  314.    ptr_idxlist[0] = "DBSYSADM.IDX_DB_ACCNUMB";
  315.    numidx = 1;
  316.  
  317. // printf ("\n RUNSTATS ON DEBITS NUMBER INDEX \n");
  318.  
  319. //   sqlustat (debits, numidx, ptr_idxlist, 'B', 'R', &sqlca);
  320. //   if (sqlca.sqlcode != 0)
  321. //   {
  322. //      goto exit_error;
  323. //   }
  324.  
  325.    goto exit_normal;
  326.  
  327.  
  328.  
  329. exit_error:
  330.                                             /* if unable to open errfile,  */
  331.                                             /* write messages to screen.   */
  332.    if ((ptr_error = fopen(errfile, "w")) == NULL)
  333.    {
  334.       printf ("\nUNABLE TO OPEN D30ACBD1.ERR\n");
  335.       printf ("%s   SQLCODE IS %ld\n",s_label,sqlca.sqlcode);
  336.       goto continue_err;
  337.    }
  338.    fprintf (ptr_error,"%s\n",s_label);     /* write error information to   */
  339.                                            /* file.                        */
  340.    fprintf (ptr_error,"SQLCODE IS %ld\n",sqlca.sqlcode);
  341.    fprintf (ptr_error,"SQLERRD[0] IS %ld\n",sqlca.sqlerrd[0]);
  342.    fprintf (ptr_error,"SQLERRD[1] IS %ld\n",sqlca.sqlerrd[1]);
  343.    fprintf (ptr_error,"SQLERRD[2] IS %ld\n",sqlca.sqlerrd[2]);
  344.    fprintf (ptr_error,"SQLERRD[3] IS %ld\n",sqlca.sqlerrd[3]);
  345.    fprintf (ptr_error,"SQLERRD[4] IS %ld\n",sqlca.sqlerrd[4]);
  346.    fprintf (ptr_error,"SQLERRD[5] IS %ld\n",sqlca.sqlerrd[5]);
  347.    memcpy (errpterm,sqlca.sqlerrp,8);
  348.    fprintf (ptr_error,"SQLERRP IS %s\n",errpterm);
  349.    rc = sqlaintp (msgbuf,512,50,&sqlca);   /* call error message retreival */
  350.    if (rc < 0)                             /* -- retrieve failed.          */
  351.    {
  352.       fprintf (ptr_error,"SQLAINTP ERROR.   Return code = %d",rc);
  353.    }
  354.    else if (rc > 0)                        /* -- retrieve successful.      */
  355.         {
  356.            fprintf (ptr_error,msgbuf);
  357.         }
  358.    fclose (ptr_error);                     /* close error file             */
  359.  
  360. continue_err:
  361.    errorlevel = 2;
  362.    EXEC SQL                                /* reset sqlerror handling to   */
  363.         WHENEVER SQLERROR CONTINUE;        /*  prevent endless error loop  */
  364.                                            /*  if rollback should fail.    */
  365.    EXEC SQL
  366.         ROLLBACK WORK;
  367.  
  368.    if (sqlca.sqlcode != 0)                 /* If error, add on to already  */
  369.                                            /* created errfile.  If unable  */
  370.                                            /* to open, write to screen.    */
  371.    {
  372.       if ((ptr_error = fopen(errfile, "a")) == NULL)
  373.       {
  374.          printf ("\nUNABLE TO OPEN D30ACBD1.ERR -- ROLLBACK FAILED.");
  375.          goto exit_normal;
  376.       }
  377.       fprintf (ptr_error,"\nSQLCODE IS %ld,  ROLLBACK FAILED.",sqlca.sqlcode);
  378.       fclose (ptr_error);
  379.    }
  380.  
  381. exit_normal:
  382.    sqlestpd (&sqlca);                      /* stop using the database      */
  383. end:
  384.    return(errorlevel);
  385. }
  386.