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


¿¹Á¦ ÀÔ·Â-SQLDA ÇÁ·Î±×·¥

´ÙÀ½Àº ÀÔ·Â SQLDA ±¸Á¶ »ç¿ëÀÇ µ¥¸ð¸¦ ³ªÅ¸³»´Â »ùÇà ÇÁ·Î±×·¥ÀÔ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº Presidents Å×À̺íÀ» ÀÛ¼ºÇÏ°í µ¥ÀÌÅÍ¿Í ÇÔ²² Å×À̺íÀ» ·ÎµåÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.

ÀÌ ÇÁ·Î±×·¥Àº SAMPLE µ¥ÀÌÅͺ£À̽ºÀÇ Presidents Å×À̺íÀ» ÀÛ¼ºÇÕ´Ï´Ù. ±×·± ´ÙÀ½ Å×ÀÌºí¿¡ Washington, Jefferson ¹× Lincoln °ªÀ» »ðÀÔÇÕ´Ï´Ù.

ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ »ç¿ëÇÏÁö ¾ÊÀ» °æ¿ì »ùÇà ÇÁ·Î±×·¥Àº ±×¸² 23°ú °°ÀÌ °¢ SQL¹®À» ó¸®Çϱâ À§ÇØ ³×Æ®¿öÅ©¿¡¼­ ³× °³ÀÇ º°µµ ¿äûÀ¸·Î µ¥ÀÌÅ͸¦ Àü¼ÛÇϵµ·Ï ¼³°èµÇ¾ú½À´Ï´Ù.

±×¸² 23. ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ¾ø´Â ÀÔ·Â-SQLDA »ùÇÃ


ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ¾ø´Â ÀÔ·Â-SQLDA »ùÇÃ

´ë½Å »ùÇà ÇÁ·Î±×·¥¿¡¼­´Â ÀúÀå ÇÁ·Î½Ãµà¾î ±â¼úÀ» Ȱ¿ëÇÏ¿© ¼­¹ö ÇÁ·Î½Ãµà¾î°¡ SQL¹®À» ÇÑ ±×·ìÀ¸·Î ½ÇÇàÇϵµ·Ï Çã¿ëÇϸ鼭 ³×Æ®¿öÅ©¿¡¼­ ¸ðµç µ¥ÀÌÅ͸¦ ÇÑ ¿äûÀ¸·Î Àü¼ÛÇÕ´Ï´Ù. ÀÌ ±â¼úÀº ±×¸² 24¿¡ ³ª¿Í ÀÖ½À´Ï´Ù.

±×¸² 24. ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ÀÖ´Â ÀÔ·Â-SQLDA »ùÇÃ


ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ÀÖ´Â ÀÔ·Â-SQLDA »ùÇÃ

»ùÇà ÀÔ·Â-SQLDA Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ ¹× »ùÇà ÀÔ·Â-SQLDA ÀúÀå ÇÁ·Î½Ãµà¾î´Â "ÀÔ·Â-SQLDA Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÇ ÀÛ¾÷ ¹æ½Ä"¿¡ ³ª¿Í ÀÖ½À´Ï´Ù.

ÀÔ·Â-SQLDA Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÇ ÀÛ¾÷ ¹æ½Ä

  1. ÀÔ·Â SQLDA ±¸Á¶ ÃʱâÈ­. ´ÙÀ½°ú °°Àº ÀÔ·Â SQLDAÀÇ Çʵ尡 ÃʱâÈ­µË´Ï´Ù.
  2. ¼­¹ö ÇÁ·Î½Ãµà¾î È£Ãâ. ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ´ÙÀ½À» »ç¿ëÇÏ¿© sample µ¥ÀÌÅͺ£À̽º À§Ä¡¿¡ ÀÖ´Â inpsrv ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
    1. È£½ºÆ® º¯¼ö°¡ ÀÖ´Â CALL¹®
    2. SQLDA°¡ ÀÖ´Â CALL¹®

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 ÆÄÀÏ¿¡ ÀÖ´Â ¼­ºê·çƾÀÔ´Ï´Ù.

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

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

