iOS Exception Handling

I am developing an iOS app that accesses an embedded SQLite database. In general, everything is working fine including connecting to the DB, running standard queries, and sending the output to a Table or file for display in an HtmlViewer. However, on one View I am allowing the user to enter an SQL select statement into a Textarea. Pressing a submit button passes the SQL statement to the constructor of a second view which executes the statement and populates a table. This works fine when the SQL statement is well formed. If there are errors in the SQL statement (such as a simple typo) the app crashes in the Simulator even though I have tried to catch the exception.

To be honest, I don’t completely understand how exceptions or catch/try work even after reading the documentation and reviewing some of the forum comments. What I would like to have happen when an error is raised is to provide the user with a MessageBox warning them to check their SQL statement and return them to the initial view for another try. Is that possible?

My attempts at catch/try seem to have no effect. My MessageBox is never displayed and the app crashes in the simulator. Here is what I’ve tried. When the SQL is well formed, there are no problems. Any suggestions/explanations/tips would be much appreciated.

 if db.connect then
    Try
      r = db.SQLSelect(sql) 
    Catch e As SQLiteException
      Dim err As Text = e.Reason
      MessageBox1.Show
    End Try

I don’t think issuing incorrect SQL to an SQLite database will always raise an SQLiteException (and I’m too lazy to check right now), but in any case you should check the db.error property. If it is true then your recordset will be nil and a NilObjectException will be raised once you try to access the recordset. So do:

      r = db.SQLSelect(sql) 
if db.error then
....

Ouch. When I attempt to run the following, the IDE says that "Type ‘SQLiteDatabase’ has no member named ‘Error’ and points at my ‘if db.Error then’ statement. Does that mean I cannot catch SQLiteDatabase errors at all.

The Language Reference seems to indicate that SQLiteDatabases have an Error property. This is my most recent attempt (which returned the no member named Error message)

  Super.Constructor
  
  dim f as FolderItem
  f = SpecialFolder.GetResource("mydb")
  Dim db As SQLiteDatabase
  db = new SQLiteDatabase
  db.DatabaseFile = f
  Dim r As SQLiteRecordSet
 
 if db.connect then
    r = db.SQLSelect(sql) 
    if db.Error then
      MessageBox1.show
    else
      MessageBox1.Title = "Good"
      MessageBox1.Show
    end
  end

You need to make sure you are using the specific iOS documentation.

SQLiteDatabase on iOS does not use the Error/ErrorMessage properties like it does on Desktop/Web.

Your initial code looks fine to me for iOS. With invalid SQL I expect the SQLiteException to be raised with information on the error in the Reason property. And in a quick test here, this is exactly what happened for me.

Have you stepped through the code in the debugger? Are you certain the app crash is occurring here?

Macmillan and Bob, thanks for the replies.

It appears that SQLite errors cannot be caught and safely handled in iOS.

My app currently includes several canned sql that a user could select from. However, the database is for research purposes and I would really like to provide a method for users to submit their own queries which I have not anticipated. Some of those SQL queries could be complicated. I can’t see an easy way to help them quickly form a fail-safe query because there are too many fields and relevant selection criteria from which they might wish to choose. Providing a textarea to input their own SQL works fine, but only if the SQL has no errors.

Does anyone have any ideas on I could attack this better. It really would improve the usefulness of the app greatly.

I tested a bit more. It looks like the exception is raised but continuing does crash the iOS app. That is definitely a bug.

Thanks Paul.

Working on your first comment, I entered a well formed SQL into the textarea and it worked fine. The I used the same SQL but misspelled “select” and the app broke at

“r = db.SQLSelect(sql)”

When I resumed, the app crashed in the simulator.

Given that you now believe it is a bug, do you have any ideas for a workaround.

No, I am not aware of a workaround. Seems like it is related to <https://xojo.com/issue/42592>, though. You can favorite it to get status updates.

Probably. the problem is internal and about select.

If you send a malformed sqlExecute then it raise the exception (no crash)
If you send a malformed sqlSelect then in the crash log I see:

0 rbframework.dylib 0x000bdb16 SQLiteRecordSet_MoveNext + 38 1 rbframework.dylib 0x001edc20 SQLiteDatabase_SQLSelect + 224 2 com.testapp.sqltst 0x00003391 SQLiteDatabase.*SQLSelect%o<SQLiteRecordSet>%o<SQLiteDatabase>yA1x + 133

so the plugin tries for do a move next to go to the first record