Retrieving query results in CLI applications

Retrieving query results is part of the larger task of processing transactions in CLI applications. Retrieving query results involves binding application variables to columns of a result set and then fetching the rows of data into the application variables. A typical query is the SELECT statement.

To retrieve each row of the result set:

1. Optional: Determine the structure of the result set, number of columns, and column types and lengths by calling SQLNumResultCols() and SQLDescribeCol().
   Note: Performing this step can reduce performance if done before the query has been executed, because it forces CLI to describe the query's columns. Information about the result set's columns is available after successful execution, and describing the result set does not incur any additional resource usage if the describe is performed after successful execution.
2. Bind an application variable to each column of the result set, by calling SQLBindCol(), ensuring that the variable type matches the column type.
   For example:

     struct
     {
       SQLINTEGER ind;
       SQLSMALLINT val;
     }
     deptnumb; /* variable to be bound to the DEPTNUMB column */
   
     struct
     {
       SQLINTEGER ind;
       SQLCHAR val[15];
     }
     location; /* variable to be bound to the LOCATION column */
     
     /* ... */
     
     /* bind column 1 to variable */
     cliRC = SQLBindCol(hstmt, 1, SQL_C_SHORT, &deptnumb.val, 0,
                        &deptnumb.ind);
     STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
   
     /* bind column 2 to variable */
     cliRC = SQLBindCol(hstmt, 2, SQL_C_CHAR, location.val, 15,
                        &location.ind);
     STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
   

   The application can use the information obtained in step 1 to determine an appropriate C data type for the application variable and to allocate the maximum storage the column value could occupy. The columns are bound to deferred output arguments, which means the data is written to these storage locations when it is fetched.

   Important: Do not de-allocate or discard variables used for deferred output arguments between the time the application binds them to columns of the result set and the time CLI writes to these arguments.
3. Repeatedly fetch the row of data from the result set by calling SQLFetch() until SQL_NO_DATA_FOUND is returned.
   For example:

     /* fetch each row and display */
     cliRC = SQLFetch(hstmt);
   
     if (cliRC == SQL_NO_DATA_FOUND)
     {
       printf("\n  Data not found.\n");
     }
     while (cliRC != SQL_NO_DATA_FOUND)
     {
       printf(" %-8d %-14.14s \n", deptnumb.val, location.val);   
   
       /* fetch next row */
       cliRC = SQLFetch(hstmt);
     }

   SQLFetchScroll() can also be used to fetch multiple rows of the result set into an array.

   If data conversion was required for the data types specified on the call to SQLBindCol(), the conversion will occur when SQLFetch() is called.

4. Optional: Retrieve columns that were not previously bound by calling SQLGetData() after each successful fetch.
   You can retrieve all unbound columns this way. For example:

     /* fetch each row and display */
     cliRC = SQLFetch(hstmt);
   
     if (cliRC == SQL_NO_DATA_FOUND)
     {
       printf("\n  Data not found.\n");
     }
     while (cliRC != SQL_NO_DATA_FOUND)
     {
       /* use SQLGetData() to get the results */
       /* get data from column 1 */
       cliRC = SQLGetData(hstmt,
                          1,
                          SQL_C_SHORT,
                          &deptnumb.val,
                          0,
                          &deptnumb.ind);
       STMT_HANDLE_CHECK(hstmt, hdbc, cliRC);
   
       /* get data from column 2 */
       cliRC = SQLGetData(hstmt,
                          2,
                          SQL_C_CHAR,
                          location.val,
                          15,
                          &location.ind);
   
       /* display the data */
       printf("  %-8d %-14.14s \n", deptnumb.val, location.val);  
   
       /* fetch the next row */
       cliRC = SQLFetch(hstmt);
     }

   Note: Applications perform better if columns are bound, rather than having them retrieved as unbound columns using SQLGetData(). However, an application may be constrained in the amount of long data it can retrieve and handle at one time. If this is a concern, then SQLGetData() may be the better choice.