Updating A SQLite Database In iOS

Targets iOS
1

Hi,

I’m having hard time adjusting to this new iOS Framework from the regular Xojo when it comes to updating a SQLite database.

In regular desktop Xojo you can update a database using this code:

rs.Edit rs.field("FirstName").StringValue=trim(txtFirst.Text) rs.field("LastName").StringValue=trim(txtLast.Text) rs.Update

What is the equivalent in iOS? I tried looking at the EEIOS example and just find it really confusing. Its too complicated for me. I guess I am a little slow but I need a really simple example. I wish the XOJO folks would just have a simple example where you can add, delete, and update a record in a SQLite Table, and show how the database gets copied so it can be edited on the actual iOS device.

Any help would be greatly appreciated.

2

SQLExecute with an update SQL statement. The iOS database combines prepared statements into the SQLExecute statement.

3

You’ll have to use an UPDATE statement. From the EE example (Customer.Save method):

[code] Dim sql As Text
sql = "UPDATE Customers SET FirstName = ?1, LastName = ?2, " + _
"Address = ?3, City = ?4, State = ?5, Zip = ?6, Phone = ?7, " + _
“Email = ?8, Taxable = ?9 WHERE ID = ?10”

// Pass in values after sql instead of doing string replacement
App.EEDB.SQLExecute(sql, FirstName, LastName, Address, City, State, _
Zip, Phone, Email, Taxable, ID)[/code]

Just use your table and column names and pass in the values you want as part of the SQLExecute method.

4

I’m still having a hard time with this.

I have a sqlite table called Table1 with the fields id (primary key), RecordNum, LastName, and FirstName.

I Open the database with this code:

[code] Dim dbFile As FolderItem

dbFile = SpecialFolder.GetResource(“Test.sqlite”)

If Not SpecialFolder.Documents.Child(“Test.sqlite”).Exists Then
// Only copy the file if it is not already there
Dim dest As FolderItem = SpecialFolder.Documents.Child(“Test.sqlite”)
dbFile.CopyTo(dest)
End If

App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile

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

If App.KTM6.Connect Then
  LoadData
  
End If

End If[/code]

I load the textfields with this code:

[code] Dim sql as Text

sql= “SELECT * FROM Table1”

rs = App.KTM6.SQLSelect(sql)
txtID.Text= (rs.Field(“id”).TextValue)
txtFirst.Text= (rs.Field(“FirstName”).TextValue)
txtLast.Text= (rs.Field(“LastName”).TextValue)
txtNum.Text= (rs.Field(“RecordNum”).TextValue)[/code]

That all works fine. I then run the app and make some changes to the text fields and try to save it using this code:

Dim sql As Text sql = "UPDATE Table1 SET FirstName = ?1, LastName = ?2, WHERE id = ?3" App.KTM6.SQLExecute(sql, txtFirst.Text, txtLast.Text, txtNum.Text)

When I run it, the app throws an exception on the last line. What am I doing wrong.

The project file is here
link text

5

Use a Try Catch on the last line and take a look at the Reason field. It will pretty much tell you what’s wrong.

6

I don’t know how to do that

7

http://documentation.xojo.com/index.php/Try

8

That is not really helpful Ben. I don’t know if I’m implementing it correctly and it tells me nothing.

The updating of a few fields in a simple example for a SQLite table in the iOS version of Xojo should not be rocket science.
I make a change in a field, I should be able to simply update the record.

9

Just based on your pasted code, it looks as though you might still be referencing your database in Resources due to the original location of dbFile, which would mean that it’s read-only and an Update would then fail. Or it could be that your SQL statement has a comma before the “WHERE” which should not be there.

I’d have to agree with Bob on the need to examine the reason for the exception. You should be able to get some idea from the debugger at the point at which the app breaks.

10

I updated the SQLiteDatabase.SQLExecute section with another example showing UPDATE code with exception handling.

11

Clearly, even in the debugger this shows what the error is. You have an error in the SQL Statement. There is an extra comma before the WHERE.

This is not rocket science and you should be learning how to debug your app.

12

And I found this just using the debugger. It is your friend.

13

Thanks Ben and Jason. That was kind of stupid of me not to see that. I was looking more at the SQLExecute line.

I have been writing software for Firefighters for almost 20 years now and using Xojo since around 2008 and I’m still not really too swift at this. Some things are just to cryptic for me to understand. I look at the documentation and the sample apps before I ask a question here, but even with all of that, I still don’t understand what is going on.

As I said before, I think it is helpful for beginners to have an “Idiot-Proof” example to build upon. The Eddies example is not that simple. A very simple iOS database app like a address book that loads the SQLite database on the device and in the iOS simulator with a Add, Delete, and Update features would be very valuable for people like me. I guess I learn better that way.

