Why will WHERE statement not work in sqlQuery in R

问题:

We keep all our laboratory data in a Sybase database. When I want to do data manipulation and analysis I read the data into R with RODBC.

library(RODBC)
channellab <- odbcConnect("Labdata")
indivs <-sqlQuery(channellab,'SELECT * from CGS.Specimen')

So far so good, except that CGS.Specimen is a table for our entire lab holdings. There are almost 40000 specimens (rows) with 66 variables. It takes an unnecessary amount of time to read especially when I am only interested in the holdings of one study which has about 1000 specimens. The obvious way to fix it would be

indivs <-sqlQuery(channellab,'SELECT * from CGS.Specimen WHERE StudyCode="RP"')  

But when I run that I get

[1] "42S22 -143 [Sybase][ODBC Driver][Adaptive Server Anywhere]Column not found: Column 'RP' not found" "[RODBC] ERROR: Could not SQLExecDirect 'SELECT * from CGS.Specimen WHERE StudyCode=\"RP\"'"

I have played with quotation marks and I have played with escape slashes but alas I have gotten nowhere.

for instance

indivs <-sqlQuery(channellab,'SELECT * from CGS.Specimen WHERE StudyCode= 'RP'  ')

Error: unexpected symbol in "indivs <-sqlQuery(channellab,'SELECT * from CGS.Specimen WHERE StudyCode= 'RP"

indivs <-sqlQuery(channellab,"SELECT * from CGS.Specimen WHERE StudyCode= 'RP'  ")

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

What do you suggest?

回答1:

The last error you report is from the .Call function to the c function RODBCFetchRows. Thus your problem with the quotation marks appears to be solved. However, with the said error you still have a problem. Perhaps the rows are not correctly reported (the c function tries to allocate vectors based on this value, but does not check for negative values). Try:

indivs <-sqlQuery(channellab,"SELECT * from CGS.Specimen WHERE StudyCode= 'RP'", believeNRows = FALSE)

回答2:

Edited:

indivs <-sqlQuery(channellab,'SELECT * from CGS.Specimen WHERE StudyCode=\'RP\'')  

Ok can you run another query where some data is returned?

WHERE StudyCode=StudyCode for example? Does this give the same error about -ve lengths?

this means that the above is correct for escaping the StudyCode. The -Ve lenght is another error. Can you return not all columns with * but specific columns where there are no nulls?

回答3:

Try option believeNRows=FALSE. This worked for me on a similar error (the negative length error).

This question was more to the point: Querying Oracle DB from Revolution R using RODBC