I have an application that needs reconnect logic using TclODBC. This app uses a mix of prepared statements and SQL executed directly. What I'm wondering is A) is there a best practice for handling reconnect logic, and B) if I reconnect upon a failure to invoke some TclODBC prepared statement, and then try to re-invoke the prepared statement, will it work? Like so:
I don't know of any best practice, I usually create an error handler using catch {} and analyze error based on error code. If error code suggests that connection is lost, try to reconnect etc.
The prepared statements are usually lost with tha database connection (strictly speaking, this is database and driver dependent functionality, and in theory this might work otherways also, but in practice not). So, you need to recreate those too. A good practice for this is to create commonly used statements always after connecting to the database (if there are not too many of those).
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have an application that needs reconnect logic using TclODBC. This app uses a mix of prepared statements and SQL executed directly. What I'm wondering is A) is there a best practice for handling reconnect logic, and B) if I reconnect upon a failure to invoke some TclODBC prepared statement, and then try to re-invoke the prepared statement, will it work? Like so:
dbh statement prepared_st "select * from foo"
-disconnect--
database connect dbh
prepared_st [list args...]
Will the prepared statement continue to work?
I don't know of any best practice, I usually create an error handler using catch {} and analyze error based on error code. If error code suggests that connection is lost, try to reconnect etc.
The prepared statements are usually lost with tha database connection (strictly speaking, this is database and driver dependent functionality, and in theory this might work otherways also, but in practice not). So, you need to recreate those too. A good practice for this is to create commonly used statements always after connecting to the database (if there are not too many of those).