Creating SQLite Query from iOS Table Data

Targets iOS
1

Hi,

I hope I’m not asking too many questions here on this forum. Its seems like it’s a question a day and I don’t want to be annoying. I really appreciate all the help you folks have given me.

I have a feature in my app where the user can get random records from a SQLite database from multiple topics. Its kind of hard to explain but here is an example. If you have a cookbook with recipes all stored in specific chapters like, Beef, Chicken, Desserts, Pasta, etc… and you want to pick certain chapters and return a user-defined number of random recipes from the chosen chapters and load them into a recordset. In my desktop app I have 2 ListBoxes. The user moves Chapter names from Listbox1 to Listbox2. The user will also enter the number of records to be returned in the recordset and that integer value is entered in txtNum TextField.

In my regular desktop app, I use the following code:

[code] // List all of the chapters that we want to select in an array.
dim i as Integer
dim chapters(-1) as string
dim sql as string

Dim TestNum As Integer //This is the integer for the number of recipes the query will return from the user-define txtNum.Test TextField

TestNum =Val(txtNum.Text)

for i = 0 to ListBox2.listCount - 1
chapters.append uppercase(EscapeSQLData( Trim(ListBox2.cell( i, 0 ) )))
next

// Build the SQL command.
sql = “select * from recipes where UPPER(chapter) in (’”+ join( chapters, “’,’” ) +"’)"+ "order by random() limit "+ str(TestNum)[/code]

That works great. But, in my iOS version I’m having a hard time transferring this code into the iOS framework. I have two Tables. The user selects the chapters from Table1 and they are loaded into Table2. The user then enters the number of recipes in the txtNum TextField. So I want the code to create a recordset from the recipe chapters listed in Table2.

This is the code I am working with:

[code] // List all of the chapters that we want to select in an array.
dim i as Integer
dim chapters(-1) as Text
dim sql as Text
Table2.AddSection("")
dim cell as iOSTableCellData

Dim TestNum As Integer
TestNum=Integer.FromText(txtNum.Text)

for i = 0 to Table2.RowCount(0)
chapters.append uppercase(EscapeSQLData( Trim(Table2.cell i, 0 ) )))
next

// Build the SQL command.
sql = “select * from recipes where UPPER(chapter) in (’”+ join( chapters, “’,’” ) +"’)"+ "order by random() limit "+ str(TestNum)[/code]

When I run the code it throws an error stating that iOSTable has no member named “cell” I thought I defined “cell” above.

Can anyone see what may be going on? This is kind of an important feature in my app and I would really appreciate any help.

2

You’ve declared this:

dim cell as iOSTableCellData

But then you never assign anything to it.

And then you try to access “cell” on Table2, when Table2 does not have a cell method or property:

chapters.append uppercase(EscapeSQLData( Trim(Table2.cell i, 0 ) )))

I think you want to use iOSTable.RowData to get the information for the row. It returns an iOSTableCellData.

3

Thanks Paul. I’m not having much luck with that, but I will keep working on it.

I am a little worried that even if I figure out how to get the text from Table2, it still may not work. For the heck of it I replaced “cell” of Table2 with just text from a textbox so I don’t have to worry about getting the chapter name from Table2. These lines…

chapters.append uppercase(EscapeSQLData(txtSelect.Text, i, 0 ) ))

And

sql = "select * from questions where UPPER(chapter) in ('"+ join( chapters, "','" ) +"')"+  "order by random() limit "+ str(TestNum)

…come back with errors stating that “EscapeSQLData” and “join” do not exist.

Is this code supported in the iOS version of Xojo? If not then the original question does not really matter, I will have to find another way of doing this.

4

Use chapters.join instead on iOS. I’ve never used EscapeSQLData so I’m not sure if there would be an equivalent.

5

[quote=212884:@James Redway]…come back with errors stating that “EscapeSQLData” and “join” do not exist.

Is this code supported in the iOS version of Xojo? If not then the original question does not really matter, I will have to find another way of doing this.[/quote]
I have no idea what EscapeSQLData is, but that is not a Xojo method.

Join is available on the Text data type.

6

Since Join is now on the Text type, you’d write it something like this:

Dim chapterList As Text = Text.Join(chapters, ",")

And use chapterList to build your SQL.

