Trouble With SQLite Statement

[Knightlite]

Hi,

I am writing an iOS app that uses a SQLite database. I am not sure about a SQlite statement.

The Name of the Table is "Questions"
The Name of the Field is "Chapter"

If I use this statement it will return all records that have the chapter name as "Fire Behavior"

Code: Select all

put "SELECT * FROM Questions WHERE Chapter='Fire Behavior'" into tSQLQuery

But what if I want a text field to be the source of the searching criteria. The text field is called "txtK"

When I use this line of code it does not work.

Code: Select all

put "SELECT * FROM Questions WHERE Chapter=' " & txtK & " ' " into tSQLQuery

I was wondering if anyone knew what the problem was with this line. I want to search for text that is displayed in the text box txtK.

Any help would be greatly appreciated.

Jim

[Mark]

Jin,

You need to refer to the field correctly. txtK is interpreted as a variable in your syntax. The correct syntax to refer to your field is

Code: Select all

fld "txtK"

Kind regards,

Mark

[Knightlite]

Thank you Mark for your reply. But I don't see how you write the query.

Code: Select all

put "SELECT * FROM Questions WHERE Chapter= fld  '" & txtK &"'" into tSQLQuery

Does not work.

Code: Select all

put "SELECT * FROM Questions WHERE Chapter= fld "txtK"" into tSQLQuery

That doe not work either. Can you tell me what the exact syntax should be.

Thank you.

Jim

[Mark]

Jim,

In

Code: Select all

put "SELECT * FROM Questions WHERE Chapter=' " & txtK & " ' " into tSQLQuery

replace

Code: Select all

txtK

with the correct reference to the field as mentioned above. Have a close look at your own syntax and take a little time to think about it.

I consider it my mission to make people think about their own code.

Kind regards,

Mark

[Klaus]

Hi Jim,

do this:
...
## Fill the variable with the content of the field with the same name
put fld "txtK" into txtK
put "SELECT * FROM Questions WHERE Chapter=' " & txtK & " ' " into tSQLQuery
...

Best

Klaus

[Knightlite]

I appreciate that Mark, and I have certainly been thinking about this code for a while, at least for the last 24 hours. I have tried to utilize SQLite Query code that works from my Visual Basic 3, Visual Basic 6, and RealBasic projects, and none seem to port well to LiveCode. They work in their platforms. but not in LiveCode, and since documentation for SQL syntax does appear to be not readily available for LiveCode, it just becomes a guessing game.

I assume that your solution as you describe it above was to utilize the code:

Code: Select all

put "SELECT * FROM Questions WHERE Chapter=' " & fld "txtK" & " ' " into tSQLQuery

At least that is how interpret what you wrote. This does not work either. Nor does:

Code: Select all

put "SELECT * FROM Questions WHERE Chapter=' " & field "txtK" & " ' " into tSQLQuery

I don't get it.

Jim

[Mark]

Hi Jim,

The syntax seems correct now. Both versions are correct. Fld is just an abbreviation of field.

Are you sure that you want to have spaces before and after the data? Perhaps you should remove the spaces right after and before the single quotes to make sure that you're searching for 'your data' instead of ' your data '.

Kind regards,

Mark

[sturgis]

Looks to me like you have some extra spaces in the query. So it will end up being: chapter = ' the contents of the field ' .. Notice the space between the single quote and the start of the contents of the field.

The only way you will find a match is if your database has a space padding at the start and end of the chapter name. Dump the extra spaces and try it again.

sql syntax is sql syntax, the difficulty is in building a string that will work. One thing I do quite a bit when i'm having trouble with this sort of thing is to use an answer dialog to actually look at the resulting the string.

Since you are putting the query string into tSQLQuery, the the next line can either "put tSQLQuery" so that you can visually check it in the message box to make sure it looks correct, or use "answer information tSQLQuery"

Might also look at some more of the examples in the dictionary including the substitution method. Also might look at merge because it can make some of the string building easier.

I appreciate that Mark, and I have certainly been thinking about this code for a while, at least for the last 24 hours. I have tried to utilize SQLite Query code that works from my Visual Basic 3, Visual Basic 6, and RealBasic projects, and none seem to port well to LiveCode. They work in their platforms. but not in LiveCode, and since documentation for SQL syntax does appear to be not readily available for LiveCode, it just becomes a guessing game.

I assume that your solution as you describe it above was to utilize the code:

Code: Select all

put "SELECT * FROM Questions WHERE Chapter=' " & fld "txtK" & " ' " into tSQLQuery

At least that is how interpret what you wrote. This does not work either. Nor does:

Code: Select all

put "SELECT * FROM Questions WHERE Chapter=' " & field "txtK" & " ' " into tSQLQuery

I don't get it.



Jim

[Knightlite]

Hi Mark and Klaus,

You were right. The spaces are what screwed things up. Mark's line of code works:

Code: Select all

put "SELECT * FROM Questions WHERE Chapter='" & fld "txtK" & "'" into tSQLQuery

And so does Klaus's

Code: Select all

put fld "txtK" into txtK
put "SELECT * FROM Questions WHERE Chapter='" & txtK & "'" into tSQLQuery

They both work great, once you take out all the spaces. That was what the problem was. I thought maybe the spaces are ignored, but they are not.

Thanks so very much for all your help. I really appreciate it. LiveCode is now the 4th language I am attempting to learn, and there are always barriers in the way. I appreciate the people on this forum who take time out of their busy days to help others.

Thanks again.

Jim

[Knightlite]

Thank you sturgis. You were certainly correct. It was the spaces. I will take your advise on using the answer dialog box.

Thanks again.

Jim

[Klaus]

Hi Jim,

...and since documentation for SQL syntax does appear to be not readily available for LiveCode...

well, SQL is SQL and LiveCode is LiveCode

This got me started with SQL:
http://www.w3schools.com/sql/default.asp


Best

Klaus