Application Development Guide

DB2DARI Stored Procedures

When invoked, the DB2DARI stored procedure performs the following:

Accepts the SQLDA data structure from the client application. (Host variables are passed through an SQLDA data structure generated by the database manager when the SQL CALL statement is executed.)
Executes on the database server under the same transaction as the client application.
Returns SQLCA information and optional output data to the client application.

Using the SQLDA in a Client Application

To use the SQLDA structure to pass values to the stored procedure, perform the following steps before calling the stored procedure:

Allocate storage for the structure with the required number of base SQLVAR elements.
Set the SQLN field to the number of SQLVAR elements allocated.
Set the SQLD field to the number of SQLVAR elements actually used.
Initialize each SQLVAR element used as follows:
- Set the SQLTYPE field to the proper data type.
- Set the SQLLEN field to the size of the data type.
- Allocate storage for the SQLDATA and SQLIND fields based upon the values in SQLTYPE and SQLLEN.

If your application will be working with character strings defined as FOR BIT DATA, you need to initialize the SQLDAID field to indicate that the SQLDA includes FOR BIT DATA definitions and the SQLNAME field of each SQLVAR that defines a FOR BIT DATA element.

If your application will be working with large objects, that is, data with types of CLOB, BLOB, or DBCLOB, you will also need to initialize the secondary SQLVAR elements. For information on the SQLDA structure, refer to the SQL Reference.

Using Host Variables in a DB2DARI Client

Declare SQLVARs using the same approach discussed in Allocating Host Variables. In addition, the client application should set the indicator of output-only SQLVARs to -1 as discussed in Data Structure Manipulation. This will improve the performance of the parameter passing mechanism by avoiding having to pass the contents of the SQLDATA pointer, as only the indicator is sent. You should set the SQLTYPE field to a nullable data type for these parameters. If the SQLTYPE indicates a non-nullable data type, the indicator variable is not checked by the database manager.

Using the SQLDA in a Stored Procedure

The stored procedure is invoked by the SQL CALL statement and executes using data passed to it by the client application. Information is returned to the client application using the stored procedure's SQLDA structure.

The parameters of the SQL CALL statement are treated as both input and output parameters and are converted into the following format for the stored procedure:

     SQL_API_RC SQL_API_FN proc_name( void *reserved1,
                                      void *reserved2,
                                      struct sqlda *inoutsqlda, 
                                      struct sqlca *sqlca )

The SQL_API_FN is a macro that specifies the calling convention for a function that may vary across each supported operating system. This macro is required when you write stored procedures or UDFs.

Following is an example of how a CALL statement maps to a server's parameter list:

     CALL OUTSRV (:empno:empind,:salary:salind)

The parameters to this call are converted into an SQLDA structure with two SQLVARs. The first SQLVAR points to the empno host variable and the empind indicator variable. The second SQLVAR points to the salary host variable and the salind indicator variable.
Note: The SQLDA structure is not passed to the stored procedure if the number of elements, SQLD, is set to 0. In this case, if the SQLDA is not passed, the stored procedure receives a NULL pointer.

Data Structure Manipulation

The database manager automatically allocates a duplicate SQLDA structure at the database server. To reduce network traffic, it is important to indicate which host variables are input-only, and which ones are output-only. The client procedure should set the indicator of output-only SQLVARs to -1. The server procedure should set the indicator for input-only SQLVARs to -128. This allows the database manager to choose which SQLVARs are passed.

Note that an indicator variable is not reset if the client or the server sets it to a negative value (indicating that the SQLVAR should not be passed). If the host variable to which the SQLVAR refers is given a value in the stored procedure or the client code, its indicator variable should be set to zero or a positive value so that the value is passed. For example, consider a stored procedure which takes one output-only parameter, called as follows:

     empind = -1; 
     EXEC SQL CALL storproc(:empno:empind);

When the stored procedure sets the value for the first SQLVAR, it should also set the value of the indicator to a non-negative value so that the result is passed back to empno.

Summary of Data Structure Usage

