Returns result set information.
%INCLUDE CTPUBLIC;
DCL 01 COMMAND FIXED BIN(31) INIT(0); 01 RETCODE FIXED BIN(31) INIT(0); 01 RESULT_TYP FIXED BIN(31); 01 BUFFER type; 01 BUFFER_LEN FIXED BIN(31); 01 OUTLEN FIXED BIN(31); CALL CTBRESIN (COMMAND, RETCODE, RESULT_TYP, BUFFER, BUFFER_LEN, OUTLEN);
(I) Handle for this client/server operation. This handle is defined in the associated CTBCMDALLOC call.
(O) Variable where the result from an executed function returns. Its value is one of the codes listed under “Returns,” in this section.
(I) Type of information to return. Assign this argument one of the following values:
Value |
Meaning |
|---|---|
CS_ROW_COUNT (800) |
The number of rows affected by the current command. |
CS_CMD_NUMBER (801) |
The number of the command that generated the current result set. |
CS_NUMDATA (803) |
The number of items in the current result set. |
(O) Variable (“buffer”) where CTBRESINFO returns the requested information. At present, this will always be an integer value.
(I) Length, in bytes, of the buffer.
If the returned value is longer than BUFFER_LEN, CTBRESINFO sets OUTLEN to the length of the requested information and returns CS_FAIL.
(O) Length, in bytes, of the retrieved information. OUTLEN is an integer variable where CTBRESINFO returns the length of the information being retrieved.
If the retrieved information is larger than BUFFER_LEN bytes, an application uses the value of OUTLEN to determine how many bytes are needed to hold the information.
CTBRESINFO returns one of the following values:
Value |
Meaning |
|---|---|
CS_SUCCEED (-1) |
Results are available for processing. |
CS_FAIL (-2) |
The routine failed. CTBRESINFO returns CS_FAIL if the requested information is larger than BUFFER_LEN bytes. |
The following code fragment demonstrates the use of CTBRESINFO. It is taken from the sample program SYCTSAA4 in Appendix A, “Sample Language Application.”
/*------------------------------------------------------------------*/
/* */
/* Subroutine to process result rows */
/* */
/*------------------------------------------------------------------*/
RESULT_ROW_PROCESSING: PROC ;
/*------------------------------------------------------------*/
/* We need to bind the data to program variables. We don't */
/* care about the indicator variable so we'll pass NULL for */
/* that parameter in OC_BIND(). */
/*------------------------------------------------------------*/
CALL CTBRESIN( CSL_CMD_HANDLE,
CSL_RC,
CS_NUMDATA,
RF_NUMDATA,
STG(RF_NUMDATA),
CF_COL_LEN ) ;
IF CSL_RC ^= CS_SUCCEED THEN
DO ;
MSGSTR = 'CTBRESINFO failed' ;
NO_ERRORS_SW = FALSE ;
CALL ERROR_OUT;
CALL ALL_DONE;
END ;
FF_ROW_NUM = FF_ROW_NUM + 1;
/*------------------------------------------------------------*/
/* display the number of connections */
/*------------------------------------------------------------*/
OR2_MAXCONNECT = CF_MAXCONNECT ;
RSLTNO(FF_ROW_NUM) = OUTPUT_ROW_STR2 ;
FF_ROW_NUM = FF_ROW_NUM + 2;
/*------------------------------------------------------------*/
/* display the number of columns */
/*------------------------------------------------------------*/
OR4_NUMDATA = RF_NUMDATA ;
RSLTNO(FF_ROW_NUM) = OUTPUT_ROW_STR4 ;
IF RF_NUMDATA ^= 2 THEN
DO ;
MSGSTR = 'CTBRESINFO returned wrong # of parms' ;
NO_ERRORS_SW = FALSE ;
CALL ERROR_OUT;
CALL ALL_DONE;
END ;
FF_ROW_NUM = FF_ROW_NUM + 2;
/*------------------------------------------------------------*/
/* Setup column headings */
/*------------------------------------------------------------*/
RSLTNO(FF_ROW_NUM) = 'FirstName EducLvl' ;
FF_ROW_NUM = FF_ROW_NUM + 1;
RSLTNO(FF_ROW_NUM) = '=========== =======' ;
DO PARM_CNT = 1 TO RF_NUMDATA ;
CALL BIND_COLUMNS ;
END ;
END RESULT_ROW_PROCESSING ;
CTBRESINFO returns information about the current result set or the current command. The current command is defined as the request that generated the current result set.
A result set is a collection of a single type of result data. Result sets are generated by requests. For more information on result sets, see CTBRESULTS.
To determine the number of the command that generated the current result set, call CTBRESINFO with RESULT_TYP as CS_CMD_NUMBER.
Client-Library keeps track of the command number by counting the number of times CTBRESULTS returns CS_CMD_DONE.
An application’s first call to CTBRESULTS following a CTBSEND call sets the command number to 1. The command number remains 1 until CTBRESULTS returns CS_CMD_DONE. The next time the application calls CTBRESULTS, the command number is incrementally increased to 2. The command number continues to increase by 1 each time CTBRESULTS is called after returning CS_CMD_DONE.
CS_CMD_NUMBER is useful in the following cases:
To determine the SQL command within a language request that generated the current result set.
To determine the select command in a stored procedure or transaction that generated the current result set.
A language request contains a string of text. This text represents one or more SQL commands or other language request statements. If the application is sending a language request, “command number” refers to the number of the statement in the language request.
For example, the following Transact-SQL string represents three Transact-SQL commands—two select statements and one insert:
select * from authors select * from titles insert newauthors select * from authors where city = "San Francisco"
The two select statements can generate result sets. In this case, the command number that CTBRESINFO returns can be from 1 to 3, depending on when CTBRESINFO is called.
When sending SQL strings to DB2, remember to use semicolons
(;) to separate SQL statements.
Inside stored procedures or transactions, only select statements cause the command number to be incremented. If a stored procedure or transaction contains seven SQL commands, three of which are select statements, the command number that CTBRESINFO returns can be any integer from 1 to 3, depending on which select statement generated the current result set.
To determine the number of result data items in the current result set, call CTBRESINFO with RESULT_TYP as CS_NUMDATA.
Results sets contain result data items. Row result sets contain columns, a parameter result set contains parameters, and a status result set contains a status. The columns, parameters, and status are known as result data items.
To determine the number of rows affected by the current command, call CTBRESINFO with RESULT_TYP as CS_ROW_COUNT.
If the current command is one that does not return rows—for example, a language command containing an insert statement—an application can get the row count immediately after CTBRESULTS returns CS_CMD_SUCCEED.
If the current command does return rows:
An application can get a total row count after processing all of the rows.
An application can get an intermediate row count any time after CTBRESULTS indicates that results are available. An intermediate row count is equivalent to the number of rows that have been fetched so far.
If the command is one that executes a stored procedure or transaction— such as a Transact-SQL exec language command or a remote procedure call—CTBRESINFO returns either the number of rows returned by the latest select statement executed by the stored procedure or transaction, or CS_NO_COUNT if the stored procedure or transaction does not execute any select statements. A stored procedure or transaction that does not contain any select statements can execute a select by calling another stored procedure or transaction that contains a select statement.
CTBRESINFO returns CS_NO_COUNT if any of the following are true:
The SQL command fails for any reason, such as a syntax error.
The command is one that never affects rows, such as a Transact-SQL print command.
The command executes a stored procedure or transaction that does not execute any select statements.
The following arguments listed in Table 3-17 are returned to RESULT_TYP after CTBRESULTS indicates that results are present.
RESULT_TYP |
CTBRESINFO returns |
BUFFER value |
|---|---|---|
CS_ROW_COUNT (800) |
The number of rows affected by the current command. |
An integer value. |
CS_CMD_NUMBER (801) |
The number of the command that generated the current result set. |
An integer value. |
CS_NUMDATA (803) |
The number of items in the current result set. |
An integer value. |
Related functions:
Related topics:
