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

¿¹: Á¤Àû SQL ÇÁ·Î±×·¥

ÀÌ »ùÇà ÇÁ·Î±×·¥Àº ´ÙÀ½°ú °°Àº Áö¿ø ¾ð¾î·Î Á¤Àû SQL¹® ¹× µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ API È£ÃâÀÇ ¿¹¸¦ º¸¿© ÁÝ´Ï´Ù.

C
static.sqc

Java
Static.sqlj

COBOL
static.sqb

REXX ¾ð¾î´Â Á¤Àû SQLÀ» Áö¿øÇÏÁö ¾ÊÀ¸¹Ç·Î »ùÇÃÀº Á¦°øµÇÁö ¾Ê½À´Ï´Ù.

ÀÌ »ùÇà ÇÁ·Î±×·¥¿¡´Â ´ÜÀÏ ÇàÀ» ¼±ÅÃÇÏ´Â Á¶È¸°¡ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù. ÀÌ·¯ÇÑ Á¶È¸´Â SELECT INTO¹®À» »ç¿ëÇÏ¿© ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

SELECT INTO¹®Àº µ¥ÀÌÅͺ£À̽ºÀÇ Å×ÀÌºí¿¡¼­ ÇÑ ÇàÀÇ µ¥ÀÌÅ͸¦ ¼±ÅÃÇϰí, ÀÌ ÇàÀÇ °ªµéÀº ¸í·É¹®¿¡ ÁöÁ¤µÈ È£½ºÆ® º¯¼ö¿¡ ÁöÁ¤µË´Ï´Ù. È£½ºÆ® º¯¼ö¿¡ ´ëÇØ¼­´Â È£½ºÆ® º¯¼ö »ç¿ë¿¡ »ó¼¼È÷ ³íÀǵǾî ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½ ¸í·É¹®Àº ¼ºÀÌ 'HAAS'ÀÎ »ç¿øÀÇ ±Þ¿©¸¦ empsal È£½ºÆ® º¯¼ö·Î Àü´ÞÇÕ´Ï´Ù.

     SELECT SALARY
       INTO :empsal
  FROM EMPLOYEE
       WHERE LASTNAME='HAAS'

SELECT INTO¹®Àº ÇÑ Çุ ¸®ÅÏÇϵµ·Ï ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. ÇÑ Çà ÀÌ»óÀ» ¹ß°ßÇÏ¸é ¿À·ù, SQLCODE -811(SQLSTATE 21000)ÀÌ ¹ß»ýÇÕ´Ï´Ù. Á¶È¸ °á°ú ¿©·¯ ÇàÀÌ ³ª¿Ã °æ¿ì Ä¿¼­¸¦ »ç¿ëÇÏ¿© ÇàÀ» ó¸®ÇØ¾ß ÇÕ´Ï´Ù. Ä¿¼­¸¦ ÀÌ¿ëÇÑ ´ÙÁß Çà ¼±Åÿ¡¼­ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

SELECT INTO¹®¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº SQL ÂüÁ¶¼­ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

SELECT¹® ÀÛ¼º ¹æ¹ý¿¡ ´ëÇÑ ¼Ò°³´Â µ¥ÀÌÅÍ °Ë»ö ¹× Á¶ÀÛ¿ë SQL¹® ÄÚµùÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

