Execute SQL with "like" statement in R Language

Question

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.

Try reversing your use of double and single quotes (single quotes on the inside, double on the outside). — joran
– joran, Commented Sep 6, 2016 at 18:26
Not sure, must be something having to do with Access, I run LIKE queries like that using RODBC to other dbs all the time. — joran
– joran, Commented Sep 6, 2016 at 18:32

Parfait · Accepted Answer · 2016-09-06 21:35:47Z

2

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%'")

answered Sep 6, 2016 at 21:35

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Praveen DA Over a year ago

this combination works well , Thanks for the Idea bro

Collectives™ on Stack Overflow

Execute SQL with "like" statement in R Language

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related