C ¿¹: V5SPCLI.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqlutil.h>
#include "util.h"
 
#define  CHECKERR(CE_STR)   if (check_error (CE_STR, &sqlca) != 0) return 1;
 
int main(int argc, char *argv[]) {
 
   EXEC SQL BEGIN DECLARE SECTION;
     char database[9];
     char userid[9];
     char passwd[19];
     char procname[255]  = "inpsrv";
     char table_name[11]    = "PRESIDENTS";
     char data_item0[21]    = "Washington";
     char data_item1[21]    = "Jefferson";
     char data_item2[21]    = "Lincoln";
     short tableind, dataind0, dataind1, dataind2;
   EXEC SQL END DECLARE SECTION;
 
   /* Declare Variables for CALL USING */
   struct sqlca    sqlca;
   struct sqlda   *inout_sqlda = NULL;
   char eBuffer[1024]; /* error message buffer */
 
 
   if (argc != 4) {
      printf ("\nUSAGE: inpcli remote_database userid passwd\n\n");
      return 1;
   }
 
   strcpy (database, argv[1]);
   strcpy (userid, argv[2]);
   strcpy (passwd, argv[3]);
   /* Connect to Remote Database */
   printf("CONNECT TO Remote Database.\n");
   EXEC SQL CONNECT TO :database USER :userid USING :passwd;
   CHECKERR ("CONNECT TO SAMPLE");
 
   /********************************************************\
   * Call the Remote Procedure via CALL with Host Variables *
   \********************************************************/
   printf("Use CALL with Host Variable to invoke the Server Procedure"
      " named inpsrv.\n");
   tableind = dataind0 = dataind1 = dataind2 = 0;
 
   EXEC SQL CALL :procname (:table_name:tableind, :data_item0:dataind0,
      :data_item1:dataind1, :data_item2:dataind2); (2a)
   /* COMMIT or ROLLBACK the transaction */
   if (SQLCODE == 0)
   { EXEC SQL COMMIT;
     printf("Server Procedure Complete.\n\n");
   }
   else
   { /* print the error message, roll back the transaction and return */
     sqlaintp (eBuffer, 1024, 80, &sqlca);
     printf("\n%s\n", eBuffer);
 
     EXEC SQL ROLLBACK;
     printf("Server Procedure Transaction Rolled Back.\n\n");
     return 1;
   }
 
 
   /* Allocate and Initialize Input SQLDA */   (1)
   inout_sqlda = (struct sqlda *)malloc( SQLDASIZE(4) );
   inout_sqlda->sqln = 4;
   inout_sqlda->sqld = 4;
 
   inout_sqlda->sqlvar[0].sqltype = SQL_TYP_NCSTR;
   inout_sqlda->sqlvar[0].sqldata = table_name;
   inout_sqlda->sqlvar[0].sqllen  = strlen( table_name ) + 1;
   inout_sqlda->sqlvar[0].sqlind  = &tableind;
 
   inout_sqlda->sqlvar[1].sqltype = SQL_TYP_NCSTR;
   inout_sqlda->sqlvar[1].sqldata = data_item0;
   inout_sqlda->sqlvar[1].sqllen  = strlen( data_item0 ) + 1;
   inout_sqlda->sqlvar[1].sqlind  = &dataind0;
 
   inout_sqlda->sqlvar[2].sqltype = SQL_TYP_NCSTR;
   inout_sqlda->sqlvar[2].sqldata = data_item1;
   inout_sqlda->sqlvar[2].sqllen  = strlen( data_item1 ) + 1;
   inout_sqlda->sqlvar[2].sqlind  = &dataind1;
 
   inout_sqlda->sqlvar[3].sqltype = SQL_TYP_NCSTR;
   inout_sqlda->sqlvar[3].sqldata = data_item2;
   inout_sqlda->sqlvar[3].sqllen  = strlen( data_item2 ) + 1;
   inout_sqlda->sqlvar[3].sqlind  = &dataind2;
 
   /***********************************************\
   * Call the Remote Procedure via CALL with SQLDA *
   \***********************************************/
   printf("Use CALL with SQLDA to invoke the Server Procedure named "
      "inpsrv.\n");
 
   tableind = dataind0 = dataind1 = dataind2 = 0;
   inout_sqlda->sqlvar[0].sqlind  = &tableind;
   inout_sqlda->sqlvar[1].sqlind  = &dataind0;
   inout_sqlda->sqlvar[2].sqlind  = &dataind1;
   inout_sqlda->sqlvar[3].sqlind  = &dataind2;
 
   EXEC SQL CALL :procname USING DESCRIPTOR :*inout_sqlda;  (2b)
   /* COMMIT or ROLLBACK the transaction */
   if (SQLCODE == 0)
   { EXEC SQL COMMIT;
     printf("Server Procedure Complete.\n\n");
   }
   else
   { /* print the error message, roll back the transaction and return */
     sqlaintp (eBuffer, 1024, 80, &sqlca);
     printf("\n%s\n", eBuffer);
 
     EXEC SQL ROLLBACK;
     printf("Server Procedure Transaction Rolled Back.\n\n");
     return 1;
   }
 
   /* Free allocated memory */
   free( inout_sqlda );
 
   /* Drop the PRESIDENTS table created by the stored procedure */
   EXEC SQL DROP TABLE PRESIDENTS;
   CHECKERR("DROP TABLE");
 
   /* Disconnect from Remote Database */
   EXEC SQL CONNECT RESET;
   CHECKERR ("CONNECT RESET");
   return 0;
}
/* end of program : inpcli.sqc */

