[R-sig-DB] RODBC connection to Oracle on 64-bit RHEL box failing

[Marc Schwartz]

On Mar 23, 2010, at 2:47 PM, Harlan Harris wrote:

> Hi, we've been using the following pathway to an Oracle server from R
> successfully for quite a while:
> 
> 32-bit R 2.9.2 on Mac OS X -> RODBC -> ODBC Manager -> Actual Oracle -> DB
> 
> (We've also been able to get this working with 64-bit R 2.10.1 and a beta
> version of the Actual Oracle driver...)


Is Actual making a 64 bit Oracle ODBC driver available?  I had not seen anything on that and had in fact queried them some time ago specifically on the availability of one. I use their 32 bit version on Snow Leopard.


> We need to be able to run our software on a 64-bit Redhat Linux box now. We
> installed instantclient_11_2_0_1_0-linux-64bit with ODBC support. That
> works. We also compiled and installed unixODBC version 2.2.14, and that
> works too. (Version 2.2.11, which is shipped with RedHat, does NOT work.)
> 
> I then installed the RODBC source, as follows (we have to install packages
> in the user directory, for uninteresting reasons):
> 
>> Sys.info()[c(1:3,5)]
>                              sysname                               release
>                              "Linux"                  "2.6.18-164.6.1.el5"
>                              version                               machine
> "#1 SMP Tue Oct 27 11:28:30 EDT 2009"                              "x86_64"
> 
>> sessionInfo()
> R version 2.10.1 (2009-12-14)
> x86_64-redhat-linux-gnu
> 
> locale:
> [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C
> [3] LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8
> [5] LC_MONETARY=C              LC_MESSAGES=en_US.UTF-8
> [7] LC_PAPER=en_US.UTF-8       LC_NAME=C
> [9] LC_ADDRESS=C               LC_TELEPHONE=C
> [11] LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
> 
> attached base packages:
> [1] stats     graphics  grDevices utils     datasets  methods   base
> 
> loaded via a namespace (and not attached):
> [1] tools_2.10.1
> 
>> install.packages("RODBC", "~/R/x86_64-redhat-linux-gnu-library/2.10/")
> trying URL '
> http://cran.revolution-computing.com/src/contrib/RODBC_1.3-1.tar.gz'
> Content type 'application/x-gzip' length 990220 bytes (967 Kb)
> opened URL
> ==================================================
> downloaded 967 Kb
> 
> * installing *source* package ŒRODBC‚ ...
> checking for gcc... gcc -m64 -std=gnu99
> checking for C compiler default output file name... a.out
> checking whether the C compiler works... yes
> checking whether we are cross compiling... no
> checking for suffix of executables...
> checking for suffix of object files... o
> checking whether we are using the GNU C compiler... yes
> checking whether gcc -m64 -std=gnu99 accepts -g... yes
> checking for gcc -m64 -std=gnu99 option to accept ANSI C... none needed
> checking how to run the C preprocessor... gcc -m64 -std=gnu99 -E
> checking for egrep... grep -E
> checking for ANSI C header files... yes
> checking for sys/types.h... yes
> checking for sys/stat.h... yes
> checking for stdlib.h... yes
> checking for string.h... yes
> checking for memory.h... yes
> checking for strings.h... yes
> checking for inttypes.h... yes
> checking for stdint.h... yes
> checking for unistd.h... yes
> checking sql.h usability... yes
> checking sql.h presence... yes
> checking for sql.h... yes
> checking sqlext.h usability... yes
> checking sqlext.h presence... yes
> checking for sqlext.h... yes
> checking for library containing SQLTables... -lodbc
> checking for SQLLEN... yes
> checking for SQLULEN... yes
> checking for long... yes
> checking size of long... 8
> configure: creating ./config.status
> config.status: creating src/Makevars
> config.status: creating src/config.h
> ** libs
> gcc -m64 -std=gnu99 -I/usr/include/R -I. -I/home/rproj/unixODBC/include/
> -I/usr/local/include    -fpic  -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2
> -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic
> -c RODBC.c -o RODBC.o
> In file included from /home/rproj/unixODBC/include/sql.h:19,
>                 from RODBC.c:41:
> /home/rproj/unixODBC/include/sqltypes.h:400: warning: type defaults to Œint‚
> in declaration of ŒSQLBIGINT‚
> /home/rproj/unixODBC/include/sqltypes.h:403: warning: type defaults to Œint‚
> in declaration of ŒSQLUBIGINT‚
> gcc -m64 -std=gnu99 -shared -L/usr/local/lib64 -o RODBC.so RODBC.o -lodbc
> -L/home/rproj/unixODBC/lib/ -L/usr/lib64/R/lib -lR
> ** R
> ** inst
> ** preparing package for lazy loading
> ** help
> *** installing help indices
>  converting help for package ŒRODBC‚
>    finding HTML links ... done
>    RODBC-internal                          html
>    RODBC-package                           html
>    odbc                                    html
>    odbcClose                               html
>    odbcConnect                             html
>    odbcDataSources                         html
>    odbcGetInfo                             html
>    odbcSetAutoCommit                       html
>    setSqlTypeInfo                          html
>    sqlColumns                              html
>    sqlCopy                                 html
>    sqlDrop                                 html
>    sqlFetch                                html
>    sqlQuery                                html
>    sqlSave                                 html
>    sqlTables                               html
>    sqlTypeInfo                             html
> ** building package indices ...
> * DONE (RODBC)
> 
> The downloaded packages are in
>    Œ/tmp/RtmpLzupy7/downloaded_packages‚
>> library(RODBC)
>> hdl <- odbcConnect("ktparep")
> 
> *** caught segfault ***
> address (nil), cause 'unknown'
> 
> Traceback:
> 1: .Call(C_RODBCDriverConnect, as.character(connection), id,
> as.integer(believeNRows),     as.logical(readOnlyOptimize))
> 2: odbcDriverConnect("DSN=ktparep")
> 3: eval(expr, envir, enclos)
> 4: eval(expr, p)
> 5: eval.parent(Call)
> 6: odbcConnect("ktparep")
> 
> 
> Anyone have any ideas? This sort of seg fault seems like a 64-bit/32-bit
> issue, but as far as I can see, everything in the chain is 64-bit...
> 
> Also, unixODBC does not seem to be under active development or support. Are
> there alternatives for accessing Oracle servers? ROracle seems even less
> active than unixODBC...
> 
> Thank you!
> 
> -Harlan


Two comments:

1. The segfault is indeed typically a consequence of a mix of 32/64 bit tools. That being said, I am not seeing anything obvious to suggest that one component or another is 32 bit.  You might want to check your environment variables (eg. LD_LIBRARY_PATH, PATH or one of the Oracle variables) or that there is an entry in /etc/ld.so.conf that is pointing to a 32 bit version of either Oracle's files or unixODBC. Also, be sure that any relevant entries in odbcinst.ini and odbc.ini are also pointing to the proper Oracle ODBC drivers.

2. If you manage to solve the above problem, you have another potential hurdle in front of you, which is that there have been reports of problems connecting to Oracle using 64 bit R/RODBC on 64 bit Linux. It would be helpful, if you do solve the above issue, to try to connect and to see what level of success you have. Prof. Ripley is aware of the issue and either is or will shortly be testing the 64 bit toolchain on Linux to see if he can identify the etiology of the problem and perhaps offer a fix or workaround.


I have not used ROracle, but others have and seem to have good luck with it. I would not let the last version dates of either unixODBC or ROracle sway your opinion. If the components are pretty stable and are not influenced by updates on the R or Oracle end of things, there may very well be no reason to update them. But I would certainly defer to the respective maintainers on those points.

HTH,

Marc Schwartz