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

[Marc Schwartz]

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;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
> 
> _______________________________________________
> 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