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

PREPARE, DESCRIBE, FETCH ¹× SQLDA »ç¿ë

Á¤Àû SQLÀ» ÀÌ¿ëÇÏ¿© Embedded SQL¹®¿¡¼­ »ç¿ëµÇ´Â È£½ºÆ® º¯¼ö´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ÄÄÆÄÀ쵃 ¶§ ¾Ë·ÁÁý´Ï´Ù. µ¿Àû SQLÀ» ÀÌ¿ëÇÏ¿© Embedded SQL¹® ¹× °á°úÀûÀ¸·Î È£½ºÆ® º¯¼ö´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ·±Å¸À ¶§±îÁö ¾Ë·ÁÁöÁö ¾Ê½À´Ï´Ù. µû¶ó¼­, µ¿Àû SQL ÀÀ¿ëÇÁ·Î±×·¥ÀÇ °æ¿ì ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ »ç¿ëµÇ´Â È£½ºÆ® º¯¼ö ¸ñ·ÏÀ» ´Ù·ç¾î¾ß ÇÕ´Ï´Ù. (PREPARE¸¦ »ç¿ëÇÏ¿©) ÁغñµÈ SELECT¹®¿¡ ´ëÇÑ È£½ºÆ® º¯¼ö Á¤º¸¸¦ ¾ò°í, ÀÌ Á¤º¸¸¦ SQL ¼³¸íÀÚ ¿µ¿ª(SQLDA)¿¡ ÀúÀåÇϱâ À§ÇØ DESCRIBE¹®À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÁÖ:Java ÀÀ¿ëÇÁ·Î±×·¥Àº SQLDA ±¸Á¶¸¦ »ç¿ëÇÏÁö ¾ÊÀ¸¹Ç·Î, PREPARE¹® ¶Ç´Â DESCRIBE¹®À» »ç¿ëÇÏÁö ¾Ê½À´Ï´Ù. JDBC ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­´Â PreparedStatement ¿ÀºêÁ§Æ® ¹× executeQuery() ¸Þ¼Òµå¸¦ »ç¿ëÇÏ¿© È£½ºÆ® ¾ð¾î Ä¿¼­¿Í °°Àº ResultSet ¿ÀºêÁ§Æ®¸¦ »ý¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. SQLJ ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­´Â CursorByPos ¶Ç´Â CursorByName Ä¿¼­¿Í ÇÔ²² SQLJ iterator ¿ÀºêÁ§Æ®¸¦ ¼±¾ðÇÏ¿© FETCH¹®À¸·ÎºÎÅÍ µ¥ÀÌÅ͸¦ ¸®ÅÏÇÒ ¼ö ÀÖ½À´Ï´Ù.

DESCRIBE¹®ÀÌ »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ½ÇÇàµÉ ¶§ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº È£½ºÆ® º¯¼ö¸¦ SQLDA¿¡ Á¤ÀÇÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö°¡ SQLDA¿¡ Á¤ÀǵǸé, Ä¿¼­¸¦ »ç¿ëÇÏ¿© °ªÀ» È£½ºÆ® º¯¼ö¿¡ ÁöÁ¤Çϱâ À§ÇØ FETCH¹®À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

PREPARE, DESCRIBE ¹× FETCH¹®¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ Á¤º¸ ¹× SQLDA¿¡ ´ëÇÑ ¼³¸íÀº SQL ÂüÁ¶¼­ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

SQLDA¸¦ »ç¿ëÇÏÁö ¾Ê°í PREPARE, DESCRIBE ¹× FETCH¹®À» »ç¿ëÇÏ´Â °£´ÜÇÑ µ¿Àû SQL ÇÁ·Î±×·¥ÀÇ ¿¹´Â ¿¹: µ¿Àû SQL ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. ´ëÈ­½Ä SQL¹®À» ó¸®Çϱâ À§ÇØ PREPARE, DESCRIBE ¹× FETCH¹®°ú SQLDA¸¦ »ç¿ëÇÏ´Â µ¿Àû SQL ÇÁ·Î±×·¥ÀÇ ¿¹´Â ¿¹: ADHOC ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

Ä¿¼­ ¼±¾ð ¹× »ç¿ë

Ä¿¼­ÀÇ µ¿Àû 󸮴 Á¤Àû SQLÀ» »ç¿ëÇÏ¿© ó¸®ÇÏ´Â °Í°ú °ÅÀÇ µ¿ÀÏÇÕ´Ï´Ù. Ä¿¼­°¡ ¼±¾ðµÉ ¶§ Á¶È¸¿Í ¿¬°üµÇ¾î ÀÖ½À´Ï´Ù.

Á¤Àû SQLÀÇ °æ¿ì Á¶È¸´Â Ä¿¼­¹® Á¤ÀÇ¿¡¼­¿Í °°ÀÌ, ÅØ½ºÆ® Çü½ÄÀ¸·Î µÈ SELECT¹®ÀÔ´Ï´Ù.

µ¿Àû SQLÀÇ °æ¿ì, Á¶È¸´Â PREPARE¹®¿¡ ÁöÁ¤µÈ ¸í·É¹® À̸§°ú ¿¬°üµÇ¾î ÀÖ½À´Ï´Ù. ÂüÁ¶µÈ È£½ºÆ® º¯¼ö´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ ÀÇÇØ Ç¥½ÃµË´Ï´Ù. Ç¥ 7¿¡´Â µ¿Àû SELECT¹®°ú ¿¬°üµÈ DECLARE¹®ÀÌ Ç¥½ÃµÇ¾î ÀÖ½À´Ï´Ù.


Ç¥ 7. µ¿Àû SELECT¿Í ¿¬°üµÈ ¸í·É¹® ¼±¾ð
¾ð¾î ¿¹Á¦ ¼Ò½º ÄÚµå
C/C++
 strcpy( prep_string, "SELECT tabname FROM syscat.tables"
                      "WHERE tabschema = ?" );
 EXEC SQL PREPARE s1 FROM :prep_string;
 EXEC SQL DECLARE c1 CURSOR FOR s1;
 EXEC SQL OPEN c1 USING :host_var;

