Jump to content

[TOPIC: topicViewTemplate]
[GLOBAL: userSmallPhoto]
Photo

SQLite Select statement with a variable
Started by ads_96 Jan 16 2019 03:37 PM

2 replies to this topic
sqlite

Best Answer Rob Miracle , 16 January 2019 - 04:41 PM

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

[TOPIC CONTROLS]
This topic has been archived. This means that you cannot reply to this topic.
[/TOPIC CONTROLS]
[modOptionsDropdown]
[/modOptionsDropdown]
[reputationFilter]
[TOPIC: post.html]
#1

ads_96

[GLOBAL: userInfoPane.html]
ads_96
  • Observer

  • 8 posts
  • Corona SDK

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?



[TOPIC: post.html]
#2

Rob Miracle

[GLOBAL: userInfoPane.html]
Rob Miracle
  • Moderator

  • 25,919 posts
  • Enterprise

  Best Answer

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



[TOPIC: post.html]
#3

ads_96

[GLOBAL: userInfoPane.html]
ads_96
  • Observer

  • 8 posts
  • Corona SDK

Thank you!!  :)




[topic_controls]
[/topic_controls]