ODBC Connection Issue

[David Harrison]

Cloverleaf 5.6 rel 2 on Solaris 10

I

Topic

[Jim Kosloskey]

David,

Check the value in the err variable from this statement:

set err [odbc SQLConnect $hdbc $dsn SQL_NTS $usr SQL_NTS $pwd SQL_NTS]

If the value is SQL_ERR then there was a problem connecting.

You can get the details of the error by executing SQLGetDiagRec (look up the syntax on the Microsoft site).

What we do if the connection is rejected is to sleep for a period of time for x number of periods. If we still have an error then we take error handling action (send email/pages, stop threads, etc.)

One thing though the thread where the ODBC activity is occuring should be in its own process so the sleep and other timing issues with ODBC do not interfere with other work in the site (including the arrival of new messages).

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

[Tom Rioux]

David,

Our code is built of a template from Jim’s.  We do the same type of error checking but take it one step further.   We have a replicated database that is a copy of the production database.   They are on separate servers.  If we can’t connect to the primary (after a set number of initial attempts) then we “fail over” to the replicated database.   When the next call to the database occurs, we will check to see if the primary database is up before making the call to the replicated database.

Thanks…

Tom

[David Harrison]

Problem is we don’t get an error; it just hangs.

[Jim Kosloskey]

Oh so you are checking the err variable and that never comes back?

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

[Tom Rioux]

You may also want to try the following:

odbc SQLGetConnectAttr $hdbc SQL_ATTR_CONNECTION_DEAD dbv 100 cnt]

Check the value in the “dbv” variable.  If it comes back as “SQL_CD_TRUE” then you don’t have a connection.  If it comes back as “SQL_CD_FALSE” then your connection is valid.

I believe that last time I spoke to Jim, they connect/disconnect with every message.   We connect at startup and keep a constant connection.   Before each call, we will run this command to ensure that we are still connected to the database.

Hope this helps…..

Tom

[David Harrison]

Tom,

I put in the line as you suggested but without the final ] but it did not return anything in $dbv.

[Tom Gilbert]

If you do get the SQL Error you can run this and this will give you more detail of why…

set err2 [odbc SQLGetDiagRec SQL_HANDLE_STMT $hstmt 1 SqlState NativeError MessageText 511 TextLength]

echo err2 $err2

echo sqlstate $SqlState

echo nativeerror $NativeError

echo messagetext $MessageText

[Author]

Replies