In this example, the application program retrieves a locator for a LOB value; then it uses the locator to extract the data from the LOB value. Using this method, the program allocates only enough storage for one piece of LOB data (the size is determined by the program) and it needs to issue only one fetch call using the cursor.
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:
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.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)
char number[7];
long deptInfoBeginLoc;
long 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;
#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: LOBLOC\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 ("USAGE: lobloc [userid passwd]");
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;
CHECKERR ("OPEN CURSOR");
do {
EXEC SQL FETCH c1 INTO :number, :resume :lobind; (2)
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;
CHECKERR ("VALUES1");
/* Locate the beginning of "Education"
section (end of "Dept.Info") */
EXEC SQL VALUES (POSSTR(:resume, 'Education'))
INTO :deptInfoEndLoc;
CHECKERR ("VALUES2");
/* Obtain ONLY the "Department Information"
section by using SUBSTR */
EXEC SQL VALUES(SUBSTR(:resume, :deptInfoBeginLoc,
:deptInfoEndLoc - :deptInfoBeginLoc)) INTO :deptBuffer;
CHECKERR ("VALUES3");
/* Append the "Department Information" section
to the :buffer var. */
EXEC SQL VALUES(:buffer || :deptBuffer) INTO :buffer;
CHECKERR ("VALUES4");
} /* endif */
} while ( 1 );
printf ("%s\n",buffer);
EXEC SQL FREE LOCATOR :resume, :deptBuffer; (3)
CHECKERR ("FREE LOCATOR");
EXEC SQL CLOSE c1;
CHECKERR ("CLOSE CURSOR");
EXEC SQL CONNECT RESET;
CHECKERR ("CONNECT RESET");
return 0;
}
/* end of program : LOBLOC.SQC */
Identification Division.
Program-ID. "lobloc".
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 empnum pic x(6).
01 di-begin-loc pic s9(9) comp-5.
01 di-end-loc pic s9(9) comp-5.
01 resume USAGE IS SQL TYPE IS CLOB-LOCATOR.
01 di-buffer USAGE IS SQL TYPE IS CLOB-LOCATOR.
01 lobind pic s9(4) comp-5.
01 buffer USAGE IS SQL TYPE IS CLOB(1K).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: LOBLOC".
* 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.
* Employee A10030 is not included in the following select, because
* the lobeval program manipulates the record for A10030 so that it
* 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' END-EXEC.
EXEC SQL OPEN c1 END-EXEC.
move "OPEN CURSOR" to errloc.
call "checkerr" using SQLCA errloc.
Move 0 to buffer-length.
perform Fetch-Loop thru End-Fetch-Loop
until SQLCODE not equal 0.
* display contents of the buffer.
display buffer-data(1:buffer-length).
EXEC SQL FREE LOCATOR :resume, :di-buffer END-EXEC. (3)
move "FREE LOCATOR" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CLOSE c1 END-EXEC.
move "CLOSE CURSOR" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CONNECT RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Main.
go to End-Prog.
Fetch-Loop Section.
EXEC SQL FETCH c1 INTO :empnum, :resume :lobind (2)
END-EXEC.
if SQLCODE not equal 0
go to End-Fetch-Loop.
* check to see if the host variable indicator returns NULL.
if lobind less than 0 go to NULL-lob-indicated.
* Value exists. Evaluate the LOB locator.
* Locate the beginning of "Department Information" section.
EXEC SQL VALUES (POSSTR(:resume, 'Department Information'))
INTO :di-begin-loc END-EXEC.
move "VALUES1" to errloc.
call "checkerr" using SQLCA errloc.
* Locate the beginning of "Education" section (end of Dept.Info)
EXEC SQL VALUES (POSSTR(:resume, 'Education'))
INTO :di-end-loc END-EXEC.
move "VALUES2" to errloc.
call "checkerr" using SQLCA errloc.
subtract di-begin-loc from di-end-loc.
* Obtain ONLY the "Department Information" section by using SUBSTR
EXEC SQL VALUES (SUBSTR(:resume, :di-begin-loc,
:di-end-loc))
INTO :di-buffer END-EXEC.
move "VALUES3" to errloc.
call "checkerr" using SQLCA errloc.
* Append the "Department Information" section to the :buffer var
EXEC SQL VALUES (:buffer || :di-buffer) INTO :buffer
END-EXEC.
move "VALUES4" to errloc.
call "checkerr" using SQLCA errloc.
go to End-Fetch-Loop.
NULL-lob-indicated.
display "NULL LOB indicated".
End-Fetch-Loop. exit.
End-Prog.
stop run.
program lobloc
implicit none
include 'sqlenv.f'
include 'sql.f'
EXEC SQL INCLUDE SQLCA
EXEC SQL BEGIN DECLARE SECTION
* (1)
character*8 userid
character*18 passwd
character*6 empnum
integer*4 di_begin_loc
integer*4 di_end_loc
SQL TYPE IS CLOB_LOCATOR resume
SQL TYPE IS CLOB_LOCATOR di_buffer
integer*2 lobind
SQL TYPE IS CLOB(1K) buffer
EXEC SQL END DECLARE SECTION
character*80 errloc
print *, 'Sample Fortran Program: LOBLOC'
print *, 'Enter your user id (default none):'
read 101, userid
101 format (a8)
if( userid(1:1) .eq. ' ' ) then
EXEC SQL CONNECT TO sample
else
print *, 'Enter your password :'
read 101, passwd
EXEC SQL CONNECT TO sample USER :userid USING :passwd
end if
errloc = 'CONNECT'
call checkerr (sqlca, errloc, *999)
* 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
errloc = 'OPEN CURSOR'
call checkerr (sqlca, errloc, *999)
10 continue
EXEC SQL FETCH c1 INTO :empnum, :resume :lobind
* (2)
if (sqlcode .ne. 0) goto 100
if (lobind .lt. 0) goto 50
* The CLOB value exists.
* Locate the beginning of the
* "Department Information" section
EXEC SQL VALUES (POSSTR(:resume, 'Department Information'))
+ INTO :di_begin_loc
errloc = 'VALUES1'
call checkerr (sqlca, errloc, *999)
* Locate the beginning of the
* "Education" section (end of Dept.Info)
EXEC SQL VALUES (POSSTR(:resume, 'Education'))
+ INTO :di_end_loc
errloc = 'VALUES2'
call checkerr (sqlca, errloc, *999)
* Obtain ONLY the "Department Information"
* section by using SUBSTR
EXEC SQL VALUES(SUBSTR(:resume, :di_begin_loc,
+ :di_end_loc - :di_begin_loc)) INTO :di_buffer
errloc = 'VALUES3'
call checkerr (sqlca, errloc, *999)
* Append the "Department Information"
* section to the :buffer variable
EXEC SQL VALUES(:buffer || :di_buffer) INTO :buffer
errloc = 'VALUES4'
call checkerr (sqlca, errloc, *999)
goto 10
50 print *,'NULL LOB indicated'
goto 10
100 print *, buffer
EXEC SQL FREE LOCATOR :resume, :di_buffer
* (3)
errloc = 'FREE LOCATOR'
call checkerr (sqlca, errloc, *999)
EXEC SQL CLOSE c1
errloc = 'CLOSE CURSOR'
call checkerr (sqlca, errloc, *999)
EXEC SQL CONNECT RESET
errloc = 'CONNECT RESET'
call checkerr (sqlca, errloc, *999)
999 stop
end