Java (JDBC)
 PreparedStatement prep_string = ("SELECT tabname FROM syscat.tables
                      WHERE tabschema = ?" );
 prep_string.setCursor("c1");
 prep_string.setString(1, host_var);
 ResultSet rs = prep_string.executeQuery();

COBOL
 MOVE "SELECT TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA = ?"
      TO PREP-STRING.
 EXEC SQL PREPARE S1 FROM :PREP-STRING END-EXEC.
 EXEC SQL DECLARE C1 CURSOR FOR S1 END-EXEC.
 EXEC SQL OPEN C1 USING :host-var END-EXEC.

FORTRAN
 prep_string = 'SELECT tabname FROM syscat.tables WHERE tabschema = ?'
 EXEC SQL PREPARE s1 FROM :prep_string
 EXEC SQL DECLARE c1 CURSOR FOR s1
 EXEC SQL OPEN c1 USING :host_var

Á¤Àû Ä¿¼­¿Í µ¿Àû Ä¿¼­ »çÀÌÀÇ ÁÖ¿ä Â÷ÀÌÁ¡Àº Á¤Àû Ä¿¼­´Â »çÀü ó¸® ÄÄÆÄÀ쵃 ¶§ ÁغñµÇ°í, µ¿Àû Ä¿¼­´Â ¼öÇàµÉ ¶§ ÁغñµË´Ï´Ù. ¶ÇÇÑ, Á¶È¸¿¡¼­ ÂüÁ¶µÇ´Â È£½ºÆ® º¯¼ö´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ ÀÇÇØ Ç¥½ÃµÇ°í, ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â Ä¿¼­°¡ ¿­¸± ¶§ ·±Å¸ÀÓ È£½ºÆ® º¯¼ö¿¡ ÀÇÇØ ´ëüµË´Ï´Ù.

Ä¿¼­ »ç¿ë ¹æ¹ý¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ´ÙÀ½ ÀýÀ» ÂüÁ¶ÇϽʽÿÀ.

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

ÀÌ »ùÇà ÇÁ·Î±×·¥Àº µ¿Àû SQL¹®¿¡ ±âÃÊÇÑ Ä¿¼­ÀÇ Ã³¸®¸¦ Ç¥½ÃÇÕ´Ï´Ù. ÀÌ ÇÁ·Î±×·¥Àº À̸§ Ä÷³¿¡ STAFF °ªÀ» °¡Áø Å×À̺íÀ» Á¦¿ÜÇϰí, SYSCAT.TABLES¿¡ ¸ðµç Å×À̺íÀ» ³ª¿­ÇÕ´Ï´Ù. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:

C
dynamic.sqc

Java
Dynamic.java

COBOL
dynamic.sqb

REXX
dynamic.cmd

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

  1. È£½ºÆ® º¯¼ö Á¤ÀÇ. ÀÌ Àý¿¡¼­´Â 3°³ÀÇ È£½ºÆ® º¯¼öÀÇ ¼±¾ðÀÌ ÀÖ½À´Ï´Ù.

    table_name
    FETCH¹®ÀÌ ½ÇÇàµÇ´Â µ¿¾È ¸®ÅϵǴ µ¥ÀÌÅ͸¦ º¸À¯ÇÏ´Â µ¥ »ç¿ëµË´Ï´Ù.
    st
    µ¿Àû SQL¹®À» ÅØ½ºÆ® Çü½ÄÀ¸·Î º¸À¯ÇÏ´Â µ¥ »ç¿ëµË´Ï´Ù.
    parm_var
    st¿¡¼­ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ ´ëüÇÒ µ¥ÀÌÅÍ °ªÀ» Á¦°øÇÕ´Ï´Ù.
  2. ¸í·É¹® Áغñ. ('?'¿¡ ÀÇÇØ Ç¥½ÃµÇ´Â) ÇϳªÀÇ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ °¡Áø SQL¹®ÀÌ È£½ºÆ® º¯¼ö·Î º¹»çµË´Ï´Ù. ÀÌ È£½ºÆ® º¯¼ö´Â °ËÁõÀ» À§ÇØ PREPARE¹®À¸·Î Àü´ÞµË´Ï´Ù. PREPARE¹®Àº SQL ÅØ½ºÆ®¸¦ ºÐ¼®ÇÏ°í »çÀü ó¸® ÄÄÆÄÀÏ·¯ ¶Ç´Â ¹ÙÀδõ¿Í °°Àº ¹æ½ÄÀ¸·Î ÆÐŰÁö¿¡ ´ëÇÑ ¾×¼¼½º ÀýÀ» ÁغñÇϴµ¥, ÀÌ´Â ¼±Çà ó¸® ÁßÀÌ ¾Æ´Ñ ¼öÇà Áß¿¡ ÀÌ·ç¾îÁý´Ï´Ù.
  3. Ä¿¼­ ¼±¾ð. DECLARE¹®Àº Ä¿¼­¸¦ µ¿ÀûÀ¸·Î ÁغñµÈ SQL¹®¿¡ ¿¬°áÇÕ´Ï´Ù. ÁغñµÈ SQL¹®ÀÌ SELECT¹®ÀÎ °æ¿ì, °á°ú Å×À̺í·ÎºÎÅÍ ÇàÀ» °Ë»öÇÏ´Â µ¥ Ä¿¼­°¡ ÇʼöÀûÀÔ´Ï´Ù.
  4. Ä¿¼­ ¿­±â. OPEN¹®Àº ÀÌÀü¿¡ ¼±¾ðµÈ Ä¿¼­¸¦ Ãʱ⼳Á¤ÇÏ¿© °á°ú Å×À̺íÀÇ Ã¹¹øÂ° Çà ¾ÕÀ» °¡¸®Åµ´Ï´Ù. USINGÀýÀº È£½ºÆ® º¯¼ö¸¦ ÁöÁ¤ÇÏ¿© ÁغñµÈ SQL¹®¿¡ ÀÖ´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ ´ëüÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö ±æÀÌ ¹× µ¥ÀÌÅÍ À¯ÇüÀº ¿¬°üµÈ Ä÷³ À¯Çü ¹× ±æÀÌ¿Í È£È¯µÇ¾î¾ß ÇÕ´Ï´Ù.
  5. µ¥ÀÌÅÍ °Ë»ö. FETCH¹®Àº °á°ú Å×À̺í·ÎºÎÅÍ table_name È£½ºÆ® º¯¼ö·Î NAME Ä÷³À» À̵¿ÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â ÇÁ·Î±×·¥ÀÌ ´Ù¸¥ ÇàÀ» ÆäÄ¡Çϱâ À§ÇØ ·çÇÁ¹éÇϱâ Àü¿¡ ÀμâµË´Ï´Ù.
  6. Ä¿¼­ ´Ý±â. CLOSE¹®Àº Ä¿¼­¸¦ ´Ý°í ÀÌ¿Í ¿¬°üµÈ ÀÚ¿øÀ» ÇØÁ¦ÇÕ´Ï´Ù.

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¶ó°í ÇÏ´Â ¿ÜºÎ ÇÁ·Î±×·¥ÀÔ´Ï´Ù.

REXX
CHECKERRÀº ÇöÀç ÇÁ·Î±×·¥ÀÇ ³¡¿¡ ÀÖ½À´Ï´Ù.

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

C ¿¹: DYNAMIC.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;
      char  table_name[19];
      char  st[80];  (1)
      char  parm_var[19];
      char userid[9];
      char passwd[19];
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: DYNAMIC\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: dynamic [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   strcpy( st, "SELECT tabname FROM syscat.tables" );
   strcat( st, " WHERE tabname <> ?" );
   EXEC SQL PREPARE s1 FROM :st;  (2)
   EMB_SQL_CHECK("PREPARE");
 
   EXEC SQL DECLARE c1 CURSOR FOR s1;  (3)
 
   strcpy( parm_var, "STAFF" );
   EXEC SQL OPEN c1 USING :parm_var;  (4)
   EMB_SQL_CHECK("OPEN");
   do {
      EXEC SQL FETCH c1 INTO :table_name;  (5)
      if (SQLCODE != 0) break;
 
      printf( "Table = %s\n", table_name );
   } while ( 1 );
 
   EXEC SQL CLOSE c1;  (6)
   EMB_SQL_CHECK("CLOSE");
 
   EXEC SQL COMMIT;
   EMB_SQL_CHECK("COMMIT");
 
   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : DYNAMIC.SQC */

Java ¿¹: Dynamic.java

import java.sql.*;
 
class Dynamic
{   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 Dynamic Sample");
      // Connect to Sample database
 
      Connection con = null;
      // URL is jdbc:db2:dbname
      String url = "jdbc:db2:sample";
 
      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 Dynamic [username password]\n");
      } 
 
      // Enable transactions
      con.setAutoCommit(false);
 
      // Perform dynamic SQL SELECT using JDBC
      try
      {   PreparedStatement pstmt1 = con.prepareStatement(
          "SELECT tabname FROM syscat.tables " +
          "WHERE tabname <> ? " +
          "ORDER BY 1"); (2)
      // set cursor name for the positioned update statement
      pstmt1.setCursorName("c1");                                 (3)
      pstmt1.setString(1, "STAFF");
      ResultSet rs = pstmt1.executeQuery();                       (4)
 
      System.out.print("\n");
      while( rs.next() )                                          (5)
      {   String tableName = rs.getString("tabname");
        System.out.println("Table = " + tableName);
      };
 
      rs.close();
      pstmt1.close();                                             (7)
      }
      catch( Exception e )
      {   throw e;
      } 
      finally
      {   // Rollback the transaction
        System.out.println("\nRollback the transaction...");
        con.rollback();
        System.out.println("Rollback done.");
      }
    } 
    catch( Exception e )
    {   System.out.println(e);
    } 
  }
}

COBOL ¿¹: DYNAMIC.SQB

       Identification Division.
       Program-ID. "dynamic".
 
       Data Division.
       Working-Storage Section.
 
           copy "sqlenv.cbl".
           copy "sql.cbl".
           copy "sqlca.cbl".
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 table-name      pic x(20).
       01 st              pic x(80).    (1)
       01 parm-var        pic x(18).
       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: DYNAMIC".
 
           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.
 
           move "SELECT TABNAME FROM SYSCAT.TABLES
      -       " ORDER BY 1
      -       " WHERE TABNAME <> ?" to st.
           EXEC SQL PREPARE s1 FROM :st END-EXEC.  (2)
           move "PREPARE" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC.  (3)
 
           move "STAFF" to parm-var.
           EXEC SQL OPEN c1 USING :parm-var END-EXEC.    (4)
           move "OPEN" to errloc.
           call "checkerr" using SQLCA errloc.
 
           perform Fetch-Loop thru End-Fetch-Loop
              until SQLCODE not equal 0.
 
           EXEC SQL CLOSE c1 END-EXEC.    (6)
           move "CLOSE" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL COMMIT END-EXEC.
           move "COMMIT" 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 :table-name END-EXEC.   (5)
           if SQLCODE not equal 0
              go to End-Fetch-Loop.
           display "TABLE = ", table-name.
       End-Fetch-Loop. exit.
 
       End-Prog.
           stop run.

REXX ¿¹: DYNAMIC.CMD

/* REXX DYNAMIC.CMD */
 
parse version rexxType .
parse source platform .
 
if platform == 'AIX/6000' & rexxType == 'REXXSAA' then
do
  rcy = SysAddFuncPkg("db2rexx")
end
else
do
  if RxFuncQuery('SQLDBS') <> 0 then
    rcy = RxFuncAdd( 'SQLDBS',  'db2ar', 'SQLDBS'  )
 
  if RxFuncQuery('SQLEXEC') <> 0 then
    rcy = RxFuncAdd( 'SQLEXEC', 'db2ar', 'SQLEXEC' )
end
 
/* pull in command line arguments */
parse arg userid passwd .
 
/* check to see if the proper number of arguments have been passed in */
   PARSE ARG dbname userid password .
   if ((dbname = "" ) | ,
       (userid <> "" & password = "") ,
      ) then do
      SAY "USAGE: dynamic.cmd <dbname> [<userid> <password>]"
 
       exit -1
   end
 
   /* connect to database */
   SAY
   SAY 'Connect to' dbname
   IF password= "" THEN
      CALL SQLEXEC 'CONNECT TO' dbname
   ELSE
      CALL SQLEXEC 'CONNECT TO' dbname 'USER' userid 'USING' password
 
   CALL CHECKERR 'Connect to '
   SAY "Connected"
 
say 'Sample REXX program: DYNAMIC'
 
st = "SELECT tabname FROM syscat.tables WHERE tabname <> ? ORDER BY 1"
call SQLEXEC 'PREPARE s1 FROM :st'  (2)
call CHECKERR 'PREPARE'
 
call SQLEXEC 'DECLARE c1 CURSOR FOR s1'  (3)
call CHECKERR 'DECLARE'
 
parm_var = "STAFF"
call SQLEXEC 'OPEN c1 USING :parm_var'  (4)
 
do while ( SQLCA.SQLCODE = 0 )
  call SQLEXEC 'FETCH c1 INTO :table_name'  (5)
  if (SQLCA.SQLCODE = 0) then
    say 'Table = ' table_name
end
 
call SQLEXEC 'CLOSE c1'  (6)
call CHECKERR 'CLOSE'
 
call SQLEXEC 'CONNECT RESET'
call CHECKERR 'CONNECT RESET'
 
 
CHECKERR:
  arg errloc
 
  if  ( SQLCA.SQLCODE = 0 ) then
    return 0
  else do
    say '--- error report ---'
    say 'ERROR occurred :' errloc
    say 'SQLCODE :' SQLCA.SQLCODE
 
    /******************************\
    * GET ERROR MESSAGE API called *
    \******************************/
    call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
    say errmsg
    say '--- end error report ---'
 
    if (SQLCA.SQLCODE < 0 ) then
      exit
    else do
      say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
      return 0
    end
  end
return 0

SQLDA ¼±¾ð

SQLDA¿¡´Â SQLVAR Ç׸ñ ¹ß»ýÀÇ º¯¼ö ¼ýÀÚ°¡ Æ÷ÇԵǾî ÀÖÀ¸¸ç, SQLVAR Ç׸ñ °¢°¢¿¡´Â ±×¸² 2¿¡ Ç¥½ÃµÈ ¹Ù¿Í °°ÀÌ ÇÑ ÇàÀÇ µ¥ÀÌÅÍ¿¡ ÇϳªÀÇ Ä÷³À» ¼³¸íÇÏ´Â ÇÊµå ¼¼Æ®°¡ µé¾î ÀÖ½À´Ï´Ù. ±âº» SQLVAR°ú 2Â÷ SQLVAR, µÎ °¡Áö À¯ÇüÀÇ SQLVAR Ç׸ñÀÌ ÀÖ½À´Ï´Ù. µÎ À¯Çü¿¡ ´ëÇÑ ¼³¸íÀº SQL ÂüÁ¶¼­ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

±×¸² 2. SQL ¼³¸íÀÚ ¿µ¿ª(SQLDA)


SQL ¼³¸íÀÚ ¿µ¿ª(SQLDA)

