ODBC connection

Clovertech Forums Read Only Archives Cloverleaf Cloverleaf ODBC connection

  • Creator
    Topic
  • #48856
    Barb Dozier
    Participant

      We are running 5.4 on a AIX clusterd machine with a SQL Server installation on its own Windows cluster.  We configured tcl scripts to connect to different tables within the SQL database.  These scripts make an initial connection and keep that connection until the process is cycled.  However, if we loose connection to our SQL server we have found that our tcl scripts do not auto reconnect and all our messages end up going to the error database.  The connection is not reestablished until the process is cycled.

      We are wondering if anyone that deals with ODBC connections typically uses persistant or non-persistant connections?  And how have you dealt with reestablishing a connection if it is lost in a persistant connection world without having to cycle the QDX process?

      Thanks,

      Barb Dozier

    Viewing 7 reply threads
    • Author
      Replies
      • #59935
        Steve Robertson
        Participant

          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.

        • #59936
          Todd Horst
          Participant

            Steve Robertson wrote:

            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.

          • #59937
            Steve Robertson
            Participant

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

              Steve Robertson

              TeamHealth, Inc.

            • #59938
              Mark McDaid
              Participant

                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.

              • #59939
                Todd Horst
                Participant

                  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…

                • #59940
                  Mark McDaid
                  Participant

                    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?

                  • #59941
                    Steve Robertson
                    Participant

                      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.

                    • #59942
                      Mark McDaid
                      Participant

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

                    Viewing 7 reply threads
                    • The forum ‘Cloverleaf’ is closed to new topics and replies.