Skip to content

Why does RODBC driver returns garbage from Sybase server on new windows 7 machine?

8 messages · Jadhav, Alok, Marc Schwartz, NISHIYAMA Tomoaki

Original
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 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:

            

      
      
      
    

        
driver or issue with RODBC library?

            

      
      
      
    

        
resolve it?

            

      
      
      
    

        
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}}

  
  


  


    

    

      
      

        


  

        

      Marc Schwartz
    

    

      
      #
    

  


  

      
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:

        

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      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:

        

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      NISHIYAMA Tomoaki
    

    

      
      #
    

  


  

      
Hi,

            

      
      
      
    

        
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:

        

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Marc Schwartz
    

    

      
      #
    

  


  

      
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:

        

            

      
      
      
    

        

      
      
    

  
  


  


      

    

    

      
      

        


  

        

      Jadhav, Alok
    

    

      
      #
    

  


  

      
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_schwartz at me.com] 
Sent: Monday, May 07, 2012 10:14 PM
To: Jadhav, Alok
Cc: 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?

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:

        

            

      
      
      
    

        
cannot attempt to replicate the problem you have or implement a working
scenario.

            

      
      
      
    

        
the SQL query to be sure that there are no strange characters and/or
things like line breaks, etc. that might cause issues.

            

      
      
      
    

        
insight into where the issue might be here.

            

      
      
      
    

        
windows 7 box.

            

      
      
      
    

        

      
      
    

            

      
      
      
    

        

      
      
    

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

  
  


  


      

    

    

      
      

        


  

        

      Jadhav, Alok
    

    

      
      #
    

  


  

      
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:

        

            

      
      
      
    

        
windows 7 box.

            

      
      
      
    

        

      
      
    

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

  
  


  


      

    

    

      
      

        


  

        

      Marc Schwartz
    

    

      
      #
    

  


  

      
On May 8, 2012, at 5:48 AM, Jadhav, Alok wrote:

        

            

      
      
      
    

        
Hi Alok,

Glad that you got it resolved. If you have not yet, you should send in a bug report to Sybase.

Regards,

Marc