RODBC returning 0 values

Question

I am accessing a commercial DB. Via prompt:

 select PersonCode, PersonDate from CODB.mastertable where PersonCode=42
 PersonCode  PersonDate 
 ----------- ------------
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011
       42 Jan  3 2011

In R:

library(RODBC)
query <- "select PersonCode, PersonDate from CODB.mastertable where PersonCode=42"

connection1 <- odbcConnect("RESDB", uid="userID", pwd="pwdaccess", believeNRows=FALSE)

sqlQuery(connection1,query)


sqlQuery(connection1,query)
    PersonCode                 PersonDate 
1          42 01/03/2011 00:00:00.000 UTC
2          42 01/03/2011 00:00:00.000 UTC
3          42 01/03/2011 00:00:00.000 UTC
4          42 01/03/2011 00:00:00.000 UTC
5          42 01/03/2011 00:00:00.000 UTC
6           0 01/03/2011 00:00:00.000 UTC
7           0 01/03/2011 00:00:00.000 UTC
8           0 01/03/2011 00:00:00.000 UTC
9           0 01/03/2011 00:00:00.000 UTC
10          0 01/03/2011 00:00:00.000 UTC

The output of the query is incorrect. Has anyone encountered this problem before? Here some additional information.

> sessionInfo()
R version 2.12.1 (2010-12-16)
Platform: x86_64-unknown-linux-gnu (64-bit)

locale:
[1] C

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base

other attached packages:
[1] timeDate_2130.92 data.table_1.5.3 RODBC_1.3-2

loaded via a namespace (and not attached):
[1] tools_2.12.1

Answer 1

Found a solution; not sure why it works, but here it is: use the option rows_at_time=1

sqlQuery(connection1,query,rows_at_time=1)

Answer 2

You can use odbcGetInfo() to view information on the driver being used.

Considering the origins of ODBC, have you tried using ROracle (DBI package) instead? RJDBC may also be a more stable option in the Linux environment, especially now that the fetch() code has been re-written in Java (as of development release 0.2-0 on R-Forge) its performance is on par with (if not better than) RODBC.

It might sound obvious, but does the problem occur with R32 (if that's even an option for you)? 32-bit vs. 64-bit driver incompatibilities can be notoriously difficult to track down; Windows will complain about an architecture mismatch using R64 with a 32-bit ODBC driver and cause odbcConnect() to fail outright, but I don't know if Linux is the same.