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

[Harlan Harris]

Any thoughts? Is there someone else or somewhere else I might ask?

 -Harlan

On Tue, Mar 22, 2011 at 1:31 PM, Harlan Harris <harlan using harris.name> wrote:

> 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]]