Osdb API example

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