ÇÊ¿äÇÑ SQLVAR Ç׸ñÀÇ ¼ö´Â °á°ú Å×À̺íÀÇ Ä÷³ ¼ö¿¡ µû¶ó ´Þ¶óÁö¹Ç·Î, ÀÀ¿ëÇÁ·Î±×·¥Àº ÇÊ¿äÇÒ ¶§ ÀûÀýÇÑ ¼öÀÇ SQLVAR ¿ä¼Ò¸¦ ÇÒ´çÇØ¾ß ÇÕ´Ï´Ù. ¾Æ·¡¿¡ »ç¿ë°¡´ÉÇÑ µÎ °¡Áö ¸Þ¼Òµå¿¡ ´ëÇØ ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù. ¾ð±ÞµÈ SQLDA Çʵ忡 ´ëÇØ¼­´Â SQL ÂüÁ¶¼­ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

À§ ¸Þ¼ÒµåÀÇ °æ¿ì, ÇÒ´çÇØ¾ß ÇÏ´Â Ãʱâ SQLVAR Ç׸ñ ¼ö¿¡ °üÇÑ ¹®Á¦°¡ ¹ß»ýÇÒ ¼ö ÀÖ½À´Ï´Ù. °¢ SQLVAR ¿ä¼Ò´Â 44¹ÙÀÌÆ®ÀÇ ÀúÀ念¿ªÀ» »ç¿ëÇÕ´Ï´Ù.(ÀÌ ÀúÀ念¿ªÀº SQLDATA ¹× SQLIND Çʵ忡 ´ëÇØ ÇÒ´çµÈ °ÍÀ» °è»êµÇÁö ¾Ê½À´Ï´Ù.) ¸Þ¸ð¸®°¡ ÃæºÐÇÒ °æ¿ì, ÃÖ´ë Å©±âÀÇ SQLDA¸¦ Á¦°øÇϴ ù¹øÂ° ¹æ¹ýÀ» ±¸ÇöÇϱⰡ ´õ ½±½À´Ï´Ù.

´õ ÀÛÀº SQLDA¸¦ ÇÒ´çÇÏ´Â µÎ ¹øÂ° ¸Þ¼Òµå´Â µ¿Àû ¸Þ¸ð¸® ÇÒ´çÀ» Áö¿øÇÏ´Â C ¹× C++¿Í °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡¸¸ Àû¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. µ¿Àû ¸Þ¸ð¸® ÇÒ´çÀ» Áö¿øÇÏÁö ¾Ê´Â COBOL ¹× FORTRAN°ú °°Àº ¾ð¾îÀÇ °æ¿ì, ù¹øÂ° ¸Þ¼Òµå¸¦ »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.

ÃÖ¼Ò SQLDA ±¸Á¶¸¦ »ç¿ëÇÏ´Â ¸í·É¹® Áغñ

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ SQLVAR Ç׸ñÀÌ Æ÷ÇԵǾî ÀÖÁö ¾ÊÀº minsqlda¶ó´Â SQLDA ±¸Á¶¸¦ ¼±¾ðÇÑ´Ù°í ÇսôÙ. SQLDAÀÇ SQLN Çʵå´Â ÇÒ´çµÈ SQLVAR Ç׸ñ ¼ö¿¡ ´ëÇØ ¼³¸íÇÕ´Ï´Ù. ÀÌ·± °æ¿ì, SQLNÀº 0À¸·Î ¼³Á¤µÇ¾î¾ß ÇÕ´Ï´Ù. ±× ´ÙÀ½, dstring ¹®ÀÚ¿­·ÎºÎÅÍ ¸í·É¹®À» ÁغñÇÏ°í ±× ¼³¸íÀ» minsqlda¿¡ ÀÔ·ÂÇÏ·Á¸é, ´ÙÀ½ SQL¹®À» ½ÇÇàÇϽʽÿÀ. (C±¸¹®À̶ó°í Çϰí, minsqlda°¡ SQLDA ±¸Á¶¿¡ ´ëÇÑ Æ÷ÀÎÅͷμ­ ¼±¾ðµÇ¾î ÀÖ´Ù°í °¡Á¤ÇÕ´Ï´Ù.)

     EXEC SQL
       PREPARE STMT INTO :*minsqlda FROM :dstring;

dstring¿¡ Æ÷ÇÔµÈ ¸í·É¹®ÀÌ °¢ Çà¿¡¼­ 20°³ Ä÷³À» ¸®ÅÏÇÏ´Â SELECT¹®À̾ú´Ù°í ÇսôÙ. PREPARE¹®(¶Ç´Â DESCRIBE¹®) ´ÙÀ½¿¡ SQLDAÀÇ SQLD Çʵ忡´Â ÁغñµÈ SELECT¹®¿¡ ´ëÇÑ °á°ú Å×À̺íÀÇ Ä÷³ ¼ö°¡ µé¾î ÀÖ½À´Ï´Ù.

SQLDA¿¡ ÀÖ´Â SQLVARÀº ´ÙÀ½°ú °°Àº °æ¿ì ¼³Á¤µË´Ï´Ù.

´ÙÀ½°ú °°Àº °æ¿ì SQLDAÀÇ SQLVARÀÌ ¼³Á¤µÇÁö ¾Ê½À´Ï´Ù(Ãß°¡ °ø°£ ¹× ¶Ç´Ù¸¥ DESCRIBEÀÇ ÇÒ´çÀÌ ÇÊ¿ä).

BIND ¸í·ÉÀÇ SQLWARN ¿É¼ÇÀ» »ç¿ëÇÏ¿© DESCRIBE(¶Ç´Â PREPARE...INTO)°¡ ´ÙÀ½ °æ°í¸¦ ¸®ÅÏÇÒ °ÍÀÎÁö ÅëÁ¦ÇÕ´Ï´Ù.

»ç¿ëÀÚÀÇ ÀÀ¿ëÇÁ·Î±×·¥ Äڵ尡 Ç×»ó ÀÌ·¯ÇÑ SQLCODE¸¦ ¸®ÅÏÇÒ ¼ö ÀÖµµ·Ï ÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. ¼±Åà ¸ñ·Ï¿¡ LOB Ä÷³ÀÌ ÀÖ°í SQLDA¿¡ ÃæºÐÄ¡ ¸øÇÑ SQLVARÀÌ ÀÖÀ» ¶§ Ç×»ó °æ°í SQLCODE +238(SQLSTATE 01005)ÀÌ ¸®Åϵ˴ϴÙ. ÀÌ´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ °á°ú ¼¼Æ®ÀÇ LOB Ä÷³À¸·Î ÀÎÇØ SQLVAR ¼ö°¡ 2¹è·Î µÇ¾î¾ß ÇÔÀ» ¾Ë ¼ö ÀÖ´Â À¯ÀÏÇÑ ¹æ¹ýÀÔ´Ï´Ù.

ÃæºÐÇÑ SQLVAR Ç׸ñÀ¸·Î SQLDA ÇÒ´ç

°á°ú Å×À̺íÀÇ Ä÷³ ¼ö°¡ °áÁ¤µÇ¸é µÎ ¹øÂ°ÀÇ Àüü Å©±â SQLDA¿¡ ´ëÇÑ ÀúÀ念¿ªÀ» ÇÒ´çÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, °á°ú Å×ÀÌºí¿¡ 20°³ Ä÷³ÀÌ ÀÖ´Â °æ¿ì(ÀÌ Áß LOB Ä÷³Àº ¾øÀ½), µÎ ¹øÂ° SQLDA ±¸Á¶ÀÎ fulsqlda°¡ Àû¾îµµ 20°³ÀÇ SQLVAR ¿ä¼Ò°¡ ÇÒ´çµÇ¾î¾ß ÇÕ´Ï´Ù(°á°ú Å×ÀÌºí¿¡ LOB ¶Ç´Â ±¸º° À¯ÇüÀÌ Æ÷ÇԵǾî ÀÖ´Â °æ¿ì´Â 40°³). ÀÌ ¿¹ÀÇ ³ª¸ÓÁö¿¡¼­´Â LOBÀ̳ª ±¸º° À¯ÇüÀÌ °á°ú Å×ÀÌºí¿¡ ¾ø´Ù°í °¡Á¤ÇÕ´Ï´Ù.

SQLDA ±¸Á¶¿ë ÀúÀ念¿ª Á¶°ÇÀº ´ÙÀ½°ú °°½À´Ï´Ù.

fulsqlda¿¡ ÇÊ¿äÇÑ SQLVAR Ç׸ñÀÇ ¼ö´Â minsqldaÀÇ SQLD Çʵ忡 ÁöÁ¤µÇ¾î ÀÖ½À´Ï´Ù. ÀÌ °ªÀº 20ÀÔ´Ï´Ù. µû¶ó¼­, ÀÌ ¿¹¿¡¼­ »ç¿ëµÈ fulsqlda¿¡ ÇÊ¿äÇÑ ÀúÀ念¿ª ÇÒ´çÀº ´ÙÀ½°ú °°½À´Ï´Ù.

     16 + (20 * sizeof(struct sqlvar))
ÁÖ:64- ºñÆ® Ç÷§Æû¿¡¼­, sizeof(struct sqlvar) ¹× sizeof(struct sqlvar2)´Â 56À» ¸®ÅÏÇÕ´Ï´Ù. 32- ºñÆ® Ç÷§Æû¿¡¼­´Â sizeof(struct sqlvar) ¹× sizeof(struct sqlvar2)´Â 44¸¦ ¸®ÅÏÇÕ´Ï´Ù.

ÀÌ °ªÀº Àüü 896¹ÙÀÌÆ®¸¦ Á¦°øÇÏ´Â °¢ SQLVAR Ç׸ñ Å©±âÀÇ 20¹è¿¡ Çì´õ Å©±â¸¦ ´õÇÑ °ªÀÔ´Ï´Ù.

SQLDASIZE ¸ÅÅ©·Î¸¦ »ç¿ëÇÏ¿© »ç¿ëÀÚ ÀÚ½ÅÀÇ °è»êÀ» ÇÇÇϰí, ¹öÀüº° Á¾¼Ó¼ºÀ» ÇÇÇÒ ¼ö ÀÖ½À´Ï´Ù.

SELECT¹® ¼³¸í

fulsqlda ¹× ÀÀ¿ëÇÁ·Î±×·¥¿¡ ´ëÇØ ÃæºÐÇÑ °ø°£À» ÇÒ´çÇÒ ¶§ ´ÙÀ½ ´Ü°è¸¦ µû¶ó¾ß ÇÕ´Ï´Ù.

  1. fulsqldaÀÇ SQLN Çʵ忡 20 °ªÀ» ÀúÀåÇÕ´Ï´Ù.
  2. µÎ ¹øÂ° SQLDA ±¸Á¶ÀÎ fulsqlda¸¦ »ç¿ëÇÏ¿© SELECT¹®¿¡ ´ëÇÑ Á¤º¸¸¦ ¾ò½À´Ï´Ù. µÎ °¡Áö ¹æ¹ýÀÌ »ç¿ë°¡´ÉÇÕ´Ï´Ù.

