Fetches result data.
%INCLUDE CTPUBLIC;
DCL 01 COMMAND FIXED BIN(31) INIT(0); 01 RETCODE FIXED BIN(31) INIT(0); 01 TYP FIXED BIN(31); 01 OFFSET FIXED BIN(31); 01 OPTION FIXED BIN(31); 01 ROWS_READ FIXED BIN(31); CALL CTBFETCH (COMMAND, RETCODE, TYP, OFFSET, OPTION, ROWS_READ);
(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) This argument is currently unused and should be passed as CS_UNUSED in order to ensure compatibility with future versions of Client-Library.
(I) This argument is currently unused and should be passed as CS_UNUSED in order to ensure compatibility with future versions of Client-Library.
(I) This argument is currently unused and should be passed as CS_UNUSED in order to ensure compatibility with future versions of Client-Library.
(I) Variable where the number of result rows is returned. This variable is of type integer. CTBFETCH sets ROWS_ READ to the number of rows read by the CTBFETCH call. This argument is required.
CTBFETCH returns one of the following values listed in Table 3-12.
Value |
Meaning |
|---|---|
CS_SUCCEED (-1) |
The routine completed successfully. CTBFETCH places the total number of rows read in ROWS_READ. |
CS_FAIL (-2) |
The routine failed. CTBFETCH places the number of rows fetched before the failure occurred in ROWS_READ. A common reason for a CTBFETCH failure is that a program variable specified through CTBBIND is too small to hold a fetched data item. |
CS_CANCELLED (-202) |
The operation was canceled. CTBFETCH places the number of rows fetched before the cancel occurred in ROWS_READ. |
CS_ROW_FAIL (-203) |
A recoverable error occurred while fetching a row. Recoverable errors include memory allocation failures and conversion errors that occur while copying row values to program variables. An application can continue calling CTBFETCH to continue retrieving rows, or can call CTBCANCEL to cancel the remaining results. CTBFETCH places the number of rows fetched before the error occurred in ROWS_READ, then continues by fetching the row after the error. |
CS_END_DATA (-204) |
No more rows are available in this result set. (Note that this is also a successful completion.) |
TDS_INVALID_PARAMETER (-4) |
One of the CTBFETCH arguments contains an illegal value. The most likely cause of this code is assigning a value other than CS_UNUSED to one of more of the reserved arguments, TYP, OFFSET, and OPTION. |
TDS_WRONG_STATE (-6) |
Program is in the wrong communication state to issue this call. It is in Send state instead of Receive state. |
The following example shows a typical use of CTBFETCH. It is taken from the SYCTSAA4 sample program in Appendix A, “Sample Language Application.”
/*------------------------------------------------------------------*/
/* */
/* Subroutine to fetch row processing */
/* */
/*------------------------------------------------------------------*/
FETCH_ROW_PROCESSING: PROC ;
CALL CTBFETCH( CSL_CMD_HANDLE,
CSL_RC,
CS_UNUSED, /* type */
CS_UNUSED, /* offset */
CS_UNUSED, /* option */
FF_ROWS_READ ) ;
SELECT( CSL_RC ) ;
WHEN( CS_SUCCEED )
DO ;
NO_MORE_ROWS = FALSE ;
CF_COL_FIRSTNME_CHAR = BLANK ;
DF_DATATYPE = CS_VARCHAR_TYPE;
DF_MAXLENGTH = LENGTH( CF_COL_FIRSTNME ) ;
DF2_DATATYPE = CS_CHAR_TYPE;
DF2_MAXLENGTH = STG(CF_COL_FIRSTNME_CHAR);
CALL CSBCONVE( CSL_CTX_HANDLE,
CSL_RC,
DATAFMT,
CF_COL_FIRSTNME,
DATAFMT2,
CF_COL_FIRSTNME_CHAR,
CF_COL_LEN);
IF CSL_RC ^= CS_SUCCEED THEN
DO ;
MSGSTR = 'CSCONVERT CS_CHAR_TYPE failed' ;
NO_ERRORS_SW = FALSE ;
CALL ERROR_OUT;
CALL ALL_DONE;
END ;
FF_ROW_NUM = FF_ROW_NUM + 1 ;
/*------------------------------------------------------------*/
/* save ROW RESULTS for later display */
/*------------------------------------------------------------*/
OR_COL_FIRSTNME_CHAR = CF_COL_FIRSTNME_CHAR;
OR_COL_EDLEVEL = CF_COL_EDLEVEL;
IF FF_ROW_NUM > 10 THEN
DO;
MSG_TEXT_1 = 'Please press return to continue!' ;
MSG_TEXT_2 = BLANK ;
CALL DISP_DATA ;
FF_ROW_NUM = 1;
PAGE_CNT = PAGE_CNT + 1 ;
/*------------------------------------------------------------*/
/* Setup column headings */
/*------------------------------------------------------------*/
RSLTNO(FF_ROW_NUM) = 'FirstName EducLvl' ;
FF_ROW_NUM = FF_ROW_NUM + 1 ;
RSLTNO(FF_ROW_NUM) = '=========== =======' ;
FF_ROW_NUM = FF_ROW_NUM + 1 ;
END ;
RSLTNO(FF_ROW_NUM) = OUTPUT_ROW_STR;
END ; /* end of WHEN( CS_SUCCEED ) */
WHEN( CS_END_DATA )
DO ;
NO_MORE_ROWS = TRUE ;
MSG_TEXT_1 = 'All rows processing completed!' ;
MSG_TEXT_2 = 'Press Clear To Exit';
CALL DISP_DATA ;
END ; /* end of WHEN( CS_END_DATA ) */
WHEN( CS_FAIL )
DO ;
NO_MORE_ROWS = TRUE ;
NO_ERRORS_SW = FALSE ;
MSGSTR =
'CTBFETCH returned CS_FAIL ret_code' ;
CALL ERROR_OUT;
END ; /* end of WHEN( CS_FAIL ) */
WHEN( CS_ROW_FAIL )
DO ;
NO_MORE_ROWS = TRUE ;
NO_ERRORS_SW = FALSE ;
MSGSTR =
'CTBFETCH returned CS_ROW_FAIL ret_code' ;
CALL ERROR_OUT;
END ; /* end of WHEN( CS_ROW_FAIL ) */
WHEN( CS_CANCELLED )
DO ;
NO_MORE_ROWS = TRUE ;
NO_ERRORS_SW = FALSE ;
MSG1O = 'CTBFETCH returned CS_CANCELLED ret_code' ;
CALL ERROR_OUT;
END ; /* end of WHEN( CS_CANCELLED ) */
OTHERWISE
DO ;
NO_MORE_ROWS = TRUE ;
NO_ERRORS_SW = FALSE ;
MSGSTR =
'CTBFETCH returned Unknown ret_code' ;
CALL ERROR_OUT;
END ; /* end of OTHERWISE */
END ; /* end of SELECT( CSL_RC ) */
END FETCH_ROW_PROCESSING ;
CTBFETCH fetches result data. “Result data” is an umbrella term for the various types of data that a server can return to an application. These types of data include:
Regular rows
Return parameters, including both message parameters and RPC return parameters
Stored procedure status results
CTBFETCH is used to fetch all of these types of data.
Conceptually, result data is returned to an application in the form of one or more rows that make up a “result set.”
Regular row result sets can contain more than one row. For example, a regular row result set might contain a hundred rows. If array binding is specified for the data items in a regular row result set, then multiple rows can be fetched with a single call to CTBFETCH. The number of rows fetched are returned in the ROWS_READ argument.
Return parameters and status results, however, only contain a single row. For this reason, even if array binding is specified, only a single row of data is fetched.
CTBRESULTS specifies the type of result available in the RESULT_TYP variable. CTBRESULTS must indicate a result type of CS_ROW_RESULT, CS_PARAM_RESULT, or CS_STATUS_RESULT before an application calls CTBFETCH.
After calling CTBRESULTS, an application can:
Process the result set by binding the result items and fetching the data, using CTBFETCH (optionally preceded by CTBDESCRIBE and CTBBIND), or
Discard the result set, using CTBCANCEL.
If an application does not cancel a result set, it must completely process the result set by repeatedly calling CTBFETCH as long as CTBFETCH continues to indicate that rows are available.
The simplest way to do this is in a loop that terminates when CTBFETCH fails to return either CS_SUCCEED or CS_ROW_FAIL. After the loop terminates, an application can check the CTBFETCH final return code to find out what caused the termination.
Regular rows can be fetched from the server one row at a time, or several rows at once.
When fetching multiple rows, the number of rows to be fetched is indicated by the FMT_COUNT field in the DATAFMT structures used to bind the data items in the result set. Note that the FMT_COUNT field must have the same value for all CTBBIND calls for a result set.
If FMT_COUNT is 0 or 1, CTBFETCH fetches one row.
A return parameter result set contains either stored procedure return parameters or message parameters.
A return parameter result set consists of a single row with a number of columns equal to the number of return parameters.
A stored procedure return status result set consists of a single row with a single column, containing the status.
Related functions:
