Çϳª ÀÌ»óÀÇ °á°ú ¼¼Æ®¸¦ DB2 CLI, ODBC, JDBC ¶Ç´Â SQLJ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏÇϱâ À§ÇØ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÄÚµåÈÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ·¯ÇÑ Áö¿øÀÇ ¾ç»óÀº ´ÙÀ½°ú °°½À´Ï´Ù.
°á°ú ¼¼Æ® 󸮿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº,
ÀÌ »ùÇà ÀúÀå ÇÁ·Î½Ãµà¾î´Â ´ÙÀ½°ú °°Àº Áö¿øµÇ´Â ¾ð¾î·Î Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
ÀÌ »ùÇà ÀúÀå ÇÁ·Î½Ãµà¾î´Â IN ¸Å°³º¯¼ö¸¦ Çϳª ½ÂÀÎÇϰí OUT ¸Å°³º¯¼ö Çϳª¿Í °á°ú ¼¼Æ® Çϳª¸¦ ¸®ÅÏÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î´Â IN ¸Å°³º¯¼ö¸¦ »ç¿ëÇÏ¿© SALARY°¡ IN ¸Å°³º¯¼öº¸´Ù Å« ÇàÀÇ STAFF Å×ÀÌºí¿¡ ´ëÇÑ NAME, JOB ±×¸®°í SALARY Ä÷³ °ªÀÌ µé¾î ÀÖ´Â °á°ú ¼¼Æ®¸¦ ÀÛ¼ºÇÕ´Ï´Ù.
CREATE PROCEDURE RESULT_SET_CLIENT (IN salValue DOUBLE, OUT sqlCode INTEGER) DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL NO DBINFO FENCED READS SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'spserver!one_result_set_to_client'
SQL_API_RC SQL_API_FN one_result_set_to_client (double *insalary, sqlint32 *out_sqlerror) { EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO return_error; l_insalary = *insalary; *out_sqlerror = 0; EXEC SQL DECLARE c3 CURSOR FOR (2) SELECT name, job, CAST(salary AS INTEGER) FROM staff WHERE salary > :l_insalary ORDER BY salary; EXEC SQL OPEN c3; (2) /* Leave cursor open to return result set */ return (0); (3) /* Copy SQLCODE to OUT parameter if SQL error occurs */ return_error: { *out_sqlerror = SQLCODE; EXEC SQL WHENEVER SQLERROR CONTINUE; return (0); } } /* end one_result_set_to_client function */
public static void resultSetToClient (double inSalaryThreshold, // double input int[] errorCode, // SQLCODE output ResultSet[] rs) // ResultSet output (4) throws SQLException { errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs try { // Get caller's connection to the database Connection con = DriverManager.getConnection("jdbc:default:connection"); // get salary result set using a parameter marker String query = "SELECT name, job, CAST(salary AS DOUBLE) " + "FROM staff " + "WHERE salary > ? " + "ORDER BY salary"; // prepare the SQL statement PreparedStatement stmt = con.prepareStatement(query); // set the value of the parameter marker (?) stmt.setDouble(1, inSalaryThreshold); // get the result set that will be returned to the client rs[0] = stmt.executeQuery(); (2) // to return a result set to the client, do not close ResultSet con.close(); (3) } catch (SQLException sqle) { errorCode[0] = sqle.getErrorCode(); } }
ÀÌ »ùÇà Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½°ú °°ÀÌ Áö¿øµÇ´Â ¾ð¾î·Î ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ ½ÂÀÎÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
ÀÌ »ùÇà Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº RESULT_SET_CLIENT ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÏ°í °á°ú ¼¼Æ®¸¦ Çϳª ½ÂÀÎÇÕ´Ï´Ù. ±×·¯¸é Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº °á°ú ¼¼Æ®ÀÇ ³»¿ëÀ» Ç¥½ÃÇÕ´Ï´Ù.
#include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlcli1.h> #include <sqlca.h> #include "utilcli.h" /* Header file for CLI sample code */ SQLCHAR stmt[50]; SQLINTEGER out_sqlcode; char out_buffer[33]; SQLINTEGER indicator; struct sqlca sqlca; SQLRETURN rc,rc1 ; char procname[254]; SQLHANDLE henv; /* environment handle */ SQLHANDLE hdbc; /* connection handle */ SQLHANDLE hstmt1; /* statement handle */ SQLHANDLE hstmt2; /* statement handle */ SQLRETURN sqlrc = SQL_SUCCESS; double out_median; int oneresultset1(SQLHANDLE); int main(int argc, char *argv[]) { SQLHANDLE hstmt; /* statement handle */ SQLHANDLE hstmt_oneresult; /* statement handle */ char dbAlias[SQL_MAX_DSN_LENGTH + 1] ; char user[MAX_UID_LENGTH + 1] ; char pswd[MAX_PWD_LENGTH + 1] ; /* Declare variables for passing data to INOUT_PARAM */ double inout_median; /* checks the command line arguments */ rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd ); if ( rc != 0 ) return( 1 ) ; /* allocate an environment handle */ printf("\n Allocate an environment handle.\n"); sqlrc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ; if ( sqlrc != SQL_SUCCESS ) { printf( "\n--ERROR while allocating the environment handle.\n" ) ; printf( " sqlrc = %d\n", sqlrc); printf( " line = %d\n", __LINE__); printf( " file = %s\n", __FILE__); return( 1 ) ; } /* allocate a database connection handle */ printf(" Allocate a database connection handle.\n"); sqlrc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc ) ; HANDLE_CHECK( SQL_HANDLE_ENV, henv, sqlrc, &henv, &hdbc ) ; /* connect to the database */ printf( " Connecting to the database %s ...\n", dbAlias ) ; sqlrc = SQLConnect( hdbc, (SQLCHAR *)dbAlias, SQL_NTS, (SQLCHAR *)user, SQL_NTS, (SQLCHAR *)pswd, SQL_NTS ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ; printf( " Connected to the database %s.\n", dbAlias ) ; /* set AUTOCOMMIT off */ sqlrc = SQLSetConnectAttr( hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_NTS) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ; /* allocate one or more statement handles */ printf(" Allocate a statement handle.\n"); sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ; sqlrc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt_oneresult ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, sqlrc, &henv, &hdbc ) ; /********************************************************\ * Call oneresultsettocaller stored procedure * \********************************************************/ rc = oneresultset1(hstmt_oneresult); rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt_oneresult ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ; /* ROLLBACK, free resources, and exit */ rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ; printf("\nStored procedure rolled back.\n\n"); /* Disconnect from Remote Database */ rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ; printf( "\n>Disconnecting .....\n" ) ; rc = SQLDisconnect( hdbc ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ; rc = SQLFreeHandle( SQL_HANDLE_DBC, hdbc ) ; HANDLE_CHECK( SQL_HANDLE_DBC, hdbc, rc, &henv, &hdbc ) ; rc = SQLFreeHandle( SQL_HANDLE_ENV, henv ) ; if ( rc != SQL_SUCCESS ) return( SQL_ERROR ) ; return( SQL_SUCCESS ) ; } int oneresultset1(hstmt) SQLHANDLE hstmt; /* statement handle */ { /********************************************************\ * Call one_result_set_to_client stored procedure * \********************************************************/ double insalary = 20000; SQLINTEGER salary_int; SQLSMALLINT num_cols; char name[40]; char job[10]; strcpy(procname, "RESULT_SET_CALLER"); (1) printf("\nCALL stored procedure: %s\n", procname); strcpy((char*)stmt,"CALL RESULT_SET_CALLER ( ?,? )"); rc = SQLPrepare(hstmt, stmt, SQL_NTS); STMT_HANDLE_CHECK( hstmt, rc); /* Bind the parameter to application variables () */ rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_DOUBLE, SQL_DOUBLE,0, 0, &insalary, 0, NULL); rc = SQLBindParameter(hstmt, 2, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER,0, 0, &out_sqlcode, 0, NULL); STMT_HANDLE_CHECK( hstmt, rc); rc = SQLExecute(hstmt); rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca); STMT_HANDLE_CHECK( hstmt, rc); rc = SQLNumResultCols( hstmt, &num_cols ) ; STMT_HANDLE_CHECK( hstmt, rc); printf("Result set returned %d columns\n", num_cols); /* bind columns to variables */ rc = SQLBindCol( hstmt, 1, SQL_C_CHAR, name, 40, &indicator); STMT_HANDLE_CHECK( hstmt, rc); rc = SQLBindCol( hstmt, 2, SQL_C_CHAR, job, 10, &indicator); STMT_HANDLE_CHECK( hstmt, rc); rc = SQLBindCol( hstmt, 3, SQL_C_LONG, &salary_int, 0, &indicator); STMT_HANDLE_CHECK( hstmt, rc); /* fetch result set returned from stored procedure */ rc = SQLFetch( hstmt ); (2) rc1 = SQLGetSQLCA(henv, hdbc, hstmt, &sqlca); STMT_HANDLE_CHECK( hstmt, rc); printf("\n--------Name---------, --JOB--, ---Salary--\n"); while (rc == SQL_SUCCESS && rc != SQL_NO_DATA_FOUND ) (3) { printf("%20s,%10s, %d\n",name,job,salary_int); rc = SQLFetch( hstmt ); } STMT_HANDLE_CHECK( hstmt, rc); /* Check that the stored procedure executed successfully */ if (rc == SQL_SUCCESS) { printf("Stored procedure returned successfully.\n"); } else { printf("Stored procedure returned SQLCODE %d\n", out_sqlcode); } rc = SQLCloseCursor(hstmt); return(rc); }
// prepare the CALL statement for RESULT_SET_CLIENT procName = "RESULT_SET_CLIENT"; sql = "CALL " + procName + "(?, ?)"; (1) callStmt = con.prepareCall(sql); // set input parameter to median value passed back by OUT_PARAM callStmt.setDouble (1, outMedian); // register the output parameter callStmt.registerOutParameter (2, Types.INTEGER); // call the stored procedure System.out.println ("\nCall stored procedure named " + procName); callStmt.execute(); // retrieve output parameter outErrorCode = callStmt.getInt(2); if (outErrorCode == 0) { System.out.println(procName + " completed successfully"); ResultSet rs = callStmt.getResultSet(); (2) while (rs.next()) { fetchAll(rs); (3) } // close ResultSet rs.close(); } else { // stored procedure failed System.out.println(procName + " failed with SQLCODE " + outErrorCode); }
ÀúÀå ÇÁ·Î½Ãµà¾î ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Á¦´ë·Î ½ÇÇàµÇÁö ¸øÇÏ¸é ´ÙÀ½ »çÇ×À» È®ÀÎÇϽʽÿÀ.
¿¹¸¦ µé¾î, OS/2 ȯ°æ¿¡¼ FENCED ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ µ¿Àû ¸µÅ© ¶óÀ̺귯¸®´Â µ¥ÀÌÅͺ£À̽º ¼¹öÀÇ instance_name\function µð·ºÅ丮¿¡ ÀÖ½À´Ï´Ù.
¿¹¸¦ µé¾î, µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº DB2DARI ÇÔ¼ö·Î¼ Windows 32ºñÆ® ¿î¿µ üÁ¦ ¶óÀ̺귯¸® myfunc.dll¿¡ Æ÷ÇÔµÈ myfunc ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ½ÇÇàÇϸç, ÀÌ ¶§ ¿¬°üµÈ CREATE PROCEDURE¹®¿¡ ÁöÁ¤µÈ °ªÀº ¹«½ÃµË´Ï´Ù.
ÁÖ: | Java ÀúÀå ÇÁ·Î½Ãµà¾î µð¹ö±ë¿¡ ´ëÇØ¼´Â Java¿¡¼ ÀúÀå ÇÁ·Î½Ãµà¾î µð¹ö±ëÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. |
´Ù¸¥ ÀÀ¿ëÇÁ·Î±×·¥¿¡¼Ã³·³, ÄÄÆÄÀÏ·¯¿Í ÇÔ²² Á¦°øµÈ µð¹ö°Å¸¦ »ç¿ëÇÏ¿© Áö¿ª FENCED ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µð¹ö±×ÇÒ ¼ö ÀÖ½À´Ï´Ù. Á¦°øµÈ µð¹ö°Å »ç¿ë¿¡ ´ëÇØ¼´Â ÄÄÆÄÀÏ·¯ ¹®¼¸¦ ÂüÁ¶ÇϽʽÿÀ.
¿¹¸¦ µé¾î, Windows NT»ó¿¡¼ Visual Studio(TM)¿Í ÇÔ²² Á¦°øµÈ µð¹ö°Å¸¦ »ç¿ëÇÏ·Á¸é ´ÙÀ½ ´Ü°è¸¦ ¼öÇàÇϽʽÿÀ.
´Ü°è 1. | DB2_STPROC_ALLOW_LOCAL_FENCED ·¹Áö½ºÆ®¸® º¯¼ö¸¦ true·Î ¼³Á¤ÇÕ´Ï´Ù. |
´Ü°è 2. | -Zi ¹× -Od Ç÷¡±×¸¦ ÀÌ¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î DLL¿¡ ´ëÇÑ ¼Ò½º ÆÄÀÏÀ» ÄÄÆÄÀÏÇÑ ÈÄ, -DEBUG ¿É¼ÇÀ» »ç¿ëÇÏ¿© DLLÀ» ¸µÅ©ÇϽʽÿÀ. |
´Ü°è 3. | °á°ú DLLÀ» ¼¹öÀÇ instance_name \function µð·ºÅ丮·Î º¹»çÇϽʽÿÀ. |
´Ü°è 4. | Visual Studio µð¹ö°Å¸¦ ÀÌ¿ëÇÏ¿© ¼¹ö»óÀÇ Å¬¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À» È£ÃâÇϽʽÿÀ. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ outcli.exeÀÇ °æ¿ì, ´ÙÀ½ ¸í·ÉÀ» ÀÔ·ÂÇϽʽÿÀ. msdev spclient.exe |
´Ü°è 5. | Visual Studio µð¹ö°Å âÀÌ ¿¸®¸é ÇÁ·ÎÁ§Æ® --> ¼³Á¤°ªÀ» ¼±ÅÃÇϽʽÿÀ. |
´Ü°è 6. | µð¹ö±× ÅÇÀ» Ŭ¸¯ÇϽʽÿÀ. |
´Ü°è 7. | Ä«Å×°í¸® È»ìÇ¥¸¦ Ŭ¸¯Çϰí Ãß°¡ DLLÀ» ¼±ÅÃÇϽʽÿÀ. |
´Ü°è 8. | »õ·Î ÀÛ¼º ¹öưÀ» Ŭ¸¯ÇÏ¿© »õ·Î¿î ¸ðµâÀ» ÀÛ¼ºÇϽʽÿÀ. |
´Ü°è 9. | ¿¶÷ ¹öưÀ» Ŭ¸¯ÇÏ¸é ¿¶÷ âÀÌ ¿¸³´Ï´Ù. |
´Ü°è 10. | spserver.dll ¸ðµâÀ» ¼±ÅÃÇϰí È®ÀÎÀ» Ŭ¸¯ÇÏ¸é ¼³Á¤°ª âÀÌ ¿¸³´Ï´Ù. |
´Ü°è 11. | ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ ¼Ò½º ÆÄÀÏÀ» ¿°í ÁßÁöÁ¡À» ¼³Á¤ÇϽʽÿÀ. |
´Ü°è 12. | °¡±â ¹öưÀ» Ŭ¸¯ÇϽʽÿÀ. Visual Studio µð¹ö°Å´Â ÀúÀå ÇÁ·Î½Ãµà¾î°¡ È£ÃâµÉ ¶§ ÁßÁöµË´Ï´Ù. |
´Ü°è 13. | ÀÌ ÁöÁ¡¿¡¼ »ç¿ëÀÚ´Â Visual Studio µð¹ö°Å¸¦ »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µð¹ö±×ÇÒ ¼ö ÀÖ½À´Ï´Ù.
|
Visual Studio µð¹ö°Å »ç¿ë¿¡ ´ëÇØ¼´Â Visual Studio Á¦Ç° ¹®¼¸¦ ÂüÁ¶ÇϽʽÿÀ.