To fetch a row of data, an application calls f90SQLFetch. A call to f90SQLFetch has the following form:
| call f90SQLFetch | (StatementHandle, iRet) |
where argument StatementHandle is the handle of a statement that has been executed. When subroutine f90SQLFetch is called, it returns a value in iRet, indicating whether the fetch was successful or not. A successful fetch returns the values stored in f90SQL constants SQL_SUCCESS or SQL_SUCCESS_WITH_INFO in iRet.
f90SQLFetch can be called with any kind of cursor, but it only moves the rowset cursor in a forward-only direction. f90SQLFetch advances the cursor to the next row and returns the data for any columns that were bound with calls to f90SQLBindCol. When the cursor reaches the end of the result set, f90SQLFetch returns SQL_NO_DATA in argument iRet.
The implementation of f90SQLFetch is driver-specific, but the general pattern is for the driver to retrieve the data for any bound columns from the data source, convert it according to the types of the bound variables, and place the converted data in those variables. If the driver cannot convert any data, f90SQLFetch returns an error code in iRet. The application can continue fetching rows, but the data for the current row is lost. What happens to the data for unbound columns depends on the driver, but most drivers either retrieve and discard it or never retrieve it at all.
The driver also sets the values of any length/indicator buffers that have been bound. If the data value for a column is NULL, the driver sets the corresponding length/indicator buffer to SQL_NULL_DATA. If the data value is not NULL, the driver sets the length/indicator buffer to the byte length of the data after conversion. If this length cannot be determined, as is sometimes the case with long data that is retrieved by more than one function call, the driver sets the length/indicator buffer to SQL_NO_TOTAL. Note that for fixed-length data types, such as integers and date structures, the byte length is the size of the data type.
For variable-length data, such as character and binary data, the driver checks the byte length of the converted data against the byte length of the buffer bound to the column; the buffer’s length is specified in the TargetVarBufferLength argument in f90SQLBindCol. If the byte length of the converted data is greater than the byte length of the buffer, the driver truncates the data to fit in the buffer, returns the untruncated length in the length/indicator buffer, returns SQL_SUCCESS_WITH_INFO in iRet, and places SQLSTATE 01004 (Data truncated) in the diagnostics. The only exception to this is if a variable-length bookmark is truncated when returned by f90SQLFetch, which returns SQLSTATE 22001 (String data, right truncated).
Fixed-length data is never truncated because the driver assumes that the size of the bound buffer is the size of the data type. Data truncation tends to be rare because the application usually binds a buffer large enough to hold the entire data value. It determines the necessary size from the metadata. However, the application might explicitly bind a buffer it knows to be too small. For example, it might retrieve and display the first 20 characters of a part description, or the first 100 characters of a long text column.
Character data returned by the driver is always null-terminated before it is returned to the application, even if it has been truncated. The null-termination character is not included in the returned byte length, but does require space in the bound buffer. This is an annoyance that Fortran programmer have to live with, because f90SQL does not automatically convert null-terminated strings put into a buffer by a call to f90SQLFetch. In addition to this, your Fortran character buffer must include the space for the null-terminator character. What this means is that when you bind a character column that is 2 bytes long in the resultset, to avoid truncation you must bind it to a character variable that is at least 3 bytes long. If you examine Example 5.4 you will note that although the column State in the record set contains only two characters, the variable that was bound to this column (Publishers%State) is 3 bytes long. This was done to allow for the null-character terminator. If the length of Publishers%State was only 2 characters, f90SQLFetch would return only the first letter of a state abbreviation in Publishers%State (the other character is used to put the null-terminator). The other problem with the method used by ODBC to return character strings is that Fortran does not recognize the null-terminator as an indicator of the end of a string, so Fortran functions like len, len_trim, trim, etc. will not work properly. To avoid this last problem, Fortran applications should use the subroutine f90SQLStrFormat before using a character variable whose content has been updated by f90SQLFetch (or any other fetching subroutine). If you forget to do this, your application will still work properly. However, Fortran functions like len_trim(), instead of returning the length of the trimmed string stored in the buffer, will return the full length of the buffer.
The application can restrict the number of rows in the result set by setting the SQL_ATTR_MAX_ROWS attribute before executing the statement that creates the result set. For example, the preview mode in an application used to format reports needs only enough data to display the first page of the report. By restricting the size of the result set, such a feature would run faster. This statement attribute is intended to reduce network traffic and might not be supported by all drivers.