Lingering Database connections

[Mike Campbell]

Cloverleaf 5.7 – patch 2 AIX platform.

I have a tcl proc, called from within an Xlate, to connect to an external database and perform queries to obtain the MRN.  It appears the connections are not closing cleanly, leaving connections active that aren’t really active.  

In the proc, I’m issuing the following before I exit:

         set err [odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt]

             echo “Free handle stmt: ” $err

         set err [odbc SQLDisconnect $hDBConnection]

             echo “Disconnect: ” $err

         set err [odbc SQLFreeHandle SQL_HANDLE_DBC $hDBConnection]

             echo “Free Handle DBC: ” $err

         set err [odbc SQLFreeHandle SQL_HANDLE_ENV $henv]

            echo “Free Handle ENV: ” $err

The echos all return

 Disconnect:  SQL_SUCCESS

 Free Handle DBC:  SQL_SUCCESS

 Free Handle ENV:  SQL_SUCCESS

 Free handle stmt:  SQL_SUCCESS

Is there another command I need to use?  

Thanks.

Mike C.

Topic

[Jim Kosloskey]

Mike,

Those are the 4 I am using for Oracle, SqlServer, and DB2 (Mainframe) and that seems to work properly.

Of course I am only invoking a Stored Procedure and not doing the business rules hence individual ODBC activity for various services inside Cloverleaf.

Are you allocating new statement handles for each statement if you are not just using Stored Procedure? If so, maybe you need to free all of those statement handles – just a thought.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Mike Campbell]

The issue here is the proc actually does 2 queries of the database.

The first gets a value that is used in the second one.  So the question is:

do I need to do two sets of close/disconnect commands?  

[If the first query fails to return a value, I do the database cleanup and get out of the proc.]  

[Jim Kosloskey]

Mike,

Once I knew I needed to close the connection completely (I am done) I would close both.

Again, if you just invoked a Stored Procedure all of that heavy lifting (not the connect/disconnect but the business rules) would be done there.

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Tom Rioux]

Mike,

We have a similar procedure here.  The only difference is that is a stand-alone proc and not called from within an Xlate.  

We have one ODBC proc that is similar to Jim’s that does a call to a stored procedure.   We also have one that is similar to yours.  In ours, we actually make calls to a database to pull information from a table.  Each call is predicated on the information from the previous call.

With both kinds of ODBC proc’s, we are utilizing another step that you don’t have in yours.  It has to do with the statement handle.  Here is what we are doing:

   catch {odbc SQLFreeStmt $hstmt SQL_CLOSE}

   catch {odbc SQLFreeHandle SQL_HANDLE_STMT $hstmt}

The SQL_CLOSE release the hold the user name has on the database.  We found that, under certain circumstances, we would free the statement handle but the instances of the user names would continue to grow on the database.   This could lead to the database shutting down.

Try adding the SQL_CLOSE to your code and see if clears up your issue.  It did for us.

Thanks…

Tom Rioux

[Mike Campbell]

Thomas, thanks for the hints.  I’ll add the catch statements to my proc.  

What I have done that seems to be working, is to ‘Keep it Simple Stupid’,

the old KISS idea.

I broke out the two queries into separate tclprocs, and ensure the connections are free’d before starting the second query.

Mike C.

[Jim Kosloskey]

If using ODBC 3.0 please note (from the Microsoft ODBC Web site):

Summary

SQLFreeHandle frees resources associated with a specific environment, connection, statement, or descriptor handle.

Note  

This function is a generic function for freeing handles. It replaces the ODBC 2.0 functions SQLFreeConnect (for freeing a connection handle) and SQLFreeEnv (for freeing an environment handle). SQLFreeConnect and SQLFreeEnv are both deprecated in ODBC 3.x. SQLFreeHandle also replaces the ODBC 2.0 function SQLFreeStmt (with the SQL_DROP Option) for freeing a statement handle. For more information, see “Comments.” For more information about what the Driver Manager maps this function to when an ODBC 3.x application is working with an ODBC 2.x driver, see Mapping Replacement Functions for Backward Compatibility of Applications.

So it appears SQLFreeStmt is not applicable in ODBC 3.0.

I am not sure why this would make a difference in your case Tom but perhaps the target DB is using ODBC 2.0??

This link gives some information regarding mixing ODBC 3.0 and ODBC 2.0:

http://msdn.microsoft.com/en-us/library/windows/desktop/ms716450(v=vs.85).aspx

email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.

[Author]

Replies