The code does not throw and exception now when run on the simulator, but it does not update the database either. On the device, it crashes the app.

The code I have for loading the database file is under App Open event. The code is:

[code] Dim dbFile As FolderItem
dbFile = SpecialFolder.GetResource(“Test.sqlite”)

If Not SpecialFolder.Documents.Child(“Test.sqlite”).Exists Then
// Only copy the file if it is not already there
Dim dest As FolderItem = SpecialFolder.Documents.Child(“Test.sqlite”)
dbFile.CopyTo(dest)
End If[/code]

I also copied the Test.sqlite file to the CopyFiles1 under iOS and have it set to App Parent Folder. How should I be referencing the database file so it can be updated on the device and simulator?

The EEiOS example has the same code and that seems to work:

[code] Dim dbFile As FolderItem
dbFile = SpecialFolder.GetResource(“EddiesElectronics.sqlite”)

If Not SpecialFolder.Documents.Child(“EddiesElectronics.sqlite”).Exists Then
// Only copy the file if it is not already there
Dim dest As FolderItem = SpecialFolder.Documents.Child(“EddiesElectronics.sqlite”)
dbFile.CopyTo(dest)
End If[/code]

14

I got it. I had the wrong code on the View. To load the data in the View it should :

[code] Dim dbFile As FolderItem

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

App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile

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

If App.KTM6.Connect Then
  LoadData
  
End If

End If
[/code]
Its working now. Thank you Ben, Jason, and Paul for your help. I really appreciate it.

15

James with regard to the location of your database, this code sets the dbFile location to the Resources folder:

Dim dbFile As FolderItem
  
dbFile = SpecialFolder.GetResource("Test.sqlite")

Then later, this code presumably sets your App DatabaseFile to dbFile:

App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile

I can’t see anywhere in between where you change the location of dbFile to your Documents folder. I can see where you copy dbFile to the Documents folder, but that doesn’t change what dbFile represents, which is the database in Resources.

16

Hi Jason,

When the initial view opens I call a method called GetData.

In that method, I run this code:

[code] Dim dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child(“Test.sqlite”)

App.KTM6 = New SQLiteDatabase
App.KTM6.DatabaseFile = dbFile

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

If App.KTM6.Connect Then
  LoadData
End If

End If[/code]

You were right, I was calling the database from Resources and not documents. I changed it and now, its seems to be working well.
Thats the correct way…right?

17

For what it’s worth I have about 10 hours of iOS specific training video at http://xojo.bkeeney.com/XojoTraining/ for subscribers. Overall there is over 60 hours of video with over 200 individual videos. Most come with project files.

Sadly, I do not have any videos specific to the new iOS SQLiteDatabase class. It’s on my to do list (which is ever growing).

18

Like many things, there’s no one correct way :). I don’t ship a database in this way; I create mine in code for various reasons but mostly because I need to be able to update it from time to time. So I don’t have a strong opinion about how you should copy it out of resources and use it. But if it’s working, then I’d say that’s good enough for me. :slight_smile:

19

I don’t see how this code would run at all:

sql = "UPDATE Table1 SET FirstName = ?1, LastName = ?2, WHERE id = ?3"
The issue is there is a comma after parameter 2. The code should be:

sql = "UPDATE Table1 SET FirstName = ?1, LastName = ?2 WHERE id = ?3"

20

I ship my products with content in the database, and I also need to be able to edit that data, or add new data. The problem I am having now is that if I edit the database in a recordset, I have to reload the recordset in order to see the changes.

If the data is edited I use this code that you guys helped me with below:

[code] Dim sql As Text
sql = “UPDATE Table1 SET FirstName = ?1, LastName = ?2 WHERE id = ?3”

Try
App.KTM6.SQLExecute(sql, txtFirst.Text, txtLast.Text, txtid.Text)
Catch e As SQLiteException
Dim err As Text = e.Reason
End Try[/code]

That works find, but the changes are not immediately seen in the recordset. If I navigate to a different record in the recordset and then navigate back to the edited record, the changes are not seen. You have to reload the recordset to see the changes and then create a bookmark of sorts to navigate back to your original edited record. Xojo is fast enough to do this without any lag.

The problem comes IF you randomize the recordset and try to make a change. Reloading the recordset will not reload in the same order.

So is there a way to update the table which is displayed in the recordset without having to reload the recordset in order to see your changes?

That would be good to do Ben. As I said earlier, I think it would be very helpful if Xojo would produce a very simple iOS SQLite example for idiots like me, such as a simple address book, where you can see how to add, edit, update, and delete records in the database. I have a hard time following the EEIOS example. I also don’t see any way of adding a new record or deleting an existing record in that example.