ÀÌ Àý¿¡¼´Â »ç¿ëÀÚÀÇ µ¿Àû SQL ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ »ç¿ëÇÏ¿© È£½ºÆ® º¯¼ö Á¤º¸¸¦ Ç¥½ÃÇÏ´Â ¹æ¹ýÀ» º¸¿© ÁÝ´Ï´Ù. ´ÙÀ½ Ç׸ñµéÀÌ Æ÷ÇԵ˴ϴÙ.
È£½ºÆ® º¯¼ö Á¤º¸(µ¥ÀÌÅÍ À¯Çü ¹× ±æÀÌ)´Â ÀÀ¿ëÇÁ·Î±×·¥ »çÀü ó¸® ÄÄÆÄÀÏ Áß¿¡¸¸ »ç¿ë°¡´ÉÇϹǷΠµ¿Àû SQL¹®¿¡´Â È£½ºÆ® º¯¼ö°¡ Æ÷Ç﵃ ¼ö ¾ø½À´Ï´Ù. ½ÇÇà½Ã È£½ºÆ® º¯¼ö Á¤º¸´Â ¾ø½À´Ï´Ù. µû¶ó¼, ÀÀ¿ëÇÁ·Î±×·¥ º¯¼ö¸¦ Ç¥½ÃÇÏ·Á¸é »õ·Î¿î ¹æ¹ýÀÌ ÇÊ¿äÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¶ó°í ÇÏ´Â Àǹ®ºÎÈ£(?)¿¡ ÀÇÇØ Ç¥½ÃµË´Ï´Ù. ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡´Â SQL¹® ³»ºÎ¿¡ È£½ºÆ® º¯¼ö°¡ À§Ä¡ÇÏ´Â Àå¼Ò¸¦ ³ªÅ¸³À´Ï´Ù. ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â SQL¹® ³»ºÎ¿¡¼ÀÇ »ç¿ë ¹®¸Æ¿¡ Á¾¼ÓÀûÀÎ µ¥ÀÌÅÍ À¯Çü ¹× ±æÀ̸¦ ÃëÇÕ´Ï´Ù.
¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚÀÇ µ¥ÀÌÅÍ À¯ÇüÀÌ »ç¿ëµÇ´Â ¸í·É¹® ¹®¸Æ°ú ±¸ºÐÀÌ µÇÁö ¾Ê´Â °æ¿ì CAST¸¦ »ç¿ëÇÏ¿© À¯ÇüÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×·± ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ ÀÔ·ÂµÈ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ·Î ¿©°ÜÁý´Ï´Ù. ÀÔ·ÂµÈ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â Á¦°øµÈ À¯ÇüÀÇ È£½ºÆ® º¯¼öó·³ Ãë±ÞµË´Ï´Ù. ¿¹¸¦ µé¾î, SELECT ? FROM SYSCAT.TABLES¹®Àº DB2°¡ °á°ú Ä÷³ÀÇ À¯ÇüÀ» ¾ËÁö ¸øÇϹǷΠÀ¯È¿ÇÏÁö ¾Ê½À´Ï´Ù. ±×·¯³ª, À¯Çüº¯È¯ ÇÔ¼ö´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ INTEGER¸¦ Ç¥½ÃÇϵµ·Ï ¾à¼ÓÇϹǷΠSELECT CAST(? AS INTEGER) FROM SYSCAT.TABLES¹®ÀÌ À¯È¿ÇÕ´Ï´Ù. µû¶ó¼ DB2´Â °á°ú Ä÷³ÀÇ À¯ÇüÀ» ¾Ð´Ï´Ù.
¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ Æ÷ÇÔµÈ ¹®ÀÚ¿Àº ´ÙÀ½°ú °°½À´Ï´Ù.
DELETE FROM TEMPL WHERE EMPNO = ?
ÀÌ ¸í·É¹®ÀÌ ½ÇÇàµÉ ¶§ È£½ºÆ® º¯¼ö ¶Ç´Â SQLDA ±¸Á¶´Â EXECUTE¹®ÀÇ USINGÀý¿¡ ÀÇÇØ ÁöÁ¤µË´Ï´Ù. È£½ºÆ® º¯¼öÀÇ ³»¿ëÀº ¸í·É¹®ÀÌ ½ÇÇàµÉ ¶§ »ç¿ëµË´Ï´Ù.
SQL¹®¿¡ Çϳª ÀÌ»óÀÇ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ Æ÷Ç﵃ ¼ö ÀÖÀ¸¸é, EXECUTE¹®ÀÇ USINGÀýÀº (°¢ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ ´ëÇØ) È£½ºÆ® º¯¼ö ¸ñ·ÏÀ» ÁöÁ¤Çϰųª °¢ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ ´ëÇØ SQLVAR Ç׸ñÀ» °¡Áø SQLDA¸¦ ½Äº°ÇØ¾ß ÇÕ´Ï´Ù.(LOBÀÇ °æ¿ì ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´ç µÎ °³ÀÇ SQLVARÀÌ ÀÖ½À´Ï´Ù.) È£½ºÆ® º¯¼ö ¸ñ·Ï ¶Ç´Â SQLVAR Ç׸ñÀº ¸í·É¹®ÀÇ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ ¼ø¼¿¡ ÀÏÄ¡Çϸç, ȣȯ°¡´ÉÇÑ µ¥ÀÌÅÍ À¯ÇüÀÌ ÀÖ¾î¾ß ÇÕ´Ï´Ù.
µ¿Àû SQL¿¡¼ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ »ç¿ëÇÏ´Â °ÍÀº Á¤Àû SQL¿¡¼ È£½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ´Â °Í°ú °°½À´Ï´Ù. ¾î¶² °æ¿ìÀ̵ç, ÃÖÀûÈ ¾Ë°í¸®ÁòÀº ºÐ»ê Åë°è¸¦ »ç¿ëÇÏÁö ¾Ê°í ÃÖÀû ¾×¼¼½º Ç÷£À» ¼±ÅÃÇÏÁö ¾ÊÀ» ¼öµµ ÀÖ½À´Ï´Ù.
¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ Àû¿ëµÇ´Â ±ÔÄ¢Àº SQL ÂüÁ¶¼ÀÇ PREPARE¹® ¾Æ·¡¿¡ ³ª¿µË´Ï´Ù.
´ÙÀ½Àº °Ë»ö ¹× °»½Å Á¶°Ç¿¡¼ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ »ç¿ëÇÏ´Â UPDATE ¿¹ÀÔ´Ï´Ù. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:
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 pname[10]; short dept; char userid[9]; char passwd[19]; char st[255]; char parm_var[6]; EXEC SQL END DECLARE SECTION; printf( "Sample C program: VARINP \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: varinp [userid passwd]\n\n"); return 1; } /* endif */ strcpy (st, "SELECT name, dept FROM staff "); strcat (st, "WHERE job = ? FOR UPDATE OF job"); EXEC SQL PREPARE s1 FROM :st; (1) EMB_SQL_CHECK("PREPARE"); EXEC SQL DECLARE c1 CURSOR FOR s1; (2) strcpy (parm_var, "Mgr"); EXEC SQL OPEN c1 USING :parm_var; (3) EMB_SQL_CHECK("OPEN"); strcpy (parm_var, "Clerk"); strcpy (st, "UPDATE staff SET job = ? WHERE CURRENT OF c1"); EXEC SQL PREPARE s2 from :st; (4) do { EXEC SQL FETCH c1 INTO :pname, :dept; (5) if (SQLCODE != 0) break; printf( "%-10.10s in dept. %2d will be demoted to Clerk\n", pname, dept ); EXEC SQL EXECUTE s2 USING :parm_var; (6) EMB_SQL_CHECK("EXECUTE"); } while ( 1 ); EXEC SQL CLOSE c1; (7) EMB_SQL_CHECK("CLOSE CURSOR"); EXEC SQL ROLLBACK; EMB_SQL_CHECK("ROLLBACK"); printf( "\nOn second thought -- changes rolled back.\n" ); EXEC SQL CONNECT RESET; EMB_SQL_CHECK("CONNECT RESET"); return 0; } /* end of program : VARINP.SQC */
import java.sql.*; class Varinp { 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 Varinp 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 Varinp [username password]\n"); } // Enable transactions con.setAutoCommit(false); // Perform dynamic SQL using JDBC try { PreparedStatement pstmt1 = con.prepareStatement( "SELECT name, dept FROM staff WHERE job = ? FOR UPDATE OF job"); (1) // set cursor name for the positioned update statement pstmt1.setCursorName("c1"); (2) pstmt1.setString(1, "Mgr"); ResultSet rs = pstmt1.executeQuery(); (3) PreparedStatement pstmt2 = con.prepareStatement( "UPDATE staff SET job = ? WHERE CURRENT OF c1"); (4) pstmt2.setString(1, "Clerk"); System.out.print("\n"); while( rs.next() ) (5) { String name = rs.getString("name"); short dept = rs.getShort("dept"); System.out.println(name + " in dept. " + dept + " will be demoted to Clerk"); pstmt2.executeUpdate(); (6) }; rs.close(); pstmt1.close(); (7) pstmt2.close(); } 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. "varinp". Data Division. Working-Storage Section. copy "sqlca.cbl". EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 pname pic x(10). 01 dept pic s9(4) comp-5. 01 st pic x(127). 01 parm-var pic x(5). 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: VARINP". * 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. move "SELECT name, dept FROM staff - " WHERE job = ? FOR UPDATE OF job" to st. EXEC SQL PREPARE s1 FROM :st END-EXEC. (1) move "PREPARE" to errloc. call "checkerr" using SQLCA errloc. EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC. (2) move "Mgr" to parm-var. EXEC SQL OPEN c1 USING :parm-var END-EXEC (3) move "OPEN" to errloc. call "checkerr" using SQLCA errloc. move "Clerk" to parm-var. move "UPDATE staff SET job = ? WHERE CURRENT OF c1" to st. EXEC SQL PREPARE s2 from :st END-EXEC. (4) move "PREPARE S2" to errloc. call "checkerr" using SQLCA errloc. * call the FETCH and UPDATE loop. perform Fetch-Loop thru End-Fetch-Loop until SQLCODE not equal 0. EXEC SQL CLOSE c1 END-EXEC. (7) move "CLOSE" to errloc. call "checkerr" using SQLCA errloc. EXEC SQL ROLLBACK END-EXEC. move "ROLLBACK" to errloc. call "checkerr" using SQLCA errloc. DISPLAY "On second thought -- changes rolled back.". 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 :pname, :dept END-EXEC. (5) if SQLCODE not equal 0 go to End-Fetch-Loop. display pname, " in dept. ", dept, " will be demoted to Clerk". EXEC SQL EXECUTE s2 USING :parm-var END-EXEC. (6) move "EXECUTE" to errloc. call "checkerr" using SQLCA errloc. End-Fetch-Loop. exit. End-Prog. stop run.