[R] need help to resolve RODBC error

Mon Mar 1 19:51:11 CET 2010

Hi,

The error that you first posted indicates a segfault (memory segmentation fault), which is the result of C (or other compiled) code that has gone awry. Typically it involves compiled code using pointers to a location in memory that the application should not be trying to access. Ideally, one tries to trap them to prevent the crash of the application, but that is not possible in every case and one needs to be able to predictably replicate the problem (ideally on the developer's system) to track it down.

That being said, if there was a bug in RODBC, there is a reasonable likelihood that it has been resolved in the year since the version that you have was released. Hence, you need to update both R and RODBC to the current versions. Neither the version of R nor the version of RODBC you have presently installed are currently supported from the perspective of development, debugging or patches. 

That you had to modify a source file that is part of unixODBC indicates that there is/was a bug there, which I will defer to those folks for further comment. However, since the development of RODBC by Prof. Ripley will almost certainly be based upon the current stable release of unixODBC, if there are pending patches that the unixODBC folks have not yet included, your local patch may very well create other issues, making the behavior of RODBC problematic.

There may very well be issues with the 64 bit tool chain, but the reports have been far and few between. The reports have been with inconsistent behavior that may very well be tied to one or more of the individual components (OS, R, RODBC, unixODBC, ODBC drivers), all of which need to be 64 bit and which makes it difficult to track down given the dependencies.

If you continue to get the segfault after upgrading to the latest versions of each of the components, then we may be able to track down the root cause of the problem.

One additional note of import, which is that once you do get things updated, be sure to use:

  rows_at_time = 1

in the calls to odbcConnect() and sqlQuery(). The default value for that argument changed to 100 in the recent versions of RODBC and this change has led to unpredictable results from queries on some systems, including my own, which is OSX 10.6.2 -> Oracle 11g on RHEL. See the help files for each function as well as the RODBC package vignette.

As I noted below, when you post back, please do begin a new thread on the R-SIG-DB list (after subscribing there), which is the appropriate location for this discussion. It will avail of you a more focused audience for DB related issues.

Regards,

Marc Schwartz

On Mar 1, 2010, at 9:06 AM, Yan Zhang wrote:

> Thanks for your quick response.
> 
> I did install the ODBC driver.  Initially the machine had unixODBC-devel-2.2.11-7.1
> unixODBC-2.2.11-7.1 (both 32 and 64 bit) which I believe were loaded with RPM as part of the Linux OS, but I had problem with the ODBC drive setup, so I downloaded unixODBC-2.2.14-p2.tar and installed on the system (not via RPM).  isql is part of the ODBC, not native oracle sql, that's why it was recommended to be used for ODBC driver test, which was what I did.  I did run into problem in this step with following error:
> 
> [IM003][unixODBC][Driver Manager]Specified driver could not be loaded
> [ISQL]ERROR: Could not SQLAllocStmt
> 
> I found a solution on the Web (link blew), modified SQLConnect.c file and followed the steps, which fixed the ODBC driver bug and my isql test was successful.
> 
> http://forums.oracle.com/forums/thread.jspa?threadID=340030&start=15&tstart=30
> 
> I can reinstall R, RODBC with the latest version, but I still would like to know what exactly the error indicate?  Does that mean version issue?  Also I found same error reported on the Web, but no solution yet.
> 
> *** caught segfault ***
> address (nil), cause 'unknown'
> 
> 
> Thanks.
> 
> Yan Zhang
> 
> -----Original Message-----
> From: Marc Schwartz [mailto:marc_schwartz at me.com]
> Sent: Friday, February 26, 2010 5:32 PM
> To: Yan Zhang
> Cc: 'R-help at lists.R-project.org'
> Subject: Re: [R] need help to resolve RODBC error
> 
> On Feb 26, 2010, at 1:04 PM, Yan Zhang wrote:
> 
>> I've installed R-2.9.2 (64 bit), unixODBC-2.2.14-p2 (64 bit) and RODBC_1.2-5 (64 bit) on a 64 bit Redhat Linux server (Red Hat Enterprise Linux Server release 5.4 (Tikanga), x86_64) release 2.6.18-164.2.1.el5.  I've tested the ODBC drive via isql and the test was success:
>> 
>> [yzhang at ROracleTest ~]$ isql -v DRTST yzhang  test
>> +---------------------------------------+
>> | Connected!                            |
>> |                                       |
>> | sql-statement                         |
>> | help [tablename]                      |
>> | quit                                  |
>> |                                       |
>> +---------------------------------------+
>> SQL> select sysdate from dual;
>> +--------------------+
>> | SYSDATE            |
>> +--------------------+
>> | 2010-02-26 13:57:00|
>> +--------------------+
>> SQLRowCount returns -1
>> 1 rows fetched
>> SQL> quit;
>> 
>> However, when I started up R console on the machine and test the RODBC connectivity to oracle, I got following error:
>> 
>> [oracle at ROracleTest R]$ R
>> 
>> R version 2.9.2 (2009-08-24)
>> Copyright (C) 2009 The R Foundation for Statistical Computing
>> ISBN 3-900051-07-0
>> 
>> R is free software and comes with ABSOLUTELY NO WARRANTY.
>> You are welcome to redistribute it under certain conditions.
>> Type 'license()' or 'licence()' for distribution details.
>> 
>> Natural language support but running in an English locale
>> 
>> R is a collaborative project with many contributors.
>> Type 'contributors()' for more information and
>> 'citation()' on how to cite R or R packages in publications.
>> 
>> Type 'demo()' for some demos, 'help()' for on-line help, or
>> 'help.start()' for an HTML browser interface to help.
>> Type 'q()' to quit R.
>> 
>>> library(RODBC)
>>> channel <- odbcConnect("DRTST", uid="yzhang", pwd="test")
>> 
>> *** caught segfault ***
>> address (nil), cause 'unknown'
>> 
>> Traceback:
>> 1: .Call(C_RODBCDriverConnect, as.character(connection), id, as.integer(believeNRows))
>> 2: odbcDriverConnect(st, ...)
>> 3: odbcConnect("DRTST", uid = "yzhang", pwd = "test")
>> 
>> Possible actions:
>> 1: abort (with core dump, if enabled)
>> 2: normal R exit
>> 3: exit R without saving workspace
>> 4: exit R saving workspace
>> 
>> I searched around and haven't found any resolution.  Please help.
>> 
>> Thanks.
>> 
>> Yan Zhang
> 
> 
> Three quick comments:
> 
> 1. R 2.9.2 is dated and 2.10.1 is available for RHEL 5 via the EPEL:
> 
>  http://fedoraproject.org/wiki/EPEL
> 
> as you can see here:
> 
>  http://download.fedora.redhat.com/pub/epel/5/x86_64/repoview/r.html
> 
> I presume that you installed R via RPMs.
> 
> 
> 2. RODBC version 1.2-5 is a year out of date and has been updated twice since then to version 1.3-0.
> 
> 
> 3. I don't see any indication above that you installed the Oracle Linux 64 bit ODBC drivers, which are available from:
> 
>  http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxx86_64soft.html
> 
> The Oracle instant client does not require or use ODBC, but native drivers. So the success of the iSQL connection only serves to confirm that important environment variables and config files are probably ok.
> 
> 
> 
> At least to start, you need to install the Oracle 64 bit ODBC driver (if you have not already) and update both R and RODBC and see if you still get the errors.
> 
> Once you get those installed/updated, be sure to read the vignette for the RODBC package, which will be available within R using:
> 
>  vignette("RODBC")
> 
> 
> Also, just to be sure that you have 64 bit R installed, check:
> 
>  .Machine$sizeof.pointer
> 
> and be sure that it returns 8, not 4.
> 
> 
> Lastly, there is a R-SIG-DB e-mail list, where this topic is better discussed. More information here:
> 
>  https://stat.ethz.ch/mailman/listinfo/r-sig-db
> 
> HTH,
> 
> Marc Schwartz