# Automatic prepared statement problem **URL:** \ **Category:** Databases\ **Created:** [December 29, 2020, 6:48pm UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108 "2020-12-29T18:48:02Z")\ **Posts on this page:** 9\ **Page:** 2
**Author:** ![Antonio\_Rinaldi](https://forum.xojo.com/user_avatar/forum.xojo.com/antonio_rinaldi/32/331_2.png) [@Antonio\_Rinaldi](https://forum.xojo.com/u/Antonio_Rinaldi)\ **Post date:** [December 30, 2020, 10:58am UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/21 "2020-12-30T10:58:28Z")
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 ---
**Author:** ![anon20074439](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/ac8455/32.png) [@anon20074439](https://forum.xojo.com/u/anon20074439)\ **Post date:** [December 30, 2020, 11:16am UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/22 "2020-12-30T11:16:13Z")
> [@anon20074439](#): > > Side note, make sure you’re not mixing old and new api calls, if you have an error using an api1 call and the api2 call is fine then it’ll raise the error from the api1 call on the api2 call. [[https://xojo.com/issue/58909](https://xojo.com/issue/58909) ]([https://xojo.com/issue/58909](https://xojo.com/issue/58909))apparently fixed this for postgresql but I just checked and its still in there for sqlite on 2020r2.1. Bug report anyone? I doubt this’ll be happening in your case though as you’re replacing code blocks and it goes back to not raising the error. 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 . ---
**Author:** ![TimStreater](https://forum.xojo.com/user_avatar/forum.xojo.com/timstreater/32/586_2.png) [@TimStreater](https://forum.xojo.com/u/TimStreater)\ **Post date:** [December 30, 2020, 11:32am UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/23 "2020-12-30T11:32:55Z")
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’ ? ---
**Author:** ![Dean\_Davidge](https://forum.xojo.com/user_avatar/forum.xojo.com/dean_davidge/32/244_2.png) [@Dean\_Davidge](https://forum.xojo.com/u/Dean_Davidge)\ **Post date:** [December 30, 2020, 5:54pm UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/24 "2020-12-30T17:54:04Z")
> [@anon20074439](#): > > 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 . 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. ---
**Author:** ![anon20074439](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/ac8455/32.png) [@anon20074439](https://forum.xojo.com/u/anon20074439)\ **Post date:** [December 30, 2020, 7:01pm UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/25 "2020-12-30T19:01:34Z")
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. ---
**Author:** ![Jeannot\_Muller](https://forum.xojo.com/user_avatar/forum.xojo.com/jeannot_muller/32/13359_2.png) [@Jeannot\_Muller](https://forum.xojo.com/u/Jeannot_Muller)\ **Post date:** [December 30, 2020, 10:21pm UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/26 "2020-12-30T22:21:47Z")
> [@anon20074439](#): > > 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. ---
**Author:** ![Dean\_Davidge](https://forum.xojo.com/user_avatar/forum.xojo.com/dean_davidge/32/244_2.png) [@Dean\_Davidge](https://forum.xojo.com/u/Dean_Davidge)\ **Post date:** [December 31, 2020, 12:55am UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/27 "2020-12-31T00:55:27Z")
> [@anon20074439](#): > > 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. 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. ---
**Author:** ![anon20074439](https://forum.xojo.com/letter_avatar_proxy/v4/letter/a/ac8455/32.png) [@anon20074439](https://forum.xojo.com/u/anon20074439)\ **Post date:** [December 31, 2020, 10:19am UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/28 "2020-12-31T10:19:55Z")
> [@Jeannot\_Muller](#): > > Is it not anyhow best practise to close the connection as soon as you don’t need any longer. 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. > [@Dean\_Davidge](#): > > 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. Fingers crossed 🙂 > [@Dean\_Davidge](#): > > 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. 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. ---
**Author:** ![Jeannot\_Muller](https://forum.xojo.com/user_avatar/forum.xojo.com/jeannot_muller/32/13359_2.png) [@Jeannot\_Muller](https://forum.xojo.com/u/Jeannot_Muller)\ **Post date:** [December 31, 2020, 1:26pm UTC](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108/29 "2020-12-31T13:26:57Z")
> [@anon20074439](#): > > 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. Thank you for the confirmation. Yes, I’m lucky that the initial handshake is nothing in comparison to my usually complex SELECTS :-). [Previous page](https://forum.xojo.com/t/automatic-prepared-statement-problem/60108.md?page=1)