Barb, I’m working on persistient connections right now. I think I have it solved
Until now, our ODBC connections were always established, used, and closed for each message processed. Doing this entails a lot of overhead on the database server (Oracle for us), and throughput can be a problem from time to time.
Here is what I’m doing now: I establish the ODBC environment and open the database connection is the start section of the tcl proc, and close/free in the shutdown section. Now, as I process each message, I check to see if the connection is still active. If so, fine. If not, I reconnect. If I can’t reconnect after a certain number of retries, I disp the message to the Cloverleaf error database.
Where I was running into a problem is when our database and listener (an Oracle-only thing) is down for backups. The connection drops and can’t be re-established. I was able to easily detect the connection drop, but I would get a tcl error due to a missing handle when I tried to reconnect. I just got that figured out this morning. I’ll be testing this over the long holiday weekend.
Some of the stuff I was running into probably won’t be a problem with SQLServer. That is, unless the machine is completely off the network. Then maybe you could have the same problem.
There was quite a bit of code and work to get this going. But it will be worth it for the extra throughput. Anyway, I’ll be glad to answer specific questions or send you some code (if my manager approves).
Best,
Steve Robertson
Team Health, Inc.