ODBC connection

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.

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.

Todd, This has worked out pretty well. We’ve been using this in production for over a year now.

Steve Robertson

TeamHealth, Inc.

Steve,

I’m trying to change some code to use a persistent connection to a SQL database, but in the run section of code, I’m having trouble figuring out how to test that the connection is still active.  I’m using the tclodbc package.  Do you have any code you could share or any suggestions?  Thanks.

this obviously may not be the best method but you can do a simple query that you know will always work. Aka this table should always exist.

select * from table.

If it fails you need to make a new connection…

Todd,

If the table is very large, wouldn’t the overhead of doing that query be more than the overhead of simply creating the connection for each message processed?

Mark, yes, a large table will be a problem. Just create a table with one row or zero rows. It’s only purpose in life will be to respond to the ODBC test.

If anyone reading this has Oracle, just use a query to dual to check the connection. (For non-Oracle folks out there, dual is a system “pseudo table” that always exists. It is used for just such occasions as this.)

Best regards,

Steve Robertson

TeamHealth, Inc.

That’s a really good idea.  The most obvious solutions always seem to escape me.  Thanks!  😀