# How to signal the database server to drop a prepared statement? **URL:** \ **Category:** Databases\ **Created:** [May 4, 2018, 7:29am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313 "2018-05-04T07:29:04Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Oliver\_Jakoubek](https://forum.xojo.com/user_avatar/forum.xojo.com/oliver_jakoubek/32/1178_2.png) [@Oliver\_Jakoubek](https://forum.xojo.com/u/Oliver_Jakoubek)\ **Post date:** [May 4, 2018, 7:29am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/1 "2018-05-04T07:29:04Z")
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). > [@](#): > > The database server maintains data structures for each prepared statement a connection creates. These > structures are only freed when the application signals to the database server that the prepared statements > are no longer needed or if the connection disconnects. To reduce the statement count for a connection, > you must execute the equivalent of a DROP STATEMENT request. The following table lists the > statements you can execute for the APIs supported by SQL Anywhere: > > Interface Statement > > ADO: RecordSet.Close > [ADO.NET](http://ADO.NET): SADataReader.Close or SADataReader.Dispose > embedded SQL: DROP STATEMENT > Java: resultSet.Close, Statement.Close > ODBC: SQLFreeStmt( hstmt, SQL\_DROP ) or SQLFreeHandle( SQL\_HANDLE\_STMT, hstmt ) 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 ---
**Author:** ![Emile\_Schwarz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/e/90ced4/32.png) [@Emile\_Schwarz](https://forum.xojo.com/u/Emile_Schwarz)\ **Post date:** [May 4, 2018, 7:34am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/2 "2018-05-04T07:34:20Z")
Set TimeOut ? ---
**Author:** ![Oliver\_Jakoubek](https://forum.xojo.com/user_avatar/forum.xojo.com/oliver_jakoubek/32/1178_2.png) [@Oliver\_Jakoubek](https://forum.xojo.com/u/Oliver_Jakoubek)\ **Post date:** [May 4, 2018, 8:16am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/3 "2018-05-04T08:16:38Z")
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](https://documentation.xojo.com/index.php/ODBCDatabase.Timeout) ---
**Author:** ![Christian\_Schmitz](https://forum.xojo.com/user_avatar/forum.xojo.com/christian_schmitz/32/158_2.png) [@Christian\_Schmitz](https://forum.xojo.com/u/Christian_Schmitz)\ **Post date:** [May 4, 2018, 8:17am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/4 "2018-05-04T08:17:54Z")
I would expect the prepared statement to go away if the lady reference is closed. Set it to nil to explicit clear it. ---
**Author:** ![Tobias\_Bussmann](https://forum.xojo.com/user_avatar/forum.xojo.com/tobias_bussmann/32/297_2.png) [@Tobias\_Bussmann](https://forum.xojo.com/u/Tobias_Bussmann)\ **Post date:** [May 4, 2018, 10:43am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/5 "2018-05-04T10:43:28Z")
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](https://xojo.com/issue/41633)\> and \<[https://xojo.com/issue/37805](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. ---
**Author:** ![Oliver\_Jakoubek](https://forum.xojo.com/user_avatar/forum.xojo.com/oliver_jakoubek/32/1178_2.png) [@Oliver\_Jakoubek](https://forum.xojo.com/u/Oliver_Jakoubek)\ **Post date:** [May 4, 2018, 12:16pm UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/6 "2018-05-04T12:16:06Z")
> [@386145:@Tobias Bussmann](#): > > I even created a cache for them 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? ---
**Author:** ![Tobias\_Bussmann](https://forum.xojo.com/user_avatar/forum.xojo.com/tobias_bussmann/32/297_2.png) [@Tobias\_Bussmann](https://forum.xojo.com/u/Tobias_Bussmann)\ **Post date:** [May 4, 2018, 12:29pm UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/7 "2018-05-04T12:29:14Z")
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. ---
**Author:** ![Cris\_Rizz](https://forum.xojo.com/letter_avatar_proxy/v4/letter/c/edb3f5/32.png) [@Cris\_Rizz](https://forum.xojo.com/u/Cris_Rizz)\ **Post date:** [May 5, 2018, 10:02pm UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/8 "2018-05-05T22:02:11Z")
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? ---
**Author:** ![Tobias\_Bussmann](https://forum.xojo.com/user_avatar/forum.xojo.com/tobias_bussmann/32/297_2.png) [@Tobias\_Bussmann](https://forum.xojo.com/u/Tobias_Bussmann)\ **Post date:** [May 7, 2018, 8:07am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/9 "2018-05-07T08:07:30Z")
I’m not aware of a way to unbind the values, but rebinding them before use does seem to work fine. ---
**Author:** ![Joost\_Rongen](https://forum.xojo.com/letter_avatar_proxy/v4/letter/j/a9a28c/32.png) [@Joost\_Rongen](https://forum.xojo.com/u/Joost_Rongen)\ **Post date:** [May 7, 2018, 9:03am UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/10 "2018-05-07T09:03:36Z")
> [@386358:@Cris Rizz](#): > > One that Xojo never reuses prepared statements which defeats the performance gains 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. ---
**Author:** ![Maximilian\_Tyrtania](https://forum.xojo.com/user_avatar/forum.xojo.com/maximilian_tyrtania/32/294_2.png) [@Maximilian\_Tyrtania](https://forum.xojo.com/u/Maximilian_Tyrtania)\ **Post date:** [May 7, 2018, 5:07pm UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/11 "2018-05-07T17:07:28Z")
You can also call [deallocate](https://www.postgresql.org/docs/8.1/static/sql-deallocate.html) manually via SQLexecute. ---
**Author:** ![system](https://forum.xojo.com/uploads/default/original/1X/455b4dcc0e61630e9f81940004ddffa49c432a46.png) [@system](https://forum.xojo.com/u/system)\ **Post date:** [October 29, 2020, 7:12pm UTC](https://forum.xojo.com/t/how-to-signal-the-database-server-to-drop-a-prepared-statement/43313/12 "2020-10-29T19:12:12Z")