In general, it might be better for you to break your longer lines into multiple shorter lines so you can more easily walk through the debugger and see how each part is created.

7

Cell is not a property of an iOSTable. You’d have to write something more like this:

Dim cell As iOSTableCellData cell = Table2.RowData(0, i) ' 0 = section, i = the row Dim cellText As Text = cell.Text

And then use cellText to append to your array.

8

Hi Paul,

I guess I’m all messed up now. I really have no idea how to do this. The full code I have is:

[code]dim i as Integer
dim chapters as Text
dim sql as Text
Table2.AddSection("")
dim cell as iOSTableCellData

Dim dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child(“KTM6.sqlite”)
If dbFile.Exists Then
App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile

If App.KTM6.Connect Then
 Dim TestNum As Integer
  
  TestNum=Integer.FromText(txtNum.Text)
  for i = 0 to Table2.RowCount(0)
    cell = Table2.RowData(0, i) ' 0 = section, i = the row
    Dim cellText As Text = cell.Text
    next
  
  // Build the SQL command.
  Dim chapterList As Text = Text.Join(celltext, ",")
  sql = "select * from questions where (chapter) in '"+ chapter list "' + order by random() limit "+ str(TestNum)
  rs = App.KTM6.SQLSelect(sql)
  
End If

Else
AlertBox.Message = dbFile.Path
AlertBox.Title = “Unable to locate database.”
AlertBox.Show
End If[/code]

I get an error the celltext does not exist, and I also get a syntax error in the sql select line.

9

You have a scoping issue: cellText does not exist outside of the For loop. Declare it outside the loop if you plan to use it outside the loop.

On your sql line you are getting a syntax error because you have a syntax error: “chapter list” is not anything and it looks like you’re also missing a + to concatenate the Text.

But I may not be helping you practice your debugging skills by telling you the answers…

10

Hi Paul,

I thought that was the purpose of this forum, to get answers when you don’t understand something. In my company, when someone does not understand how to do something, I give them the answer. In fact I provide all my customers unlimited toll free tech support 7 days a week. I understand you probably can’t do that since you have over 290,000 users world wide. I only have 50,000, but I am all by myself writing code and handling only 3 or 4 tech support calls we get each day. I do thank you folks at Xojo for helping me get some of those 50,000 customers. Without your products I would be having a much harder time. But still I need help from time to time…lately a lot because of the new iOS framework. I don’t really like bothering people, but I don’t know where else to turn.

The code I posted above was code taken from a DeskTop Xojo app. In keeping in the cross-platform spirit, I thought I could use that with the iOS app I am trying to build. It does not work, and I can’t find documentation that I can comprehend to help me understand how it should work. I’m not a computer scientist. Although I have been trying to write code since I was 13 using a computer the size of a freezer, used VB and have been a Xojo customer since May 27, 2003, I still have a lot of problems understanding certain things. But, its not from lack of trying.

I think I have the sql statement correct since I no longer get a syntax error.

sql = "select * from questions where (chapter) in   '" + chapterlist +  "'  order by random() limit "+ (txtNum.Text)

But, since the rest of the other code does not work, I guess I will try to find another way of doing this without trying to join the rows of the iOS Table.

Thanks again for your help. I do appreciate it.

11

I already did tell you the answers: Fix the scope problem for cellText and fix the syntax of the sql assignment. It looks like you’ve fixed the SQL assignment. Did you fix the scope issue?

12

If I knew how to fix it, don’t you think I would have fixed it??

13

Oh. Well, when I’m back in my office I’ll see if I can look at it more closely.

14

Hopefully this gets you closer to where you want:

// List all of the chapters that we want to select in an array.
Dim chapters() As Text
For i As Integer = 0 To Table2.RowCount(0) - 1
  Dim cell As iOSTableCellData
  cell = Table2.RowData(0, i) ' 0 = section, i = the row
  Dim cellText As Text = cell.Text
  chapters.Append(cellText)
Next

// Build the SQL command.
Dim testNum As Integer = Integer.FromText(txtNum.Text)
Dim chapterList As Text = Text.Join(chapters, ",")
Dim sql As Text
sql = "SELECT * FROM recipes WHERE Upper(chapter) IN ('" + chapterList + "')" + _
  " ORDER BY Random() LIMIT " + testNum.ToText
15

Thank you very much Paul. As soon as I’m back at my computer I will try it out. Thanks again.

