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

[Harlan Harris]

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]]