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")
1 0
2 0
3 0
4 0
5 0
6 0
Obviously, I'd like to see NAs here, not 0s. Let me give you some of my
specs:
> version
_
platform x86_64-unknown-linux-gnu
arch x86_64
os linux-gnu
system x86_64, linux-gnu
status
major 1
minor 9.1
year 2004
month 06
day 21
language R
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
do.
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.
Kevin