¸í·É¹®À» µÎ ¹ø ÁغñÇÏ´Â ºñ¿ëÀÌ »ï°¡µÇ¹Ç·Î DESCRIBE¹®À» »ç¿ëÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. DESCRIBE¹®Àº »õ·Î¿î SQLDA ±¸Á¶¿¡ µé¾î°¥ Áغñ Á¶ÀÛÀ» ÇÏ´Â µ¿¾È ÀÌÀü¿¡ ¾òÀº Á¤º¸¸¦ Àç»ç¿ëÇÕ´Ï´Ù. ´ÙÀ½ ¸í·É¹®ÀÌ ¹ßÇàµÉ ¼ö ÀÖ½À´Ï´Ù.

     EXEC SQL DESCRIBE STMT INTO :fulsqlda

ÀÌ ¸í·É¹®ÀÌ ½ÇÇàµÈ ÈÄ °¢ SQLVAR ¿ä¼Ò¿¡´Â °á°ú Å×À̺íÀÇ ÇÑ Ä÷³¿¡ ´ëÇÑ ¼³¸íÀÌ µé¾î ÀÖ½À´Ï´Ù.

Çà º¸À¯¸¦ À§ÇÑ ÀúÀ念¿ª Ãëµæ

SQLDA ±¸Á¶¸¦ »ç¿ëÇÑ °á°ú Å×À̺í ÇàÀ» ÆäÄ¡Çϱâ Àü¿¡ ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½À» ¼öÇàÇØ¾ß ÇÕ´Ï´Ù.

  1. °¢ SQLVAR ¼³¸íÀ» ºÐ¼®ÇÏ¿© ÇØ´ç Ä÷³ °ª¿¡ ¾î´À Á¤µµÀÇ °ø°£ÀÌ ÇÊ¿äÇÑÁö °áÁ¤ÇÕ´Ï´Ù.

    ´ëÇü ¿ÀºêÁ§Æ®(LOB) °ªÀÇ °æ¿ì SELECT°¡ ¼³¸íµÉ ¶§ SQLVAR¿¡ Á¦°øµÇ´Â µ¥ÀÌÅÍ À¯ÇüÀº SQL_TYP_xLOBÀÔ´Ï´Ù. ÀÌ µ¥ÀÌÅÍ À¯ÇüÀº ÀÏ¹Ý LOB È£½ºÆ® º¯¼ö¿¡ ÀÏÄ¡ÇÕ´Ï´Ù. Áï, Àüü LOB°¡ ÇÑ ¹ø¿¡ ¸Þ¸ð¸®¿¡ ÀúÀåµË´Ï´Ù. ÀÌ´Â (¼­³Ê °³ÀÇ MB±îÁö) ¼ÒÇü LOB¿¡ ´ëÇØ ÀÛµ¿µÇÁö¸¸ ´ëÇü LOB(1GB¶ó°í Çϸé)¿¡ ´ëÇØ ÀÌ µ¥ÀÌÅÍ À¯ÇüÀ» »ç¿ëÇÒ ¼ö ¾ø½À´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ÀÌ SQLVAR¿¡¼­ Ä÷³ Á¤Àǰ¡ SQL_TYP_xLOB_LOCATOR ¶Ç´Â SQL_TYPE_xLOB_FILE·Î º¯°æµÇ¾î¾ß ÇÕ´Ï´Ù.(SQLVARÀÇ SQLTYPE Çʵ带 º¯°æÇϸé SQLLEN Çʵ嵵 º¯°æÇØ¾ß ÇÕ´Ï´Ù.) SQLVAR¿¡¼­ÀÇ Ä÷³ Á¤ÀǸ¦ º¯°æÇÑ ÈÄ ÀÀ¿ëÇÁ·Î±×·¥Àº »õ·Î¿î À¯Çü¿¡ ´ëÇØ ¿Ã¹Ù¸¥ Å©±âÀÇ ÀúÀ念¿ªÀ» ÇÒ´çÇÒ ¼ö ÀÖ½À´Ï´Ù. LOB¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº ¿ÀºêÁ§Æ® °ü°èÇü ±â´É »ç¿ëÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

  2. ÇØ´ç Ä÷³ÀÇ °ª¿¡ ´ëÇÑ ÀúÀ念¿ªÀ» ÇÒ´çÇÕ´Ï´Ù.
  3. SQLDA ±¸Á¶ÀÇ SQLDATA Çʵ忡 ÇÒ´çµÈ ÀúÀ念¿ªÀÇ ÁÖ¼Ò¸¦ ÀúÀåÇÕ´Ï´Ù.

ÀÌ ´Ü°è´Â °¢ Ä÷³ÀÇ ¼³¸íÀ» ºÐ¼®ÇÏ°í °¢ SQLDATA ÇʵåÀÇ ³»¿ëÀ» ÇØ´ç Ä÷³ÀÇ °ªÀ» º¸À¯Çϱ⿡ ÃæºÐÇÑ Å©±âÀÇ ÀúÀ念¿ªÀÇ ÁÖ¼Ò·Î ´ëüÇÏ¿© ÀÌ·ç¾îÁý´Ï´Ù. ±æÀÌ ¼Ó¼ºÀº LOB À¯ÇüÀÌ ¾Æ´Ñ µ¥ÀÌÅÍ Ç׸ñ¿¡ ´ëÇÑ °¢ SQLVARÀÇ SQLLEN Çʵ忡¼­ °áÁ¤µË´Ï´Ù. À¯ÇüÀÌ BLOB, CLOB ¶Ç´Â DBCLOBÀÎ Ç׸ñÀÇ °æ¿ì, ±æÀÌ ¼Ó¼ºÀº 2Â÷ SQLVAR Ç׸ñÀÇ SQLLONGLEN Çʵå·ÎºÎÅÍ °áÁ¤µË´Ï´Ù.

¶ÇÇÑ, ÁöÁ¤µÈ Ä÷³ÀÌ ³Î(NULL)À» Çã¿ëÇÒ °æ¿ì, ÀÀ¿ëÇÁ·Î±×·¥Àº SQLIND ÇÊµå ³»¿ëÀ» Ä÷³¿¡ ´ëÇÑ Ç¥½Ã±â º¯¼ö ÁÖ¼Ò·Î ´ëÃ¼ÇØ¾ß ÇÕ´Ï´Ù.

Ä¿¼­ ó¸®

SQLDA ±¸Á¶°¡ Á¦´ë·Î ÇÒ´çµÇ¸é SELECT¹®°ú ¿¬°üµÈ Ä¿¼­¸¦ ¿­ ¼ö ÀÖ°í, FETCH¹®ÀÇ USING DESCRIPTORÀýÀ» ÁöÁ¤ÇÏ¿© ÇàÀ» ÆäÄ¡ÇÒ ¼ö ÀÖ½À´Ï´Ù.

¿Ï·á°¡ µÇ¸é Ä¿¼­¸¦ ´Ý°í µ¿Àû ÇÒ´çµÈ ¸Þ¸ð¸®¸¦ ¸±¸®½ºÇØ¾ß ÇÕ´Ï´Ù.

SQLDA ±¸Á¶ ÇÒ´ç

C·Î SQLDA ±¸Á¶¸¦ ÀÛ¼ºÇÏ·Á¸é È£½ºÆ® ¾ð¾î·Î INCLUDE SQLDA¹®À» Æ÷ÇÔ½ÃŰ°Å³ª, ±¸Á¶ Á¤ÀÇ È®º¸¸¦ À§ÇØ SQLDA Æ÷ÇÔ ÆÄÀÏÀ» Æ÷ÇÔ½ÃŰ½Ê½Ã¿À. ±×·± ÈÄ, SQLDA Å©±â°¡ °íÁ¤µÇ¾î ÀÖÁö ¾ÊÀ¸¹Ç·Î ÀÀ¿ëÇÁ·Î±×·¥Àº SQLDA ±¸Á¶¿¡ ´ëÇÑ Æ÷ÀÎÅ͸¦ ¼±¾ðÇϰí ÀúÀ念¿ªÀ» ÇÒ´çÇØ¾ß ÇÕ´Ï´Ù. SQLDA ±¸Á¶ÀÇ ½ÇÁ¦ Å©±â´Â SQLDA¸¦ »ç¿ëÇÏ¿© Àü´ÞµÇ´Â ±¸º° µ¥ÀÌÅÍ Ç׸ñÀÇ ¼ö¿¡ µû¶ó ´Þ¶óÁý´Ï´Ù.(SQLDA 󸮸¦ À§ÇØ ÀÀ¿ëÇÁ·Î±×·¥À» ÄÚµåÈ­ÇÏ´Â ¹æ¹ýÀÇ ¿¹´Â ¿¹: ADHOC ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.)

C/C++ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡¼­, ¸ÅÅ©·Î´Â SQLDA ÇÒ´çÀ» °¡´ÉÇÏ°Ô Çϱâ À§ÇØ Á¦°øµË´Ï´Ù. HP-UX Ç÷§ÆûÀÇ ¿¹¿Ü·Î ÀÌ ¸ÅÅ©·Î´Â ´ÙÀ½ÀÇ Çü½ÄÀ» °®½À´Ï´Ù.

     #define SQLDASIZE(n) (offsetof(struct sqlda, sqlvar) + (n) × sizeof(struct sqlvar))

HP-UX Ç÷§Æû¿¡¼­ ¸ÅÅ©·Î´Â ´ÙÀ½ÀÇ Çü½ÄÀ» °®½À´Ï´Ù.

     #define SQLDASIZE(n) (sizeof(struct sqlda) + (n-1) × sizeof(struct sqlvar))

ÀÌ ¸ÅÅ©·ÎÀÇ È¿°ú´Â SQLDAÀÇ ÇÊ¿äÇÑ ÀúÀ念¿ªÀ» n°³ÀÇ SQLVAR ¿ä¼Ò¸¦ ÀÌ¿ëÇÏ¿© °è»êÇÏ´Â °ÍÀÔ´Ï´Ù.

