home *** CD-ROM | disk | FTP | other *** search
/ OS/2 Shareware BBS: 10 Tools / 10-Tools.zip / VSCPPv8.zip / VACPP / IBMCPP / samples / VISBUILD / RAPSHEET / CPPOV23 / DBQUERY.SQC < prev    next >
Text File  |  1995-05-25  |  19KB  |  430 lines
  1. #include <stdlib.h>
  2. #include <stdio.h>
  3. #include <string.h>
  4. #include <sqlenv.h>                                                                            
  5. #include <rap.h>
  6. #ifndef _c
  7. #define _c
  8. #include <dbquery.h>
  9. #undef _c
  10. #endif
  11.  
  12. EXEC SQL INCLUDE SQLCA;                                                                   
  13.  
  14.  
  15. /*DETAILS                                                                  */
  16.   EXEC SQL BEGIN DECLARE SECTION;
  17.  
  18.    long      numberOfAliases=0;
  19.    long      numberOfArrests=0;
  20.    long      numberOfLastSeens=0;
  21.    long      numberOfSuspects=0;
  22.  
  23.    char      SLNAME[30];             /* Hold area for return name                */
  24.    char      SFNAME[20];             /* Hold area for return name                */
  25.    short     SGENDER;               /* Hold area for return GENDER              */
  26.    short     SHCOLOR;               /* Hold area for return HAIR COLOR          */
  27.    short     SECOLOR;               /* Hold area for return EYE  COLOR          */
  28.    short     SWEIGHT;               /* Hold area for return weight              */
  29.    short     SHEIGHT;               /* Hold area for return height              */
  30.    short     SHSCAR;                /* Hold area for return scar                */
  31.    short     SHMOLE;                /* Hold area for return mole                */
  32.    short     SHTATTOO;              /* Hold area for return tattoo              */
  33.    char      SBDATE[10];            /* Hold area for return date                */
  34.    long      SBOOK;                 /* Hold area for return book number         */
  35.    long      SMFRONT;               /* Hold area for return front view RC ID    */
  36.    long      SMRIGHT;               /* Hold area for return right view RC ID    */
  37.    long      SLEFT;                 /* Hold area for return left view RC ID     */
  38.    char      SSTREET[30];           /* Hold area for return street              */
  39.    char      SCITY[20];             /* Hold area for return city                */
  40.    char      SCOUNTY[20];           /* Hold area for return county              */
  41.    char      SSTATE[2];             /* Hold area for return state               */
  42.    char      SZIP[5];               /* Hold area for return zip                 */
  43.    char      SINFO[100];            /* Hold area for return misc info.          */
  44.    char      SMO[100];              /* Hold area for return motus operandi      */
  45.    char      SSCARDSC[50];          /* Hold area for return scar desc           */
  46.    char      SMOLEDSC[50];          /* Hold area for return mole desc           */
  47.    char      STATODSC[50];          /* Hold area for return tattoo desc         */
  48. /*ARRESTS                                                                       */
  49.    short     ARCHARGE;              /* Hold area for charge                     */
  50.    short     ARSTATUS;              /* Hold area for status                     */
  51.    char      ARSTDATE[10];          /* Hold area for arrest date                */
  52.    char      ARREPORT[200];         /* Hold area for arrest report              */
  53. /*ALIASES                                                                       */
  54.    char      ALNAME[50];            /* Hold area for alias name                 */
  55. /*LAST SEEN                                                                     */
  56.    char      LSLOC[50];             /* Hold area for last seen location         */
  57.    char      LSWITNESS[50];         /* Hold area for     "     by               */
  58.    char      LSDATE[10];            /* Hold area for     "     date             */
  59.    char      LSSTREET[30];          /* Hold area for     "     street           */
  60.  
  61.    char      LSCITY[20];            /* Hold area for     "     city             */
  62.    char      LSSTATE[2];            /* Hold area for     "     state            */
  63.    char      LSCOMMENT[50];         /* Hold area for     "     comments         */
  64.  
  65.   EXEC SQL END DECLARE SECTION;                                                            
  66.  
  67.   SQL_API_RC sql_rc = 0;
  68.   
  69. /*****************************************************************/ 
  70. /*                                                               */
  71. /* setupDataBase                                                 */
  72. /*                                                               */
  73. /*****************************************************************/
  74.  
  75. long setupDatabase()
  76. {
  77.  
  78.   EXEC SQL WHENEVER NOT FOUND GO TO error;
  79.   EXEC SQL WHENEVER SQLERROR GO TO error;
  80.  
  81.   sql_rc = sqleisig(&sqlca);
  82.   EXEC SQL CONNECT TO RAP IN SHARE MODE;
  83.   EXEC SQL SELECT COUNT(*) INTO :numberOfSuspects FROM SUSPECT_DETAIL;
  84.  
  85.   EXEC SQL DECLARE suspect_cursor CURSOR FOR
  86.        SELECT SUSPECT_LAST_NAME, SUSPECT_FIRST_NAME, SUSPECT_GENDER, SUSPECT_HAIR_COLOR,
  87.         SUSPECT_EYE_COLOR,  SUSPECT_WEIGHT, SUSPECT_HEIGHT, SUSPECT_HAS_SCAR,
  88.         SUSPECT_HAS_MOLE, SUSPECT_HAS_TATTOO, SUSPECT_BIRTH_DATE,
  89.         SUSPECT_BOOK_NUM, SUSPECT_MUG_FRONT, SUSPECT_MUG_RIGHT,
  90.         SUSPECT_MUG_LEFT, SUSPECT_STREET, SUSPECT_CITY,
  91.         SUSPECT_COUNTY, SUSPECT_STATE, SUSPECT_ZIP, SUSPECT_INFO,
  92.         SUSPECT_MO, SUSPECT_SCAR_DESC, SUSPECT_MOLE_DESC,
  93.         SUSPECT_TATTOO_DSC
  94.        FROM SUSPECT_DETAIL;
  95.   EXEC SQL OPEN suspect_cursor;
  96.  
  97.   EXEC SQL DECLARE alias_cursor CURSOR FOR
  98.        SELECT ALIAS_NAME
  99.        FROM   SUSPECT_ALIAS
  100.        WHERE  ALIAS_SUSPECT = :SBOOK;
  101.   
  102.  
  103.   EXEC SQL DECLARE arrest_cursor CURSOR FOR
  104.        SELECT ARREST_CHARGE, ARREST_STATUS, ARREST_STATUS_DATE, ARREST_REPORT
  105.        FROM   SUSPECT_ARREST
  106.        WHERE  ARREST_SUSPECT = :SBOOK;
  107.  
  108.   EXEC SQL DECLARE lastSeen_cursor CURSOR FOR
  109.        SELECT LAST_SEEN_LOCATION, LAST_SEEN_BY, LAST_SEEN_DATE, LAST_SEEN_STREET,
  110.               LAST_SEEN_CITY, LAST_SEEN_STATE, LAST_SEEN_COMMENT
  111.        FROM   SUSPECT_LAST_SEEN
  112.        WHERE  LAST_SEEN_SUSPECT = :SBOOK;
  113.  
  114.   error:
  115.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  116.     return (SQLCODE);
  117. }
  118.  
  119.  
  120. /*****************************************************************/ 
  121. /*                                                               */
  122. /* howManySuspects                                               */
  123. /*                                                               */
  124. /*   Returns the number of suspect records in the database       */
  125. /*****************************************************************/
  126. long howManySuspects()
  127. {
  128.    return (numberOfSuspects);
  129. }
  130.  
  131. /*****************************************************************/ 
  132. /*                                                               */
  133. /* howManyAliases                                                */
  134. /*                                                               */
  135. /*   Returns the number of alias   records in the database       */
  136. /*****************************************************************/
  137. long howManyAliases(unsigned long bookNumber)
  138. {
  139.  
  140.    SBOOK = bookNumber;
  141.    EXEC SQL WHENEVER NOT FOUND GO TO error;
  142.    EXEC SQL WHENEVER SQLERROR GO TO error;
  143.    EXEC SQL SELECT COUNT(*) INTO :numberOfAliases 
  144.             FROM   SUSPECT_ALIAS
  145.             WHERE  ALIAS_SUSPECT = :SBOOK;
  146.  
  147.   error:
  148.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  149.     return (numberOfAliases);
  150. }
  151.  
  152. /*****************************************************************/ 
  153. /*                                                               */
  154. /* howManyArrests                                                */
  155. /*                                                               */
  156. /*   Returns the number of arrest    records in the database       */
  157. /*****************************************************************/
  158. long howManyArrests(unsigned long bookNumber)
  159. {
  160.    SBOOK = bookNumber;
  161.    EXEC SQL WHENEVER NOT FOUND GO TO error;
  162.    EXEC SQL WHENEVER SQLERROR GO TO error;
  163.    EXEC SQL SELECT COUNT(*) INTO :numberOfArrests
  164.             FROM   SUSPECT_ARREST
  165.             WHERE  ARREST_SUSPECT = :SBOOK;
  166.  
  167.   error:
  168.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  169.     return (numberOfArrests);
  170. }
  171.  
  172. /*****************************************************************/ 
  173. /*                                                               */
  174. /* howManyLastSeens                                              */
  175. /*                                                               */
  176. /*   Returns the number of lastseen records in the database      */
  177. /*****************************************************************/
  178. long howManyLastSeens(unsigned long bookNumber)
  179. {
  180.    SBOOK = bookNumber;
  181.    EXEC SQL WHENEVER NOT FOUND GO TO error;
  182.    EXEC SQL WHENEVER SQLERROR GO TO error;
  183.    EXEC SQL SELECT COUNT(*) INTO :numberOfLastSeens
  184.             FROM   SUSPECT_LAST_SEEN
  185.             WHERE  LAST_SEEN_SUSPECT = :SBOOK;
  186.  
  187.   error:
  188.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  189.     return (numberOfLastSeens);
  190. }
  191.  
  192.  
  193. /*****************************************************************/ 
  194. /*                                                               */
  195. /* getNextSuspect                                                */
  196. /*                                                               */
  197. /*    Returns the next suspect record from the database          */
  198. /*****************************************************************/
  199. long getNextSuspect(SUSPECT_STRUCT *suspectStructure)
  200. {
  201.  
  202.   printf ("DBQUERY==> Entered getNextSuspect\n");
  203.   EXEC SQL WHENEVER NOT FOUND GO TO error;
  204.   EXEC SQL WHENEVER SQLERROR GO TO error;
  205.  
  206.   printf ("DBQUERY==> About to perform FETCH of suspect info.\n");
  207.   EXEC SQL FETCH suspect_cursor
  208.            INTO :SLNAME, :SFNAME, :SGENDER, :SHCOLOR, :SECOLOR, :SWEIGHT, :SHEIGHT,
  209.                 :SHSCAR, :SHMOLE, :SHTATTOO, :SBDATE, :SBOOK,
  210.                 :SMFRONT, :SMRIGHT, :SLEFT, :SSTREET, :SCITY,
  211.                 :SCOUNTY, :SSTATE, :SZIP, :SINFO, :SMO, :SSCARDSC,
  212.                 :SMOLEDSC, :STATODSC;
  213.   printf ("DBQUERY==> Completed FETCH of suspect info. Return code is %d.\n",SQLCODE);
  214.  
  215.   memset(suspectStructure,0,sizeof(SUSPECT_STRUCT));
  216.   printf ("DBQUERY==> Initialized structure. book_number is %d.\n",suspectStructure->book_number);
  217.   memcpy(suspectStructure->lastName,SLNAME,LAST_NAME_LEN);
  218.   printf ("DBQUERY==> Set lastName to %s.\n",suspectStructure->lastName);
  219.   memcpy(suspectStructure->firstName,SFNAME,FIRST_NAME_LEN);
  220.   memcpy(suspectStructure->street,SSTREET,ADDRESS_STREET_LEN);
  221.   memcpy(suspectStructure->city,SCITY,ADDRESS_CITY_LEN);
  222.   memcpy(suspectStructure->county,SCOUNTY,ADDRESS_COUNTY_LEN);
  223.   memcpy(suspectStructure->state,SSTATE,ADDRESS_STATE_LEN);
  224.   memcpy(suspectStructure->zip,SZIP,ADDRESS_ZIP_LEN);
  225.   memcpy(suspectStructure->info,SINFO,SUSPECT_INFO_LEN);
  226.   suspectStructure->hasMole = SHMOLE;
  227.   suspectStructure->hasScar = SHSCAR;
  228.   suspectStructure->hasTattoo =  SHTATTOO;
  229.   suspectStructure->book_number = SBOOK;
  230.   suspectStructure->gender =     SGENDER;
  231.   suspectStructure->hairColor =  SHCOLOR;
  232.   suspectStructure->eyeColor =   SECOLOR;
  233.   suspectStructure->weight =     SWEIGHT;
  234.   suspectStructure->height =     SHEIGHT;
  235.   memcpy(suspectStructure->birthDate,SBDATE,DATE_LEN);
  236.   suspectStructure->mugFront =   SMFRONT;
  237.   suspectStructure->mugRight =   SMRIGHT;
  238.   suspectStructure->mugLeft =    SLEFT;
  239.   memcpy(suspectStructure->MO,SMO,SUSPECT_MO_DESC_LEN);
  240.   memcpy(suspectStructure->scarDescription,SSCARDSC,SUSPECT_TRAIT_DESC_LEN);
  241.   memcpy(suspectStructure->moleDescription,SMOLEDSC,SUSPECT_TRAIT_DESC_LEN);
  242.   memcpy(suspectStructure->tattooDescription,STATODSC,SUSPECT_TRAIT_DESC_LEN);
  243.  
  244.   printf ("DBQUERY==> Leaving getNextSuspect\n");
  245.  
  246.   error:
  247.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  248.     return(SQLCODE);
  249. }
  250.  
  251. /*****************************************************************/ 
  252. /*                                                               */
  253. /* getNextAlias                                                  */
  254. /*                                                               */
  255. /*    Returns the next alias   record from the database          */
  256. /*****************************************************************/
  257. long getNextAlias(char * anAliasName, unsigned long bookNumber)
  258. {
  259.  
  260.  
  261.   EXEC SQL WHENEVER NOT FOUND GO TO error;
  262.   EXEC SQL WHENEVER SQLERROR GO TO error;
  263.  
  264.  
  265.   EXEC SQL FETCH alias_cursor
  266.            INTO :ALNAME;
  267.   memset(anAliasName,0,ALIAS_NAME_LEN+1);
  268.   memcpy(anAliasName, ALNAME,ALIAS_NAME_LEN);
  269.  
  270.   error:
  271.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  272.     return(SQLCODE);
  273. }
  274.  
  275. /*****************************************************************/ 
  276. /*                                                               */
  277. /* getNextArrest                                                 */
  278. /*                                                               */
  279. /*    Returns the next arrest  record from the database          */
  280. /*****************************************************************/
  281. long getNextArrest(ARREST_STRUCT *arrestStructure, unsigned long bookNumber)
  282. {
  283.  
  284.  
  285.   EXEC SQL WHENEVER NOT FOUND GO TO error;
  286.   EXEC SQL WHENEVER SQLERROR GO TO error;
  287.  
  288.   EXEC SQL FETCH arrest_cursor
  289.            INTO  :ARCHARGE, :ARSTATUS, :ARSTDATE, :ARREPORT;
  290.          
  291.  
  292.   memset(arrestStructure,0,sizeof(ARREST_STRUCT));
  293.   arrestStructure->arrestCharge = ARCHARGE;
  294.   arrestStructure->arrestStatus = ARSTATUS;
  295.   memcpy(arrestStructure->arrestDate,ARSTDATE,DATE_LEN);
  296.   memcpy(arrestStructure->arrestReport,ARREPORT,ARREST_REPORT_LEN);
  297.  
  298.   error:
  299.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  300.     return(SQLCODE);
  301. }
  302.  
  303. /*****************************************************************/ 
  304. /*                                                               */
  305. /* getNextLastSeen                                               */
  306. /*                                                               */
  307. /*    Returns the next lastseen record from the database         */
  308. /*****************************************************************/
  309. long getNextLastSeen(LAST_SEEN_STRUCT *lastSeenStructure, unsigned long bookNumber)
  310. {
  311.  
  312.  
  313.  
  314.   EXEC SQL WHENEVER NOT FOUND GO TO error;
  315.   EXEC SQL WHENEVER SQLERROR GO TO error;
  316.  
  317.  
  318.   EXEC SQL FETCH lastSeen_cursor
  319.            INTO  :LSLOC, :LSWITNESS, :LSDATE, :LSSTREET, :LSCITY, :LSSTATE, :LSCOMMENT;
  320.  
  321.   memset(lastSeenStructure,0,sizeof(LAST_SEEN_STRUCT));
  322.   memcpy(lastSeenStructure->lastSeenLocation,LSLOC,LS_LOC_LEN);
  323.   memcpy(lastSeenStructure->lastSeenWitness,LSWITNESS,LS_WITNESS_LEN);
  324.   memcpy(lastSeenStructure->lastSeenDate,LSDATE,DATE_LEN);
  325.   memcpy(lastSeenStructure->lastSeenStreet,LSSTREET,ADDRESS_STREET_LEN);
  326.   memcpy(lastSeenStructure->lastSeenCity,LSCITY,ADDRESS_CITY_LEN);
  327.   memcpy(lastSeenStructure->lastSeenState,LSSTATE,ADDRESS_STATE_LEN);
  328.   memcpy(lastSeenStructure->lastSeenComment,LSCOMMENT,COMMENT_LEN);
  329.  
  330.   error:
  331.     EXEC SQL WHENEVER SQLERROR CONTINUE;
  332.     return(SQLCODE);
  333. }
  334.  
  335. /*****************************************************************/ 
  336. /*                                                               */
  337. /* closeAlC()                                                    */
  338. /*                                                               */
  339. /*   Closes the alias cursor                                     */
  340. /*****************************************************************/
  341. long closeAlC()
  342. {
  343.    EXEC SQL WHENEVER SQLERROR GO TO error;
  344.    EXEC SQL CLOSE alias_cursor;
  345.   error:
  346.    return (SQLCODE);
  347. }
  348. /*****************************************************************/ 
  349. /*                                                               */
  350. /* closeArC                                                      */
  351. /*                                                               */
  352. /*   Closes the database                                         */
  353. /*****************************************************************/
  354. long closeArC()
  355. {
  356.    EXEC SQL WHENEVER SQLERROR GO TO error;
  357.    EXEC SQL CLOSE arrest_cursor;
  358.   error:
  359.    return (SQLCODE);
  360. }
  361. /*****************************************************************/ 
  362. /*                                                               */
  363. /* closeLsC                                                      */
  364. /*                                                               */
  365. /*   Closes the last seen cursor                                 */
  366. /*****************************************************************/
  367. long closeLsC()
  368. {
  369.    EXEC SQL WHENEVER SQLERROR GO TO error;
  370.    EXEC SQL CLOSE lastSeen_cursor;
  371.   error:
  372.    return (SQLCODE);
  373. }
  374.  
  375. /*****************************************************************/ 
  376. /*                                                               */
  377. /* openAlC()                                                     */
  378. /*                                                               */
  379. /*   opens  the alias cursor                                     */
  380. /*****************************************************************/
  381. long openAlC()
  382. {
  383.    EXEC SQL WHENEVER SQLERROR GO TO error;
  384.    EXEC SQL OPEN  alias_cursor;
  385.   error:
  386.    return (SQLCODE);
  387. }
  388. /*****************************************************************/ 
  389. /*                                                               */
  390. /* openArC                                                      */
  391. /*                                                               */
  392. /*   opens  the arrest cursor                                    */
  393. /*****************************************************************/
  394. long openArC()
  395. {
  396.    EXEC SQL WHENEVER SQLERROR GO TO error;
  397.    EXEC SQL OPEN  arrest_cursor;
  398.   error:
  399.    return (SQLCODE);
  400. }
  401. /*****************************************************************/ 
  402. /*                                                               */
  403. /* openLsC                                                      */
  404. /*                                                               */
  405. /*   opens  the last seen cursor                                 */
  406. /*****************************************************************/
  407. long openLsC()
  408. {
  409.    EXEC SQL WHENEVER SQLERROR GO TO error;
  410.    EXEC SQL OPEN lastSeen_cursor;
  411.   error:
  412.    return (SQLCODE);
  413. }
  414.  
  415. /*****************************************************************/ 
  416. /*                                                               */
  417. /* closeDB                                                       */
  418. /*                                                               */
  419. /*   Closes the database                                         */
  420. /*****************************************************************/
  421. long closeDB()
  422. {
  423.    EXEC SQL WHENEVER SQLERROR GO TO error;
  424.    EXEC SQL CONNECT RESET;
  425.   error:
  426.    return (SQLCODE);
  427. }
  428.  
  429.  
  430.