Problem Loading SQLite Recordset

Targets iOS
1

Hi,

I have an SQLite database that I am trying to access in the IOS version of Xojo. I am getting an nil error on the Open Event when I try running the code below on the line:

rs = db.SQLSelect(sql)

The full code is:

[code]Dim db As New SQLiteDatabase

Dim dbFile As FolderItem
dbFile = SpecialFolder.Documents.Child(“Orders.sqlite”)

If dbFile.Exists Then
db.DatabaseFile = dbFile
If db.Connect Then
//proceed with database operations here…
Else
Dim err As Text = “Could not open the database.”
End If
End If

Dim sql as Text

sql = “SELECT * FROM Orders602”

Dim rs As SQLiteRecordSet
Try
rs = db.SQLSelect(sql)
Catch e As SQLiteException
ErrorLabel.Text = e.Reason
Return
End Try

rs.MoveLast

txtFirst.Text = rs.Field(“First”).TextValue
[/code]

Can anyone see what I am doing wrong. Loading an SQLite database seems to be a little different than loading one for a desktop app. Any help would be greatly appreciated.

Jim

2

Actually, iOSSQLiteDatabase has almost exactly the same API as the one used for desktop/web/console.

Does the DB exist? If not, you code will most certainly crash.

3

Hi Paul,

Thanks for trying to help me.

This is the code I use for my desktop apps and it works fine. But it does not work under iOS.

[code] Orders= new RealSQLDatabase
Orders.DatabaseFile = GetFolderItem(“Orders.rsd”)
If not Orders.Connect Then
msgbox “Cannot open the database. Aborting.”
Quit
end

Dim sqlOrders as string
sqlOrders = “select * from Orders602”
rsOrders=Orders.SQLSelect(sqlOrders)
rsOrders.Movelast[/code]

But, I get an error that comes back and says GetFolderItem does not exist. I changed the database so it is sqlite instead of rsd. I have the database file in the same folder as the app. So I don’t know what I am doing wrong.

4

What does this mean? How are you getting the database into the same folder as an iOS app?

You’ll want to use a Copy File build step to copy the file and the use SpecialFolder.GetResource to get a FolderItem to it. Then you’ll be able to open it.

Look at the EddiesElectronics example in Examples/iOS/Apps to see how this is done.

5

I just put it in the same folder as the Xojo file. I guess that was wrong. I was copying code from your documentation SQLDatabase under iOS section.

I will take a look at the code for Eddies. By the way when you try to build the Eddies app, Xojo locks up and you have to force quit it.

Thanks Paul. I appreciate your help.

6

Remember, your iOS apps run on the iOS Simulator, so any files your app uses have to be copied over to the Simulator.

[quote=151188:@James Redway]I will take a look at the code for Eddies. By the way when you try to build the Eddies app, Xojo locks up and you have to force quit it.
[/quote]
I presume you mean Run and not Build? Anyway, I am not seeing any problem with running or building the EE example on my two Mac here.

7

Thank you Paul. That did the trick. I can now connect to the database. I really appreciate your help.

It was on the BUILD. That was me… again. The code was not signed. Once I did that, it did not lock up.

I don’t know if you are aware of this or it might be something I’m doing again, but I tested the Eddie app on an iPad Retina and the app crashes when you edit a customer’s information and then press “Done” The app just disappears from the screen.

8

James,

I’m experiencing the same issue (and happy to not be alone). I also had the same problem trying to run the EE example, but it finally started up in the emulator.

I’m not clear on what you did to get it running (it may be too early in the AM). Can you elaborate? Thanks!

9

Hi Michael,

Yesterday, when I tried to build the EE example to test it on the iPad, it would lock up Xojo and I would have to Force Quit it. I then realized there was no code signing, so I added my code signing and then it worked. It would load fine on the device.

The app works fine in the iOS simulator. It you change a record, it records the changes, but when I try to edit and save the changes on an iPad the app crashes.

10

I’ve tested EE on an iPhone and I don’t recall it crashing, but I haven’t tried it on an iPad recently. I’ll give that a shot to see if I can reproduce the crash.

11

I’m getting a sudden quit of EEiOS on my iPhone 5 after editing a record and hitting done.

12

Thanks all. I got it working. I was getting hung up on using the build script to pass the db to the debug folder using the Copy File event. It helps to get the build script steps in the right order!

13

Could you please tell a beginner like me what do you mean by copying to the simulator ?

I am working on porting a simple game application from OSX to IOS that uses a sqlite database. Where do I copy the database ? I presume that as long as I run the application, it will be somewhere on the computer hard disk. But when I will build it, how do I do the transfer to the mobile ?

14

@Gilles Rioux I’m just learning it too. Here’s what I did to finally get it working.

  1. Get your project open and click on Build Settings->iOS section.
  2. Then select the Insert->Build Step->Copy Files
  3. This creates a CopyFiles1 under the iOS section. Click on that.
  4. This should open a blank window with the small title: “Drag files into the list…”
  5. Drag files into that window that you want to copy.
  6. On the right side (inspector) make sure the destination is set to “Resource Folder”.
  7. Then test with Run. I tested with 1 Button and a TextArea to list the resources. Here is my code for the Action of the Button.

[code] Dim db as new SQLiteDatabase
Dim dbFile as FolderItem
dbFile=SpecialFolder.GetResource(“Main.db”)
Dim sql As Text
sql = “SELECT * FROM whatever”

Dim data As SQLiteRecordSet
If dbFile.Exists Then
db.DatabaseFile = dbFile
If db.Connect Then
Try
data = db.SQLSelect(sql)
Catch e As SQLiteException
TextArea1.Text = e.Reason
Return
End Try

  If data <> Nil Then
    While Not data.EOF
      TextArea1.Text = TextArea1.Text + data.IdxField(2).TextValue
      data.MoveNext
    Wend
    data.Close
  End If
Else
  Dim err As Text = "Could not open the database."
End If

End If[/code]

Its not the final code and not sleek, but its my first test to get it working. The error checking is because I couldn’t figure out the correct commands to use (before looking at examples).

I hope this helps.

15

OK, thank you. It was the “build step - copy files” that I did not understood. Now it is working.

16

Yeah it was steps #2 and #6 that were getting me, and then using “SpecialFolder.GetResource(“Main.db”)”