´ÙÀ½ ¿¹Á¦´Â ÀúÀå ÇÁ·Î½Ãµà¾î, ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ º¹»ç ÀÛ¾÷ ³»¿ª, ±×¸®°í ÀúÀå ÇÁ·Î½Ãµà¾î »ç¿ëÀ» À§ÇÑ DB2 Everyplace ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇÕ´Ï´Ù. ¿¹Á¦ ÀÀ¿ëÇÁ·Î±×·¥ÀÇ ¸ñÀûÀº ¸ðºô »ç¿ëÀÚ°¡ °èÁ¤ ¹ë·±½º¸¦ È®ÀÎÇϰí DB2 Everyplace ¿ø°Ý ÀúÀå ÇÁ·Î½Ãµà¾î È£ÃâÀ» »ç¿ëÇÏ¿© Àú±Ý°ú ´çÁ ¿¹±Ý °£¿¡ ¿¹±ÝÀ» Àü¼ÛÇϵµ·Ï ÇÏ´Â °ÍÀÔ´Ï´Ù. DB2 Universal Database¿¡¼ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ´Â ¹æ¹ý¿¡ ´ëÇÑ Á¤º¸´Â DB2 Universal Database ÀÀ¿ëÇÁ·Î±×·¥ °³¹ß ¾È³»¼¸¦ ÂüÁ¶ÇϽʽÿÀ.
µ¥ÀÌÅÍ ¿øº» ÀÛ¼º
ÀÌ ¿¹Á¦´Â MYSAMPLE¶ó ºÒ·ÁÁö´Â DB2 Database¸¦ »ç¿ëÇÕ´Ï´Ù. MYSAMPLE µ¥ÀÌÅͺ£À̽º¸¦ ¼öµ¿À¸·Î ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. MYSAMPLE µ¥ÀÌÅͺ£À̽º¸¦ ÀÛ¼ºÇÏ·Á¸é DB2 ¸í·É ÇÁ·ÒÇÁÆ®¿¡ ´ÙÀ½ ¸í·É¹®À» ÀÔ·ÂÇϽʽÿÀ.
CREATE table db2e.MYACCOUNT ( Name char(16), Saving int, Checking int) INSERT into db2e.MYACCOUNT values('Michael', 5000, 5000) INSERT into db2e.MYACCOUNT values('Frank', 5000, 5000)
µ¥ÀÌÅͺ£À̽º¸¦ ÀÛ¼ºÇÑ ÈÄ, ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ¿© µ¥ÀÌÅͺ£À̽ºÀÇ µ¥ÀÌÅ͸¦ ¼öÁ¤ÇϽʽÿÀ.
ÀúÀå ÇÁ·Î½Ãµà¾î ÀÛ¼º
ÀÌ ¿¹Á¦´Â MYPROC()¶ó ºÒ·ÁÁö´Â ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ »ç¿ëÇÕ´Ï´Ù. ÀÌ ÇÁ·Î½Ãµà¾î´Â Account Name, Option, Transfer Amount, Saving Balance, Checking Balance¿Í °°Àº 5°³ÀÇ ¸Å°³º¯¼ö¸¦ °¡Áý´Ï´Ù. ´ÙÀ½ ¸ñ·ÏÀº °¢ ¸Å°³º¯¼öÀÇ ¸ñÀûÀ» ½Äº°ÇÕ´Ï´Ù.
Account Name: Input Parameter to identify the account. Option: Input Parameter to determine what to do. There are three options: 1: Check balance. 2: Transfer from saving to checking. 3: Transfer from checking to saving. Transfer Amount: Input Parameter of the amount to transfer between checking and saving. Saving Balance: Output Parameter returning the balance of saving account Checking Balance: Output Parameter returning the balance of checking account
´ÙÀ½ ÄÚµå´Â ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ºôµåÇÕ´Ï´Ù.
SQL_API_RC SQL_API_FN myProc(char * szName, int * nCmd, int * nAmount, int * nSaving, int * nChecking) { SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; SQLRETURN rc; int nRetSize; SQLCHAR str1[]="select saving, checking from db2e.myaccount where name = ?"; SQLCHAR str2[]="update db2e.myaccount set saving=saving - ?, checking=checking + ? where name=?"; SQLCHAR str3[]="update db2e.myaccount set saving=saving + ?, checking=checking - ? where name=?"; //**************************************************************** //* Prepare connection and statement //**************************************************************** rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); //checkerror rc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc); //checkerror rc = SQLSetConnectAttr(hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, SQL_NTS); //checkerror rc = SQLConnect(hdbc, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS); //checkerror rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt); //checkerror //**************************************************************** //* Update account //**************************************************************** if ( *nCmd == 2 || *nCmd == 3 ){ if ( *nCmd == 2 ){ //Transfer from saving to checking rc = SQLPrepare(hstmt, str2, SQL_NTS); //checkerror } if ( *nCmd == 3 ){ //Transfer from checking to saving rc = SQLPrepare(hstmt, str3, SQL_NTS); //checkerror } rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)nAmount, 0, NULL ); //checkerror rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)nAmount, 0, NULL ); //checkerror rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, (SQLPOINTER)szName, 0, NULL ); //checkerror rc = SQLExecute(hstmt); //checkerror } //**************************************************************** //* Retrieve account balance //**************************************************************** rc = SQLPrepare(hstmt, str1, SQL_NTS); //checkerror rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, (SQLPOINTER)szName, 0, NULL );//checkerror rc = SQLExecute(hstmt);//checkerror if ( rc == SQL_SUCCESS || rc == SQL_SUCCESS_WITH_INFO ) { while ( (rc = SQLFetch(hstmt) ) == SQL_SUCCESS ){ rc = SQLGetData( hstmt, (SQLSMALLINT)1, SQL_C_LONG, nSaving, sizeof(int) , &nRetSize ) ; //checkerror rc = SQLGetData( hstmt, (SQLSMALLINT)2, SQL_C_LONG, nChecking, sizeof(int) , &nRetSize ) ; //checkerror } } //**************************************************************** //* Clean up //**************************************************************** rc = SQLEndTran( SQL_HANDLE_DBC, hdbc, SQL_COMMIT ); SQLFreeHandle(SQL_HANDLE_STMT, hstmt); SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); SQLFreeHandle(SQL_HANDLE_ENV, henv); return (0); }
Win32 Ç÷§Æû¿¡¼ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µ¿Àû ¸µÅ© ¶óÀ̺귯¸®(mydll.dll)·Î ºôµåÇÑ ÈÄ, À̸¦ \SQLLIB\function µð·ºÅ丮¿¡ º¹»çÇϽʽÿÀ. ±×·± ´ÙÀ½, ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇϽʽÿÀ.
DB2 CONNECT TO MYSAMPLE
CREATE PROCEDURE db2e.MYPROC (IN szName CHAR(16), IN nCmd INTEGER, IN nAmount INTEGER, OUT nSaving INTEGER, OUT nChecking INTEGER ) DYNAMIC RESULT SETS 1 LANGUAGE C PARAMETER STYLE GENERAL NO DBINFO FENCED MODIFIES SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'mydll!myProc'@
½ºÅ©¸³Æ®¸¦ ¼öÇàÇÏ·Á¸é, ´ÙÀ½ ¸í·ÉÀ» ÀÔ·ÂÇϽʽÿÀ.
db2 -td@ -vf regscript.scr
ÀúÀå ÇÁ·Î½Ãµà¾î db2e.MYPROC°¡ Áö±Ý ±¸¼ºµË´Ï´Ù. ´ÙÀ½, ¸ðºô ÀåÄ¡ °ü¸® ¼¾Å͸¦ »ç¿ëÇÏ¿© º¹»ç ÀÛ¾÷ ³»¿ªÀ» ÀÛ¼ºÇϽʽÿÀ.
¿¡ÀÌÀüÆ® ¾î´ðÅÍ º¹»ç ÀÛ¾÷ ³»¿ª ÀÛ¼º
dbname=mysample;procname=db2e.MYPROC
dbnameÀº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ »ç¿ëµÈ µ¥ÀÌÅͺ£À̽ºÀÔ´Ï´Ù. procnameÀº ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ À̸§ÀÔ´Ï´Ù.
AgentAdapter º¹»ç ÀÛ¾÷ ³»¿ªÀ» ÀÛ¼ºÇÑ ÈÄ, »ç¿ëÀÚ, ±×·ì ¹× º¹»ç ÀÛ¾÷ ³»¿ª ¼¼Æ®¸¦ ÀÛ¼ºÇϽʽÿÀ.
¿ø°Ý Á¶È¸ ¹× ÀúÀå ÇÁ·Î½Ãµà¾î ¾î´ðÅ͸¦ »ç¿ëÇϱâ À§ÇØ DB2 Everyplace ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇϱâ
ÀÌ »ùÇÃÀº DB2 Everyplace Win32 ÄÜ¼Ö ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¿© ¿ø°Ý Á¶È¸ ¹× ÀúÀå ÇÁ·Î½Ãµà¾î ¾î´ðÅ͸¦ Å×½ºÆ®ÇÕ´Ï´Ù. »ùÇà ÀÀ¿ëÇÁ·Î±×·¥Àº myclient.exe·Î ºÒ·ÁÁý´Ï´Ù. »ùÇà ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½ ¼¼ °³ÀÇ ¸Å°³º¯¼ö¸¦ »ç¿ëÇÕ´Ï´Ù.
Account Name: Identify the account to access. Option: Identify the action to perform. The options are: 1: Check balance. 2: Transfer from savings to checking. 3: Transfer from checking to savings. Amount: Amount to transfer between checking and saving.
¿¹¸¦ µé¾î, Michael °èÁ¤¿¡¼ $1000¸¦ Àú±Ý¿¡¼ ´çÁ ¿¹±ÝÀ¸·Î Àü¼ÛÇÏ·Á¸é ´ÙÀ½ ¸í·ÉÀ» ÀÔ·ÂÇϽʽÿÀ.
myclient.exe Michael 2 1000
´ÙÀ½°ú °°Àº ÀÀ´äÀÌ ¸®Åϵ˴ϴÙ.
Saving = 4000 Checking = 6000
»ùÇà ÀÀ¿ëÇÁ·Î±×·¥ ÄÚµå
´ÙÀ½ ÀýÀº »ùÇà ÀÀ¿ëÇÁ·Î±×·¥¿¡ ´ëÇÑ Äڵ带 Æ÷ÇÔÇÕ´Ï´Ù. ÄÚµå´Â ¿ø°Ý µ¥ÀÌÅÍ ¿øº»¿¡ ¿¬°áÇϱâ À§ÇØ SQLConnect() ÇÔ¼ö¿¡ ´ëÇÑ ¿¬°á ¹®ÀÚ¿À» ÇÊ¿ä·Î ÇÕ´Ï´Ù. ¿¬°á ½ºÆ®¸µÀÇ Çü½ÄÀº ´ÙÀ½°ú °°½À´Ï´Ù.
http://IPAddr:port/db2e/servlet/com.ibm.mobileservices.adapter.agent.AgentServlet?DB=mysample
¿©±â¼, IPAddr:port´Â IP ÁÖ¼ÒÀÌ°í ¼¹öÀÇ Æ÷Æ® ¹øÈ£ÀÔ´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½°ú °°½À´Ï´Ù.
http://192.168.0.11:8080/db2e/servlet/ com.ibm.mobileservices.adapter.agent.AgentServlet?DB=mysample
int main(int argc, char * argv[]) { SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; SQLRETURN rc; SQLCHAR strSQL[] = "CALL db2e.MYPROC(?,?,?,?,?)"; int nInd4, nInd5; int nSaving = 0, nChecking =0 ; int nCmd =0, nAmount=0; SQLCHAR strConnect[254]; //**************************************************************** //* Check input parameters //**************************************************************** if ( argc < 4 ){ printf("\nUsage : myClient AccountName Cmd Amount"); printf("\n cmd 1 : query balance"); printf("\n cmd 2 : Transfer from Saving to Checking"); printf("\n cmd 3 : Trnasfer from Checking to Saving"); return (99); } nCmd = atoi(argv[2]); nAmount = atoi(argv[3]); //**************************************************************** //* Allocate handles //**************************************************************** rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv); //checkerror rc = SQLAllocHandle( SQL_HANDLE_DBC, henv, &hdbc); //checkerror if (argc == 5){ strcpy(strConnect,"http://"); strcat(strConnect,argv[4]); strcat(strConnect,"/db2e/servlet/com.ibm.mobileservices.adapter.agent.AgentServlet?DB=mysample"); }else{ strcpy(strConnect,"http://127.0.0.1:8080/db2e/servlet/com.ibm.mobileservices .adapter.agent.AgentServlet?DB=mysample"); } //**************************************************************** //* Connect to remote database //**************************************************************** rc = SQLConnect(hdbc, strConnect, SQL_NTS, "userex", SQL_NTS, "userex", SQL_NTS ); //checkerror rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt); //checkerror //**************************************************************** //* Prepare, Bind , and Execute the statement //**************************************************************** rc = SQLPrepare(hstmt,strSQL, SQL_NTS); //checkerror rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, 0, 0, (SQLPOINTER)argv[1], 0, NULL ); //checkerror rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&nCmd, sizeof(int), NULL); //checkerror rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&nAmount, sizeof(int), NULL ); //checkerror rc = SQLBindParameter(hstmt, 4, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&nSaving, sizeof(int), &nInd4); //checkerror rc = SQLBindParameter(hstmt, 5, SQL_PARAM_OUTPUT, SQL_C_LONG, SQL_INTEGER, 0, 0, (SQLPOINTER)&nChecking, sizeof(int), &nInd5 ); //checkerror rc = SQLExecute(hstmt); //checkerror //**************************************************************** //* Print the balance //**************************************************************** printf("\nSaving = %d",nSaving); printf("\nChecking = %d",nChecking); SQLFreeHandle(SQL_HANDLE_STMT, hstmt); SQLDisconnect(hdbc); SQLFreeHandle(SQL_HANDLE_DBC, hdbc); SQLFreeHandle(SQL_HANDLE_ENV, henv); return 0; }
»ùÇà ÀÀ¿ëÇÁ·Î±×·¥À» ÄÄÆÄÀÏÇÑ ÈÄ, ¿ø°Ý Á¶È¸ ¹× ÀúÀå ÇÁ·Î½Ãµà¾î ¾î´ðÅÍ ÀÀ¿ëÇÁ·Î±×·¥À» Å×½ºÆ®ÇϽʽÿÀ.