Á¤Àû ÇÁ·Î±×·¥ ÀÛ¾÷ ¹æ¹ý

  1. SQLCA Æ÷ÇÔ. INCLUDE SQLCA¹®Àº SQLCA ±¸Á¶¸¦ Á¤ÀÇ ¹× ¼±¾ðÇϰí, ±¸Á¶ ³»ÀÇ ¿ä¼Ò·Î¼­ SQLCODE ¹× SQLSTATE¸¦ Á¤ÀÇÇÕ´Ï´Ù. SQLCA ±¸Á¶ÀÇ SQLCODE Çʵå´Â SQL¹®À̳ª µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ API È£ÃâÀÌ ½ÇÇàµÉ ¶§¸¶´Ù µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡ ÀÇÇØ Áø´Ü Á¤º¸·Î °»½ÅµË´Ï´Ù.
  2. È£½ºÆ® º¯¼ö Á¤ÀÇ. SQL BEGIN DECLARE SECTION¹® ¹× END DECLARE SECTION¹®Àº È£½ºÆ® º¯¼ö ¼±¾ðÀ» ºÐ¸®ÇÕ´Ï´Ù. ÀÌ´Â SQL¹®¿¡¼­ ÂüÁ¶µÉ ¼ö ÀÖ´Â º¯¼öµéÀÔ´Ï´Ù. È£½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ¿© µ¥ÀÌÅ͸¦ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À¸·Î Àü´ÞÇϰųª ¸®ÅϵǴ µ¥ÀÌÅ͸¦ º¸À¯ÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â SQL¹®¿¡¼­ ÂüÁ¶µÉ ¶§ ÄÝ·Ð(:)À¸·Î ½ÃÀ۵˴ϴÙ. È£½ºÆ® º¯¼ö »ç¿ë¿¡¼­ ÀÚ¼¼ÇÑ Á¤º¸¸¦ ÂüÁ¶ÇϽʽÿÀ.
  3. µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°á. ÇÁ·Î±×·¥Àº sample µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇÏ¿© ÀÌ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ °øÀ¯ ¾×¼¼½º¸¦ ¿äûÇÕ´Ï´Ù.(START DATABASE MANAGER API È£ÃâÀ̳ª db2start ¸í·ÉÀÌ ¹ßÇàµÇ¾ú´Ù°í ÀüÁ¦ÇÕ´Ï´Ù.) °øÀ¯ ¾×¼¼½º¸¦ »ç¿ëÇÏ¿© µ¿ÀÏÇÑ µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇÏ´Â ´Ù¸¥ ÇÁ·Î±×·¥µé¿¡°Ôµµ ¾×¼¼½º°¡ ±ÇÇÑ ºÎ¿©µË´Ï´Ù.
  4. µ¥ÀÌÅÍ °Ë»ö. SELECT INTO¹®Àº Á¶È¸¸¦ ±âÃÊ·Î ÇÏ¿© ´ÜÀÏ °ªÀ» °Ë»öÇÕ´Ï´Ù. ÀÌ ¿¹¿¡¼­´Â LASTNAME Ä÷³ °ªÀÌ JOHNSONÀÎ EMPLOYEE Å×À̺í·ÎºÎÅÍ FIRSTNME Ä÷³À» °Ë»öÇÕ´Ï´Ù. SYBIL °ªÀÌ ¸®ÅϵǾî firstname È£½ºÆ® º¯¼ö¿¡ ³õÀÔ´Ï´Ù. DBw¿¡¼­ Á¦°øµÇ´Â »ùÇà Å×À̺íÀº SQL ÂüÁ¶¼­ÀÇ ºÎ·Ï¿¡ ³ª¿­µË´Ï´Ù.
  5. ¿À·ù ó¸®. CHECKERR ¸ÅÅ©·Î/ÇÔ¼ö´Â ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÔ´Ï´Ù. ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÇ À§Ä¡´Â »ç¿ëµÈ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡ µû¶ó ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù.

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

    Java
    SQL ¿À·ù´Â SQLExceptionÀ¸·Î¼­ µå·Î¿ìµÇ°í, ÀÀ¿ëÇÁ·Î±×·¥ÀÇ catch ºí·Ï¿¡¼­ 󸮵˴ϴÙ.

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

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

  6. µ¥ÀÌÅͺ£À̽º¿¡¼­ ¿¬°áÇØÁ¦. ÇÁ·Î±×·¥Àº CONNECT RESET¹®À» ½ÇÇàÇÏ¿© µ¥ÀÌÅͺ£À̽º·ÎºÎÅÍÀÇ ¿¬°áÀ» ÇØÁ¦ÇÕ´Ï´Ù. SQLJ ÇÁ·Î±×·¥Àº ÇÁ·Î±×·¥ÀÌ ¸®Å쵃 ¶§ µ¥ÀÌÅͺ£À̽º ¿¬°áÀ» ÀÚµ¿À¸·Î ´Ý½À´Ï´Ù.

