[R] Why does RODBC driver returns garbage from Sybase server on new windows 7 machine?

[Marc Schwartz]

On May 4, 2012, at 2:38 AM, Alok Jadhav wrote:

> Hi, 
> 
> I am trying to query a Sybase database on my new windows 7 machine.  I am
> using native sybase driver "Adaptive server Enterprise" following is example
> code 
> 
> conn <- sprintf("driver=Adaptive server
> Enterprise;server=PHKSESMD01;database=smd_live;uid=temp_user;password=temp_pass;port=2301") 
> chan <- odbcDriverConnect(conn) 
> x <- sqlQuery(chan,sql,as.is=as.is) 
> odbcClose(chan) 
> 
> I am able to connect to the database and get the data using above code. But
> when the data is more than couple of hundred lines (4 columns.. its not
> much), i get garbage data. I usually get 5 to 6 rows correctly and remaining
> data is NAs.  Is this an issue with ODBC driver or issue with RODBC library?
> I would think driver is ok because it works fine in other applications.  Can
> someone suggest what could be wrong here and how to resolve it? 
> 
> Regards, 
> Alok  



1. This query is better posted to r-sig-db, which is dedicated to R and DB connectivity issues. More info at:

 https://stat.ethz.ch/mailman/listinfo/r-sig-db

2. It is not clear what 'as.is = as.is' in the call to sqlQuery is. Is the value of 'as.is' defined before the call someplace else that you have not shown here, along with the 'sql' query itself? 

3. Try using 'rows_at_time = 1' as well as 'believeNRows = FALSE' in the calls as below. That may help with corrupted data coming back. I have to use the former with Oracle. That is also referenced in ?odbcConnect with a comment specific to Sybase.

  chan <- odbcDriverConnect(conn, rows_at_time = 1, believeNRows = FALSE) 
  x <- sqlQuery(chan, sql, as.is = as.is, rows_at_time = 1, believeNRows = FALSE) 


Regards,

Marc Schwartz