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_schwa...@me.com] Sent: Monday, May 07, 2012 10:14 PM To: Jadhav, Alok Cc: r-sig-db@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_schwa...@me.com] >> Sent: Friday, May 04, 2012 10:54 PM >> To: Jadhav, Alok >> Cc: r-h...@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@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db =============================================================================== Please access the attached hyperlink for an important el...{{dropped:4}} _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
