この例では、アプリケーション・プログラムが LOB 値のロケーターを検索し、そのロケーターを使用して LOB 値からデータを抽出します。プログラムは、この方法で LOB データの一部分に必要なだけの記憶域 (サイズはプログラムにより決まる)を割り振るので、カーソルを使用して 1 度呼び出しを行うだけでかまいません。
CHECKERR マクロ / 関数は、プログラム外部にあるエラー検査ユーティリティーです。エラー検査ユーティリティーの所在は、ご使用のプログラム言語により異なります。
このエラー検査ユーティリティーのソース・コードについては、 プログラム例での GET ERROR MESSAGE の使用を参照してください。
#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; (1) 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; (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; 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 ); printf ("%s\n",buffer); EXEC SQL FREE LOCATOR :resume, :deptBuffer; (3) 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 */
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 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.