¿¹Á¦ ÀÔ·Â-SQLDA ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ÀÛ¾÷ ¹æ½Ä

  1. ¼­¹ö ÇÁ·Î½Ãµà¾î ¼±¾ð. ÇÁ·Î½Ãµà¾î´Â SQLDA ¹× SQLCA ±¸Á¶ÀÇ Æ÷ÀÎÅ͸¦ ¼ö¿ëÇÕ´Ï´Ù.
  2. Å×À̺í ÀÛ¼º. SQLDA ±¸Á¶ÀÇ Ã¹¹øÂ° SQLVAR¿¡ Àü´ÞµÈ µ¥ÀÌÅ͸¦ »ç¿ëÇÏ¿© CREATE TABLE¹®ÀÌ »ý¼ºµÇ°í Presidents Å×À̺íÀ» ÀÛ¼ºÇϵµ·Ï ½ÇÇàµË´Ï´Ù.
  3. »ðÀÔ ¸í·É¹® Áغñ. ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ ?°¡ Ç¥½ÃµÈ INSERT¹®ÀÌ ÁغñµË´Ï´Ù.
  4. µ¥ÀÌÅÍ »ðÀÔ. SQLDA ±¸Á¶ÀÇ µÎ ¹øÂ°¿¡¼­ ³× ¹øÂ° SQLVAR¿¡ Àü´ÞµÈ µ¥ÀÌÅ͸¦ »ç¿ëÇÏ¿© ÀÌÀü¿¡ ÁغñµÈ INSERT¹®À» ½ÇÇàÇÕ´Ï´Ù. ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â Washington, Jefferson ¹× Lincoln °ªÀ¸·Î ´ëüµË´Ï´Ù. ÀÌ·± °ªÀº Presidents Å×ÀÌºí¿¡ »ðÀԵ˴ϴÙ.
  5. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏ. ¼­¹ö ÇÁ·Î½Ãµà¾î¿¡¼­ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÇ SQLCA·Î SQLCA¸¦ º¹»çÇϰí, Æ®·£Àè¼ÇÀÌ ¼º°øÇÑ °æ¿ì COMMIT¹®À» ¹ßÇàÇϰí, ¼­¹ö ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ ´õ ÀÌ»óÀÇ È£ÃâÀÌ ¾øÀ½À» Áö½ÃÇÏ´Â SQLZ_DISCONNECT_PROC °ªÀ» ¸®ÅÏÇÕ´Ï´Ù.

