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

[Marc Schwartz]

Hi,

I had thought of that as well, however, at least based upon prior posts, if there is an architecture mis-match between R/RODBC/ODBC (not all components are either 32 OR 64 bit), you will not be able to successfully connect to the DB server, much less run a query. 

In other words, there would be an error issued from the call to odbc[Driver]Connect() before even getting to sqlQuery(). On some Linuxen (eg RHEL), a mis-match has caused segfaults upon trying to connect. 

Regards,

Marc

On May 7, 2012, at 9:20 AM, NISHIYAMA Tomoaki wrote:

> Hi,
> 
>> Same functions work well on my old machine
>> which is a windows XP. I am trying this first time on my windows 7 box.
> 
> 
> Did you check the dependency of the error on 32 bit version and 
> 64 bit version of R?
> 
> I assume you used a 32 bit version on Windows XP and now perhaps using the
> 64 bit version. If so, you might try if it works on the 32 bit version
> of R (which usually should run on windows 7 as well).
> 
> 
> On 2012/05/07, at 9:24, Jadhav, Alok wrote:
> 
>> 
>> [Moving this mail from R-Help to R-SIG-DB ]
>> 
>> Marc,
>> 
>> The below mentioned code is inside a function and the value of as.is is
>> passed to the function, default value being FALSE.
>> 
>> I tried your suggestion of passing rows_at_time=1 and
>> believeNRows=FALSE. This did not solve the problem. It removed the
>> garbage data in the result set but the data is truncated. 
>> 
>> In my query, I am expecting a result of 200 rows. Earlier I was getting
>> around 17 rows with correct data and 3 rows with Nas. After above change
>> now I am getting total of 17 rows with no NAs in the output. But data is
>> not complete.
>> 
>> I am tempted to try a different driver but still think driver is not a
>> problem as the same driver in other app works without any issues. Seems
>> like an issue with RODBC.  Same functions work well on my old machine
>> which is a windows XP. I am trying this first time on my windows 7 box.
>> 
>> Regards,
>> Alok
>> 
>> 
>> 
>> Please follow the attached hyperlink to an important disclaimer
>> http://www.credit-suisse.com/asiapac/legal/securities/ 
>> 
>> 
>> -----Original Message-----
>> From: Marc Schwartz [mailto:marc_schwartz using me.com] 
>> Sent: Friday, May 04, 2012 10:54 PM
>> To: Jadhav, Alok
>> Cc: r-help using r-project.org
>> Subject: Re: [R] Why does RODBC driver returns garbage from Sybase
>> server on new windows 7 machine?
>> 
>> 
>> 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