COBOLÀ» »ç¿ëÇÏ¿© SQLDA ±¸Á¶¸¦ ÀÛ¼ºÇϱâ À§ÇØ INCLUDE SQLDA¹®À» Æ÷ÇÔ½ÃŰ°Å³ª COPY¹®À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ÃÖ´ë ¼öÀÇ SQLVAR°ú SQLDA°¡ »ç¿ëÇÏ´Â ÀúÀ念¿ª Å©±â¸¦ Á¦¾îÇÏ·Á¸é COPY¹®À» »ç¿ëÇϽʽÿÀ. ¿¹¸¦ µé¾î, SQLVARÀÇ ±âº»°ªÀ» 1489¿¡¼­ 1·Î º¯°æÇÏ·Á¸é ´ÙÀ½°ú °°Àº COPY¹®À» »ç¿ëÇϽʽÿÀ.

     COPY "sqlda.cbl"
       replacing --1489--
       by --1--.

FORTRAN ¾ð¾î´Â ÀÚµ¿ Á¤ÀÇ µ¥ÀÌÅÍ ±¸Á¶ ¶Ç´Â µ¿Àû ÇÒ´çÀ» Á÷Á¢ Áö¿øÇÏÁö ¾Ê½À´Ï´Ù. FORTRANÀÇ °æ¿ì SQLDA Æ÷ÇÔ ÆÄÀÏÀÌ Á¦°øµÇÁö ¾Ê½À´Ï´Ù. FORTRANÀ¸·Î µ¥ÀÌÅÍ ±¸Á¶·Î¼­ SQLDA¸¦ Áö¿øÇÏ´Â °ÍÀÌ °¡´ÉÇÏÁö ¾Ê±â ¶§¹®ÀÔ´Ï´Ù. »çÀü ó¸® ÄÄÆÄÀÏ·¯´Â FORTRAN ÇÁ·Î±×·¥¿¡¼­ INCLUDE SQLDA¹®À» ¹«½ÃÇÕ´Ï´Ù.

±×·¯³ª, FORTRAN ÇÁ·Î±×·¥¿¡¼­ Á¤Àû SQLDA ±¸Á¶¿Í À¯»çÇÑ ±¸Á¶¸¦ ÀÛ¼ºÇÒ ¼ö ÀÖÀ¸¸ç, SQLDA¸¦ »ç¿ëÇÒ ¼ö ÀÖ´Â °÷¿¡¼­ ÀÌ ±¸Á¶¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. sqldact.f ÆÄÀÏ¿¡´Â FORTRANÀ¸·Î SQLDA ±¸Á¶¸¦ ¼±¾ðÇÏ´Â µ¥ µµ¿òÀÌ µÇ´Â »ó¼ö°¡ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù.

ÇÊ¿äÇÑ SQLDA ¿ä¼Ò¿¡ Æ÷ÀÎÅÍ °ªÀ» ÁöÁ¤Çϱâ À§ÇØ SQLGADDR¿¡ ´ëÇÑ È£ÃâÀ» ½ÇÇàÇÕ´Ï´Ù.

´ÙÀ½ Å×À̺íÀº ÇϳªÀÇ SQLDA ¿ä¼Ò¸¦ °¡Áø SQLDA ±¸Á¶ÀÇ ¼±¾ð ¹× »ç¿ëÀ» Ç¥½ÃÇÕ´Ï´Ù.
¾ð¾î ¿¹Á¦ ¼Ò½º ÄÚµå
C/C++
 #include <sqlda.h>
 struct sqlda *outda = (struct sqlda *)malloc(SQLDASIZE(1));
 
 /* DECLARE LOCAL VARIABLES FOR HOLDING ACTUAL DATA */
 double sal;
 short salind;
 
 /* INITIALIZE ONE ELEMENT OF SQLDA */
 memcpy( outda->sqldaid,"SQLDA   ",sizeof(outda->sqldaid));
 outda->sqln = outda->sqld = 1;
 outda->sqlvar[0].sqltype = SQL_TYP_NFLOAT;
 outda->sqlvar[0].sqllen  = sizeof( double );.
 outda->sqlvar[0].sqldata = (unsigned char *)&sal;
 outda->sqlvar[0].sqlind  = (short *)&salind;

COBOL
       WORKING-STORAGE SECTION.
       77 SALARY          PIC S99999V99 COMP-3.
       77 SAL-IND         PIC S9(4)     COMP-5.
 
          EXEC SQL INCLUDE SQLDA END-EXEC
 
      * Or code a useful way to save unused SQLVAR entries.
      * COPY "sqlda.cbl" REPLACING --1489-- BY --1--.
 
            01 decimal-sqllen pic s9(4) comp-5.
            01 decimal-parts redefines decimal-sqllen.
               05 precision pic x.
               05 scale pic x.
 
      * Initialize one element of output SQLDA
            MOVE 1 TO SQLN
            MOVE 1 TO SQLD
            MOVE SQL-TYP-NDECIMAL TO SQLTYPE(1)
 
      * Length = 7 digits precision and 2 digits scale
            MOVE x"07" TO PRECISION.
            MOVE x"02" TO SCALE.
            MOVE DECIMAL-SQLLEN TO O-SQLLEN(1).
            SET SQLDATA(1) TO ADDRESS OF SALARY
            SET SQLIND(1)  TO ADDRESS OF SAL-IND

FORTRAN
      include 'sqldact.f'
 
      integer*2  sqlvar1
      parameter ( sqlvar1 = sqlda_header_sz + 0*sqlvar_struct_sz )
 
C     Declare an Output SQLDA -- 1 Variable
      character    out_sqlda(sqlda_header_sz + 1*sqlvar_struct_sz)
 
      character*8  out_sqldaid     ! Header
      integer*4    out_sqldabc
      integer*2    out_sqln
      integer*2    out_sqld
 
      integer*2    out_sqltype1    ! First Variable
      integer*2    out_sqllen1
      integer*4    out_sqldata1
      integer*4    out_sqlind1
      integer*2    out_sqlnamel1
      character*30 out_sqlnamec1
 
      equivalence( out_sqlda(sqlda_sqldaid_ofs), out_sqldaid )
      equivalence( out_sqlda(sqlda_sqldabc_ofs), out_sqldabc )
      equivalence( out_sqlda(sqlda_sqln_ofs), out_sqln       )
      equivalence( out_sqlda(sqlda_sqld_ofs), out_sqld       )
      equivalence( out_sqlda(sqlvar1+sqlvar_type_ofs), out_sqltype1 )
      equivalence( out_sqlda(sqlvar1+sqlvar_len_ofs), out_sqllen1   )
      equivalence( out_sqlda(sqlvar1+sqlvar_data_ofs), out_sqldata1 )
      equivalence( out_sqlda(sqlvar1+sqlvar_ind_ofs), out_sqlind1   )
      equivalence( out_sqlda(sqlvar1+sqlvar_name_length_ofs),
     +             out_sqlnamel1                                   )
      equivalence( out_sqlda(sqlvar1+sqlvar_name_data_ofs),
     +             out_sqlnamec1                                   )
 
C     Declare Local Variables for Holding Returned Data.
         real*8        salary
      integer*2    sal_ind
 
C     Initialize the Output SQLDA (Header)
      out_sqldaid  = 'OUT_SQLDA'
      out_sqldabc  = sqlda_header_sz + 1*sqlvar_struct_sz
      out_sqln     = 1
      out_sqld     = 1
C     Initialize VAR1
      out_sqltype1 = SQL_TYP_NFLOAT
      out_sqllen1  = 8
      rc = sqlgaddr( %ref(salary), %ref(out_sqldata1) )
      rc = sqlgaddr( %ref(sal_ind), %ref(out_sqlind1) )

µ¿Àû ¸Þ¸ð¸® ÇÒ´çÀ» Áö¿øÇÏÁö ¾Ê´Â ¾ð¾î·Î, ¿øÇÏ´Â ¼öÀÇ SQLVAR ¿ä¼Ò¸¦ °¡Áø SQLDA´Â È£½ºÆ® ¾ð¾î·Î ¸í½ÃÀûÀ¸·Î ¼±¾ðµÇ¾î¾ß ÇÕ´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ÀÇ Çʿ信 ÀÇÇØ ÃæºÐÇÑ SQLVAR ¿ä¼Ò¸¦ ¼±¾ðÇϵµ·Ï ÇϽʽÿÀ.

SQLDA ±¸Á¶¸¦ »ç¿ëÇÑ µ¥ÀÌÅÍ Àü´Þ

SQLDA¸¦ »ç¿ëÇÑ µ¥ÀÌÅÍ Àü´ÞÇϸé È£½ºÆ® º¯¼ö ¸ñ·ÏÀ» »ç¿ëÇÒ ¶§º¸´Ù À¶Å뼺ÀÌ Áõ°¡µË´Ï´Ù. ¿¹¸¦ µé¾î, C ¾ð¾î·Î µÈ DECIMAL µ¥ÀÌÅÍ¿Í °°Àº º»·¡ÀÇ È£½ºÆ® ¾ð¾î°¡ ¾ø´Â µ¥ÀÌÅ͸¦ Àü¼ÛÇϱâ À§ÇØ SQLDA¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ADHOC¶ó´Â »ùÇà ÇÁ·Î±×·¥Àº ÀÌ ±â¼úÀ» »ç¿ëÇÏ´Â ¿¹ÀÔ´Ï´Ù(¿¹: ADHOC ÇÁ·Î±×·¥ ÂüÁ¶). ¼öÄ¡ °ª ¹× ±âÈ£ À̸§ÀÌ ¾î¶»°Ô ¿¬°üµÇ¾î ÀÖ´ÂÁö Ç¥½ÃÇÏ´Â Æí¸®ÇÑ »óÈ£ÂüÁ¶ ¸ñ·ÏÀº Ç¥ 8ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

