Á¤Àû 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¹®¿¡ ±âÃÊÇÑ Ä¿¼ÀÇ Ã³¸®¸¦ Ç¥½ÃÇÕ´Ï´Ù. ÀÌ ÇÁ·Î±×·¥Àº À̸§ Ä÷³¿¡ STAFF °ªÀ» °¡Áø Å×À̺íÀ» Á¦¿ÜÇϰí, SYSCAT.TABLES¿¡ ¸ðµç Å×À̺íÀ» ³ª¿ÇÕ´Ï´Ù. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:
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; 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 */
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); } } }
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 */ 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¿¡´Â SQLVAR Ç׸ñ ¹ß»ýÀÇ º¯¼ö ¼ýÀÚ°¡ Æ÷ÇԵǾî ÀÖÀ¸¸ç, SQLVAR Ç׸ñ °¢°¢¿¡´Â ±×¸² 2¿¡ Ç¥½ÃµÈ ¹Ù¿Í °°ÀÌ ÇÑ ÇàÀÇ µ¥ÀÌÅÍ¿¡ ÇϳªÀÇ Ä÷³À» ¼³¸íÇÏ´Â ÇÊµå ¼¼Æ®°¡ µé¾î ÀÖ½À´Ï´Ù. ±âº» SQLVAR°ú 2Â÷ SQLVAR, µÎ °¡Áö À¯ÇüÀÇ SQLVAR Ç׸ñÀÌ ÀÖ½À´Ï´Ù. µÎ À¯Çü¿¡ ´ëÇÑ ¼³¸íÀº SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
±×¸² 2. SQL ¼³¸íÀÚ ¿µ¿ª(SQLDA)
![]() |
ÇÊ¿äÇÑ SQLVAR Ç׸ñÀÇ ¼ö´Â °á°ú Å×À̺íÀÇ Ä÷³ ¼ö¿¡ µû¶ó ´Þ¶óÁö¹Ç·Î, ÀÀ¿ëÇÁ·Î±×·¥Àº ÇÊ¿äÇÒ ¶§ ÀûÀýÇÑ ¼öÀÇ SQLVAR ¿ä¼Ò¸¦ ÇÒ´çÇØ¾ß ÇÕ´Ï´Ù. ¾Æ·¡¿¡ »ç¿ë°¡´ÉÇÑ µÎ °¡Áö ¸Þ¼Òµå¿¡ ´ëÇØ ¼³¸íµÇ¾î ÀÖ½À´Ï´Ù. ¾ð±ÞµÈ SQLDA Çʵ忡 ´ëÇØ¼´Â SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
À§ ¸Þ¼ÒµåÀÇ °æ¿ì, ÇÒ´çÇØ¾ß ÇÏ´Â Ãʱâ SQLVAR Ç׸ñ ¼ö¿¡ °üÇÑ ¹®Á¦°¡ ¹ß»ýÇÒ ¼ö ÀÖ½À´Ï´Ù. °¢ SQLVAR ¿ä¼Ò´Â 44¹ÙÀÌÆ®ÀÇ ÀúÀ念¿ªÀ» »ç¿ëÇÕ´Ï´Ù.(ÀÌ ÀúÀ念¿ªÀº SQLDATA ¹× SQLIND Çʵ忡 ´ëÇØ ÇÒ´çµÈ °ÍÀ» °è»êµÇÁö ¾Ê½À´Ï´Ù.) ¸Þ¸ð¸®°¡ ÃæºÐÇÒ °æ¿ì, ÃÖ´ë Å©±âÀÇ SQLDA¸¦ Á¦°øÇϴ ù¹øÂ° ¹æ¹ýÀ» ±¸ÇöÇϱⰡ ´õ ½±½À´Ï´Ù.
´õ ÀÛÀº SQLDA¸¦ ÇÒ´çÇÏ´Â µÎ ¹øÂ° ¸Þ¼Òµå´Â µ¿Àû ¸Þ¸ð¸® ÇÒ´çÀ» Áö¿øÇÏ´Â C ¹× C++¿Í °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡¸¸ Àû¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. µ¿Àû ¸Þ¸ð¸® ÇÒ´çÀ» Áö¿øÇÏÁö ¾Ê´Â COBOL ¹× FORTRAN°ú °°Àº ¾ð¾îÀÇ °æ¿ì, ù¹øÂ° ¸Þ¼Òµå¸¦ »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥ÀÌ 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Àº ´ÙÀ½°ú °°Àº °æ¿ì ¼³Á¤µË´Ï´Ù.
ù¹øÂ° SQLD SQLVAR Ç׸ñÀÌ ¼³Á¤µÇ¾ú°í SQLDOUBLED´Â °ø¹éÀ¸·Î ¼³Á¤µË´Ï´Ù.
2* SQLD SQLVAR Ç׸ñÀÌ ¼³Á¤µÇ°í, SQLDOUBLED°¡ 2·Î ¼³Á¤µË´Ï´Ù.
ù¹øÂ° SQLD SQLVAR Ç׸ñÀÌ ¼³Á¤µÇ¾ú°í SQLDOUBLED´Â °ø¹éÀ¸·Î ¼³Á¤µË´Ï´Ù. SQLWARN ¹ÙÀÎµå ¿É¼ÇÀÌ YESÀÎ °æ¿ì °æ°í SQLCODE +237 (SQLSTATE 01594)ÀÌ ¹ßÇàµË´Ï´Ù.
´ÙÀ½°ú °°Àº °æ¿ì SQLDAÀÇ SQLVARÀÌ ¼³Á¤µÇÁö ¾Ê½À´Ï´Ù(Ãß°¡ °ø°£ ¹× ¶Ç´Ù¸¥ DESCRIBEÀÇ ÇÒ´çÀÌ ÇÊ¿ä).
SQLVAR Ç׸ñÀÌ ¼³Á¤µÇÁö ¾Ê°í SQLDOUBLED°¡ °ø¹éÀ¸·Î ¼³Á¤µË´Ï´Ù. SQLWARN ¹ÙÀÎµå ¿É¼ÇÀÌ YESÀÎ °æ¿ì, °æ°í SQLCODE +236(SQLSTATE 01005)ÀÌ ½ÇÇàµË´Ï´Ù.
¼º°øÀûÀÎ DESCRIBE¸¦ À§ÇØ SQLD SQLVARÀ» ÇÒ´çÇϽʽÿÀ.
SQLVAR Ç׸ñÀÌ ¼³Á¤µÇÁö ¾Ê°í SQLDOUBLED°¡ °ø¹éÀ¸·Î ¼³Á¤µË´Ï´Ù. SQLWARN ¹ÙÀÎµå ¿É¼ÇÀÌ YESÀ̰í, °æ°í SQLCODE +239(SQLSTATE 01005)°¡ ½ÇÇàµË´Ï´Ù.
±¸º° À¯ÇüÀÇ À̸§À» Æ÷ÇÔÇÏ¿©, ¼º°øÀûÀÎ DESCRIBE¸¦ À§ÇØ 2*SQLD SQLVARÀ» ÇÒ´çÇÕ´Ï´Ù.
SQLVAR Ç׸ñÀÌ ¼³Á¤µÇÁö ¾Ê°í SQLDOUBLED°¡ °ø¹éÀ¸·Î ¼³Á¤µË´Ï´Ù. (SQLWARN ¹ÙÀÎµå ¿É¼Ç ¼³Á¤¿¡ °ü°è¾øÀÌ) °æ°í SQLCODE +238(SQLSTATE 01005)ÀÌ ½ÇÇàµË´Ï´Ù.
¼º°øÀûÀÎ DESCRIBE¸¦ À§ÇØ 2*SQLD SQLVARÀ» ÇÒ´çÇÕ´Ï´Ù.
BIND ¸í·ÉÀÇ SQLWARN ¿É¼ÇÀ» »ç¿ëÇÏ¿© DESCRIBE(¶Ç´Â PREPARE...INTO)°¡ ´ÙÀ½ °æ°í¸¦ ¸®ÅÏÇÒ °ÍÀÎÁö ÅëÁ¦ÇÕ´Ï´Ù.
»ç¿ëÀÚÀÇ ÀÀ¿ëÇÁ·Î±×·¥ Äڵ尡 Ç×»ó ÀÌ·¯ÇÑ SQLCODE¸¦ ¸®ÅÏÇÒ ¼ö ÀÖµµ·Ï ÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. ¼±Åà ¸ñ·Ï¿¡ LOB Ä÷³ÀÌ ÀÖ°í SQLDA¿¡ ÃæºÐÄ¡ ¸øÇÑ SQLVARÀÌ ÀÖÀ» ¶§ Ç×»ó °æ°í SQLCODE +238(SQLSTATE 01005)ÀÌ ¸®Åϵ˴ϴÙ. ÀÌ´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ °á°ú ¼¼Æ®ÀÇ LOB Ä÷³À¸·Î ÀÎÇØ SQLVAR ¼ö°¡ 2¹è·Î µÇ¾î¾ß ÇÔÀ» ¾Ë ¼ö ÀÖ´Â À¯ÀÏÇÑ ¹æ¹ýÀÔ´Ï´Ù.
°á°ú Å×À̺íÀÇ Ä÷³ ¼ö°¡ °áÁ¤µÇ¸é µÎ ¹øÂ°ÀÇ Àüü Å©±â 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 ¸ÅÅ©·Î¸¦ »ç¿ëÇÏ¿© »ç¿ëÀÚ ÀÚ½ÅÀÇ °è»êÀ» ÇÇÇϰí, ¹öÀüº° Á¾¼Ó¼ºÀ» ÇÇÇÒ ¼ö ÀÖ½À´Ï´Ù.
fulsqlda ¹× ÀÀ¿ëÇÁ·Î±×·¥¿¡ ´ëÇØ ÃæºÐÇÑ °ø°£À» ÇÒ´çÇÒ ¶§ ´ÙÀ½ ´Ü°è¸¦ µû¶ó¾ß ÇÕ´Ï´Ù.
¸í·É¹®À» µÎ ¹ø ÁغñÇÏ´Â ºñ¿ëÀÌ »ï°¡µÇ¹Ç·Î DESCRIBE¹®À» »ç¿ëÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. DESCRIBE¹®Àº »õ·Î¿î SQLDA ±¸Á¶¿¡ µé¾î°¥ Áغñ Á¶ÀÛÀ» ÇÏ´Â µ¿¾È ÀÌÀü¿¡ ¾òÀº Á¤º¸¸¦ Àç»ç¿ëÇÕ´Ï´Ù. ´ÙÀ½ ¸í·É¹®ÀÌ ¹ßÇàµÉ ¼ö ÀÖ½À´Ï´Ù.
EXEC SQL DESCRIBE STMT INTO :fulsqlda
ÀÌ ¸í·É¹®ÀÌ ½ÇÇàµÈ ÈÄ °¢ SQLVAR ¿ä¼Ò¿¡´Â °á°ú Å×À̺íÀÇ ÇÑ Ä÷³¿¡ ´ëÇÑ ¼³¸íÀÌ µé¾î ÀÖ½À´Ï´Ù.
SQLDA ±¸Á¶¸¦ »ç¿ëÇÑ °á°ú Å×À̺í ÇàÀ» ÆäÄ¡Çϱâ Àü¿¡ ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½À» ¼öÇàÇØ¾ß ÇÕ´Ï´Ù.
´ëÇü ¿ÀºêÁ§Æ®(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¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº ¿ÀºêÁ§Æ® °ü°èÇü ±â´É »ç¿ëÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ÀÌ ´Ü°è´Â °¢ Ä÷³ÀÇ ¼³¸íÀ» ºÐ¼®ÇÏ°í °¢ SQLDATA ÇʵåÀÇ ³»¿ëÀ» ÇØ´ç Ä÷³ÀÇ °ªÀ» º¸À¯Çϱ⿡ ÃæºÐÇÑ Å©±âÀÇ ÀúÀ念¿ªÀÇ ÁÖ¼Ò·Î ´ëüÇÏ¿© ÀÌ·ç¾îÁý´Ï´Ù. ±æÀÌ ¼Ó¼ºÀº LOB À¯ÇüÀÌ ¾Æ´Ñ µ¥ÀÌÅÍ Ç׸ñ¿¡ ´ëÇÑ °¢ SQLVARÀÇ SQLLEN Çʵ忡¼ °áÁ¤µË´Ï´Ù. À¯ÇüÀÌ BLOB, CLOB ¶Ç´Â DBCLOBÀÎ Ç׸ñÀÇ °æ¿ì, ±æÀÌ ¼Ó¼ºÀº 2Â÷ SQLVAR Ç׸ñÀÇ SQLLONGLEN Çʵå·ÎºÎÅÍ °áÁ¤µË´Ï´Ù.
¶ÇÇÑ, ÁöÁ¤µÈ Ä÷³ÀÌ ³Î(NULL)À» Çã¿ëÇÒ °æ¿ì, ÀÀ¿ëÇÁ·Î±×·¥Àº SQLIND ÇÊµå ³»¿ëÀ» Ä÷³¿¡ ´ëÇÑ Ç¥½Ã±â º¯¼ö ÁÖ¼Ò·Î ´ëÃ¼ÇØ¾ß ÇÕ´Ï´Ù.
SQLDA ±¸Á¶°¡ Á¦´ë·Î ÇÒ´çµÇ¸é SELECT¹®°ú ¿¬°üµÈ Ä¿¼¸¦ ¿ ¼ö ÀÖ°í, FETCH¹®ÀÇ USING DESCRIPTORÀýÀ» ÁöÁ¤ÇÏ¿© ÇàÀ» ÆäÄ¡ÇÒ ¼ö ÀÖ½À´Ï´Ù.
¿Ï·á°¡ µÇ¸é Ä¿¼¸¦ ´Ý°í µ¿Àû ÇÒ´çµÈ ¸Þ¸ð¸®¸¦ ¸±¸®½ºÇØ¾ß ÇÕ´Ï´Ù.
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¸¦ »ç¿ëÇÑ µ¥ÀÌÅÍ Àü´ÞÇϸé È£½ºÆ® º¯¼ö ¸ñ·ÏÀ» »ç¿ëÇÒ ¶§º¸´Ù À¶Å뼺ÀÌ
Áõ°¡µË´Ï´Ù. ¿¹¸¦ µé¾î, C ¾ð¾î·Î µÈ DECIMAL µ¥ÀÌÅÍ¿Í °°Àº º»·¡ÀÇ È£½ºÆ®
¾ð¾î°¡ ¾ø´Â µ¥ÀÌÅ͸¦ Àü¼ÛÇϱâ À§ÇØ SQLDA¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ADHOC¶ó´Â
»ùÇà ÇÁ·Î±×·¥Àº ÀÌ ±â¼úÀ» »ç¿ëÇÏ´Â ¿¹ÀÔ´Ï´Ù(¿¹: ADHOC ÇÁ·Î±×·¥ ÂüÁ¶).
¼öÄ¡ °ª ¹× ±âÈ£ À̸§ÀÌ ¾î¶»°Ô ¿¬°üµÇ¾î ÀÖ´ÂÁö Ç¥½ÃÇÏ´Â Æí¸®ÇÑ »óÈ£ÂüÁ¶
¸ñ·ÏÀº Ç¥ 8ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
¼ýÀÚ °ª°ú ÀÌ¿¡ »óÀÀÇÏ´Â ±âÈ£ À̸§ | ||||
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 | ||
|
µ¿Àû 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¹®À» ó¸®Çϱâ À§ÇØ ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½À» ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÀÌ ´Ü°è¿¡ ´ëÇØ¼´Â ´ÙÀ½ Àý¿¡¼ »ó¼¼È÷ ¼³¸íÇÕ´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥À» ÀÌ¿ëÇÏ¿© ÀÓÀÇÀÇ SQL¹®À» ÀúÀåÇÒ ¼ö ÀÖ´Â °æ¿ì, µ¥ÀÌÅÍ À¯ÇüÀÌ VARCHAR, LONG VARCHAR, CLOB, VARGRAPHIC, LONG VARGRAPHIC ¶Ç´Â DBCLOBÀÎ Ä÷³À» °¡Áø Å×ÀÌºí¿¡ ÀúÀåÇÒ ¼ö ÀÖ½À´Ï´Ù. VARGRAPHIC, LONG VARGRAPHIC ¹× DBCLOB µ¥ÀÌÅÍ À¯ÇüÀº DBCS(2¹ÙÀÌÆ® ¹®ÀÚ Áö¿ø) ¹× EUC(È®Àå UNIX ÄÚµå) ȯ°æ¿¡¼¸¸ »ç¿ë°¡´ÉÇÕ´Ï´Ù.
ÁغñµÈ ¹öÀüÀÌ ¾Æ´Ñ ¼Ò½º SQL¹®À» ÀúÀåÇØ¾ß ÇÕ´Ï´Ù. ÀÌ´Â Å×ÀÌºí¿¡ ÀúÀåµÈ ¹öÀüÀ» ½ÇÇàÇϱâ Àü¿¡ °¢ ¸í·É¹®À» °Ë»öÇÑ ÈÄ ÁغñÇØ¾ß ÇÑ´Ù´Â °ÍÀ» ÀǹÌÇÕ´Ï´Ù. ±Ùº»ÀûÀ¸·Î »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥Àº ¹®ÀÚ¿·ÎºÎÅÍ SQL¹®À» ÁغñÇϰí ÀÌ ¸í·É¹®À» µ¿ÀûÀ¸·Î ½ÇÇàÇÕ´Ï´Ù.
ÀÌ »ùÇà ÇÁ·Î±×·¥Àº SQLDA¸¦ »ç¿ëÇÏ¿© ´ëÈ½Ä SQL¹®À» ó¸®ÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
ÁÖ: | ¿¹ adhoc.sqc ´Â C ¾ð¾î¿¡¸¸ ÀÖ½À´Ï´Ù. |
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 ÆÄÀÏÀÇ À¯Æ¿¸®Æ¼·Î¼ Á¦°øµÇ´Â Ãß°¡ ÇÁ·Î½Ãµà¾î¸¦ »ç¿ëÇÕ´Ï´Ù. ¿©±â¿¡´Â ´ÙÀ½ÀÌ Æ÷ÇԵ˴ϴÙ.
#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 */