Tips: Run and call IBM i procedures

Running an IBM i procedure

ODBC provides a standard interface for calling database procedures. The implementation of database procedures differs significantly across various databases. This simple example follows the recommended approach for running an IBM i procedure.

1. Set up a CREATE PROCEDURE statement for the procedure and create it. The creation of the procedure defines the procedure and only needs to be done once. The definition that it provides is available to all applications which run against the database, including ODBC applications.
2. Prepare the CALL statement to call the procedure.
3. Bind the parameters of the procedure, indicating whether each parameter is to be used for input to the procedure, output from the procedure, or input/output.
4. Call the procedure.

Calling IBM i procedures using Visual Basic

Use care in coding the SQLBindParameter functions. Never use Visual Basic strings as a buffer when binding either columns (SQLBindCol) or parameters (SQLBindParameter). Instead, use byte arrays, which–unlike strings–will not be moved around in memory. See Example: Call an IBM i stored procedure by using Visual Basic for more information.

Pay careful attention to the data types that are involved. There may be subtle differences with those that you use with, for instance, a SELECT statement. Also, ensure that you have an adequately sized buffer for output and input/output parameters. The way that you code the IBM i procedure can affect performance significantly. Whenever possible, avoid closing the program with exit() in C language and with SETON LR in RPG languages. Preferably, use RETRN or return, but you may need to re-initialize variables on each call, and by-pass file opens.