| | | | | Browse by category |
We observed that neither shutting down the database or killing the database listener process generates an error in the SourcePro DB application program. Can you provide a solution how to approach this in SourcePro DB, so that we receive an error in case of one of the connection loss situations in order to reopen the connection(s)?
Action
In chapter 6 of the current SourcePro DB User's Guide, we give a general idea of how the status() of SourcePro DB objects works. Unfortunately, a point we don't clarify with the RWDBConnection, is that the status of the object only reflects the success of its initial use. In this case, the connection's creation. Subsequent errors on the connection are not reflected in the status() method; this is a functionality we did intend the class to have at this time.
That behavior would be useful, but there are a couple of reasons why it isn't there. One, we would need to change things internally, to have a dictionary of vendor errors and their meanings. This will require a bit of work. Also, sometimes, connections fix themselves (believe it or not), and we would have no way of telling if a lost connection was restored/good again. We may continue to investigate this option, but any changes won't be made soon.
The most general way to tell if ANY kind of network connection is good, is to use it. Even then, you can't tell if you have lost a connection, or if you just have to wait a really long time for a reply; typically you assume that after an arbitrary time has gone by, the DB (or other end of the socket) is no longer there. Until we try to access the database, we don't know if it isn't there anymore. This is why the test case you may have written, can't tell that the database is gone.
In SourcePro DB, we recommend checking the results of any action you perform on the database. That is, check the status of the RWDBResult from the operation (e.g. execute()) you have performed. You can try to create a RWDBSelector statement, execute() it, and monitor the results.
Alternately, or in conjunction with this, register a test with the error handler, for any specific error codes from the database you happen to know about. For example, if a time-out is detected but the socket is still good, you could attempt to merely re-run the query to see if it works a second time. But if the socket has become corrupted, maybe you want to close the RWDBConnection, and try to reopen it.
Further, throwing an exception (in either case) would allow you to skip out of any code that would attempt to continue using the connection, and invoke some kind of recovery or roll-back procedure.
What you want to do when a connection is lost is probably pretty specific to the database you are using, and what you can do is on the client side. In general, you can try to use the RWDBConnection::close() and then open() methods, to try and re-establish the connection. You can call these functions only when the connection is valid. In another words, once a connection is not valid, you may or may not be able to open it (it really depends on how serious your server error is). Sometimes when reopen failed, you may still create new database instances to get rid of the problem. Note also that the status returned for open() must be tested (it isn't guaranteed to work) as the reason the connection was lost may still prevent reconnecting. Also note that any resources allocated to the connection will be lost; hence, objects like RWDBReaders will become worthless, and must be discarded.
Even after installing an error handler on the RWDBDatabase object and catching specific errors indicating a connection failure with the database, you might have difficulty restoring the connection. So, when you try to use the open() method of the RWDBConnection class to restore the connection, it may not work. In this case, the only way to restore the connection is to reassign the RWDBConnection variable using the connection() method of the RWDBDatabase class.
Be sure you are using explicit connections, and disable the connection pool the RWDBDatabase object uses (refer our Knowledge Base article ' Default and Explicit Connections and the DBTools Connection Pool'), so that you may explicitly attempt to restart the connections.
