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

Mixtec

When you say "via prompt", what application are you using? Are you connecting via ODBC or directly? With R, what ODBC driver are you using? There are known bugs in RODBC with 64-bit Linux, but they usually give you 0's when you should get NA's (NULL data). I've never seen corruption of non-NULL data. – Moonstone

I think you have something very funky going on with your R installation (locale maybe?). This looks very similar to your earlier question. Does this happen in both instances you showed in your previous question? – Kailey

@harlan 1. sqsh, or also a frontend called dbvis. Other users get the same results using other applications. 2. How can I tell the ODBC driver being used? I'd like to supply the information. @joshua no, this is not the buggy machine I had problems with yesterday. Although I must say that after 3 strikes, I may have to move some of the critical code to Python. I can't afford mistakes. – Mixtec

do these other applications rely on ODBC, or do they connect directly or via JDBC or something? Someone must have installed the ODBC driver on that Linux box -- ask them! – Moonstone

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)

Mixtec

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.

Vernonvernor

Sadly, ROracle has not been updated in almost four years. R's support for Oracle is a real problem with use in Enterprise systems, and someone has to invest in a solution! Trying to patch ODBC to deal with 64-bit issues is a giant pain, and the author doesn't have time to deal with it. – Moonstone