ÁÖ:¼­¹ö ÇÁ·Î½Ãµà¾î´Â AIX ½Ã½ºÅÛ¿¡¼­ REXX·Î ÀÛ¼ºµÉ ¼ö ¾ø½À´Ï´Ù.

C ¿¹: V5SPSRV.SQC

#include <memory.h>
#include <string.h>
#include <sqlenv.h>
#include <sqlutil.h>
 
#ifdef __cplusplus
extern "C"
#endif
SQL_API_RC SQL_API_FN inpsrv(void *reserved1,      (1)
           void *reserved2,
           struct sqlda   *inout_sqlda,
           struct sqlca   *ca)
{
   /* Declare a local SQLCA */
   EXEC SQL INCLUDE SQLCA;
 
   /* Declare Host Variables */
   EXEC SQL BEGIN DECLARE SECTION;
     char table_stmt[80]  = "CREATE TABLE ";
     char insert_stmt[80] = "INSERT INTO ";
     char insert_data[21];
   EXEC SQL END DECLARE SECTION;
 
   /* Declare Miscellanous Variables */
   int  cntr = 0;
   char *table_name;
   char *data_items[3];
   short  data_items_length[3];
   int  num_of_data = 0;
 
   /*-----------------------------------------------------------------*/
   /* Assign the data from the SQLDA to local variables so that we    */
   /* don't have to refer to the SQLDA structure further.  This will  */
   /* provide better portability to other platforms such as DB2 MVS   */
   /* where they receive the parameter list differently.              */
   /*-----------------------------------------------------------------*/
 
 
   table_name  = inout_sqlda->sqlvar[0].sqldata;
   num_of_data = inout_sqlda->sqld - 1;
 
   for (cntr = 0; cntr < num_of_data; cntr++)
   {
      data_items[cntr] = inout_sqlda->sqlvar[cntr+1].sqldata;
      data_items_length[cntr] = inout_sqlda->sqlvar[cntr+1].sqllen;
   }
 
   /*-----------------------------------------------------------------*/
   /* Create President Table                                          */
   /* - For simplicity, we'll ignore any errors from the              */
   /*   CREATE TABLE so that you can run this program even when the   */
   /*   table already exists due to a previous run.                   */
   /*-----------------------------------------------------------------*/
 
   EXEC SQL WHENEVER SQLERROR CONTINUE;
   strcat(table_stmt, table_name);
   strcat(table_stmt, " (name CHAR(20))");  (2)
 
   EXEC SQL EXECUTE IMMEDIATE :table_stmt;
 
   EXEC SQL WHENEVER SQLERROR GOTO ext;
 
   /*-----------------------------------------------------------------*/
   /* Generate and execute a PREPARE for an INSERT statement, and     */
   /* then insert the three presidents.                               */
   /*-----------------------------------------------------------------*/
 
   strcat(insert_stmt, table_name );
   strcat(insert_stmt, "  VALUES (?)");  (3)
 
   EXEC SQL PREPARE S1 FROM :insert_stmt;
 
   for (cntr = 0; cntr < num_of_data; cntr++)
   {
      strncpy(insert_data, data_items[cntr], data_items_length[cntr]);
      insert_data[data_items_length[cntr]] = '\0';
      EXEC SQL EXECUTE S1 USING :insert_data;  (4)
   }
 
   /*-----------------------------------------------------------------*/
   /* Return to caller                                                */
   /*   -  Copy the SQLCA                                             */
   /*   -  Update the output SQLDA.  Since there's no output to       */
   /*      return, we are setting the indicator values to -128 to     */
   /*      return only a null value.                                  */
   /*-----------------------------------------------------------------*/
 
ext:  (5)
   memcpy(ca, &sqlca, sizeof(struct sqlca));
   if (inout_sqlda != NULL)
   {
     for (cntr = 0; cntr < inout_sqlda->sqld; cntr++)
     {
       *(inout_sqlda->sqlvar[cntr].sqlind) = -128;
     }
   }
 
   return(SQLZ_DISCONNECT_PROC);
}
 


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