[R-sig-DB] RODBC with Oracle and 64-bit Linux (encore)
Prof Brian Ripley
r|p|ey @end|ng |rom @t@t@@ox@@c@uk
Thu Nov 18 19:40:11 CET 2010
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/
University of Oxford, Tel: +44 1865 272861 (self)
1 South Parks Road, +44 1865 272866 (PA)
Oxford OX1 3TG, UK Fax: +44 1865 272595
More information about the R-sig-DB
mailing list