Application Development Guide

Example: Extracting a Document To a File

This program example shows how CLOB elements can be retrieved from a table into an external file.

How the Sample LOBFILE Program Works

1. Declare host variables. The BEGIN DECLARE SECTION and END DECLARE SECTION statements delimit the host variable declarations. Host variables are prefixed with a colon (:) when referenced in an SQL statement. A CLOB FILE REFERENCE host variable is declared.
2. CLOB FILE REFERENCE host variable is set up. The attributes of the FILE REFERENCE is set up. A file name without a fully declared path is, by default, placed in the current working directory.
3. Select in to the CLOB FILE REFERENCE host variable. The data from the resume field is selected into the filename referenced by the host variable.

The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:

C

For C programs that call DB2 APIs, the sqlInfoPrint function in utilapi.c is redefined as API_SQL_CHECK in utilapi.h. For C embedded SQL programs, the sqlInfoPrint function in utilemb.sqc is redefined as EMB_SQL_CHECK in utilemb.h.

COBOL

CHECKERR is an external program named checkerr.cbl

See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.

C Sample: LOBFILE.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sql.h>
#include "utilemb.h"
 
EXEC SQL INCLUDE SQLCA;
 
int main(int argc, char *argv[]) {
 
   EXEC SQL BEGIN DECLARE SECTION; (1)
      SQL TYPE IS CLOB_FILE resume;
      short lobind;
      char userid[9];
      char passwd[19];
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: LOBFILE\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: lobfile [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   strcpy (resume.name, "RESUME.TXT");  (2)
   resume.name_length = strlen("RESUME.TXT");
   resume.file_options = SQL_FILE_OVERWRITE;
 
   EXEC SQL SELECT resume INTO :resume :lobind FROM emp_resume  (3)
      WHERE resume_format='ascii' AND empno='000130';
 
   if (lobind < 0) {
      printf ("NULL LOB indicated \n");
   } else {
      printf ("Resume for EMPNO 000130 is in file : RESUME.TXT\n");
   } /* endif */
 
   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : LOBFILE.SQC */

COBOL Sample: LOBFILE.SQB

       Identification Division.
       Program-ID. "lobfile".
 
       Data Division.
       Working-Storage Section.
 
           copy "sqlenv.cbl".
           copy "sql.cbl".
           copy "sqlca.cbl".
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.                     (1)
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
       01 resume            USAGE IS SQL TYPE IS CLOB-FILE.
       01 lobind            pic s9(4) comp-5.
           EXEC SQL END DECLARE SECTION END-EXEC.
 
       77 errloc          pic x(80).
 
       Procedure Division.
       Main Section.
           display "Sample COBOL program: LOBFILE".
 
      * Get database connection information.
           display "Enter your user id (default none): " 
                with no advancing.
           accept userid.
 
           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.
 
      * Passwords in a CONNECT statement must be entered in a VARCHAR
      * format with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial " ".
 
           EXEC SQL CONNECT TO sample USER :userid USING :passwd
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
 
           move "RESUME.TXT" to resume-NAME.                            (2)
           move 10 to resume-NAME-LENGTH.
           move SQL-FILE-OVERWRITE to resume-FILE-OPTIONS.
 
           EXEC SQL SELECT resume INTO :resume :lobind                  (3)
                    FROM emp_resume
                    WHERE resume_format = 'ascii'
                    AND empno = '000130' END-EXEC.
           if lobind less than 0 go to NULL-LOB-indicated.
 
           display "Resume for EMPNO 000130 is in file : RESUME.TXT".
           go to End-Main.
 
       NULL-LOB-indicated.
           display "NULL LOB indicated".
 
       End-Main.
           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
       End-Prog.
                  stop run.