C ¿¹: STATIC.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
 
EXEC SQL INCLUDE SQLCA;  (1)
 
int main(int argc, char *argv[])
{   int  rc = 0; 
 
    char dbAlias[15] ;
    char user[15] ; 
    char pswd[15] ; 
 
    EXEC SQL BEGIN DECLARE SECTION; (2)
        char firstname[13];
    EXEC SQL END DECLARE SECTION;
 
    /* checks the command line arguments */
    rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd ); (3)
    if ( rc != 0 ) return( rc ) ;
 
    printf("\n\nSample C program: STATIC\n");
 
    /* initialize the embedded application */
    rc = EmbAppInit( dbAlias, user, pswd);
    if ( rc != 0 ) return( rc ) ;
 
    EXEC SQL SELECT FIRSTNME INTO :firstname  (4)
            FROM employee
            WHERE LASTNAME = 'JOHNSON';
    EMB_SQL_CHECK("SELECT statement");  (5)
 
    printf( "First name = %s\n", firstname );
 
    /* terminate the embedded application */
    rc = EmbAppTerm( dbAlias);
    return( rc ) ;
}
/* end of program : STATIC.SQC */

Java ¿¹: Static.sqlj

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
 
class Static
{   static
  {   try
    {   Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
      }
      catch (Exception e)
      {   System.out.println ("\n  Error loading DB2 Driver...\n");
        System.out.println (e);
        System.exit(1);
      }
  }
 
  public static void main(String argv[])
  {   try
    {   System.out.println ("  Java Static Sample");
 
      String url = "jdbc:db2:sample";       // URL is jdbc:db2:dbname
      Connection con = null;
 
      // Set the connection                 (3)
      if (argv.length == 0)
      {   // connect with default id/password
        con = DriverManager.getConnection(url);
      }
      else if (argv.length == 2)
      {   String userid = argv[0];
        String passwd = argv[1];
 
        // connect with user-provided username and password
        con = DriverManager.getConnection(url, userid, passwd);
      }
      else
      {   throw new Exception("\nUsage: java Static [username password]\n");
      }
 
      // Set the default context
      DefaultContext ctx = new DefaultContext(con);
      DefaultContext.setDefaultContext(ctx);
 
      String firstname = null;
 
      #sql { SELECT FIRSTNME INTO :firstname
           FROM employee
           WHERE LASTNAME = 'JOHNSON' } ;   (4)
 
 
      System.out.println ("First name = " + firstname);
    }
 
    catch( Exception e ) (5)
    {
      System.out.println (e);
    }
  }
}

COBOL ¿¹: STATIC.SQB

       Identification Division.
       Program-ID. "static".
 
       Data Division.
       Working-Storage Section.
 
           copy "sql.cbl".
           copy "sqlca.cbl".                (1)
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.    (2)
       01 firstname         pic x(12).
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
           EXEC SQL END DECLARE SECTION END-EXEC.
 
       77 errloc          pic x(80).
 
       Procedure Division.
       Main Section.
           display "Sample COBOL program: STATIC".
 
           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   (3)
              END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL SELECT FIRSTNME INTO :firstname         (4)
                    FROM EMPLOYEE
                    WHERE LASTNAME = 'JOHNSON' END-EXEC.
           move "SELECT" to errloc.
           call "checkerr" using SQLCA errloc.            (5)
 
           display "First name = ", firstname.
 
           EXEC SQL CONNECT RESET END-EXEC.            (6)
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
 
       End-Prog.
           stop run.


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