¸ñÇ¥ ¸ñÀûÁö·ÎÀÇ LOB Ç¥Çö½Ä ÁöÁ¤ÀÌ µÉ ¶§±îÁö LOB °ª ¹ÙÀÌÆ®¿¡´Â À̵¿ÀÌ ¾ø½À´Ï´Ù. ÀÌ´Â ¹®ÀÚ¿ ÇÔ¼ö ¹× ¿¬»êÀÚ¿Í ÇÔ²² »ç¿ëµÇ´Â LOB °ª À§Ä¡ ÁöÁ¤ÀÚ°¡ ÁöÁ¤µÉ ¶§±îÁö Ç¥Çö½ÄÀÌ ¿¬±âµÇ´Â Ç¥Çö½ÄÀ» ÀÛ¼ºÇÒ ¼ö ÀÖÀ½À» ÀǹÌÇÕ´Ï´Ù. À̸¦ LOB Ç¥Çö½ÄÀÇ Æò°¡ ¿¬±â¶ó°í ÇÕ´Ï´Ù.
ÀÌ ¿¹¿¡¼´Â EMP_RESUME Àç°³ Å×ÀÌºí ³»¿¡¼ ƯÁ¤ Àç°³(empno = '000130')°¡ Ž»öµË´Ï´Ù. Àç°³ÀÇ ºÎ¼ Á¤º¸ ÀýÀº Àç°³ ³¡ºÎºÐ¿¡ º¹»ç, Àß¶ó³»±â ¹× Ãß°¡µË´Ï´Ù. ±×·± ÈÄ ÀÌ »õ·Î¿î Àç°³´Â EMP_RESUME Å×ÀÌºí¿¡ »ðÀԵ˴ϴÙ. ÀÌ Å×À̺íÀÇ ¿ø·¡ Àç°³´Â º¯°æµÇÁö ¾ÊÀº »óÅ·Π³²¾Æ ÀÖ½À´Ï´Ù.
À§Ä¡ ÁöÁ¤ÀÚ´Â ¿ø·¡ Àç°³·ÎºÎÅÍ ¹ÙÀÌÆ®¸¦ ½ÇÁ¦ À̵¿½ÃŰ°Å³ª º¹»çÇÏÁö ¾Ê°í »õ·Î¿î Àç°³ÀÇ °ËÅä ¹× ¾î¼Àºí¸®¸¦ Çã¿ëÇÕ´Ï´Ù. ¹ÙÀÌÆ®ÀÇ À̵¿Àº ÃÖÁ¾ ÇÒ´çµÉ ¶§±îÁö Áï, INSERT¹® -- ¹× ¼¹ö¿¡¼¸¸ ¹ßÇàÇÏÁö ¾Ê½À´Ï´Ù.
Æò°¡ ¿¬±â´Â DB2¿¡°Ô LOB I/O ¼º´ÉÀ» Çâ»ó½Ãų ±âȸ¸¦ Á¦°øÇÕ´Ï´Ù. LOB ±â´É ÃÖÀûÈ ¾Ë°í¸®ÁòÀÌ LOB Ç¥Çö½ÄÀ» ´ëü Ç¥Çö½ÄÀ¸·Î º¯È¯ÇÏ·Á Çϱ⠶§¹®¿¡ ¹ß»ýÇÕ´Ï´Ù. ÀÌ·¯ÇÑ ´ëü Ç¥Çö½ÄÀº µ¿ÀÏÇÑ °á°ú¸¦ »êÃâÇÏÁö¸¸ º¸´Ù ÀûÀº ¼öÀÇ µð½ºÅ© I/O°¡ ÇÊ¿äÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
°£´ÜÈ÷ ¸»Çؼ LOB À§Ä¡ ÁöÁ¤ÀÚ´Â ¼ö¸¹Àº ÇÁ·Î±×·¡¹Ö ½Ã³ª¸®¿À¿¡ ÀÌ»óÀûÀÔ´Ï´Ù.
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 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'; (2) 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; (3) 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); (4) EMB_SQL_CHECK("INSERT"); printf ("LOBEVAL completed\n"); /* free the locators */ (5) 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 */
Identification Division. Program-ID. "lobeval". 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 hv-start-deptinfo pic s9(9) comp-5. 01 hv-start-educ pic s9(9) comp-5. 01 hv-return-code pic s9(9) comp-5. 01 hv-new-section-buffer USAGE IS SQL TYPE IS CLOB(5K). 01 hv-doc-locator1 USAGE IS SQL TYPE IS CLOB-LOCATOR. 01 hv-doc-locator2 USAGE IS SQL TYPE IS CLOB-LOCATOR. 01 hv-doc-locator3 USAGE IS SQL TYPE IS CLOB-LOCATOR. EXEC SQL END DECLARE SECTION END-EXEC. 77 errloc pic x(80). Procedure Division. Main Section. display "Sample COBOL program: LOBEVAL". * 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. * Delete any instance of "A00130" from previous executions EXEC SQL DELETE FROM emp_resume WHERE empno = 'A00130' END-EXEC. * use a single row select to get the document EXEC SQL SELECT resume INTO :hv-doc-locator1 (2) FROM emp_resume WHERE empno = '000130' AND resume_format = 'ascii' END-EXEC. move "SELECT" to errloc. call "checkerr" using SQLCA errloc. * 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 END-EXEC. (3) move "VALUES1" to errloc. call "checkerr" using SQLCA errloc. EXEC SQL VALUES (POSSTR(:hv-doc-locator1, 'Education')) INTO :hv-start-educ END-EXEC. move "VALUES2" to errloc. call "checkerr" using SQLCA errloc. * 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 END-EXEC. move "VALUES3" to errloc. call "checkerr" using SQLCA errloc. * move Department Information section into 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 END-EXEC. move "VALUES4" to errloc. call "checkerr" using SQLCA errloc. * Append the new section to the end (assume it has been filled) * Effectively, this just moves the Dept Info to the bottom of * the resume. EXEC SQL VALUES (:hv-doc-locator2 || :hv-new-section-buffer) INTO :hv-doc-locator3 END-EXEC. move "VALUES5" to errloc. call "checkerr" using SQLCA errloc. * Store this resume in the table. * This is where the LOB value bytes really move. EXEC SQL INSERT INTO emp_resume (4) VALUES ('A00130', 'ascii', :hv-doc-locator3) END-EXEC. move "INSERT" to errloc. call "checkerr" using SQLCA errloc. display "LOBEVAL completed". EXEC SQL FREE LOCATOR :hv-doc-locator1, :hv-doc-locator2, (5) :hv-doc-locator3 END-EXEC. move "FREE LOCATOR" to errloc. call "checkerr" using SQLCA errloc. EXEC SQL CONNECT RESET END-EXEC. move "CONNECT RESET" to errloc. call "checkerr" using SQLCA errloc. End-Prog. stop run.
ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÖ´Â ÀÏ¹Ý È£½ºÆ® º¯¼öÀÇ °æ¿ì, È£½ºÆ® º¯¼ö¿¡ ³Î(NULL) °ªÀ» ¼±ÅÃÇÒ ¶§ ±× °ªÀÌ ³Î(NULL)ÀÓÀ» ÀǹÌÇϴ ǥ½Ã±â º¯¼ö¿¡ À½¼ö °ªÀÌ ÇÒ´çµË´Ï´Ù. ±×·¯³ª LOB À§Ä¡ ÁöÁ¤ÀÚÀÇ °æ¿ì Ç¥½Ã±â º¯¼öÀÇ Àǹ̴ ¾à°£¾¿ ´Ù¸¨´Ï´Ù. À§Ä¡ ÁöÁ¤ÀÚ È£½ºÆ® º¯¼ö ÀÚü´Â °áÄÚ ³Î(NULL)ÀÏ ¼ö ¾øÀ¸¹Ç·Î, À½¼ö Ç¥½Ã±â º¯¼ö °ªÀº LOB À§Ä¡ ÁöÁ¤ÀÚ¿¡ ÀÇÇØ Ç¥½ÃµÇ´Â LOB °ªÀÌ ³Î(NULL)ÀÓÀ» ³ªÅ¸³À´Ï´Ù. ³Î(NULL) Á¤º¸´Â Ç¥½Ã±â º¯¼ö °ªÀ» »ç¿ëÇÏ¿© Ŭ¶óÀÌ¾ðÆ®¿¡ Áö¿ª »óÅ·Π³²¾Æ ÀÖ½À´Ï´Ù. ¼¹ö´Â À¯È¿ÇÑ À§Ä¡ ÁöÁ¤ÀÚ·Î ³Î(NULL) °ªÀ» ÃßÀûÇÏÁö ¾Ê½À´Ï´Ù.