continue reading and testing SQLite of Xojo,
I have take latest Xojo 2019r3.2
opened example Databases/SQLite/SQLiteExample.xojo_binary_project
In the EditWindow, Save button Action() I have changed query to have error,
City have no assigned:
Var sql As String = “UPDATE Team SET Name = ?, Coach = ?, City WHERE ID = ?”
issue1: This Action() handler still have OLD deprecated SQLExecute() call, and it is in try-catch.
Try
ps.ExecuteSQL
Catch err As DatabaseException
MessageDialog.Show("DB Error: " + err.Message)
Return
End Try
Self.Close
An exception of course NOT happens, because method is old.
issue2: I have changed it to new ExecuteSQL() and Hmm, still there is no exception.
This example should not be using a prepared statement as executeSQL is a prepared statement, and obviously trying to catch an exception on SQLExecute is not going to happen.
PreparedStatement.ExecuteSQL should throw an exception, but doesn’t.
I now very rarely use prepared statements anymore it is much more convenient to just pass the parameters using exececuteSQL.
// Get the values from the TextFields
Var name As String = NameField.Value
Var coach As String = CoachField.Value
Var city As String = CityField.Value
// Use an UPDATE statement to modify the row with the values from
// the TextFields.
Var sql As String = "UPDATE Team SET Name = ?, Coach = ?, City WHERE ID = ?"
Var ps As SQLitePreparedStatement = App.DB.Prepare(sql)
// Set the type and value for each parameter in the SQL
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(0, name)
ps.BindType(1, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(1, coach)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
ps.Bind(2, city)
ps.BindType(3, SQLitePreparedStatement.SQLITE_INTEGER)
ps.Bind(3, mPk)
Try
ps.ExecuteSQL
Catch err As DatabaseException
MessageDialog.Show("DB Error: " + err.Message)
Return
End Try
Self.Close
// Get the values from the TextFields
Var name As String = NameField.Value
Var coach As String = CoachField.Value
Var city As String = CityField.Value
// Use an UPDATE statement to modify the row with the values from
// the TextFields.
Var sql As String = "UPDATE Team SET Name = ?, Coach = ?, City = ? WHERE ID = ?"
try
App.DB.ExecuteSQL(sql, name, coach, city, mPk)
Catch err as DatabaseException
MessageDialog.Show("DB Error: " + err.Message)
Return
End Try
Self.Close
And I suspect this will throw exceptions like you expect to.
Oh and BTW, the problem is that you’ve defined three “variables” and supplied four properties. I had to add “= ?” after “city” in the SQL statement.
Looking at the docs, it does not say that PreparedSQLStatement.ExecuteSQL throws exceptions either, whereas Database.ExecuteSQL explicitly does. I’m not saying that you’re incorrect, just that the fact that it doesn’t throw and we documented it that way may mean that it was intentional. We’ll have to wait for more engineers to get to their desks before we can determine that. I’ll ask @William_Yu to respond.
This is all dependent on what the database back end reports to us. If it doesn’t report an error we don’t throw. You may find SQLite to be one of the most lax in error reporting out of all the databases.
No, this was fixed in April so we should already have seen it. Also, that is a different error than the deliberate one done by the OP.
Both the error in the OP’s code, and the different error in 59604 generate an exception when the ExecuteSQL runs. I don’t know why the prepare doesn’t generate one, but anyone who wants to know could ask on the SQLite Users’ Forum I imagine.
Meanwhile you should all follow @Greg O’Lone’s advice. You don’t need to use prepared statements here.
Well it’s in the docs anyway. The fix isn’t. Our doc system doesn’t have a way to deal with this at the moment when we do a point release so late in the cycle of the next one.
Or we fixed this.