ct_fetch

Description

Fetch result data.

Syntax

CS_RETCODE ct_fetch(cmd, type, offset, option,
           rows_read)
 
 CS_COMMAND *cmd;
 CS_INT                type;
 CS_INT                offset;
 CS_INT                option;
 CS_INT                 *rows_read;

Parameters

cmd: A pointer to the CS_COMMAND structure managing a client/server operation.

type: This parameter is currently unused and must be passed as CS_UNUSED to ensure compatibility with future versions of Client-Library.

offset: This parameter is currently unused and must be passed as CS_UNUSED to ensure compatibility with future versions of Client-Library.

option: This parameter is currently unused and must be passed as CS_UNUSED to ensure compatibility with future versions of Client-Library.

rows_read

A pointer to an integer variable. ct_fetch sets rows_read to the number of rows read by the ct_fetch call.

rows_read is an optional parameter intended for use by applications using array binding.

In asynchronous mode, *rows_read is not set until ct_fetch completes.

Returns

ct_fetch returns the following values:

**Table 3-42: ct_fetch return values**
Return value	Meaning
CS_SUCCEED	The routine completed successfully. ct_fetch places the number of rows read in rows_read. The application must continue to call ct_fetch*, as the result data is not yet completely fetched.
CS_END_DATA	All rows of the current result set have been fetched. The application should call ct_results to get the next result set.
CS_ROW_FAIL	A recoverable error occurred while fetching a row. The application must continue calling ct_fetch to keep retrieving rows, or can call ct_cancel to cancel the remaining results. When using array binding, CS_ROW_FAIL indicates a partial result is available in the bound arrays. ct_fetch sets row_count to indicate the number of rows transferred (including the row containing the error) and transfers no rows after that row. The next call to ct_fetch* will read rows starting with the row after the one where the error occurred. Recoverable errors include memory allocation failures and conversion errors (such as overflowing the destination buffer) that occur while copying row values to program variables. In the case of buffer-overflow errors, ct_fetch sets the corresponding indicator variable(s) to a value greater than 0. Indicator variables must have been specified in the application’s calls to ct_bind*.
CS_FAIL	The routine failed. ct_fetch places the number of rows fetched in rows_read. This number includes the failed row. Unless the routine failed due to application error (for example, bad parameters), additional result rows are not available. If ct_fetch* returns CS_FAIL, an application must call ct_cancel with type as CS_CANCEL_ALL before using the affected command structure to send another command. If ct_cancel returns CS_FAIL, the application must call ct_close(CS_FORCE_CLOSE) to force the connection closed.
CS_CANCELED	The current result set and any additional result sets have been canceled. Data is no longer available. ct_fetch places the number of rows fetched before the cancel occurred in *rows_read.
CS_PENDING	Asynchronous network I/O is in effect. See “Asynchronous programming”.
CS_BUSY	An asynchronous operation is already pending for this connection. See “Asynchronous programming”.

A common reason for a ct_fetch failure is that a program variable specified through ct_bind is not large enough for a fetched data item.

Examples

Example 1

        /* ex_fetch_data()*/

        CS_RETCODE CS_PUBLIC

         ex_fetch_data(cmd)

         CS_COMMAND  *cmd;

             CS_RETCODE    retcode;

             CS_INT        num_cols;

             CS_INT        i;

             CS_INT        j;

             CS_INT        row_count = 0;

             CS_INT         rows_read;

/*

             ** Determine the number of columns in this

             ** result set.

*/

             ...CODE DELETED.....

            /* Get column descriptions and bind columns */

             ...CODE DELETED.....

/*

             ** Fetch the rows.  Loop while ct_fetch() returns

             ** CS_SUCCEED or CS_ROW_FAIL

*/

             while (((retcode = ct_fetch(cmd, CS_UNUSED,

                 CS_UNUSED, CS_UNUSED,&rows_read)) ==

                 CS_SUCCEED) || (retcode == CS_ROW_FAIL))

/*

                 ** Increment our row count by the number of

                 ** rows just fetched.

*/

                 row_count = row_count + rows_read;

                /* Check if we hit a recoverable error */

                 if (retcode == CS_ROW_FAIL)

                     fprintf(stdout, "Error on row %d.\n",

                         row_count);

/*

                 ** We have a row.  Loop through the columns

                 ** displaying the column values.

*/

                 for (i = 0; i < num_cols; i++)

                     ...CODE DELETED.....

                 fprintf(stdout, "\n");

            /* Free allocated space */

             ...CODE DELETED.....