Ç¥ 8. DB2 V2 SQLDA SQL À¯Çü
¼ýÀÚ °ª°ú ÀÌ¿¡ »óÀÀÇÏ´Â ±âÈ£ À̸§
SQL Ä÷³ À¯Çü SQLTYPE ¼ýÀÚ °ª SQLTYPE ±âÈ£ À̸§1
DATE 384/385 SQL_TYP_DATE / SQL_TYP_NDATE
TIME 388/389 SQL_TYP_TIME / SQL_TYP_NTIME
TIMESTAMP 392/393 SQL_TYP_STAMP / SQL_TYP_NSTAMP
n/a2 400/401 SQL_TYP_CGSTR / SQL_TYP_NCGSTR
BLOB 404/405 SQL_TYP_BLOB / SQL_TYP_NBLOB
CLOB 408/409 SQL_TYP_CLOB / SQL_TYP_NCLOB
DBCLOB 412/413 SQL_TYP_DBCLOB / SQL_TYP_NDBCLOB
VARCHAR 448/449 SQL_TYP_VARCHAR / SQL_TYP_NVARCHAR
CHAR 452/453 SQL_TYP_CHAR / SQL_TYP_NCHAR
LONG VARCHAR 456/457 SQL_TYP_LONG / SQL_TYP_NLONG
n/a3 460/461 SQL_TYP_CSTR / SQL_TYP_NCSTR
VARGRAPHIC 464/465 SQL_TYP_VARGRAPH / SQL_TYP_NVARGRAPH
GRAPHIC 468/469 SQL_TYP_GRAPHIC / SQL_TYP_NGRAPHIC
LONG VARGRAPHIC 472/473 SQL_TYP_LONGRAPH / SQL_TYP_NLONGRAPH
FLOAT 480/481 SQL_TYP_FLOAT / SQL_TYP_NFLOAT
REAL4 480/481 SQL_TYP_FLOAT / SQL_TYP_NFLOAT
DECIMAL5 484/485 SQL_TYP_DECIMAL / SQL_TYP_DECIMAL
INTEGER 496/497 SQL_TYP_INTEGER / SQL_TYP_NINTEGER
SMALLINT 500/501 SQL_TYP_SMALL / SQL_TYP_NSMALL
n/a 804/805 SQL_TYP_BLOB_FILE / SQL_TYPE_NBLOB_FILE
n/a 808/809 SQL_TYP_CLOB_FILE / SQL_TYPE_NCLOB_FILE
n/a 812/813 SQL_TYP_DBCLOB_FILE / SQL_TYPE_NDBCLOB_FILE
n/a 960/961 SQL_TYP_BLOB_LOCATOR / SQL_TYP_NBLOB_LOCATOR
n/a 964/965 SQL_TYP_CLOB_LOCATOR / SQL_TYP_NCLOB_LOCATOR
n/a 968/969 SQL_TYP_DBCLOB_LOCATOR / SQL_TYP_NDBCLOB_LOCATOR
Note:Á¤ÀÇµÈ À¯ÇüÀº sqllib µð·ºÅ丮ÀÇ include ¼­ºêµð·ºÅ丮¿¡ ÀÖ´Â sql.h Æ÷ÇÔ ÆÄÀÏ¿¡ ÀÖ½À´Ï´Ù. (¿¹, C ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡¼­ sqllib/include/sql.h)

  1. COBOL ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡¼­´Â SQLTYPE À̸§Àº ¹ØÁÙ(_)À» »ç¿ëÇÏÁö ¾Ê°í ´ë½Å ÇÏÀÌÇÂ(-)À» »ç¿ëÇÕ´Ï´Ù.
  2. ³Î(null) Á¾·á ±×·¡ÇÈ ¹®ÀÚ¿­ÀÔ´Ï´Ù.
  3. ³Î(null) Á¾·á ¹®ÀÚ¿­ÀÔ´Ï´Ù.
  4. SQLDA¿¡¼­ DOUBLE°ú REALÀº ±æÀÌ °ª(4 ¶Ç´Â 8)¿¡¼­ Â÷À̰¡ ³³´Ï´Ù.
  5. Á¤¹Ðµµ´Â ù¹øÂ° ¹ÙÀÌÆ®ÀÔ´Ï´Ù. ½ºÄÉÀÏÀº µÎ ¹øÂ° ¹ÙÀÌÆ®ÀÔ´Ï´Ù.

´ëÈ­½Ä SQL¹® ó¸®

µ¿Àû SQLÀ» »ç¿ëÇÏ´Â ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇÏ¿© ÀÓÀÇ SQL¹®À» ó¸®ÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, ÀÀ¿ëÇÁ·Î±×·¥ÀÌ »ç¿ëÀڷκÎÅÍ SQL¹®À» Çã¿ëÇÒ °æ¿ì, ÀÀ¿ëÇÁ·Î±×·¥Àº ¸í·É¹®¿¡ ´ëÇÑ ÀÌÀü Áö½Ä ¾øÀÌ ¸í·É¹®À» ½ÇÇàÇÒ ¼ö ÀÖ¾î¾ß ÇÕ´Ï´Ù.

SQLDA ±¸Á¶¿¡¼­ PREPARE ¹× DESCRIBE¹®À» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥Àº ½ÇÇà ÁßÀÎ SQL¹® À¯ÇüÀ» °áÁ¤ÇÏ°í ±×¿¡ µû¶ó Á¶ÀÛÀ» ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

´ëÈ­½Ä SQL¹®À» ó¸®ÇÏ´Â ÇÁ·Î±×·¥ÀÇ ¿¹¸¦ º¸·Á¸é ¿¹: ADHOC ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

¸í·É¹® À¯Çü °áÁ¤

SQL¹®ÀÌ ÁغñµÉ ¶§ ¸í·É¹® À¯Çü°ú °ü·ÃµÈ Á¤º¸´Â SQLDA ±¸Á¶¸¦ °ËÅäÇÏ¿© °áÁ¤µË´Ï´Ù. ÀÌ Á¤º¸´Â INTOÀý¿¡¼­ ¸í·É¹®ÀÌ ÁغñµÉ ¶§ ¶Ç´Â ÀÌÀü¿¡ ÁغñµÈ ¸í·É¹®¿¡ ´ëÇØ DESCRIBE¹®À» ¹ßÇàÇÏ¿© SQLDA ±¸Á¶¿¡ µÑ ¼ö ÀÖ½À´Ï´Ù.

¾î¶² °æ¿ìÀÌµç µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº °ªÀ» ±¸Á¶ÀÇ SQLD Çʵ忡 °ªÀ» µÓ´Ï´Ù. ÀÌ °ªÀº SQL¹®¿¡ ÀÇÇØ »ý¼ºµÈ °á°ú Å×ÀÌºí¿¡ ÀÖ´Â Ä÷³ ¼ö¸¦ ³ªÅ¸³À´Ï´Ù. SQLD Çʵ忡 Á¦·Î(0) °ªÀÌ Æ÷ÇԵǾî ÀÖÀ» °æ¿ì ¸í·É¹®Àº SELECT¹®ÀÌ ¾Æ´Õ´Ï´Ù. ¸í·É¹®ÀÌ ÀÌ¹Ì ÁغñµÇ¾úÀ¸¹Ç·Î EXECUTE¹®À» »ç¿ëÇÏ¿© Áï½Ã ½ÇÇàµÉ ¼ö ÀÖ½À´Ï´Ù.

¸í·É¹®¿¡ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ Æ÷ÇԵǾî ÀÖ´Â °æ¿ì USINGÀýÀ» SQL ÂüÁ¶¼­¿¡¼­¿Í °°ÀÌ ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. USINGÀýÀº È£½ºÆ® º¯¼ö ¸ñ·Ï ¶Ç´Â SQLDA ±¸Á¶¸¦ ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù.

SQLD Çʵ尡 Á¦·Îº¸´Ù Ŭ °æ¿ì ¸í·É¹®Àº SELECT¹®À̰í, ´ÙÀ½ Àý¿¡¼­ ¼³¸íµÈ ´ë·Î ó¸®ÇØ¾ß ÇÕ´Ï´Ù.

Varying-List SELECT¹®

varying-list SELECT¹®Àº ¸®ÅϵǴ Ä÷³ À¯Çü ¹× Ä÷³ ¼ö°¡ »çÀü ó¸® ÄÄÆÄÀ쵃 ¶§ ¾Ë·ÁÁöÁö ¾Ê´Â ¸í·É¹®ÀÔ´Ï´Ù. ÀÌ·± °æ¿ì ÀÀ¿ëÇÁ·Î±×·¥Àº °á°ú Å×À̺íÀÇ ÇàÀ» º¸À¯Çϵµ·Ï ¼±¾ðÇØ¾ß ÇÒ È£½ºÆ® º¯¼ö¸¦ ¹Ì¸® ¾ËÁö ¸øÇÕ´Ï´Ù.

varying-list SELECT¹®À» ó¸®Çϱâ À§ÇØ ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½À» ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

  1. SQLDA ¼±¾ð. varying-list SELECT¹®À» ó¸®Çϱâ À§ÇØ SQLDA ±¸Á¶¸¦ »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.
  2. INTOÀýÀ» »ç¿ëÇÑ ¸í·É¹® PREPARE. ±×·± ÈÄ ÀÀ¿ëÇÁ·Î±×·¥Àº ¼±¾ðµÈ SQLDA ±¸Á¶¿¡ ÃæºÐÇÑ SQLVAR ¿ä¼Ò°¡ ÀÖ´ÂÁö °áÁ¤ÇÕ´Ï´Ù. ±×·¸Áö ¾ÊÀº °æ¿ì, ÀÀ¿ëÇÁ·Î±×·¥Àº ÇÊ¿äÇÑ ¼öÀÇ SQLVAR ¿ä¼Ò¸¦ °¡Áø ´Ù¸¥ SQLDA ±¸Á¶¸¦ ÇÒ´çÇÏ°í »õ·Î¿î SQLDA¸¦ »ç¿ëÇÏ¿© Ãß°¡ DESCRIBE¹®À» ½ÇÇàÇÕ´Ï´Ù.
  3. SQLVAR ¿ä¼Ò ÇÒ´ç. °¢ SQLVAR¿¡ ÇÊ¿äÇÑ È£½ºÆ® º¯¼ö ¹× Ç¥½Ã±â¿¡ ´ëÇÑ ÀúÀ念¿ªÀ» ÇÒ´çÇÕ´Ï´Ù. ÀÌ ´Ü°è¿¡´Â µ¥ÀÌÅÍ ¹× Ç¥½Ã±â º¯¼ö¿¡ ´ëÇØ ÇÒ´çµÈ ÁÖ¼Ò¸¦ °¢ SQLVAR ¿ä¼Ò¿¡ µÓ´Ï´Ù.
  4. SELECT¹® ó¸®. Ä¿¼­´Â ÁغñµÈ ¸í·É¹®°ú ¿¬°ü ¹× ¿­·Á ÀÖ°í, ÇàµéÀº ÀûÀýÈ÷ ÇÒ´çµÈ SQLDA ±¸Á¶¸¦ »ç¿ëÇÏ¿© ÆäÄ¡µË´Ï´Ù.

ÀÌ ´Ü°è¿¡ ´ëÇØ¼­´Â ´ÙÀ½ Àý¿¡¼­ »ó¼¼È÷ ¼³¸íÇÕ´Ï´Ù.

ÀÏ¹Ý »ç¿ëÀڷκÎÅÍ SQL ¿äû ÀúÀå

