Retrieve results

By running queries or CALL statements SQL can returns result sets to the application program. Running an SQL SELECT statement returns the selected rows in a result set. The SQLFetch API can then be used to sequentially retrieve the rows from the result set into the application program's internal storage.

You also may issue a SELECT statement where you do not specify what columns you want returned. For example, SELECT * FROM RWM.DBFIL selects all columns in the table. You may not know what columns or how many columns will be returned. You can use the SQLNumResultCols API to retrieve the number of result columns in the result set. The SQLDescribeCol API can be used to obtain a description of the attributes of each column in the result set.

SQLNumResultCols

Returns the number of columns in a result set.

• A storage buffer that receives the information is passed as a parameter.

 SQLSMALLINT nResultCols;

rc = SQLNumResultCols(hstmt, &nResultCols);

SQLDescribeCol

Returns the result descriptor for one column in a result set.

• Column name
• Column type
• Column size

  This is used with SQLNumResultCols to retrieve information about the columns returned. Using this approach, as opposed to hard coding the information in the program, makes for more flexible programs.

  The programmer first uses SQLNumResultCols to find out how many columns were returned in the result set by a select statement. Then a loop is set up to use SQLDescribeCol to retrieve information about each column.

In C, this statement is coded:

 
SQLCHAR szColName[51];
SQLSMALLINT lenColName, colSQLtype, scale, nullable;
SQLUSMALLINT colNum = 1;
SQLUINTEGER cbColDef;

rc = SQLDescribeCol(hstmt, colNum, szColName, sizeof(szColName),
                                  &lenColName, &colSQLtype, &cbColDef, &scale, &nullable);

SQLBindCol

Assigns the storage and data type for a column in a result set:

• Storage buffer that receives the information.
• Length of storage buffer.
• Data type conversion.

In C, this statement is coded:

SQLUSMALLINT colNum = 1;
SQLUINTEGER cbColDef;
SQLINTEGER idNum, indPtr, strlen_or_indPtr;
SQLCHAR szIDName[51];

colNum = 1;
rc = SQLBindCol(hstmt, colNum, SQL_C_LONG, &idNum, sizeof(SQLINTEGER), &indPtr);
colNum = 2;
rc = SQLBindCol(hstmt, colNum, SQL_C_CHAR, szIDName, sizeof(szIDName), &strlen_or_indPtr);

Note: If you use this with Visual Basic, it is recommended that you use an array of Byte data type in place of String data types.

SQLFetch

Each time SQLFetch is called, the driver fetches the next row. Bound columns are stored in the locations specified. Data for unbound columns may be retrieved using SQLGetData.

In C, this statement is coded:

rc = SQLFetch(hstmt);

Visual Basic does not directly support pointers or fixed memory location ANSI character null-terminated strings. For this reason, it is best to use another method to bind Character and Binary parameters. One method is to convert Visual Basic String data types to/from an array of Byte data types and bind the array of Byte. Another method is to use the SQLGetData function instead of SQLBindCol.

SQLGetData

Retrieves data for unbound columns after a fetch. In this example, three columns are returned and SQLGetData is used to move them to the correct storage location.

In C, this statement is coded:

SQLCHAR szTheName[16], szCredit[2];
float iDiscount, iTax;

rc = SQLFetch(hstmt);     
rc = SQLGetData(hstmt, 1, SQL_C_CHAR, szTheName, 16, &strlen_or_indPtr);
rc = SQLGetData(hstmt, 2, SQL_C_FLOAT, &iDiscount, sizeof(float), &indPtr);
rc = SQLGetData(hstmt, 3, SQL_C_CHAR, szCredit, 2, &strlen_or_indPtr);
rc = SQLGetData(hstmt, 4, SQL_C_FLOAT, &iTax, sizeof(float), &indPtr);

          

In Visual Basic, this statement is coded:

     rc = SQLFetch(hStmt)
    If rc = SQL_NO_DATA_FOUND Then
        Call DisplayWarning("No record found!")
        rc = SQLCloseCursor(hStmt)
        If rc <> SQL_SUCCESS Then
            Call DspSQLDiagRec(SQL_HANDLE_STMT, hStmt, "Close cursor failed.")
        End If
    Else
        ' Reset lcbBuffer for the call to SQLGetData
        lcbBuffer = 0
        'Get part ID from the fetched record
        rc = SQLGetData(hStmt, 1, SQL_C_LONG, _
						lPartIDReceived, Len(lPartIDReceived), lcbBuffer)
        If rc <> SQL_SUCCESS And rc <> SQL_SUCCESS_WITH_INFO Then _
            Call DspSQLDiagRec(SQL_HANDLE_STMT, hStmt, _
							 "Problem getting data for PartID column")
        
        'Get part description from the fetched record
        rc = SQLGetData(hStmt, 2, SQL_C_CHAR, _
						szDescription(0), 257, lcbBuffer)
        If rc <> SQL_SUCCESS And rc <> SQL_SUCCESS_WITH_INFO Then _
            Call DspSQLDiagRec(SQL_HANDLE_STMT, hStmt, _
							"Problem getting data for PartDescription column")
  
        'Get part provider from the fetched record
        rc = SQLGetData(hStmt, 3, SQL_C_CHAR, _
						szProvider(0), 257, lcbBuffer)
        If rc <> SQL_SUCCESS And rc <> SQL_SUCCESS_WITH_INFO Then _
            Call DspSQLDiagRec(SQL_HANDLE_STMT, hStmt, _
							 "Problem getting data for PartProvider column")
        
        Call DisplayMessage("Record found!")
        rc = SQLCloseCursor(hStmt)
        If rc <> SQL_SUCCESS Then _
            Call DspSQLDiagRec(SQL_HANDLE_STMT, hStmt, "Close cursor failed.")
    End If