[R] Run time error when executing sqlQuery using the 64-bit version of R with 64-bit RODBC package in a Solaris 10 Sparc machine.

[Marc Schwartz]

On Jan 20, 2010, at 1:36 PM, Eric Ma wrote:

> 
> Thanks Marc for the quick reply.  I confirmed the R binary I built is indeed
> 64-bit.
> 
> sqlplus works fine, so is the odbcConnect() call.
> 
> Any idea the error is thrown by RODBC or R?
> 
> Eric


If you are getting the same error as in the original post from Alex:

  https://stat.ethz.ch/pipermail/r-help/2009-December/222467.html

which was:

Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max,
buffsize,  :
  negative length vectors are not allowed


Then the error is occurring in a call to a C function used within an RODBC R function.

>From a review of the sequence of events in the code, sqlQuery() calls odbcQuery() internally. odbcQuery() calls a C function RODBCQuery using .Call(). 

If there are no errors in that C function call, then sqlQuery() calls sqlGetResults(), which is the workhorse R function in terms of getting the query result set back from the server and formatting the result set into an R data frame and returning that.

Within sqlGetResults(), there is a call to odbcFetchRows() to actually return the result set from the server and that R function calls the C function RODBCFetchRows via .Call(). That is the root source of the error.


Presuming that you are using:

   sqlQuery(channel, query, rows_at_time = 1)

you can try to use the R debug() function to, in effect, set a breakpoint in the R code when odbcFetchRows() is called to review the value of the arguments to the C function before it is called. 

You can use:

  debug(odbcFetchRows)

at an R prompt and then run your sqlQuery() call. Be sure to use the 'rows_at_time = 1' argument.

When the R code gets into the sqlGetResults() function and then calls odbcFetchRows(), the run will stop and leave you at a:

  Browse[x]>

prompt, where the 'x' will be a number. At that prompt, type:

  ls()

which will list the defined R objects at that point.

The ones that you are interested in are:

  channel
  max  
  buffsize
  nullstring
  believeNRows


and see what values are returned. With any luck it might help provide some more information.

After you get that information, at the Browse prompt type Q to return back to a normal R prompt.

Post back with the results and we can go from there, probably having to get Prof. Ripley involved depending upon where things stand. 

HTH,

Marc