Creating a stored procedure using the sample application

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.

Open a DB2 command window.
Connect to the MYSAMPLE database using the following command:
```
DB2 CONNECT TO MYSAMPLE
```

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.

Related concepts

The remote query and stored procedure adapter

Related tasks

Related reference