Why does RODBC driver returns garbage from Sybase server on new windows 7 machine?
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 at me.com] Sent: Friday, May 04, 2012 10:54 PM To: Jadhav, Alok Cc: r-help at 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