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:
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
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?
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.
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.