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

check_error is redefined as CHECKERR and is located in the util.c file.

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 "util.h"
 
#ifdef DB268K
/* Need to include ASLM for 68K applications */
#include <LibraryManager.h>
#endif
 
EXEC SQL INCLUDE SQLCA;
 
#define CHECKERR(CE_STR) if (check_error (CE_STR, &sqlca) != 0) return 1;
 
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;
 
#ifdef DB268K
   /* Before making any API calls for 68K environment,
      need to initial the Library Manager */
	InitLibraryManager(0,kCurrentZone,kNormalMemory);
	atexit(CleanupLibraryManager);
#endif
 
   printf( "Sample C program: LOBFILE\n" );
 
   if (argc == 1) {
      EXEC SQL CONNECT TO sample;
	  CHECKERR ("CONNECT TO SAMPLE");
   }
   else if (argc == 3) { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;
      CHECKERR ("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;
   CHECKERR ("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.

[ DB2 List of Books | Search the DB2 Books ]