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

[Jadhav, Alok]

Marc,

 I am sure that my R version is 32 bit. Though I am not sure which
driver is picked up, 32bit or 64 bit. My box has both the drivers
installed. I will look into it further. 
I have tried a simple query which returns single row and it is working
fine. This issue happens only in case when the result set is larger than
100 rows.

I have a new observation. I was wrong when I said that this query was
working fine on old machine (windows xp) with same driver. Looks like
windows xp driver is different. I was using Merant driver on xp and
using sybase native driver ASE on windows 7. Looks like this is actually
a driver issue because I tried to use the same driver from a python
script and I am getting exactly same truncated result from my python
script. This clearly shows that RODBC doesn't has any problem. I will
test this out with a different driver and let you know.

Thanks for your time.

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:14 PM
To: Jadhav, Alok
Cc: 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?

Alok,

One additional thought as I was driving to work this morning.

Try running your RODBC connect/query code outside of the function and
use a simple query like "select * from TABLENAME". Also be sure to use a
clean working environment. 

Just to eliminate the possibility that there is something in your
workspace, function and/or a more complex query, if you are using one,
that is not obvious.

Marc

On May 7, 2012, at 8:03 AM, Marc Schwartz wrote:

> Hi Alok,
> 
> Unfortunately, I don't have access to either Windows or Sybase, so I
cannot attempt to replicate the problem you have or implement a working
scenario.
> 
> You might just re-check the character vector that you are passing for
the SQL query to be sure that there are no strange characters and/or
things like line breaks, etc. that might cause issues.
> 
> I will need to defer to others to see if anyone might have some
insight into where the issue might be here.
> 
> Regards,
> 
> Marc
> 
> On May 6, 2012, at 7:24 PM, 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
> 
> _______________________________________________
> R-sig-DB mailing list -- R Special Interest Group 
> R-sig-DB using r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db


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