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

[Jadhav, Alok]

[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


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