ÀÀ¿ëÇÁ·Î±×·¥À» ÀÌ¿ëÇÏ¿© ÀÓÀÇÀÇ SQL¹®À» ÀúÀåÇÒ ¼ö ÀÖ´Â °æ¿ì, µ¥ÀÌÅÍ À¯ÇüÀÌ VARCHAR, LONG VARCHAR, CLOB, VARGRAPHIC, LONG VARGRAPHIC ¶Ç´Â DBCLOBÀÎ Ä÷³À» °¡Áø Å×ÀÌºí¿¡ ÀúÀåÇÒ ¼ö ÀÖ½À´Ï´Ù. VARGRAPHIC, LONG VARGRAPHIC ¹× DBCLOB µ¥ÀÌÅÍ À¯ÇüÀº DBCS(2¹ÙÀÌÆ® ¹®ÀÚ Áö¿ø) ¹× EUC(È®Àå UNIX ÄÚµå) ȯ°æ¿¡¼­¸¸ »ç¿ë°¡´ÉÇÕ´Ï´Ù.

ÁغñµÈ ¹öÀüÀÌ ¾Æ´Ñ ¼Ò½º SQL¹®À» ÀúÀåÇØ¾ß ÇÕ´Ï´Ù. ÀÌ´Â Å×ÀÌºí¿¡ ÀúÀåµÈ ¹öÀüÀ» ½ÇÇàÇϱâ Àü¿¡ °¢ ¸í·É¹®À» °Ë»öÇÑ ÈÄ ÁغñÇØ¾ß ÇÑ´Ù´Â °ÍÀ» ÀǹÌÇÕ´Ï´Ù. ±Ùº»ÀûÀ¸·Î »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥Àº ¹®ÀÚ¿­·ÎºÎÅÍ SQL¹®À» ÁغñÇϰí ÀÌ ¸í·É¹®À» µ¿ÀûÀ¸·Î ½ÇÇàÇÕ´Ï´Ù.

¿¹: ADHOC ÇÁ·Î±×·¥

ÀÌ »ùÇà ÇÁ·Î±×·¥Àº SQLDA¸¦ »ç¿ëÇÏ¿© ´ëÈ­½Ä SQL¹®À» ó¸®ÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
ÁÖ:¿¹ adhoc.sqc ´Â C ¾ð¾î¿¡¸¸ ÀÖ½À´Ï´Ù.

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

  1. SQLDA ±¸Á¶ Á¤ÀÇ INCLUDE SQLDA¹®Àº SQLDA ±¸Á¶¸¦ Á¤ÀÇ ¹× ¼±¾ðÇϸç, À̸¦ »ç¿ëÇÏ¿© µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡¼­ ÇÁ·Î±×·¥À¸·Î, ¶Ç´Â ÇÁ·Î±×·¥¿¡¼­ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À» µ¥ÀÌÅ͸¦ Àü´ÞÇÕ´Ï´Ù.
  2. SQLCA ±¸Á¶ Á¤ÀÇ INCLUDE SQLCA¹®Àº SQLCA ±¸Á¶¸¦ Á¤ÀÇÇϰí, ±¸Á¶ ³»ÀÇ ¿ä¼Ò·Î¼­ SQLCODE¸¦ Á¤ÀÇÇÕ´Ï´Ù. SQLCA ±¸Á¶ÀÇ SQLCODE Çʵå´Â SQL¹®À» ½ÇÇàÇÑ ÈÄ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡ ÀÇÇÑ Áø´Ü Á¤º¸·Î °»½ÅµË´Ï´Ù.
  3. È£½ºÆ® º¯¼ö Á¤ÀÇ. BEGIN DECLARE SECTION ¹× END DECLARE SECTION¹®Àº È£½ºÆ® º¯¼ö ¼±¾ðÀ» ºÐ¸®ÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â SQL¹®¿¡¼­ ÂüÁ¶µÉ ¶§ ÄÝ·Ð(:)À¸·Î ½ÃÀ۵˴ϴÙ.
  4. µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°á. ÇÁ·Î±×·¥Àº »ç¿ëÀÚ°¡ ÁöÁ¤ÇÑ µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇÏ¿© ÀÌ¿¡ ´ëÇÑ °øÀ¯ ¾×¼¼½º¸¦ ¿äûÇÕ´Ï´Ù.(START DATABASE MANAGER API È£ÃâÀ̳ª db2start ¸í·ÉÀÌ ¹ßÇàµÇ¾ú´Ù°í ÀüÁ¦ÇÕ´Ï´Ù.) °øÀ¯ ¸ðµå·Î µ¿ÀÏÇÑ µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇÏ·Á°í ÇÏ´Â ±âŸ ÇÁ·Î±×·¥¿¡°Ôµµ ¾×¼¼½º°¡ ±ÇÇÑ ºÎ¿©µË´Ï´Ù.
  5. Á¡°Ë ¿Ï·á. SQLCA ±¸Á¶¿¡¼­ CONNECT TO¹®ÀÌ ¼º°øÀûÀ¸·Î ¿Ï·áµÇ¾ú´ÂÁö Á¡°ËµË´Ï´Ù. SQLCODE °ª 0Àº ¿¬°áÀÌ ¼º°øÇßÀ½À» ³ªÅ¸³À´Ï´Ù.
  6. ´ëÈ­½Ä ÇÁ·ÒÇÁÆ®. SQL¹®Àº ÇÁ·ÒÇÁÆ®¸¦ ÅëÇØ ÀԷµǸç, process_statement ÇÔ¼ö·Î Àü¼ÛµË´Ï´Ù.
  7. Æ®·£Àè¼Ç Á¾·á - COMMIT. ÀÛ¾÷ ´ÜÀ§(UOW)´Â »ç¿ëÀÚ°¡ ¼±ÅÃÇÒ °æ¿ì COMMITÀ¸·Î Á¾·áµË´Ï´Ù. ÃÖÁ¾ COMMIT ÀÌÈÄ ÀÔ·ÂµÈ SQL¹®ÀÌ ¿äûÇÑ ¸ðµç º¯°æ»çÇ×Àº µ¥ÀÌÅͺ£À̽º¿¡ ÀúÀåµË´Ï´Ù.
  8. Æ®·£Àè¼Ç Á¾·á - ROLLBACK. ÀÛ¾÷ ´ÜÀ§(UOW)´Â »ç¿ëÀÚ°¡ ¼±ÅÃÇÒ °æ¿ì ROLLBACKÀ¸·Î Á¾·áµË´Ï´Ù. ÃÖÁ¾ COMMIT ¶Ç´Â ÇÁ·Î±×·¥ ½ÃÀÛ ÀÌÈÄ ÀÔ·ÂµÈ SQL¹®ÀÌ ¿äûÇÑ ¸ðµç º¯°æ»çÇ×Àº ½ÇÇà Ãë¼ÒµË´Ï´Ù.
  9. µ¥ÀÌÅͺ£À̽º·ÎºÎÅÍ ¿¬°áÇØÁ¦. ÇÁ·Î±×·¥Àº CONNECT RESET¹®À» ½ÇÇàÇÏ¿© µ¥ÀÌÅͺ£À̽º·ÎºÎÅÍÀÇ ¿¬°áÀ» ÇØÁ¦ÇÕ´Ï´Ù. ¸®Å쵃 ¶§ SQLCA°¡ ¼º°øÀûÀ¸·Î ¿Ï·áµÇ¾ú´ÂÁö Á¡°ËµË´Ï´Ù.
  10. SQL¹® ÅØ½ºÆ®¸¦ È£½ºÆ® º¯¼ö·Î º¹»ç. ¸í·É¹® ÅØ½ºÆ®´Â È£½ºÆ® º¯¼ö st°¡ ÁöÁ¤ÇÑ µ¥ÀÌÅÍ ¿µ¿ªÀ¸·Î º¹»çµË´Ï´Ù.
  11. SQLDA ó¸® Áغñ. init_da ÇÁ·Î½Ãµà¾î¸¦ ÅëÇØ Ãʱâ SQLDA ±¸Á¶°¡ ¼±¾ðµÇ°í ¸Þ¸ð¸®°¡ ÇÒ´çµÇ¾î, SQL¹®ÀÌ »ý¼ºÇÒ ¼ö ÀÖ´Â Ãâ·Â À¯ÇüÀ» °áÁ¤ÇÕ´Ï´Ù. ÀÌ PREPARE¹®À¸·ÎºÎÅÍ ¸®ÅÏµÈ SQLDA´Â SQL¹®À¸·ÎºÎÅÍ ¸®Å쵃 Ä÷³ ¼ö¸¦ º¸°íÇÕ´Ï´Ù.
  12. SQLDA º¸°í¼­ Ãâ·Â Ä÷³ÀÌ Á¸ÀçÇÕ´Ï´Ù. SQL¹®Àº SELECT¹®ÀÔ´Ï´Ù. SQLDA´Â ÁغñµÈ SQL¹®ÀÌ »óÁÖÇÒ ¸Þ¸ð¸® °ø°£À» ÇÒ´çÇϱâ À§ÇØ init_da ÇÁ·Î½Ãµà¾î¸¦ ÅëÇØ ÃʱâÈ­µË´Ï´Ù.
  13. SQLDA´Â Ãâ·Â Ä÷³À» º¸°íÇÏÁö ¾Ê½À´Ï´Ù. ¸®Å쵃 Ä÷³ÀÌ ¾ø½À´Ï´Ù. SQL¹®Àº EXECUTE¹®À» »ç¿ëÇÏ¿© µ¿ÀûÀ¸·Î ½ÇÇàµË´Ï´Ù.
  14. SQLDA¿ë ¸Þ¸ð¸® °ø°£Áغñ SQLDA¿¡¼­ Ä÷³ ±¸Á¶¸¦ ¹Ý¿µÇϵµ·Ï ¸Þ¸ð¸®°¡ ÇÒ´çµË´Ï´Ù. ÇÊ¿äÇÑ ¾çÀÇ ¸Þ¸ð¸®´Â SQLDA¿¡ ÀÖ´Â Ä÷³ ±¸Á¶ÀÇ SQLLEN ¹× SQLTYPE¿¡ ÀÇÇØ ¼±Åõ˴ϴÙ.
  15. Ä¿¼­ ¼±¾ð ¹× ¿­±â. DECLARE¹®Àº pcurs Ä¿¼­¸¦ sqlStatement¿¡ ÀÖ´Â µ¿ÀûÀ¸·Î ÁغñµÈ SQL¹®À» ¿¬°áÇϰí Ä¿¼­°¡ ¿­·Á ÀÖ½À´Ï´Ù.
  16. Çà °Ë»ö. FETCH¹®Àº ´ÙÀ½ Çà¿¡ Ä¿¼­¸¦ µÎ°í Çà ³»¿ëÀ» SQLDA·Î À̵¿½Ãŵ´Ï´Ù.
  17. Ä÷³ Á¦¸ñ Ç¥½Ã. ÆäÄ¡µÈ ù¹øÂ° ÇàÀº Ä÷³ Á¦¸ñ Á¤º¸ÀÔ´Ï´Ù.
  18. Çà Á¤º¸ Ç¥½Ã. ¿¬¼ÓµÈ °¢ FETCH·ÎºÎÅÍ ¼öÁýµÈ Á¤º¸ ÇàÀÌ Ç¥½ÃµË´Ï´Ù.
  19. Ä¿¼­ ´Ý±â. CLOSE¹®Àº Ä¿¼­¸¦ ´Ý°í ÀÌ¿Í ¿¬°áµÈ ÀÚ¿øÀ» ¸±¸®½ºÇÕ´Ï´Ù.

