Sqlfetch-load the next row
Purpose
Sqlfetch ()Forward the cursor to the next row of the result set and retrieve any bound columns.
Sqlfetch ()Can be used to receive data directly to your useSqlbindcol ()In the specified variable, you can also callSqlgetdata ()To receive columns separately. If conversion is indicated when a column is bound, callSqlfetch ()The data is also converted.
Syntax
SQLRETURN SQLFetch (SQLHSTMT hstmt);
Function independent variable
Table 59. sqlfetch Independent Variables
Data Type |
Independent Variable |
Use |
Description |
Sqlhstmt |
Hstmt |
Input |
Statement handle |
Usage
Only when the most recent pairHstmtThe SELECT statement can be called only when it is executed.Sqlfetch ().
UseSqlbindcol ()The number of bound Application variables must not exceed the number of columns in the result set. OtherwiseSqlfetch ()Will fail.
If you have not calledSqlbindcol ()To bind any columnSqlfetch ()Instead of Returning data to the application, the cursor only advances. In this case, you can callSqlgetdata ()To obtain all columns individually. WhenSqlfetch ()When the cursor advances to the next row, data in the unbound column is discarded.
If any bound variable is not large enough, it cannot be stored.Sqlfetch ()The returned data is truncated. If the character data is truncated, SQL _success_with_info is returned and an sqlstate indicating that the data has been truncated is generated.Sqlbindcol ()Latency output independent variablePcbvalueContains the actual length of the column data retrieved from the server. The application should set the output length and input length (fromSqlbindcol ()OfPcbvalueAndCbvaluemaxTo determine which character columns have been truncated.
If truncation involves the right digit of the decimal point, the truncation of the numeric data type is not reported. If truncation occurs on the left of the decimal point, an error is returned (see diagnostics ).
The Truncation of the graphic data type is considered to be the same as that of the character data type. There is only one difference, that is, fillingRgbvalueBuffer to make it less than or equalSqlbindcol ()Specified inCbvaluemaxThe nearest double byte. The graphic data transmitted between the DB2 udb cli and the application will never be terminated with null.
CallSqlfreestmt ()To close the cursor and discard other data and associated resources.
Return code
- SQL _success
- SQL _success_with_info
- SQL _error
- SQL _invalid_handle
- SQL _no_data_found
If no rows exist in the result set, or the previousSqlfetch ()If the call has loaded all rows from the result set, SQL _no_data_found is returned.
Diagnosis
Table 60. sqlfetch sqlstate
Sqlstate |
Description |
Description |
01004 |
Truncated data |
Data returned for one or more columns has been truncated. The string value is truncated on the right side. (If no error occurs, SQL _success_with_info is returned .) |
Hy001 |
Memory Allocation failed |
The driver cannot allocate the memory necessary to support the execution or completion of this function. |
Hy010 |
Function Order Error |
The specifiedHstmtNot in the executed status. This function is called without calling sqlexecute or sqlexecdirect first. |
Hy013* |
Memory Management Problems |
The driver cannot access the memory required to support the execution or completion of this function. |
Example
For information about code examples, see the Code does not guarantee declarations.
/*************************************************************************** file = fetch.c**** Example of executing an SQL statement.** SQLBindCol & SQLFetch is used to retrive data from the result set** directly into application storage.**** Functions used:**** SQLAllocConnect SQLFreeConnect** SQLAllocEnv SQLFreeEnv** SQLAllocStmt SQLFreeStmt** SQLConnect SQLDisconnect**** SQLBindCol SQLFetch** SQLTransact SQLExecDirect** SQLError****************************************************************************/ #include <stdio.h>#include <string.h>#include "sqlcli.h" #define MAX_STMT_LEN 255 int initialize(SQLHENV *henv, SQLHDBC *hdbc); int terminate(SQLHENV henv, SQLHDBC hdbc); int print_error (SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt); int check_error (SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, SQLRETURN frc); /********************************************************************* main** - initialize** - terminate*******************************************************************/int main(){ SQLHENV henv; SQLHDBC hdbc; SQLCHAR sqlstmt[MAX_STMT_LEN + 1]=""; SQLRETURN rc; rc = initialize(&henv, &hdbc); if (rc == SQL_ERROR) return(terminate(henv, hdbc)); {SQLHSTMT hstmt; SQLCHAR sqlstmt[]="SELECT deptname, location from org where division = 'Eastern'"; SQLCHAR deptname[15], location[14]; SQLINTEGER rlength; rc = SQLAllocStmt(hdbc, &hstmt); if (rc != SQL_SUCCESS ) check_error (henv, hdbc, SQL_NULL_HSTMT, rc); rc = SQLExecDirect(hstmt, sqlstmt, SQL_NTS); if (rc != SQL_SUCCESS ) check_error (henv, hdbc, hstmt, rc); rc = SQLBindCol(hstmt, 1, SQL_CHAR, (SQLPOINTER) deptname, 15, &rlength); if (rc != SQL_SUCCESS ) check_error (henv, hdbc, hstmt, rc); rc = SQLBindCol(hstmt, 2, SQL_CHAR, (SQLPOINTER) location, 14, &rlength); if (rc != SQL_SUCCESS ) check_error (henv, hdbc, hstmt, rc); printf("Departments in Eastern division:/n"); printf("DEPTNAME Location/n"); printf("-------------- -------------/n"); while ((rc = SQLFetch(hstmt)) == SQL_SUCCESS) { printf("%-14.14s %-13.13s /n", deptname, location); } if (rc != SQL_NO_DATA_FOUND ) check_error (henv, hdbc, hstmt, rc); rc = SQLFreeStmt(hstmt, SQL_DROP); if (rc != SQL_SUCCESS ) check_error (henv, hdbc, SQL_NULL_HSTMT, rc); } rc = SQLTransact(henv, hdbc, SQL_COMMIT); if (rc != SQL_SUCCESS ) check_error (henv, hdbc, SQL_NULL_HSTMT, rc); terminate(henv, hdbc); return (0);}/* end main */ /********************************************************************* initialize** - allocate environment handle** - allocate connection handle** - prompt for server, user id, & password** - connect to server*******************************************************************/ int initialize(SQLHENV *henv, SQLHDBC *hdbc){SQLCHAR server[SQL_MAX_DSN_LENGTH], uid[30], pwd[30];SQLRETURN rc; rc = SQLAllocEnv (henv); /* allocate an environment handle */ if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); rc = SQLAllocConnect (*henv, hdbc); /* allocate a connection handle */ if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); printf("Enter Server Name:/n"); gets(server); printf("Enter User Name:/n"); gets(uid); printf("Enter Password Name:/n"); gets(pwd); if (uid[0] == '/0') { rc = SQLConnect (*hdbc, server, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS); if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); } else { rc = SQLConnect (*hdbc, server, SQL_NTS, uid, SQL_NTS, pwd, SQL_NTS); if (rc != SQL_SUCCESS ) check_error (*henv, *hdbc, SQL_NULL_HSTMT, rc); } return(SQL_SUCCESS);}/* end initialize */ /********************************************************************* terminate** - disconnect** - free connection handle** - free environment handle*******************************************************************/int terminate(SQLHENV henv, SQLHDBC hdbc){SQLRETURN rc; rc = SQLDisconnect (hdbc); /* disconnect from database */ if (rc != SQL_SUCCESS ) print_error (henv, hdbc, SQL_NULL_HSTMT); rc = SQLFreeConnect (hdbc); /* free connection handle */ if (rc != SQL_SUCCESS ) print_error (henv, hdbc, SQL_NULL_HSTMT); rc = SQLFreeEnv (henv); /* free environment handle */ if (rc != SQL_SUCCESS ) print_error (henv, hdbc, SQL_NULL_HSTMT); return(rc);}/* end terminate */ /********************************************************************* - print_error - call SQLError(), display SQLSTATE and message*******************************************************************/ int print_error (SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt){SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];SQLINTEGER sqlcode;SQLSMALLINT length; while ( SQLError(henv, hdbc, hstmt, sqlstate, &sqlcode, buffer, SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS ) { printf("/n **** ERROR *****/n"); printf(" SQLSTATE: %s/n", sqlstate); printf("Native Error Code: %ld/n", sqlcode); printf("%s /n", buffer); }; return ( SQL_ERROR);} /* end print_error */ /********************************************************************* - check_error - call print_error(), checks severity of return code*******************************************************************/int check_error (SQLHENV henv, SQLHDBC hdbc, SQLHSTMT hstmt, SQLRETURN frc){SQLRETURN rc; print_error(henv, hdbc, hstmt); switch (frc){ case SQL_SUCCESS : break; case SQL_ERROR : case SQL_INVALID_HANDLE: printf("/n ** FATAL ERROR, Attempting to rollback transaction **/n"); rc = SQLTransact(henv, hdbc, SQL_ROLLBACK); if (rc != SQL_SUCCESS) printf("Rollback Failed, Exiting application/n"); else printf("Rollback Successful, Exiting application/n"); terminate(henv, hdbc); exit(frc); break; case SQL_SUCCESS_WITH_INFO : printf("/n ** Warning Message, application continuing/n"); break; case SQL_NO_DATA_FOUND : printf("/n ** No Data Found ** /n"); break; default : printf("/n ** Invalid Return Code ** /n"); printf(" ** Attempting to rollback transaction **/n"); SQLTransact(henv, hdbc, SQL_ROLLBACK); terminate(henv, hdbc); exit(frc); break; } return(SQL_SUCCESS); } /* end check_error */
Reference
- Sqlbindcol-bind a column to an application variable
- Sqlexecute-execution statement
- Sqlexecdirect-directly execute the statement
- Sqlgetcol-one of the rows in the search result set
- Sqlfetchscroll-fetch from scroll cursor