CODEWITHSUNDEEP
rrodbc
Praveen DA
Praveen DA

Reputation: 354

Execute SQL with "like" statement in R Language

I am trying to execute a SQL Query through R to get the data from Access DB

Normal SQL statement works fine, but when it comes to like statement its throwing error

Below is code : 

library(RODBC);
channel = odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb,    *.accdb)};DBQ=C:/Users/ADMIN/Documents/R.accdb")
test = sqlQuery(channel ,paste('SELECT R.ID, R.Template, R.WEDate FROM R WHERE R.Template Like "*slow*"'))

Error: [1] "07002 -3010 [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 2."
[2] "[RODBC] ERROR: Could not SQLExecDirect 'SELECT R.ID, R.Template, R.WEDate FROM R WHERE (R.Template Like \"slow\")'

Is there a way to fix this.

Upvotes: 2

Views: 1425

Answers (1)

Parfait
Parfait

Reputation: 107587

Consider both of @joran's suggestions with single quote enclosing string literals AND using the ANSI-92 wildcard operator %. You would use asterisk, * (ANSI-89 mode) when running an internal query, namely inside the MSAccess.exe GUI program (which defaults to DAO) or if you connect externally to Access with DAO. Meanwhile, ADO connections uses the percent symbol which most external interfaces uses including RODBC.

I was able to reproduce your issue and both these remedies worked. Also, no need to use paste() as you are not concatenating any other object to query statement.

library(RODBC);
channel = odbcDriverConnect("Driver={Microsoft Access Driver (*.mdb, *.accdb)}; 
                             DBQ=C:/Users/ADMIN/Documents/R.accdb")

test = sqlQuery(channel, 
                "SELECT R.ID, R.Template, R.WEDate FROM R WHERE R.Template Like '%slow%'")

Upvotes: 2

Related Questions