Database connection for prepared statements

Question

Using an ODBC connection to a mysql database, the connection times-out after a period of 8 hours (the default).

In order for my application to be resilient, it must recreate this connection to issue a prepared statement query.

Is it possible to issue a new database connection for a prepared statement?

Does it make sense to do so?

It appears like prepared statements are created on a connection basis.

The current solution is to :

1. Reconnect
2. Re-prepare the queries
3. Execute query

Answer 1

No, prepared statements are tied to a connection, not the other way around. Your current way of handling a timed out connection is fine.

Answer 2

I think, having the connection opened to SQL database for 8 hours is a bad practise. Do you have some long-running operation, that requires so much time? Then consider having a stored procedure in your database, that should dump results into some temporary table, which you can check later.

But normally, the application should release the connection (and, ahh, all prepared statements with it) ASAP. To speedup the connection opening, you can use connection pooling (it should exist for ODBC as well). Connection pool will check that connection is "alive" before returning it to application, for example, see validationQuery parameter for Java DBCP.