/******************************************************************************
**
** Source File Name = lobeval.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 values.
**          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
**          outputs the first 15 lines of the resume.
**
** An external function "check_error" is contained 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[]) {

   EXEC SQL BEGIN DECLARE SECTION; /* :rk.1:erk. */
      char userid[9];
      char passwd[19];
      sqlint32        hv_start_deptinfo;
      sqlint32        hv_start_educ;
      sqlint32        hv_return_code;
      SQL TYPE IS CLOB(5K) hv_new_section_buffer;
      SQL TYPE IS CLOB_LOCATOR hv_doc_locator1;
      SQL TYPE IS CLOB_LOCATOR hv_doc_locator2;
      SQL TYPE IS CLOB_LOCATOR hv_doc_locator3;
   EXEC SQL END DECLARE SECTION;


   printf( "Sample C program: LOBEVAL\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: lobeval [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   /* delete any instance of "A00130" from previous executions of this sample */
   EXEC SQL DELETE FROM emp_resume WHERE empno = 'A00130';

   /* Use a single row select to get the document */
   EXEC SQL SELECT resume INTO :hv_doc_locator1 FROM emp_resume
      WHERE empno = '000130' AND resume_format = 'ascii';  /* :rk.2:erk. */
   EMB_SQL_CHECK("SELECT");

   /* Use the POSSTR function to locate the start of
      sections "Department Information" & "Education" */
   EXEC SQL VALUES (POSSTR(:hv_doc_locator1, 'Department Information'))
      INTO :hv_start_deptinfo; /* :rk.3:erk. */
   EMB_SQL_CHECK("VALUES1");

   EXEC SQL VALUES (POSSTR(:hv_doc_locator1, 'Education'))
      INTO :hv_start_educ;
   EMB_SQL_CHECK("VALUES2");

   /* Replace Department Information Section with nothing */
   EXEC SQL VALUES (SUBSTR(:hv_doc_locator1, 1, :hv_start_deptinfo -1)
      || SUBSTR (:hv_doc_locator1, :hv_start_educ))
      INTO :hv_doc_locator2;
   EMB_SQL_CHECK("VALUES3");

   /* Move Department Information Section into the hv_new_section_buffer */
   EXEC SQL VALUES (SUBSTR(:hv_doc_locator1, :hv_start_deptinfo,
      :hv_start_educ -:hv_start_deptinfo)) INTO :hv_new_section_buffer;
   EMB_SQL_CHECK("VALUES4");

   /* Append our new section to the end (assume it has been filled in)
      Effectively, this just moves the Department Information to the bottom
      of the resume. */
   EXEC SQL VALUES (:hv_doc_locator2 || :hv_new_section_buffer) INTO
      :hv_doc_locator3;
   EMB_SQL_CHECK("VALUES5");

   /* Store this resume section in the table. This is where the LOB value
      bytes really move */
   EXEC SQL INSERT INTO emp_resume VALUES ('A00130', 'ascii',
      :hv_doc_locator3);  /* :rk.4:erk. */
   EMB_SQL_CHECK("INSERT");

   printf ("LOBEVAL completed\n");

   /* free the locators */  /* :rk.5:erk. */
   EXEC SQL FREE LOCATOR :hv_doc_locator1, :hv_doc_locator2, : hv_doc_locator3;
   EMB_SQL_CHECK("FREE LOCATOR");

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