EMB_SQL_CHECK ¸ÅÅ©·Î/ÇÔ¼ö´Â ÀÌÇÁ·Î±×·¥ÀÇ ¿ÜºÎ¿¡ ÀÖ´Â ¿À·ù Á¡°Ë À¯Æ¿¸®Æ¼ÀÔ´Ï´Ù. DB2 API¸¦ È£ÃâÇÏ´Â C ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilapi.c¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilapi.h¿¡¼­ API_SQL_CHECK·Î¼­ ÂüÁ¶µË´Ï´Ù. C embedded SQL ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilemb.sqc¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilemb.h¿¡¼­ EMB_SQL_CHECK·Î¼­ ÂüÁ¶µË´Ï´Ù. ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼¿¡ ´ëÇÑ ¼Ò½º Äڵ忡 ´ëÇØ¼­´Â ¿¹Á¦ ÇÁ·Î±×·¥¿¡¼­ GET ERROR MESSAGE »ç¿ëÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

ÀÌ ¿¹¿¡¼­´Â utilemb.sqc ÆÄÀÏÀÇ À¯Æ¿¸®Æ¼·Î¼­ Á¦°øµÇ´Â Ãß°¡ ÇÁ·Î½Ãµà¾î¸¦ »ç¿ëÇÕ´Ï´Ù. ¿©±â¿¡´Â ´ÙÀ½ÀÌ Æ÷ÇԵ˴ϴÙ.

init_da
ÁغñµÈ SQL¹®¿¡ ´ëÇÑ ¸Þ¸ð¸®¸¦ ÇÒ´çÇÕ´Ï´Ù. SQLDASIZE¶ó´Â ³»ºÎ ¼­¼ú ÇÔ¼ö´Â ÀûÀýÇÑ ¸Þ¸ð¸®·®À» °è»êÇÏ´Â µ¥ »ç¿ëµË´Ï´Ù.
alloc_host_vars
SQLDA Æ÷ÀÎÅͷκÎÅÍ µ¥ÀÌÅÍ¿¡ ´ëÇÑ ¸Þ¸ð¸®¸¦ ÇÒ´çÇÕ´Ï´Ù.
free_da
SQLDA µ¥ÀÌÅÍ ±¸Á¶¸¦ »ç¿ëÇÏ´Â µ¥ ÇÒ´çµÈ ¸Þ¸ð¸®¸¦ ºñ¿ó´Ï´Ù.
print_var
SQLDA SQLVAR º¯¼ö¸¦ ÀμâÇÕ´Ï´Ù. ¿ì¼± ÀÌ ÇÁ·Î½Ãµà¾î´Â µ¥ÀÌÅÍ À¯ÇüÀ» °áÁ¤Çϰí, µ¥ÀÌÅ͸¦ ÀμâÇÏ´Â µ¥ ÇÊ¿äÇÑ ÇØ´ç ¼­ºê·çƾÀ» È£ÃâÇÕ´Ï´Ù.
display_da
Àü´ÞµÈ Æ÷ÀÎÅÍÀÇ Ãâ·ÂÀ» Ç¥½ÃÇÕ´Ï´Ù. print_var ÇÁ·Î½Ãµà¾î¿¡¼­ °ËÅäµÈ ´ë·Î, Ãâ·Â µ¥ÀÌÅÍÀÇ ±¸Á¶¿¡ °üÇÑ ¸ðµç Á¤º¸¸¦ ÀÌ Æ÷ÀÎÅͷκÎÅÍ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

C ¿¹: ADHOC.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlcodes.h>
#include <sqlda.h> (1)
#include "utilemb.h"
 
#ifdef DB268K
  /* Need to include ASLM for 68K applications */
  #include <LibraryManager.h>
#endif
 
EXEC SQL INCLUDE SQLCA ; (2)
 
#define SQLSTATE sqlca.sqlstate
 
int process_statement( char * ) ;
 
int main( int argc, char *argv[] ) {
 
   int rc ;
   char sqlInput[256] ;
   char st[1024] ;
 
   EXEC SQL BEGIN DECLARE SECTION ; (3)
      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 : ADHOC interactive SQL\n" ) ;
 
   /* Initialize the connection to a database. */
   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 ; (4)
      EMB_SQL_CHECK( "CONNECT TO SAMPLE" ) ; (5)
   }
   else {
      printf( "\nUSAGE: adhoc [userid passwd]\n\n" ) ;
      return( 1 ) ;
   } /* endif */
 
   printf( "Connected to database SAMPLE\n" ) ;
 
   /* Enter the continuous command line loop. */
   *sqlInput = '\0' ;
   while ( ( *sqlInput != 'q' ) && ( *sqlInput != 'Q' ) ) { (6)
 
      printf( "Enter an SQL statement or 'quit' to Quit :\n" ) ;
      gets( sqlInput ) ;
 
      if ( ( *sqlInput == 'q' ) || ( *sqlInput == 'Q' ) ) break ;
 
      if ( *sqlInput == '\0' ) { /* Don't process the statement */
         printf( "No characters entered.\n" ) ;
         continue ;
      }
 
      strcpy( st, sqlInput ) ;
      while ( sqlInput[strlen( sqlInput ) - 1] == '\\' ) {
         st[strlen( st ) - 1] = '\0' ;
         gets( sqlInput ) ;
         strcat( st, sqlInput ) ;
      }
 
      /* Process the statement. */
      rc = process_statement( st ) ;
 
   }
 
   printf( "Enter 'c' to COMMIT or Any Other key to ROLLBACK the transaction :\n" ) ;
   gets( sqlInput ) ;
   if ( ( *sqlInput == 'c' ) || ( *sqlInput == 'C' ) ) {
      printf( "COMMITING the transactions.\n" ) ;
      EXEC SQL COMMIT ;  (7)
      EMB_SQL_CHECK( "COMMIT" ) ;
   }
   else { /* assume that the transaction is to be rolled back */
      printf( "ROLLING BACK the transactions.\n" ) ;
      EXEC SQL ROLLBACK ; (8)
      EMB_SQL_CHECK( "ROLLBACK" ) ;
   }
 
   EXEC SQL CONNECT RESET ; (9)
   EMB_SQL_CHECK( "CONNECT RESET" ) ;
 
   return( 0 ) ;
 
}
 
/*******************************************************************
 * FUNCTION : process_statement
 * This function processes the inputted statement and then prepares the
 *  procedural SQL implementation to take place.
*******************************************************************/
int process_statement ( char * sqlInput ) {
 
   int counter = 0 ;
   struct sqlda * sqldaPointer ;
   short sqlda_d ;
 
   EXEC SQL BEGIN DECLARE SECTION ; (3)
      char st[1024] ;
   EXEC SQL END DECLARE SECTION ;
 
   strcpy( st, sqlInput ) ; (10)
   /* allocate an initial SQLDA temp pointer to obtain information
      about the inputted "st" */
 
   init_da( &sqldaPointer, 1 ) ; (11)
 
   EXEC SQL PREPARE statement1 from :st ;
   /* EMB_SQL_CHECK( "PREPARE" ) ; */
 
   EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer ;
 
   /* Expecting a return code of 0 or SQL_RC_W236,
      SQL_RC_W237, SQL_RC_W238, SQL_RC_W239 for cases
      where this statement is a SELECT statment. */
   if ( SQLCODE != 0           &&
        SQLCODE != SQL_RC_W236 &&
        SQLCODE != SQL_RC_W237 &&
        SQLCODE != SQL_RC_W238 &&
        SQLCODE != SQL_RC_W239
      ) {
      /* An unexpected warning/error has occurred. Check the SQLCA. */
      EMB_SQL_CHECK( "DESCRIBE" ) ;
   } /* end if */
 
   sqlda_d = sqldaPointer->sqld ;
   free( sqldaPointer ) ;
 
   if ( sqlda_d > 0 ) { (12)
 
      /* this is a SELECT statement, a number of columns
         are present in the SQLDA */
 
      if ( SQLCODE == SQL_RC_W236 || SQLCODE == 0) 
         /* this out only needs a SINGLE SQLDA */
         init_da( &sqldaPointer, sqlda_d ) ;
 
      if ( SQLCODE == SQL_RC_W237 ||
           SQLCODE == SQL_RC_W238 ||
           SQLCODE == SQL_RC_W239 )
         /* this output contains columns that need a DOUBLED SQLDA */
         init_da( &sqldaPointer, sqlda_d * 2 ) ;
 
      /* need to reassign the SQLDA with the correct number
         of columns to the SQL statement */
      EXEC SQL DESCRIBE statement1 INTO :*sqldaPointer ;
      EMB_SQL_CHECK( "DESCRIBE" ) ;
 
      /* allocating the proper amount of memory
         space needed for the variables */
      alloc_host_vars( sqldaPointer ) ; (14)
 
      /* Don't need to check the SQLCODE for declaration of cursors */
      EXEC SQL DECLARE pcurs CURSOR FOR statement1 ; (15)
 
      EXEC SQL OPEN pcurs ; (15)
      EMB_SQL_CHECK( "OPEN" ) ;
 
      EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer; (16)
      EMB_SQL_CHECK( "FETCH" ) ;
 
      /* if the FETCH is successful, obtain data from SQLDA */
      /* display the column titles */
      display_col_titles( sqldaPointer ) ; (17)
 
      /* display the rows that are fetched */
      while ( SQLCODE == 0 ) {
         counter++ ;
         display_da( sqldaPointer ) ; (18)
         EXEC SQL FETCH pcurs USING DESCRIPTOR :*sqldaPointer ;
      }  /* endwhile */
 
      EXEC SQL CLOSE pcurs ; (19)
      EMB_SQL_CHECK( "CLOSE CURSOR" ) ;
      printf( "\n %d record(s) selected\n\n", counter ) ;
 
      /* Free the memory allocated to this SQLDA. */
      free_da( sqldaPointer ) ;
 
   } else { /* this is not a SELECT statement, execute SQL statement */ (13)
      EXEC SQL EXECUTE statement1 ;
      EMB_SQL_CHECK( "Executing the SQL statement" ) ;
   }  /* end if */
 
   return( 0 ) ;
 
}     /* end of program : ADHOC.SQC */


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