Why does RODBC driver returns garbage from Sybase server on new windows 7 machine?
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 at me.com] Sent: Monday, May 07, 2012 10:56 PM To: NISHIYAMA Tomoaki Cc: Jadhav, Alok; r-sig-db at 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 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;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}}