[R-sig-DB] RODBC with Oracle and 64-bit Linux (encore)
Harlan Harris
h@r|@n @end|ng |rom h@rr|@@n@me
Thu Nov 18 19:47:11 CET 2010
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