[R] RODBC tables

[Marc Schwartz]

On Jul 4, 2012, at 10:10 AM, Lorcan Treanor wrote:

> Dear Sir/Madam,
> 
> I am desperately in need of some help. I am trying to access tables from
> the oracle database and inserting them into R via a data frame and I keep
> getting an error saying that "Error in .Call(C_RODBCFetchRows,
> attr(channel, "handle_ptr"), max, buffsize,  :
>  negative length vectors are not allowed".
> 
> My connection is fine and my code for this is:
> check<-odbcConnect(dsn="********",uid="*********",pwd="******")
> 
> There are terms instead of the *'s but I am not sure if I should disclose
> them because this is work-related. I have looked all over the internet and
> tried hundreds of solutions but have had no luck.
> 
> These are the code I tried to use to get the table i called "ANYTHING" into
> R for which the negative length vectors errors came up.
> 
> sqlTables(check,schema="******")
> nowfetchmewillwheaton<-sqlFetch(check,"ANYTHING")
> 
> Do the errors imply that my connection is wrong, my code is wrong or that
> the table is not in the correct place in oracle.
> 
> Please let me know if you can help or if you can give me the email address
> of someone who can.
> 
> Kind Regards,
> 
> Lorcan Treanor


You don't need to post the content of the "*"s. That is not needed, so you do not need to compromise your security.

This issue has come up before and I don't know, other than in one case years ago, that a definitive solution was ever posted. It seemed to be otherwise associated with 64 bit installations and there were some hints of integer overflow or an ODBC driver stability issue, but I don't recall that being confirmed. 

In the one case where Prof. Ripley had posted a work-around, it was back in 2002 and involved querying tables with an underscore ('_') in the table name. I don't know that this issue is still relevant as I use RODBC on OSX (Fedora Linux for years previously) to access Oracle tables and views and they do have underscores in the names.

Some comments:

1. You are better off subscribing to and posting this query to the r-sig-db list which is focused on R and databases. More info here:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

Be sure to include information about your OS, R version including 32 or 64 bit. See the R Posting Guide for additional information on what default info to include in a post.

2. Any chance that you do not have appropriate permission to access the "ANYTHING" table? You may want to verify that with your DBAdmin. Is that actually the name of the table?

3. Try using:

   nowfetchmewilwheaton <- sqlQuery(check, "select * from ANYTHING")
  
and see if that works. 

Regards,

Marc Schwartz