How to signal the database server to drop a prepared statement?

Hi, currently I’m coding a command line tool that uses a timer to check for “job definitions” in a database table and then processes those jobs (like sending an email or writing data to a csv file).

I’m using prepared statements extensively and this led me to serious problems with my database server (SAP [Sybase] SQL Anywhere 16). Connection to the database is over an ODBCDatabase object, the prepared statements are ODBCPreparedStatement objects.

The problem is: the SQL Anywhere server reserves space for every prepared statement. The statement exists on the server either until the connection ends or the statement gets explicitly dropped by the client. There is a max_statement_count option in SQL Anywhere (defaults to 50).

Because my tool is intended to have a “long-living” connection to the database disconnecting and connecting after every statement would be only a workaround.

Is there a proper way to signal the server to drop a statement? Setting the prepared statement object in Xojo to Nil doesn’t help because the server does not know it. ODBCPreparedStatement has no method like “drop” or “close”, neither has ODBCDatabase.

Thank you

Set TimeOut ?

As I see it Timeout is the time the database object waits for the connection to succeed (?)

https://documentation.xojo.com/index.php/ODBCDatabase.Timeout

I would expect the prepared statement to go away if the lady reference is closed.
Set it to nil to explicit clear it.

This is a problem with PostgreSQLPreparedStatement as well. They are left orphaned on the server if the object goes out of scope. See <https://xojo.com/issue/41633> and <https://xojo.com/issue/37805> The easiest workaround is to keep the PreparedStatement objects and reuse them. I even created a cache for them so each statement is prepared only once per DB-connection in my applications.

Good idea, Tobias. How did you build your cache? A dictionary at app level with a descriptive name as key and the prepared statement as value?

actually, I use the whole SQL statement as key for the dictionary. The Cache-Dictionary is a property of my Database subclass where I’ve overwritten the Prepare Method so that it first checks if the statement was already prepared, if not a PreparedStatement is created by Super.Prepare(Statement) and added to the cache. The found or created statement is then returned. You can also add some cleanup of the Cache on connect/close and maybe have some fancy mechanism to decide if and how long to cache the statement. But with a server-side limit of only 50 you will likely rather try to reconnect one that is reached.
When re-using prepared statements, make sure you freshly bind all values every time you make use of it to not get in trouble with old, ‘ghost’ values from the last use of the PS.

Hi Tobias, I’m building a web app using PostgreSQL on Xojo Cloud. And I noticed this problem with prepared statements. One that Xojo never reuses prepared statements which defeats the performance gains. And two, that it never deallocates the prepared statements when they go out of scope.

I came upon this post and I would like to implement your caching solution for the prepared statements. Is there a way to clear the previously bound values on the prepared statement or is just the act of re-binding new values enough to avoid problems?

I’m not aware of a way to unbind the values, but rebinding them before use does seem to work fine.

About two years ago i already asked questions about this and never got a clear answer since my experience was/is Xojo in fact not cashing / reusing prepared statements on the PG server at all.

You can also call deallocate manually via SQLexecute.