64-bit ODBC considerations

When you use an ODBC function like SQLBindCol, SQLBindParameter, or SQLGetData, some of the parameters are typed as SQLLEN or SQLULEN in the function prototype. Depending on the date of the Microsoft ODBC API Reference documentation that you are looking at, you might see the same parameters described as SQLINTEGER or SQLUINTEGER.

SQLLEN and SQLULEN data items are 64 bits in a 64-bit ODBC application and 32 bits in a 32-bit ODBC application. SQLINTEGER and SQLUINTEGER data items are 32 bits on all platforms.

To illustrate the problem, the following ODBC function prototype was excerpted from an older copy of the Microsoft ODBC API Reference.

SQLRETURN SQLGetData(
     SQLHSTMT     StatementHandle,
     SQLUSMALLINT ColumnNumber,
     SQLSMALLINT  TargetType,
     SQLPOINTER   TargetValuePtr,
     SQLINTEGER   BufferLength,
     SQLINTEGER  *StrLen_or_IndPtr);

Compare this with the actual function prototype found in sql.h in Microsoft Visual Studio version 8.

SQLRETURN  SQL_API SQLGetData(
    SQLHSTMT      StatementHandle,
    SQLUSMALLINT  ColumnNumber, 
    SQLSMALLINT   TargetType,
    SQLPOINTER    TargetValue, 
    SQLLEN        BufferLength,
    SQLLEN       *StrLen_or_Ind);

As you can see, the BufferLength and StrLen_or_Ind parameters are now typed as SQLLEN, not SQLINTEGER. For the 64-bit platform, these are 64-bit quantities, not 32-bit quantities as indicated in the Microsoft documentation.

To avoid issues with cross-platform compilation, SQL Anywhere provides its own ODBC header files. For Windows platforms, you should include the ntodbc.h header file. For Unix platforms such as Linux, you should include the unixodbc.h header file. Use of these header files ensures compatibility with the corresponding SQL Anywhere ODBC driver for the target platform.

The following table lists some common ODBC types that have the same or different storage sizes on 64-bit and 32-bit platforms.

ODBC API 64-bit platform 32-bit platform
SQLINTEGER 32 bits 32 bits
SQLUINTEGER 32 bits 32 bits
SQLLEN 64 bits 32 bits
SQLULEN 64 bits 32 bits
SQLSETPOSIROW 64 bits 16 bits
SQL_C_BOOKMARK 64 bits 32 bits
BOOKMARK 64 bits 32 bits

If you declare data variables and parameters incorrectly, then you may encounter incorrect software behavior.

The following table summarizes the ODBC API function prototypes that have changed with the introduction of 64-bit support. The parameters that are affected are noted. The parameter name as documented by Microsoft is shown in parentheses when it differs from the actual parameter name used in the function prototype. The parameter names are those used in the Microsoft Visual Studio version 8 header files.

ODBC API Parameter (Documented Parameter Name)
SQLBindCol

SQLLEN BufferLength

SQLLEN *Strlen_or_Ind

SQLBindParam

SQLULEN LengthPrecision

SQLLEN *Strlen_or_Ind

SQLBindParameter

SQLULEN cbColDef (ColumnSize)

SQLLEN cbValueMax (BufferLength)

SQLLEN *pcbValue (Strlen_or_IndPtr)

SQLColAttribute

SQLLEN *NumericAttribute

SQLColAttributes

SQLLEN *pfDesc

SQLDescribeCol

SQLULEN *ColumnSize (ColumnSizePtr)

SQLDescribeParam

SQLULEN *pcbParamDef (ParameterSizePtr)

SQLExtendedFetch

SQLLEN irow (FetchOffset)

SQLULEN *pcrow (RowCountPtr)

SQLFetchScroll

SQLLEN FetchOffset

SQLGetData

SQLLEN BufferLength

SQLLEN *Strlen_or_Ind (Strlen_or_IndPtr)

SQLGetDescRec

SQLLEN *Length (LengthPtr)

SQLParamOptions

SQLULEN crow,

SQLULEN *pirow

SQLPutData

SQLLEN Strlen_or_Ind

SQLRowCount

SQLLEN *RowCount (RowCountPtr)

SQLSetConnectOption

