| Specification: | DB2 CLI 2.1 | ODBC 1.0 |
SQLMoreResults() determines whether there is more information available on the statement handle which has been associated with:
Syntax
SQLRETURN SQLMoreResults (SQLHSTMT StatementHandle); /* hstmt */
Function Arguments
Table 124. SQLMoreResults Arguments
| Data Type | Argument | Use | Description |
|---|---|---|---|
| SQLHSTMT | StatementHandle | input | Statement handle. |
Usage
This function is used to return multiple results set in a sequential manner upon the execution of:
Refer to Using Arrays to Input Parameter Values and Returning Result Sets from Stored Procedures for more information.
After completely processing the first result set, the application can call SQLMoreResults() to determine if another result set is available. If the current result set has unfetched rows, SQLMoreResults() discards them by closing the cursor and, if another result set is available, returns SQL_SUCCESS.
If all the result sets have been processed, SQLMoreResults() returns SQL_NO_DATA_FOUND.
If SQLFreeStmt() is called with the SQL_CLOSE option, or SQLFreeHandle() is called with HandleType set to SQL_HANDLE_STMT, all pending result sets on this statement handle are discarded.
Return Codes
Diagnostics
Table 125. SQLMoreResults SQLSTATEs
| SQLSTATE | Description | Explanation |
|---|---|---|
| 40003 08S01 | Communication link failure. | The communication link between the application and data source failed before the function completed. |
| 58004 | Unexpected system failure. | Unrecoverable system error. |
| HY001 | Memory allocation failure. | DB2 CLI is unable to allocate memory required to support execution or completion of the function. |
| HY010 | Function sequence error. | The function was called while in a data-at-execute
(SQLParamData(), SQLPutData()) operation.
The function was called while within a BEGIN COMPOUND and END COMPOUND SQL operation. |
| HY013 | Unexpected memory handling error. | DB2 CLI was unable to access memory required to support execution or completion of the function. |
| HYT00 | Timeout expired. | The timeout period expired before the data source returned the result set. Timeouts are only supported on non-multitasking systems such as Windows 3.1 and Macintosh System 7. The timeout period can be set using the SQL_ATTR_QUERY_TIMEOUT attribute for SQLSetConnectAttr(). |
In addition SQLMoreResults() can return the SQLSTATEs associated with SQLExecute().
Restrictions
The ODBC specification of SQLMoreResults() also allow counts associated with the execution of parameterized INSERT, UPDATE, and DELETE statements with arrays of input parameter values to be returned. However, DB2 CLI does not support the return of such count information.
/* From CLI sample ordrep.c */
/* ... */
SQLCHAR * stmt =
/* Common Table expression (or Define Inline View) */
"WITH order (ord_num, cust_num, prod_num, quantity, amount) AS ( "
"SELECT c.ord_num, c.cust_num, l.prod_num, l.quantity, "
"price(char(p.price, '.'), p.units, char(l.quantity, '.')) "
"FROM ord_cust c, ord_line l, product p "
"WHERE c.ord_num = l.ord_num "
"AND l.prod_num = p.prod_num "
"AND cast (cust_num as integer) = ? "
"), "
"totals (ord_num, total) AS ( "
"SELECT ord_num, sum(decimal(amount, 10, 2)) "
"FROM order GROUP BY ord_num "
") "
/* The 'actual' SELECT from the inline view */
"SELECT order.ord_num, cust_num, prod_num, quantity, "
"DECIMAL(amount,10,2) amount, total "
"FROM order, totals "
"WHERE order.ord_num = totals.ord_num" ;
/* Array of customers to get list of all orders for */
SQLINTEGER Cust[] = {
10, 20, 30, 40, 50, 60, 70, 80, 90, 100,
110, 120, 130, 140, 150, 160, 170, 180, 190, 200,
210, 220, 230, 240, 250,
} ;
/* Row-Wise (Includes buffer for both column data and length) */
typedef struct {
SQLINTEGER Ord_Num_L ;
SQLINTEGER Ord_Num ;
SQLINTEGER Cust_Num_L ;
SQLINTEGER Cust_Num ;
SQLINTEGER Prod_Num_L ;
SQLINTEGER Prod_Num ;
SQLINTEGER Quant_L ;
SQLDOUBLE Quant ;
SQLINTEGER Amount_L ;
SQLDOUBLE Amount ;
SQLINTEGER Total_L ;
SQLDOUBLE Total ;
} ord_info ;
ord_info ord_array[row_array_size] ;
SQLUINTEGER num_rows_fetched ;
SQLUSMALLINT row_status_array[row_array_size], i, j ;
/* ... */
/* Get details and total for each order Row-Wise */
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_PARAMSET_SIZE,
( SQLPOINTER ) row_array_size,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_LONG,
SQL_INTEGER,
0,
0,
Cust,
0,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLExecDirect( hstmt, stmt, SQL_NTS ) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* SQL_ROWSET_SIZE sets the max number of result rows to fetch each time */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_ARRAY_SIZE,
( SQLPOINTER ) row_set_size,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Set Size of One row, Used for Row-Wise Binding Only */
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_BIND_TYPE,
( SQLPOINTER ) sizeof( ord_info ) ,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROW_STATUS_PTR,
( SQLPOINTER ) row_status_array,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLSetStmtAttr( hstmt,
SQL_ATTR_ROWS_FETCHED_PTR,
( SQLPOINTER ) &num_rows_fetched,
0
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Bind column 1 to the Ord_num Field of the first row in the array */
rc = SQLBindCol( hstmt,
1,
SQL_C_LONG,
( SQLPOINTER ) & ord_array[0].Ord_Num,
0,
&ord_array[0].Ord_Num_L
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Bind remaining columns ... */
/* ... */
/*
NOTE: This sample assumes that an order will never have more
rows than row_set_size. A check should be added below to call
SQLExtendedFetch multiple times for each result set.
*/
while ( SQLFetchScroll( hstmt, SQL_FETCH_NEXT, 0 ) != SQL_NO_DATA ) {
printf( "**************************************\n" ) ;
printf( "Orders for Customer: %ld\n", ord_array[0].Cust_Num ) ;
printf( "**************************************\n" ) ;
i = 0 ;
while ( i < num_rows_fetched ) {
if ( row_status_array[i] == SQL_ROW_SUCCESS ||
row_status_array[i] == SQL_ROW_SUCCESS_WITH_INFO
) {
printf( "\nOrder #: %ld\n", ord_array[i].Ord_Num ) ;
printf( " Product Quantity Price\n" ) ;
printf( " -------- ---------------- ------------\n" ) ;
j = i ;
while ( ord_array[j].Ord_Num == ord_array[i].Ord_Num ) {
printf( " %8ld %16.7lf %12.2lf\n",
ord_array[i].Prod_Num,
ord_array[i].Quant,
ord_array[i].Amount
) ;
i++ ;
if ( i >= num_rows_fetched ) break ;
if ( row_status_array[i] != SQL_ROW_SUCCESS )
if ( row_status_array[i] != SQL_ROW_SUCCESS_WITH_INFO )
break ;
}
printf( " ============\n" ) ;
printf( " %12.2lf\n",
ord_array[j].Total
) ;
}
else i++ ;
}
}
References