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

[Jadhav, Alok]

Hi Marc,

After investigation, I found out the issue. The issue is with the
precision of numeric column which is not handled properly by ASE driver
on windows 7. So one of the columns in the query was the culprit. I
found the correct query to get the data. 

Issue was plainly with sybase driver only.



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: Monday, May 07, 2012 10:56 PM
To: NISHIYAMA Tomoaki
Cc: Jadhav, Alok; r-sig-db using r-project.org
Subject: Re: [R-sig-DB] [R] Why does RODBC driver returns garbage from
Sybase server on new windows 7 machine?

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;passwor
>>> d=
>>> 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

=============================================================================== 
Please access the attached hyperlink for an important el...{{dropped:4}}