SQLULEN Value

SQLSetDescRec

SQLLEN Length

SQLLEN *StringLength (StringLengthPtr)

SQLLEN *Indicator (IndicatorPtr)

SQLSetParam

SQLULEN LengthPrecision

SQLLEN *Strlen_or_Ind (Strlen_or_IndPtr)

SQLSetPos

SQLSETPOSIROW irow (RowNumber)

SQLSetScrollOptions

SQLLEN crowKeyset

SQLSetStmtOption

SQLULEN Value

Some values passed into and returned from ODBC API calls through pointers have changed to accommodate 64-bit applications. For example, the following values for the SQLSetStmtAttr and SQLSetDescField functions are no longer SQLINTEGER/SQLUINTEGER. The same rule applies to the corresponding parameters for the SQLGetStmtAttr and SQLGetDescField functions.

ODBC API Type for Value/ValuePtr variable
SQLSetStmtAttr(SQL_ATTR_FETCH_BOOKMARK_PTR)

SQLLEN * value

SQLSetStmtAttr(SQL_ATTR_KEYSET_SIZE)

SQLULEN value

SQLSetStmtAttr(SQL_ATTR_MAX_LENGTH)

SQLULEN value

SQLSetStmtAttr(SQL_ATTR_MAX_ROWS)

SQLULEN value

SQLSetStmtAttr(SQL_ATTR_PARAM_BIND_OFFSET_PTR)

SQLULEN * value

SQLSetStmtAttr(SQL_ATTR_PARAMS_PROCESSED_PTR)

SQLULEN * value

SQLSetStmtAttr(SQL_ATTR_PARAMSET_SIZE)

SQLULEN value

SQLSetStmtAttr(SQL_ATTR_ROW_ARRAY_SIZE)

SQLULEN value

SQLSetStmtAttr(SQL_ATTR_ROW_BIND_OFFSET_PTR)

SQLULEN * value

SQLSetStmtAttr(SQL_ATTR_ROW_NUMBER)

SQLULEN value

SQLSetStmtAttr(SQL_ATTR_ROWS_FETCHED_PTR)

SQLULEN * value

SQLSetDescField(SQL_DESC_ARRAY_SIZE)

SQLULEN value

SQLSetDescField(SQL_DESC_BIND_OFFSET_PTR)

SQLLEN * value

SQLSetDescField(SQL_DESC_ROWS_PROCESSED_PTR)

SQLULEN * value

SQLSetDescField(SQL_DESC_DISPLAY_SIZE)

SQLLEN value

SQLSetDescField(SQL_DESC_INDICATOR_PTR)

SQLLEN * value

SQLSetDescField(SQL_DESC_LENGTH)

SQLLEN value

SQLSetDescField(SQL_DESC_OCTET_LENGTH)

SQLLEN value

SQLSetDescField(SQL_DESC_OCTET_LENGTH_PTR)

SQLLEN * value

Note that the current Microsoft ODBC API Reference for SQLSetConnectAttr/SQLGetConnectAttr describes the numeric attribute values as SQLUINTEGER. Note that the type of these attribute values differs from the SQLSetStmtAttr/SQLGetStmtAttr attribute values which are described as SQLULEN.

Caution

There is one connection attribute, SQL_ATTR_ODBC_CURSORS, that is handled by the Microsoft ODBC Driver Manager. Although the Microsoft ODBC API Reference says the attribute is a SQLUINTEGER value specifying how the Driver Manager uses the ODBC cursor library, the 64-bit version of the Driver Manager returns a 64-bit SQLULEN value for SQLGetConnectAttr.

__int64 datavalue = 0x1234567812345678;
rc = SQLGetConnectAttr( hdbc, attr, &datavalue, 0, 0 );

After the call to SQLGetConnectAttr, the value of datavalue is 0x0000000000000002 which demonstrates that a 64-bit value is stored. Care must be exercised to avoid this Microsoft Driver Manager bug. Other attribute values are handled by the SQL Anywhere ODBC driver which returns SQLUINTEGER values as described by the ODBC API Reference.

For more information, see the Microsoft article "ODBC 64-Bit API Changes in MDAC 2.7" at [external link] http://support.microsoft.com/kb/298678.