[R-sig-DB] RODBC with Oracle and 64-bit Linux (encore)

Harlan Harris h@r|@n @end|ng |rom h@rr|@@n@me
Mon Nov 22 19:04:21 CET 2010 

Looping R-Sig-DB back in for posterity...

We did manage to get this to work! The answer is that Easysoft offers two
versions of their driver. The version you get by default from their web
site, "odbc-oracle-3.2.26-linux-x86-64-ul64", is a 64-bit driver with 64-bit
SQLLEN and SQLULEN values. That causes (for reasons I don't fully
understand) problems with the integration with RODBC. You can request from
Easysoft a driver called "odbc-oracle-3.2.26-linux-x86-64" which is a 64-bit
driver with 32-bit values for those constants. That driver works (modulo an
issue with Oracle timestamp types, which can be dealt with by as.is=TRUE).

 -Harlan

On Thu, Nov 18, 2010 at 1:47 PM, Harlan Harris <harlan using harris.name> wrote:

> Thanks Marc and Prof. Ripley.
>
> The believeNRows option doesn't help. I'll try installing the newest R
> version and see if that helps.
>
> Would I have to compile R with debugging symbols enabled to use the
> debugger? Never done that...
>
>  -Harlan
>
>
> On Thu, Nov 18, 2010 at 1:40 PM, Prof Brian Ripley <ripley using stats.ox.ac.uk>wrote:
>
>> One thing I don't see being tried:
>>
>> ?odbcConnect says
>>
>> believeNRows: logical.  Is the number of rows returned by the ODBC
>>          connection believable?  Not true for some Oracle and Sybase
>>          drivers, apparently, nor for Actual Technologies' SQLite
>>          driver for Mac OS X.
>>
>> Given the hint about Oracle, it looks worth a try, although I think the
>> usual indication is claiming zero rows are available when a positive number
>> are.
>>
>> You can run R under a debugger to find out exactly what is going wrong.
>>
>>
>> On Thu, 18 Nov 2010, Harlan Harris wrote:
>>
>>  This is the version of R you get by "yum install R" in CentOS 5 from
>>> EPEL.
>>> Here you are:
>>>
>>>  sessionInfo()
>>>>
>>> R version 2.11.1 (2010-05-31)
>>> 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
>>>
>>> other attached packages:
>>> [1] RODBC_1.3-2
>>>
>>> loaded via a namespace (and not attached):
>>> [1] tools_2.11.1
>>>
>>> -Harlan
>>>
>>>
>>> On Thu, Nov 18, 2010 at 1:13 PM, Marc Schwartz <marc_schwartz using me.com>
>>> wrote:
>>>
>>>  OK....the more I think about this, the more I believe that there is an
>>>> integer overflow problem. This may yet be an indication of a 32/64 bit
>>>> conflict someplace.
>>>>
>>>> The question then is where and why.
>>>>
>>>> Which version of R are you running? Provide the output of:
>>>>
>>>>  sessionInfo()
>>>>
>>>> Marc
>>>>
>>>> On Nov 18, 2010, at 12:03 PM, Harlan Harris wrote:
>>>>
>>>>  I wondered about the -1 too...
>>>>>
>>>>> But nope, neither of these suggestions work...
>>>>>
>>>>>  .Machine$sizeof.pointer
>>>>>>
>>>>> [1] 8
>>>>>
>>>>>> hdl <- odbcConnect('ORACLE', rows_at_time=1)> sqlQuery(hdl, 'select
>>>>>>
>>>>> name from catalog.program where rownum < 2', rows_at_time=1, as.is
>>>> =TRUE)
>>>>
>>>>> Error in odbcQuery(channel, query, rows_at_time) :
>>>>>  Calloc could not allocate (-1 of 22816) memory
>>>>>
>>>>>  -Harlan
>>>>>
>>>>> On Thu, Nov 18, 2010 at 12:52 PM, Marc Schwartz <marc_schwartz using me.com>
>>>>>
>>>> wrote:
>>>>
>>>>> OK.  What is interesting, albeit, it may be a red herring, is the -1 in
>>>>>
>>>> the error message. Is that an indication of an integer wrap around or
>>>> something more subtle?
>>>>
>>>>>
>>>>> Please be sure that you are running 64 bit R. Check the result of:
>>>>>
>>>>>  .Machine$sizeof.pointer
>>>>>
>>>>> It should return 8 if you are running 64 bit R. If it returns 4, you
>>>>> are
>>>>>
>>>> running 32 bit R, which would conflict with the rest of the 64 bit tool
>>>> chain.
>>>>
>>>>>
>>>>> If by chance you are running 32 bit R, you will need to install 64 bit
>>>>> R
>>>>>
>>>> and reinstall your CRAN packages, including RODBC, since any C/FORTRAN
>>>> code
>>>> would be compiled as 32 bit programs.
>>>>
>>>>>
>>>>> Can you also perhaps try a query that should result in a small result
>>>>>
>>>> set? Perhaps a single column from a single row?
>>>>
>>>>>
>>>>>
>>>>> Marc
>>>>>
>>>>> On Nov 18, 2010, at 11:29 AM, Harlan Harris wrote:
>>>>>
>>>>>  Quick response -- thanks! Nope, doesn't help.
>>>>>>
>>>>>>   library(RODBC)
>>>>>>> hdl <- odbcConnect('ORACLE', rows_at_time=1)
>>>>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10',
>>>>>>>
>>>>>> rows_at_time=1)
>>>>
>>>>> Error in odbcQuery(channel, query, rows_at_time) :
>>>>>>  Calloc could not allocate (-1 of 22816) memory
>>>>>>
>>>>>>  -Harlan
>>>>>>
>>>>>> On Thu, Nov 18, 2010 at 12:26 PM, Marc Schwartz <marc_schwartz using me.com
>>>>>> >
>>>>>>
>>>>> wrote:
>>>>
>>>>> On Nov 18, 2010, at 11:15 AM, Harlan Harris wrote:
>>>>>>
>>>>>>  Hi all,
>>>>>>>
>>>>>>> I'm trying again to get the R<->Oracle toolchain to work on 64-bit
>>>>>>>
>>>>>> Linux
>>>>
>>>>> (CentOS 5/RHEL 5). As previously established, it's nearly impossible,
>>>>>>> although I'm awfully close now and just need one bit of help.
>>>>>>>
>>>>>>> Here's what works:
>>>>>>>
>>>>>>> I installed the 64-bit Oracle Instant Client Basic and ODBC rpms
>>>>>>>
>>>>>> (version
>>>>
>>>>> 11.2.0.2.0).
>>>>>>> export ORACLE_HOME=/usr/lib/oracle/11.2/client64/
>>>>>>> export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
>>>>>>> export TNS_ADMIN=/etc/oracle
>>>>>>> set up /etc/oracle/tnsname.ora
>>>>>>>
>>>>>>> The version of unixODBC that's installed on Redhat 5 is 2.2.11. That
>>>>>>>
>>>>>> version
>>>>
>>>>> is old and doesn't work with 64-bit Oracle Instant Client. Both Oracle
>>>>>>>
>>>>>> and
>>>>
>>>>> Redhat are aware of this and stubbornly refuse to fix it. Therefore I
>>>>>>> installed easysoft's ODBC Oracle driver. Note that this costs
>>>>>>>
>>>>>> something like
>>>>
>>>>> $2000 to license, but if you've got Oracle, you can probably pay for
>>>>>>>
>>>>>> it.
>>>>
>>>>>
>>>>>>> The easysoft driver installs to /usr/local/easysoft. It includes
>>>>>>>
>>>>>> unixODBC
>>>>
>>>>> 2.2.12. (I uninstalled the factor 2.2.11 version)
>>>>>>>
>>>>>>> I confirmed that it was properly configured and working by running
>>>>>>> /usr/local/easysoft/unixODBC/bin/isql and querying my database.
>>>>>>>
>>>>>> Username and
>>>>
>>>>> password are in /etc/odbc.ini.
>>>>>>>
>>>>>>> I symlinked /usr/local/easysoft/unixODBC/lib, /include, and /bin to
>>>>>>> /usr/local/lib, /include and /bin, respectively.
>>>>>>>
>>>>>>> Now I install RODBC from source. It seems to work, finding sql.h,
>>>>>>>
>>>>>> sqlext.h,
>>>>
>>>>> and -lodbc.
>>>>>>>
>>>>>>>  library(RODBC)
>>>>>>>> hdl <- odbcConnect('ORACLE')
>>>>>>>> sqlQuery(hdl, 'select * from catalog.program where rownum < 10')
>>>>>>>>
>>>>>>> Error in odbcQuery(channel, query, rows_at_time) :
>>>>>>>  Calloc could not allocate (-1 of 22816) memory
>>>>>>>
>>>>>>>
>>>>>>> So close! What am I missing?
>>>>>>>
>>>>>>> Thanks!
>>>>>>>
>>>>>>> -Harlan
>>>>>>>
>>>>>>
>>>>>>
>>>>>> Harlan, glad to see some progress, albeit at a notable cost.
>>>>>>
>>>>>> Try using 'rows_at_time = 1' in the calls to both odbcConnect() and
>>>>>>
>>>>> sqlQuery().
>>>>
>>>>>
>>>>>> I have found the need to use that setting, rather than the default
>>>>>> 100,
>>>>>>
>>>>> in order to successfully retrieve data from our Oracle server on OSX.
>>>>
>>>>>
>>>>>> The smaller value may also help to workaround the memory allocation
>>>>>>
>>>>> issue, presuming that something more subtle is not going on.
>>>>
>>>>>
>>>>>> HTH,
>>>>>>
>>>>>> Marc Schwartz
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>        [[alternative HTML version deleted]]
>>>
>>> _______________________________________________
>>> R-sig-DB mailing list -- R Special Interest Group
>>> R-sig-DB using stat.math.ethz.ch
>>> https://stat.ethz.ch/mailman/listinfo/r-sig-db
>>>
>>>
>> --
>> Brian D. Ripley,                  ripley using stats.ox.ac.uk
>> Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/<http://www.stats.ox.ac.uk/%7Eripley/>
>> University of Oxford,             Tel:  +44 1865 272861 (self)
>> 1 South Parks Road,                     +44 1865 272866 (PA)
>> Oxford OX1 3TG, UK                Fax:  +44 1865 272595
>>
>
>

	[[alternative HTML version deleted]]

More information about the R-sig-DB mailing list