ÀÀ¿ëÇÁ·Î±×·¥ °³¹ß ¾È³»¼­

¿¹: CLOB °ª¿¡ ´ëÇØ ÀÛ¾÷ÇÒ À§Ä¡ ÁöÁ¤ÀÚ »ç¿ë

ÀÌ ¿¹¿¡¼­ ÀÀ¿ëÇÁ·Î±×·¥Àº LOB °ª¿¡ ´ëÇÑ À§Ä¡ ÁöÁ¤ÀÚ¸¦ °Ë»öÇÑ ÈÄ, À§Ä¡ ÁöÁ¤ÀÚ¸¦ »ç¿ëÇÏ¿© LOB °ªÀ¸·ÎºÎÅÍ µ¥ÀÌÅ͸¦ ÃßÃâÇÕ´Ï´Ù. ÀÌ ¹æ¹ýÀ» »ç¿ëÇÏ¿© ÇÁ·Î±×·¥Àº LOB µ¥ÀÌÅÍ ÇÑ Á¶°¢¿¡ ÇÊ¿äÇÑ ÀúÀ念¿ª(Å©±â´Â ÇÁ·Î±×·¥¿¡ ÀÇÇØ °áÁ¤µÊ)¸¸ ÇÒ´çÇϰí, Ä¿¼­¸¦ »ç¿ëÇÏ¿© ÇϳªÀÇ ÆäÄ¡ È£Ã⸸ ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù.

»ùÇà LOBLOC ÇÁ·Î±×·¥ ÀÛ¾÷ ¹æ¹ý

  1. È£½ºÆ® º¯¼ö Á¤ÀÇ. BEGIN DECLARE SECTION ¹× END DECLARE SECTION¹®Àº È£½ºÆ® º¯¼ö ¼±¾ðÀ» ºÐ¸®ÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â SQL¹®¿¡¼­ ÂüÁ¶µÉ ¶§ ÄÝ·Ð(:)À¸·Î ½ÃÀ۵˴ϴÙ. CLOB LOCATOR È£½ºÆ® º¯¼ö°¡ ¼±¾ðµË´Ï´Ù.
  2. LOB °ªÀ» LOCATOR È£½ºÆ® º¯¼ö·Î ÆäÄ¡. CURSOR ¹× FETCH ·çƾÀ» »ç¿ëÇÏ¿© µ¥ÀÌÅͺ£À̽º¿¡¼­ÀÇ LOB Çʵå À§Ä¡¸¦ È£½ºÆ® º¯¼ö À§Ä¡ ÁöÁ¤ÀÚ·Î È®º¸ÇÕ´Ï´Ù.
  3. LOB LOCATORS ºñ¿ì±â. ÀÌ ¿¹¿¡¼­ »ç¿ëÇÏ´Â LOB LOCATORS´Â ÀÌÀü¿¡ ¿¬°áµÈ °ªÀ¸·ÎºÎÅÍ À§Ä¡ ÁöÁ¤ÀÚ¸¦ ¸±¸®½ºÇÏ¿© ºñ¿öÁý´Ï´Ù.

CHECKERR ¸ÅÅ©·Î/ÇÔ¼ö´Â ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÔ´Ï´Ù. ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÇ À§Ä¡´Â »ç¿ëµÈ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡ µû¶ó ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù.

C
DB2 API¸¦ È£ÃâÇÏ´Â C ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilapi.c¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilapi.h¿¡¼­ API_SQL_CHECK·Î¼­ ÂüÁ¶µË´Ï´Ù. C embedded SQL ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilemb.sqc¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilemb.h¿¡¼­ EMB_SQL_CHECK·Î¼­ ÂüÁ¶µË´Ï´Ù.

COBOL
CHECKERRÀº checkerr.cbl¶ó°í ÇÏ´Â ¿ÜºÎ ÇÁ·Î±×·¥ÀÔ´Ï´Ù.

FORTRAN
CHECKERRÀº util.f ÆÄÀÏ¿¡ ÀÖ´Â ¼­ºê·çƾÀÔ´Ï´Ù.

ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼¿¡ ´ëÇÑ ¼Ò½º Äڵ忡 ´ëÇØ¼­´Â ¿¹Á¦ ÇÁ·Î±×·¥¿¡¼­ GET ERROR MESSAGE »ç¿ëÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

C ¿¹: LOBLOC.SQC

#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 */

COBOL ¿¹: LOBLOC.SQB

       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.


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]