IBM DB2 Everyplace Sync Server Administration Guide Version 7 Release 2 Modification 1


Sample application using the remote query and stored procedure adapter

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.

  1. Open a DB2 command window.
  2. Connect to the MYSAMPLE database using the following command:
    DB2 CONNECT TO MYSAMPLE
    
  3. Register the stored procedure using a script named regscript.scr to configure options. The following code is used for this script:
    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

  1. Open the Mobile Devices Administration Center from the start menu.
  2. Select the Subscriptions folder of the Mobile Devices Administration Center.
  3. Right-click on the Subscriptions folder of the Mobile Devices Administration Center and select Create Custom Subscription.
  4. Type subex in the Name field.
  5. Select the AgentAdapter in the Adapter field.
  6. Select None in the Encryption field. Encryption is not support with the AgentAdapter.
  7. Click the Launch Customizer button. The Source database window opens.
  8. Type a DB2 user ID with access privileges to the database into the User ID field
  9. Type the password for the user ID in the Password and Verify password field.
  10. In the Other field, type the following line:
    dbname=mysample;procname=db2e.MYPROC
    

    The dbname is the database used by the stored procedure. The procname is the name of the stored procedure.

  11. Click OK to close the Source database window. Click OK to close the Create Custom Subscription notebook.

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.


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]