[R-sig-DB] RODBC and Oracle 11g Issue.....

[Joe O]

> OK....stoopid question time:
> 
> 1. I presume that you installed the 64 bit version of
> Oracle's instant client and ODBC driver from:
> 
>    http://www.oracle.com/technology/software/tech/oci/instantclien/htdocs/linuxx86_64soft.html

 Absolutely. From my first post, I know this was done properly (and configured properly) since the sqlplus interface works just fine.
 
> 2. You are running a 64 bit version of R?  Check the
> output of .Machine$sizeof.pointer and be sure that it shows
> 8, not 4.

  It is showing 8, see session output below.
> 
> 3. You are using a 64 bit version of unixODBC?

  Yes, I downloaded the latest version yesterday, and compiled it on the machine locally. I also know this is configured correctly because the iSQL session output in my first post is correct as well.
 
> Just to be sure, since the entire tool chain needs to be 64
> bit, although typically you might get a segfault or other
> problem when mixing 32/64 bit.

 Yep, that is clear. iSQL and sqlplus are working OK, so I think the stack is aligned properly. 

> Also, just for the heck of it, can you post the output of:
> 
>   unclass(channel)

  See below
 
> 
> Lastly, try using 'case = "toupper"' in the odbcConnect()
> call to see if that makes any difference:
> 
>   channel <- odbcConnect("OracleLocal",
> uid="parts", pwd="parts", rows_at_time = 1, case =
> "toupper")

  Again, no dice. See below

[ruser using akoyavsrv05 ~]$ R

R version 2.10.0 (2009-10-26)
Type 'q()' to quit R.

> .Machine$sizeof.pointer
[1] 8
> library(RODBC)
> channel <- odbcConnect("OracleLocal", uid="parts", pwd="parts", rows_at_time = 1, case = "toupper")
> unclass(channel)

[1] 1
attr(,"connection.string")
[1] "DSN=OracleLocal;UID=parts;PWD=******;DBQ=VSRV06;DBA=W;APA=T;EXC=T;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=0;MLD=0;ODA=F;"
attr(,"handle_ptr")
<pointer: 0x1495a80>
attr(,"case")
[1] "toupper"
attr(,"id")
[1] 83755
attr(,"believeNRows")
[1] TRUE
attr(,"colQuote")
[1] "\""
attr(,"tabQuote")
[1] "\""
attr(,"interpretDot")
[1] TRUE
attr(,"encoding")
[1] ""
attr(,"rows_at_time")
[1] 1
attr(,"isMySQL")
[1] FALSE
attr(,"call")
odbcDriverConnect(connection = "DSN=OracleLocal;UID=parts;PWD=******;",
    case = "toupper", rows_at_time = 1)
> sqlQuery(channel, "select distinct segment from parts", errors=TRUE, rows_at_time = 1)

character(0)



Now here is something interesting...at least R is talking to Oracle:

> sqlQuery(channel, "select distinct segment from partsddddddddd",errors=TRUE,rows_at_time = 1)

[1] "42S02 942 [Oracle][ODBC][Ora]ORA-00942: table or view does not exist\n"

[2] "[RODBC] ERROR: Could not SQLExecDirect 'select distinct segment from partsddddddddd'"

It knows no table named 'partsddddddddd' exists....so Oracle is processing the query and returning an error, which R is getting.


No errors in the machine wide ODBC log that is enabled.