DatabaseException versus Database.Error

[Beatrix_Willius]

When are exceptions used and when errors for SQLite? I thought that only exceptions were to be used anymore. But I see a mix of exceptions and errors. In the following code I’m making a simple database and add one row of data:

Var dbFile As New FolderItem("MyDB.sqlite")

Var db As SQLiteDatabase
db = New SQLiteDatabase
db.DatabaseFile = dbFile
Try
  call db.CreateDatabaseFile
  Dim sql As String = "CREATE TABLE ""AccountsAndMailClients""(""AccountID"" Text PRIMARY KEY, ""Accountname"" Text NOT NULL, ""Username"" Text, ""Servername"" Text, ""Port"" Integer, ""Authentication"" Text, ""UseSSL"" Boolean, ""IsIMAP"" Boolean NOT NULL)"
  
  db.ExecuteSQL(sql)
Catch error As DatabaseException
  MessageBox("Database error: " + error.Message)
End Try


try
  
  dim theSQL as String= "INSERT INTO AccountsAndMailClients(AccountID, AccountName, ServerName, IsIMAP, Port, Authentication, UseSSL) VALUES(?, ?, ?, 1, 993, 'Password' true)"
  dim thePreparedStatement as SQLitePreparedStatement = db.Prepare(theSQL)
  thePreparedStatement.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
  thePreparedStatement.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
  thePreparedStatement.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
  thePreparedStatement.SQLExecute("AccountID", "AccountName", "ServerName")
  
catch err as DatabaseException
  MessageBox("Database error: " + err.Message)
end try

It took me quite a while to see that I missed a comma in the INSERT. The exception is never reached. The error is set instead:

Bug or feature?

macOS 10.13+10.15, Xojo 2019.3 and latest whatnot.

[DerkJ]

Preparedstatements can now be passed as simple parameters inside the SelectSQL and ExecuteSQL methods. You should use that instead.

The preparedstatement is not trowing an exception, could be a bug. Since i don’t see it noted as deprecated.

[Dale_Arends]

Bug or feature? Unclear. The docs state that an error in the SQL will cause the Prepare statement to throw a Database Error so what you are seeing is, technically, correct. Whether it should be seen as an exception is another issue.

[Tim_Hare]

SQLitePreparedStatement is the old method and should not throw an exception. That said, it isn’t clear if there should be a replacement or not.

[Wayne_Golding]

I have never had a prepare statement return an error, it’s not until you execute the statement that you’ll get an error.

I do notice that you are using SQLExecute which will not throw an exception, try using executeSQL instead.

Also as others have noted you don’t need to create the prepared statement or set the bind types with API 2.0 commands.

  dim theSQL as String= "INSERT INTO AccountsAndMailClients(AccountID, AccountName, ServerName, IsIMAP, Port, Authentication, UseSSL) VALUES(?, ?, ?, 1, 993, 'Password' true)"
  db.executeSQL(theSQL, "AccountID", "AccountName", "ServerName")

Would be the API 2.0 code and would have thrown the exception.

[Beatrix_Willius]

Thanks, Wayne, to my eyes ExecuteSQL and SQLExecute look the same. According to the docs SQLExecute is deprecated. But I don’t see that after doing a Cmd-K. Made a bug report.

[TimStreater]

What do you mean by “Look the same” ?

[Tim_Parnell]

@TimStreater

What do you mean by “Look the same” ?

They’re flip flop versions of each other and it’s not clear which one is which unless you’re super familiar with the framework.

[TimStreater]

I just changhed the insides of my wrapper methods and carried on as before.

[Jeannot_Muller]

I believe Beatrix meant that it is difficult to realize that there is a difference in either using ExecuteSQL or SQLExecute and remembering what one should be used for. If you are typing fast an trust autocomplete you most likely will be happy that the IDE is autocompleting w/o realizing there might be a trap.