Automatic prepared statement problem

I’ve done a quick test and every thing works fine.
For sure there is a hidden character or some other type error.

In any case an exception for A is another evidence for some type error or hidden char.

The API2 prepared statements in your case are reliable

1 Like

Just thinking about this again, if its not the non-ascii character then this is probably what is happening as you’re bringing non-param’d selects in and out with your commented tests so those won’t exhibit the problem anyway. The problem only surfaces when you use param’d api2 selects.

A way to quickly test for this would be to use another new database connection that you’re 100% sure hasn’t been touched by any other code just for this one select (add it in manually just above the selectsql) and if everything works as expected it should quickly tell you if this is what is happening .

Whereas I’ve seen no problems at all with them, and I use no other.

If you’re doing a COMMIT, then one assumes you’re also doing a BEGIN TRANSACTION. Remember that in SQLite, you don’t need to BEGIN or COMMIT a single statement, since SQLite will open a transaction for you. IOW, in this sequence:

Session.db.ExecuteSQL ("BEGIN TRANSACTION")
RowSet = Session.db.SelectSQL (sql, ...)
Session.db.ExecuteSQL ("COMMIT")

the BEGIN and COMMIT may be removed. Or do you mean something else by ‘commit’ ?

1 Like

Creating a new connection appears to have solved the problem for now. This is part of a fairly large app that was initially developed with the old API. Should I create two connections in the session, one for the old API calls and one for the new ones?

Your explanation makes sense as the error message said “near A” and I had taken every A out of the query. I have another error when inserting a new record the error message is “Cannot commit - no transaction is active.”

My thanks to everyone, especially Julian, for your help in figuring this out. I just filed bug report 63188.

1 Like

Nice, glad you got to the bottom of that one, very random indeed.

I just did a few more tests. It looks like API2 SelectSQL with prepared statements is totally borked, if you cause an error/exception, trap it, skip it, then in the same db connection run an error free selectsql with a prepared statement it will fail with the error that you previously caught so its not clearing the error state from either api1 or api2.

So your best temporary solution would be to establish a new clean connection to the db unless you can guarantee that you’re not going to raise an error or that error could show up later when there is in fact no error. Fun.

Is it not anyhow best practise to close the connection as soon as you don’t need any longer. At least that’s how I’m doing it for years, but never really asked myself why, it is just the ways I’m always doing it to reduce the amount of active connections to an absolute minimum. Not saying that his particular issue doesn’t sound like a nasty bug, but only to better understand if my approach is wrong.

I am hoping this only happens if Xojo recognizes the error. In developing my app I had several instances of trying to write a record to a MySQL database where there was no error, but the record was not written (wrong field name). I already test for an active connection so if I find and error and close the connection, that should take care of it, I hope.

I have converted the app that started this whole thing entirely to API 2.0 and thought I would be in the clear. Guess not.

It depends on the use case as there’s overhead in opening/closing the connection but for a local db with infrequent access it should be fine.

Fingers crossed :slight_smile:

Doh, yes early adopters are usually always stung but once the bugs are ironed out its hopefully smooth sailing as you’ve already done the lions share of the work.

1 Like

Thank you for the confirmation. Yes, I’m lucky that the initial handshake is nothing in comparison to my usually complex SELECTS :-).