The following example will create a stored procedure, a subscription to the stored procedure, and a DB2 Everyplace application to use the stored procedure. The purpose of the example application is to allow a mobile user to check an account balance and transfer money between a savings and a checking account by using a DB2 Everyplace remote stored procedure call. For information on how to create stored procedures on DB2 Universal Database, please see DB2 Universal Database Application Development Guide.
Create a data source
This example uses a DB2 database named MYSAMPLE. You need to manually create the MYSAMPLE database. To create the MYSAMPLE database, enter the following statements at the DB2 command prompt:
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)
After creating the database, create a stored procedure to modify the data in the database.
Create a stored procedure
This example uses a stored procedure named MYPROC(). This procedure takes five parameters: Account Name, Option, Transfer Amount, Saving Balance, Checking Balance. The following list identifies the purpose of each of the parameters:
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
The following code builds the stored procedure:
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); }
On the Win32 platform, after building the stored procedure into a dynamic link library (mydll.dll), copy it to the \SQLLIB\function directory. Next, register the stored procedure.
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'@
To run the script, enter the following command:
db2 -td@ -vf regscript.scr
The stored procedure db2e.MYPROC is now configured. Next, create a subscription using the Mobile Devices Administration Center.
Create the Agent Adapter subscription
dbname=mysample;procname=db2e.MYPROC
The dbname is the database used by the stored procedure. The procname is the name of the stored procedure.
After creating the AgentAdapter subscription, create a user, group, and subscription set.
Create a DB2 Everyplace application to use the remote query and stored procedure adapter
This sample uses a DB2 Everyplace Win32 console application to test the remote query and stored procedure adapter. The sample application is called myclient.exe. The sample application uses the following three parameters:
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.
For example, to transfer $1000 from savings to checking on the Michael account, enter the following command:
myclient.exe Michael 2 1000
The following response is returned:
Saving = 4000 Checking = 6000
Sample application code
The following section contains the code for the sample application. The code requires a connection string for the SQLConnect() function to connect to the remote data source. The format of the connection string is:
http://IPAddr:port/db2e/servlet/com.ibm.mobileservices.adapter.agent.AgentServlet?DB=mysample
where IPAddr:port is the IP address and port number of the server. For example:
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; }
After compiling the sample application, test the remote query and stored procedure adapter application.