/******************************************************************************
**
** Source File Name = lobloc.sqc
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000 
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE: This sample program demonstrates the use LOB locators.
**          The program creates a CURSOR, and fetches the contents of
**          the "emp_resume" table (the SAMPLE database must be
**          installed with the "db2sampl" executable), and then
**          obtains the information specific to the "Department".
**          This information is then copied over to a buffer.
**          After the whole table has been scaned the program outputs
**          the content of the buffer (information specific to the
**          "department" for all the resumes in the "emp_resume"
**          table.
**
** An external function "check_error" is found in the file "util.c"
** which must be compiled along with this file.  
**
**    EXTERNAL DEPENDENCIES :
**       - Ensure existence of database for precompile purposes.
**       - Precompile with the SQL precompiler (PREP in DB2)
**       - Bind to a database (BIND in DB2)
**       - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
**         or the Microsoft Visual C++ compiler (Windows) 
**         or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
**   -  "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
**   -  "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"


EXEC SQL INCLUDE SQLCA;


int main(int argc, char *argv[]) {

#ifdef DB2MAC
   char * bufptr;
#endif

   EXEC SQL BEGIN DECLARE SECTION; /* :rk.1:erk. */
      char        number[7];
      sqlint32    deptInfoBeginLoc;
      sqlint32    deptInfoEndLoc;
      SQL TYPE IS CLOB_LOCATOR resume;
      SQL TYPE IS CLOB_LOCATOR deptBuffer;
      short lobind;
      char buffer[1000]="";
      char userid[9];
      char passwd[19];
   EXEC SQL END DECLARE SECTION;


   printf( "Sample C program: LOBLOC\n" );

   if (argc == 1) {
      EXEC SQL CONNECT TO sample;
	  EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else if (argc == 3) { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;
      EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else {
      printf ("\nUSAGE: lobloc [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   /* Employee A10030 is not included in the following select, because 
      the lobeval program manipulates the record for A10030 so that it is 
      not compatible with lobloc */
 
   EXEC SQL DECLARE c1 CURSOR FOR
            SELECT empno, resume FROM emp_resume WHERE resume_format='ascii' 
            AND empno <> 'A00130'; 

   EXEC SQL OPEN c1;
   EMB_SQL_CHECK("OPEN CURSOR");

   do {
      EXEC SQL FETCH c1 INTO :number, :resume :lobind;  /* :rk.2:erk. */
      if (SQLCODE != 0) break;
      if (lobind < 0) {
         printf ("NULL LOB indicated\n");
      } else {
         /* EVALUATE the LOB LOCATOR */
         /* Locate the beginning of "Department Information" section */
         EXEC SQL VALUES (POSSTR(:resume, 'Department Information'))
            INTO :deptInfoBeginLoc;
         EMB_SQL_CHECK("VALUES1");

         /* Locate the beginning of "Education" section (end of "Dept.Info" */
         EXEC SQL VALUES (POSSTR(:resume, 'Education'))
            INTO :deptInfoEndLoc;
         EMB_SQL_CHECK("VALUES2");

         /* Obtain ONLY the "Department Information" section by using SUBSTR */
         EXEC SQL VALUES(SUBSTR(:resume, :deptInfoBeginLoc,
            :deptInfoEndLoc - :deptInfoBeginLoc)) INTO :deptBuffer;
         EMB_SQL_CHECK("VALUES3");

         /* Append the "Department Information" section to the :buffer var. */
         EXEC SQL VALUES(:buffer || :deptBuffer) INTO :buffer;
         EMB_SQL_CHECK("VALUES4");
      } /* endif */
   } while ( 1 );

#ifdef DB2MAC     
   /* Need to convert the newline character for the Mac */
   bufptr = &(buffer[0]);
   while ( *bufptr != '\0' ) {
      if ( *bufptr == 0x0A ) *bufptr = 0x0D;
	  bufptr++;
   }
#endif
      
   printf ("%s\n",buffer);

   EXEC SQL FREE LOCATOR :resume, :deptBuffer; /* :rk.3:erk. */
   EMB_SQL_CHECK("FREE LOCATOR");

   EXEC SQL CLOSE c1;
   EMB_SQL_CHECK("CLOSE CURSOR");

   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : lobloc.sqc */