[R] Package RODBC sqlQueries

[Marc Schwartz]

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