/*

             ** We're done processing rows.  Let's check the

             ** final return value of ct_fetch().

*/

             switch ((int)retcode)

                 case CS_END_DATA:

                     /* Everything went fine */

                     fprintf(stdout, "All done processing

                         rows.\n");

                     retcode = CS_SUCCEED;

                     break;

                case CS_FAIL:

                     /* Something terrible happened */

                     ex_error("ex_fetch_data: ct_fetch()

                         failed");

                     return retcode;

                     break;

                default:

                     /* We got an unexpected return value */

                     ex_error("ex_fetch_data: ct_fetch() \

                         returned an unexpected retcode");

                     return retcode;

                     break;

            return retcode;

This code excerpt is from the exutils.c example program.

Usage

Result data is an umbrella term for all the types of data that a server can return to an application. The types of data include:
- Regular rows
- Cursor rows
- Return parameters, such as message parameters, stored procedure return parameters, extended error data, and registered procedure notification parameters.
- Stored procedure status values
- Compute rows
ct_fetch 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 and cursor row result sets can contain more than one row. For example, a regular row result set might contain a hundred rows.

If array binding has been specified for the data items in a regular row or cursor row result set, then multiple rows can be fetched with a single call to ct_fetch.

Asynchronous applications should always specify array binding to fetch multiple rows at a time. This ensures that the application has sufficient time in which to accomplish something before Client-Library calls the application’s completion callback routine.

Return parameter, status number, and compute-row result sets, however, only contain a single “row.” For this reason, even if array binding is specified, only a single row of data is fetched.
ct_results sets *result_type to indicate the type of result available. ct_results must indicate a result type of CS_ROW_RESULT, CS_CURSOR_RESULT, CS_PARAM_RESULT, CS_STATUS_RESULT, or CS_COMPUTE_RESULT before an application calls ct_fetch.
After ct_results returns a result_type that indicates fetchable results, an application can:
- Retrieve the result row(s) by binding the result items and fetching the data. A typical application calls ct_res_info to get the number of data items, ct_describe to get data descriptions, ct_bind to bind result items, ct_fetch to fetch result rows, and ct_get_data, if the result set contains large text or image values.
- Retrieve result rows using ct_dyndesc or ct_dynsqlda with ct_fetch. Typically, only applications that execute dynamic SQL commands use these routines, but ct_dyndesc or ct_dynsqlda can be used to process fetchable data returned by any command type.
- Discard the result rows using ct_cancel for non-cursor results and ct_cursor(CS_CURSOR_CLOSE) for cursor results.
If an application does not cancel a result set, it must completely process the result set by calling ct_fetch as long as ct_fetch continues to indicate that rows are available.

The simplest way to do this is in a loop that terminates when ct_fetch fails to return either CS_SUCCEED or CS_ROW_FAIL. After the loop terminates, an application can use a switch-type statement against ct_fetch’s final return code to find out what caused the termination.

If a result set contains zero rows, an application’s first ct_fetch call will return CS_END_DATA.

An application must call ct_fetch in a loop even if a result set contains only a single row. An application must call ct_fetch until it fails to return either CS_SUCCEED or CS_ROW_FAIL.
If a conversion error occurs when retrieving a result item, the rest of the items in the row are retrieved. If truncation occurs, the indicator variable, if any, provided in the application’s ct_bind call for this item is set to the actual length of the result data.

ct_fetch returns CS_ROW_FAIL if a conversion or truncation error occurs.

Fetching regular rows and cursor rows

Regular rows and cursor rows can be fetched one row at a time, or several rows at once.
An application indicates the number of rows to be fetched per ct_fetch call using the datafmt−>count field in its ct_bind calls that bind result columns to program variables. If datafmt−>count is 0 or 1, each call to ct_fetch fetches one row. If datafmt−>count is greater than one, then array binding is considered to be in effect and each call to ct_fetch fetches datafmt−>count rows. Note that datafmt−>count must have the same value for all ct_bind calls for a result set.
When fetching multiple rows, if a conversion error occurs on one of the rows, no more rows are retrieved by this ct_fetch call.

Fetching return parameters

Several types of data can be returned to an application as a parameter result set, including:
- Stored procedure return parameters
- Message parameters
Extended error data and registered procedure notification parameters are also returned as parameter result sets, but since an application does not call ct_results to process these types of data, the application never sees a result type of CS_PARAM_RESULT. Instead, the row of parameters is simply available to be fetched after the application retrieves the CS_COMMAND structure containing the data.
A return parameter result set consists of a single row with a number of columns equal to the number of return parameters.

Fetching a return status

A stored procedure return status result set consists of a single row with a single column, the status number.

Fetching compute rows

Compute rows result from the compute clause of a select statement.
A compute row result set consists of a single row with a number of columns equal to the number of aggregate operators in the compute clause that generated the row.
Each compute row is considered to be a distinct result set.