16

Hi Paul,

I was working on this code and I can’t seem to get it to work. I started over and made a sample app with a database. I created an sqlite database that has two tables. One is a list of Chapters and the second is a list of Recipes.

When the app is run Table1 reads the Chapters table and lists all the chapters in that Table1. You can then click on Table1 and choose the Chapters that you want to create a random selection from and they are displayed in Table2. You can then enter the number of recipes in a textbox which defaults to 10 and click on the Create List button. The code is suppose to randomly search the chapters displayed in Table2 from the Recipes Database Table and display them in Table3.

Unfortunately, it does not work. It does not throw and error but it does not display anything. I was wondering if anyone can see what is going wrong.

The project file is here.
link text

Here is the actual code on the Create List Button

[code] Dim dbFile As FolderItem

dbFile = SpecialFolder.Documents.Child(“KTM6.sqlite”)

If dbFile.Exists Then
App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile

If App.KTM6.Connect Then
  
  // List all of the chapters that we want to select in an array.
  Dim chapters() As Text
  For i As Integer = 0 To Table2.RowCount(0) - 1
    Dim cell As iOSTableCellData
    cell = Table2.RowData(0, i) ' 0 = section, i = the row
    Dim cellText As Text = cell.Text
    chapters.Append(cellText)
  Next
  
  // Build the SQL command.
  Dim testNum As Integer = Integer.FromText(txtNum.Text)
  Dim chapterList As Text = Text.Join(chapters, ",")
  Dim sql As Text
  sql = "SELECT * FROM Recipes WHERE Upper(Chapter) IN ('" + chapterList + "')" + " ORDER BY Random() LIMIT " + testNum.ToText
  rs = App.KTM6.SQLSelect(sql)
  
  Table3.AddSection("")
  While Not rs.eof
    Table3.AddRow(0,  (rs.Field("Type").TextValue))
    
    rs.MoveNext
  wend
  
End If

Else
AlertBox.Message = dbFile.Path
AlertBox.Title = “Unable to locate database.”
AlertBox.Show
End If

rs.MoveFirst
txtK.Text= (rs.Field(“Type”).TextValue)[/code]

17

I set a breakpoint on the first line of the cmdCreate.Action code and stepped through the debugger. When I got to rs.EOF I saw that if skipped immediately to the “wend”. This tells me the RecordSet was empty.

So I checked the the sql variable in the debugger. It was this:

SELECT * FROM Recipes WHERE Upper(Chapter) IN ('Fish,Chicken,Pasta') ORDER BY Random() LIMIT 10

That is valid SQL but it is not going to do what you expect. That SQL essentially says to look for Chapter values called “Fish,Chicken,Pasta” (all one word). You actually want to look for Chapter values containing each of those words. So you need to have each item that is part of the “IN” clause be in quotes. It should instead be:

SELECT * FROM Recipes WHERE Upper(Chapter) IN ('Fish','Chicken','Pasta') ORDER BY Random() LIMIT 10

But that won’t return any results either because you are calling “Upper(Chapter)” but the values in your IN clause are not uppercase. It should actually be:

SELECT * FROM Recipes WHERE Upper(Chapter) IN ('FISH','CHICKEN','PASTA') ORDER BY Random() LIMIT 10

So that is what is wrong. Once I determined that, fixing it was straightforward.

First, you want to save the values into the chapters array so that the quotes are included and the values are uppercase. Change this line:

Dim cellText As Text = cell.Text

to

Dim cellText As Text = "'" + cell.Text.Uppercase + "'"

Then you need to update the sql assignment so that it does not add the quotes because they are already included:

sql = "SELECT * FROM Recipes WHERE Upper(Chapter) IN (" + chapterList + ")" + " ORDER BY Random() LIMIT " + testNum.ToText

With these two changes, your example is working for me.

18

Thank you very much Paul! i would have never figured it out. I have been working on it for a few days, and was just not getting anywhere. I very much appreciate the help. Thanks a lot.

19

Why you use cellText?
You can append directly the upper case value and join instead of with the comma char, with the single quote - comma - single quote sequence.

in the loop:
chapters.Append(cell.Text.Uppercase)

Building sql:
… Upper(Chapter) IN (’" + Text.Join(chapters, “’, '”) + "’) …