[R-sig-DB] NULL data not mapped to NA with RODBC on 64-bit Mac OS X

[Harlan Harris]

Hello,

I seem to have hit a bug in RODBC on 64-bit versions of R on Mac OS X. I'm
running R 2.12.1, and using R to talk to Oracle and other databases using
RODBC 1.3-2. In 32-bit mode (R32), everything works. (The ODBC drivers are
the ones from Actual.) In 64-bit mode (R), it seems as if NULL data is not
being treated as missing. For example, a NULL value in an integer column is
being returned as a 0 instead, which is wrong/bad.

Diving into the code and Google a bit, it seems like an issue that's been
seen before in other contexts. Here's the relevant C code, from
RODBCFetchRows:

        for(row = thisHandle->rowsUsed;
        row < thisHandle->rowsFetched && j <= maximum;
        j++, row++)
        {
        thisHandle->rowsUsed++;
        if(j > blksize) {
            blksize *= 2;
            for (i = 0; i < nc; i++)
            SET_VECTOR_ELT(data, i,
                       lengthgets(VECTOR_ELT(data, i), blksize));
        }
        for (i = 0; i < nc; i++) {
            SQLLEN len = thisHandle->ColData[i].IndPtr[row];
            switch(thisHandle->ColData[i].DataType) {
            case SQL_DOUBLE:
            REAL(VECTOR_ELT(data, i))[j-1] =
                len == SQL_NULL_DATA ? NA_REAL :
                thisHandle->ColData[i].RData[row];
            break;

etc...

Googling for SQL_NULL_DATA and related terms finds this page, about the same
issue in a Python driver:

http://code.google.com/p/pyodbc/issues/detail?id=51

It appears as if SQLLEN might be unsigned in 64-bit builds, while
SQL_NULL_DATA is -1. Casting len to be an int rather than an unsigned int
seems to solve the problem for them.

On my machine, /usr/include/sqltypes.h starts like this:

/*
 *  sqltypes.h
 *
 *  $Id: sqltypes.h,v 1.23 2007/10/07 13:27:13 source Exp $
 *
 *  ODBC typedefs
 *
 *  The iODBC driver manager.

and defines SQLLEN as follows:

#ifdef _WIN64
typedef INT64                   SQLLEN;
typedef UINT64                  SQLULEN;
typedef UINT64                  SQLSETPOSIROW;
#elif defined(STRICT_ODBC_TYPES)
typedef long                    SQLLEN;
typedef unsigned long           SQLULEN;
typedef unsigned short          SQLSETPOSIROW;
#else
#define SQLLEN                  long
#define SQLULEN                 unsigned long
#define SQLSETPOSIROW           unsigned short
#endif

Seems to me like SQLLEN should be signed, but apparently not?

Can anyone help? Thank you!

 -Harlan

	[[alternative HTML version deleted]]