Package RODBC sqlQueries
On Oct 26, 2012, at 11:02 AM, Steven Ranney <steven.ranney at gmail.com> wrote:
All -
I'm new to SQL and the RODBC package. I've read the documentation
associated with the RODBC package, but I'm still having problems with
my SQL statements; I think my syntax, particularly with respect to my
WHERE statement, is off but I can't find any documentation as to why.
When I run a query from within the Access2007 database, it looks like this:
SELECT tblDataFieldRawSiteVisit.*
FROM tblDataFieldRawSiteVisit
WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan
Site Mapping Detail"));
After inserting that (verbatim) into my R code like this:
testData = sqlQuery(db, SELECT tblDataFieldRawSiteVisit.*
FROM tblDataFieldRawSiteVisit
WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan
Site Mapping Detail"));)
I get the following error message:
Error in source(.trPaths[5], echo = TRUE, max.deparse.length = 150) :
C:\Users\sranney\AppData\Roaming\Tinn-R\tmp\selection.r:2:32:
unexpected symbol
1: db <<- odbcConnectAccess2007(paste(dbPath, dbName, sep = ''))
2: testData = sqlQuery(db, SELECT tblDataFieldRawSiteVisit.
^
Previous RODBC/SQL statements used by other coders at my firm have a
single quotation mark around the SQL query, so I try that:
testData = sqlQuery(db, 'SELECT tblDataFieldRawSiteVisit.*
FROM tblDataFieldRawSiteVisit
WHERE (((tblDataFieldRawSiteVisit.dataForm)="Oyster Transition Plan
Site Mapping Detail"));')
Try something like this. You generally should use double quotes for both and escape the inner double quotes so that they are retained in the character vector passed. Finally, it looks like you are getting newline characters in to the SQL query passed, presumably because your code is on multiple lines. Note the \n's in the error message.
# Build the query using ?paste
Query <- paste("SELECT tblDataFieldRawSiteVisit.*",
"FROM tblDataFieldRawSiteVisit",
"WHERE (((tblDataFieldRawSiteVisit.dataForm)=\"Oyster Transition Plan",
"Site Mapping Detail\"));")
testData <- sqlQuery(db, Query)
It is also possible that you may not need to use the final semi-colon.
Regards,
Marc Schwartz
I get no error messages, but I when call for testData, I get the following:
testData
[1] "07002 -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1." [2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT tblDataFieldRawSiteVisit.*\nFROM tblDataFieldRawSiteVisit\nWHERE (((tblDataFieldRawSiteVisit.dataForm)=\"Oyster Transition Plan Site Mapping Detail\"));'" I can run SELECT FROM statements successfully. I cannot run SELECT FROM WHERE statements without running into some sort of error. Can anyone offer advice as to why I'm having an issue with the WHERE part of this SQL query? I have double checked spelling on my databases, columns, and values. Thank you - SR Steven H. Ranney