SQLite Select statement with a variable

[ads_96]

How do I use a user defined variable in a SQLite Select statement? I tried this but got an error:

for row in db:nrows( "SELECT \* FROM books WHERE title LIKE %" .. bookTitle .. "% ORDER BY author\_last\_name" ) do

I’ve seen bind used but only for integers. The variable bookTitle is a string.

Any suggestions?

[rob]

The problem is you need quotes around the string inside the query.  For instance:

SELECT \* FROM books WHERE title LIKE "%Hobbit%" ORDER BY author\_last\_name

But that also has to be a string for Lua. And if the variable bookTitle happens to contains an apostrophe, you get into quoting issues in a hurry. Luckily, Lua has three ways to quote strings:  " ", ’ ’ and [[]].

You might want to try:

for row in db:nrows( [[SELECT \* FROM books WHERE title LIKE "%]] .. bookTitle .. [[%" ORDER BY author\_last\_name]] ) do

(Untested of course)

Rob

[ads_96]

Thank you!! 

[rob]

The problem is you need quotes around the string inside the query.  For instance:

SELECT \* FROM books WHERE title LIKE "%Hobbit%" ORDER BY author\_last\_name

But that also has to be a string for Lua. And if the variable bookTitle happens to contains an apostrophe, you get into quoting issues in a hurry. Luckily, Lua has three ways to quote strings:  " ", ’ ’ and [[]].

You might want to try:

for row in db:nrows( [[SELECT \* FROM books WHERE title LIKE "%]] .. bookTitle .. [[%" ORDER BY author\_last\_name]] ) do

(Untested of course)

Rob

[ads_96]

Thank you!!