CALL

Invokes a stored procedure defined with the Remote Query and Stored Procedure Adapter for the DB2 Everyplace Sync Server. A stored procedure, for example, executes at the location of the remote database, and returns data to the DB2 Everyplace client application.

Programs using the SQL CALL statement are designed to run in two parts, one on the client and the other on the server.

Invocation

Remote stored procedures are invoked from a DB2 Everyplace application by passing the following CALL statement syntax to SQLPrepare() followed by SQLExecute().

Syntax 

>>-CALL--procedure-name--+-------------+--+------+-------------><
                         |    .-,-.    |  '-(--)-'
                         |    V   |    |
                         '-(----?-+--)-'

Description

procedure-name
Identifies the procedure to call at the remote server. The procedure identified must be defined in the AgentAdapter subscription at the current Sync Server.
?
The ? in the CALL statement syntax diagram denotes a parameter marker corresponding to an argument for a stored procedure. All arguments must be passed using parameter markers.

Rules

none

Notes

The CALL statement uses the remote query and stored procedure adapter included with DB2 Everyplace Sync Server. DB2 Everyplace Sync Server is required to use the CALL statement in DB2 Everyplace applications. DB2 Everyplace does not support local stored procedures.

For additional information, see the data sources section of the DB2 Everyplace Sync Server 관리 안내서.

Example

A compete example of how to use the CALL statement and remote query and stored procedure adapter is available in the DB2 Everyplace Sync Server 관리 안내서. The following sample shows only the coding of the CALL statement in a sample application.

A stored procedure MYPROC() is defined at the source server for database mysample. An AgentAdapter subscription is defined at the DB2 Everyplace Sync Server with the following attributes: 

User ID:  db2admin 
Password: db2admin 
Other:    dbname=mysample;procname= db2e.MYPROC

Sample program using the CALL statement: 

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,"/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;

Related reference