[R] RODBC repeated rows problem

Kevin Bartz 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")
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




More information about the R-help mailing list