Returns result set information.
COPY CTPUBLIC.
01 COMMAND PIC S9(9) COMP SYNC. 01 RETCODE PIC S9(9) COMP SYNC. 01 RESULT-TYP PIC S9(9) COMP SYNC. 01 BUFFER PIC S9(9) 01 BUFFER-LEN PIC S9(9) COMP SYNC. 01 OUTLEN PIC S9(9) COMP SYNC.
CALL 'CTBRESIN' USING 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 “Return value,” 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.
This argument is typically:
01 BUFFER PIC S9(9) COMP SYNC.
(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 SYCTSAA5 in Appendix A, “Sample Language Requests.”
*========================================================
*== ==
*== Subroutine to process result rows ==
*== ==
*========================================================
RESULT-ROW-PROCESSING.
CALL 'CTBRESIN' USING CSL-CMD-HANDLE,
CSL-RC,
CS-NUMDATA,
RF-NUMDATA,
RF-NUMDATA-SIZE,
CF-COL-LEN.
IF CSL-RC NOT EQUAL CS-SUCCEED
THEN
MOVE SPACES TO MSGSTR
STRING 'CTBRESINFO failed' DELIMITED BY SIZE
INTO MSGSTR
PERFORM PRINT-MSG
PERFORM ALL-DONE
END-IF.
COMPUTE FF-ROW-NUM = FF-ROW-NUM + 1.
*********************************
* display number of connections *
*********************************
MOVE CF-MAXCONNECT TO OR2-MAXCONNECT.
MOVE OUTPUT-ROW-STR2 TO RSLTNO(FF-ROW-NUM).
COMPUTE FF-ROW-NUM = FF-ROW-NUM + 2.
*********************************
* display the number of columns *
*********************************
MOVE RF-NUMDATA TO OR4-NUMDATA.
MOVE OUTPUT-ROW-STR4 TO RSLTNO(FF-ROW-NUM).
IF RF-NUMDATA NOT EQUAL 2
THEN
STRING 'CTBRESINFO returned wrong # of parms' DELIMITED
BY SIZE INTO MSGSTR
PERFORM PRINT-MSG
PERFORM ALL-DONE
END-IF.
COMPUTE FF-ROW-NUM = FF-ROW-NUM + 2.
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 and “Results”.
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 be increased 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—for example 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