Table 53 summarizes the use of the various structure fields by the stored procedures application. In the table, sqlda is an SQLDA structure passed to the stored procedure and n is a numeric value indicating a specific SQLVAR element of the SQLDA. The numbers on the right refer to the notes following the table.

Table 53. Stored Procedures Parameter Variables

Input/Output SQLDA sqlda.SQLDAID

4

sqlda.SQLDABC

4

sqlda.SQLN
2
4

sqlda.SQLD
2 3
5

Input/Output SQLVAR sqlda.n.SQLTYPE
2 3
5

sqlda.n.SQLLEN
2 3
5

sqlda.n.SQLDATA 1 2 3

6
8

sqlda.n.SQLIND 1 2 3

6
8 9

sqlda.n.SQLNAME.length

6 7

sqlda.n.SQLNAME.data

6 7

sqlda.n.SQLDATATYPE_NAME
2 3
5

sqlda.n.SQLLONGLEN
2 3
5

sqlda.n.SQLDATALEN 1 2 3

6 7

SQLCA (all elements)

6 7

Note:

Before invoking the stored procedure, the client application must:
Allocate storage for the pointer element based on SQLTYPE and SQLLEN.
Initialize the element with the appropriate data.

When called by the application, the database manager:
Sends data in the original element to a duplicate element allocated at the stored procedure. The SQLN element is initialized with the data in the SQLD element.

When invoked, the stored procedure can:
Alter data in the duplicate element. The data can be altered as needed since it is not checked for validity or returned to the client application.

When the stored procedure terminates, the database manager:
Checks data in the duplicate elements. If the values in these fields do not match the data in the original elements, an error is returned.
Returns data in the duplicate elements to the original element.
The data can be altered as needed since it is not checked for validity.
The data pointed to by the elements can be altered as needed since they are not checked for validity but are returned to the client application.
The SQLIND field is not passed in or out if SQLTYPE indicates the column type is not nullable.

Input/Output SQLDA and SQLCA Structures

The stored procedure runs using any information passed in the input variables of the SQLDA structure. Information is returned to the client in the output variables of the SQLDA. Do not change the value of the SQLD, SQLTYPE, and SQLLEN fields of the SQLDA, as these fields are compared to the original values set by the client application before data is returned. If they are different, one of the following SQLCODEs is returned:

SQLCODE -1113 (SQLSTATE 39502): The data type of a variable (that is, the value in SQLTYPE) has changed.
SQLCODE -1114 (SQLSTATE 39502): The length of a variable (that is, the value in SQLLEN) has changed.
SQLCODE -1115 (SQLSTATE 39502): The SQLD field has changed.

In addition, do not change the pointer for the SQLDATA and the SQLIND fields, although you can change the value that is pointed to by these fields.
Note: It is possible to use the same variable for both input and output.

Before the stored procedure returns, SQLCA information should be explicitly copied to the SQLCA parameter of the stored procedure.

Return Values for DB2DARI Stored Procedures

The return value of the stored procedure is never returned to the client application. It is used by the database manager to determine if the server procedure should be released from memory upon exit.

The stored procedure returns one of the following values:

SQLZ_DISCONNECT_PROC: Tells the database manager to release (unload) the library.
SQLZ_HOLD_PROC: Tells the database manager to keep the server library in main memory so that the library will be ready for the next invocation of the stored procedure. This may improve performance.

If the stored procedure is invoked only once, it should return SQLZ_DISCONNECT_PROC.

If the client application issues multiple calls to invoke the same stored procedure, SQLZ_HOLD_PROC should be the return value of the stored procedure. The stored procedure will not be unloaded.

If SQLZ_HOLD_PROC is used, the last invocation of the stored procedure should return the value SQLZ_DISCONNECT_PROC to free the stored procedure library from main memory. Otherwise, the library remains in main memory until the database manager is stopped. As an alert to the stored procedure, the client application could pass a flag in one of the parameters indicating the final call.

[ Top of Page | Previous Page | Next Page ]