On Wed, 19 Nov 2008 16:38:54 -0800, Don Dwiggins <ddwiggins@advpubtech.com> wrote:
I've been using adbapi (with pyodbc talking to MS SQL Server) successfully in a Windows service. I've discovered that occasionally, the network between the service and the database machine goes down and comes back up (either that or the database server itself cycles down and back up). When this happens, any pyodbc connections hanging around are corrupted, so that the next query gets an exception (class pyodbc.Error). Currently, the best thing I can do is to restart the service.
I'm trying to rewrite the service to be able to catch, analyze, and respond to exceptions from pyodbc. In particular, in the case above, it's possible to reconnect and retry the query. If the network connection is still down, I'd like to log it, send a message to alert someone, or whatever.
How do you tell the difference between a network error which prevented a statement from being executed by the SQL server and a network error which only prevented the response indicating that the statement was successfully executed from being returned to you? If you can't tell the difference, how do you ensure that you don't re-execute statements which modify the database causing corruption of your data?
I've rewritten a ConnectionPool.runQuery call to use runInteraction to call a function (in a thread) that works with the cursor created in runInteraction to execute the query. The function wraps the cursor.execute(...) in a try-except. I can successfully catch the exception, and I try to recover by doing cursor.reconnect(), then cursor.reopen(), and finally cursor.execute(...) again. I'm finding that this fails with a "wrong connection for thread" exception. I haven't been able to figure out why that should happen; more importantly, I'm not sure that I'm going about this in the right way. Any insights or pointers to code that does this kind of thing would be appreciated.
I've never used pyodbc, but presumably the exception indicates you're using the objects in a thread where they're not allowed to be used. You should find out what the threading restrictions of the module are and then see where you're violating them. One thing to keep in mind is that ConnectionPool uses a ThreadPool. That means you're never guaranteed that two different functions will run in the same thread. Whichever thread in the pool is free will run the next task. Jean-Paul