[R] RODBC repeated rows problem
kbartz at loyaltymatrix.com
Thu Jul 15 00:08:47 CEST 2004
Hello everyone! I'm having the dreaded repeated rows problem in RODBC.
Specifically, when I have a NULL value in a column, odbcFetchRows reads the
value not as NULL or NA but as the most recent non-NULL value in the column.
If there is no such non-NULL column, odbcFetchRows reads the value as 0. Let
me give you an example (though you won't be able to run it with the
"product" table--just use some table you've defined):
> head(sqlQuery(channel, "select NULL from product"))
c("0", "0", "0", "0", "0", "0")
Obviously, I'd like to see NAs here, not 0s. Let me give you some of my
system x86_64, linux-gnu
Other specs: I'm reading a unixODBC ODBC driver. When I run from isql and
try the same query, I get, as I might expect, a bunch of blanks.
SQL> select top 5 NULL from product
SQLRowCount returns 5
5 rows fetched
It shouldn't be relevant, but the ODBC is powered by a FreeTDS driver hooked
to an MS SQL Server 2000 Professional.
I noticed a previous note from Professor Ripley reporting that this problem
had been fixed with a version of RODBC from long ago. I am running RODBC
version 1.04, the latest version, however, so I'm at a loss as to what to
One last note: I looked at the relevant line from sql.h that defines my
SQL_NULL_DATA, which RODBC (presumably) compares to its results. It says:
#define SQL_NULL_DATA (-1)
Unfortunately, I have no idea what this should mean to me :(
Are there any further diagnostics I can run? Any new versions of software I
should install? Any workarounds for the time being?
Thanks for any help you can provide.
More information about the R-help