Check Database Connection

Hi,
I created a CDatabase object that connects to a remote database by ODBC. I would like to know the connection status before I execute a query in my application.
(if there is no connection, the applications falls)

Is there any way to know if the connection to the DB is on or off (in case the DB computer is turned off or any connection failure had occured )????
Thanks in advance
kukiyaAsked:
Who is Participating?
 
peterchen092700Connect With a Mentor Commented:
>> CDatabase::IsOpen()
AFAIK it only checks that the ODBC Handle != NULL...
0
 
kukiyaAuthor Commented:
I forgot to say the DB is SQL Server
0
 
peterchen092700Commented:
a) what is "the application fails"?
b) do you have the query execution in a TRY/CATCH block? (If any error occurs, a CDBException will be thrown)
c) There is not much use in checking the connection beforehand - if the DB disconencts while the query runs, your applicaiton would fail again. In fact, since it would require an additional roundtrip to the server, it's likely a performance hit.


 
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TriskelionCommented:
What about CDatabase::IsOpen();
0
 
jhanceCommented:
In my opinion, you should code your application such that ANY possibility w.r.t. the database server is accounted for.  For starters the possible outcomes are:

1) network is down
2) network is up but server is down
3) server is up but not accepting connections
4) server is up and accepting connections but ODBC is broken.
5) server is up, accepting connection, ODBC is OK, but something fails in mid stream.
6) All is well and everything works.

Since any of the above may happen at any time, you should be prapared to handle ANY of them at ANY time.  Otherwise your application will most likely hang or crash.
0
 
kukiyaAuthor Commented:
I tryied to put the code  "IsOpen" under try/catch.
But the problem is that the ODBC checks for connection for some time, and then there is a message of time out.
(Since the DB server was off intentionally)
it doesnt even get to the Catch code.
0
 
kukiyaAuthor Commented:
What I extually need is the "SQLConnect" function that checks the ODBC connection.
The problem is that I don't know how to set the timeout.
Meaning: in case of connection failure, it takes about 2 min to get the error of the connection
(2 min to check the connection).

How do I decrease the timeout feature ????
0
 
jhanceCommented:
See SQLSetConnectAttr and the SQL_ATTR_CONNECTION_TIMEOUT item there.
0
 
kukiyaAuthor Commented:
I did looked for that , but nothing worked for me.
I maybe wrote something that didn't work.

My code:
SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void*)5, 0);

The above should set the time to 5 sec.
although it steel takes a long time to check the connection.

Did I write my code well ???

Thanks!!!!
0
 
jhanceCommented:
>>>Did I write my code well ???

No!

1)

The docs say:

"Returns

SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_ERROR, or SQL_INVALID_HANDLE."

Where do you check for SQL_SUCCESS?  Unless it returns that, how do you know the call was successful?

2) I said use the SQL_ATTR_CONNECTION_TIMEOUT property.  You set SQL_LOGIN_TIMEOUT but in fact SQL_LOGIN_TIMEOUT seems NOT to exist.  The proper attribute constant is SQL_ATTR_LOGIN_TIMEOUT.  I suspect you MAY want to set BOTH but NOT SQL_LOGIN_TIMEOUT.
0
 
jhanceCommented:
Oh, #3...

3) The construct:

SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (void*)5, 0);

IS WRONG!!!!!!  

What you want is:

if(SQLSetConnectAttr(hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)5, SQL_IS_UINTEGER) != SQL_SUCCESS){
  // Process whatever error seems appropriate...
}
0
 
kukiyaAuthor Commented:
I wrote the following code with your advise,
but it's still takes a great time .
     
/*Allocate environment handle */
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
     
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set the ODBC version environment attribute */
etcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
         
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Allocate connection handle */
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
                             
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
/* Set login timeout to 5 seconds. */
SQLSetConnectAttr(hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)5, SQL_IS_UINTEGER);

/* Connect to data source */
retcode = SQLConnect(hdbc, (SQLCHAR*) "MyODBCName", SQL_NTS,(SQLCHAR*) "", SQL_NTS,(SQLCHAR*) "", SQL_NTS);

//if (retcode !=SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO){
AfxMessageBox( _T(":No ODBC Connection") );
}
0
 
jhanceCommented:
1) What is a "great time"?
2) What about the SQL_ATTR_LOGIN_TIMEOUT?
3) What statement above is causing the delay?
0
 
kukiyaAuthor Commented:
1)about 2 min , but certainlly not 5 sec.
2) I added :

SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)5, SQL_IS_UINTEGER);

(in addition to SQL_ATTR_CONNECTION_TIMEOUT, but it didn't help!)

3)The statment that takes about 2 min is:

/* Connect to data source */
retcode = SQLConnect(hdbc, (SQLCHAR*) "MyODBCName", SQL_NTS,(SQLCHAR*) "", SQL_NTS,(SQLCHAR*) "", SQL_NTS);


Thanks  !!!!!

0
 
anandsudhirCommented:
Continuing this thread,

I'm also facing the exactly same problem as "kukiya", but i'm using SQLDriverConnect instead of "SQLConnect".

DB on SQL Server 2000. Client calls DB using ODBC.

Steps to reproduce problem:

1. Call SQLSetConnectAttr() SQL_ATTR_CONNECTION_TIMEOUT (which, as per MSDN, is actually ignored) to 10 seconds and SQL_ATTR_LOGIN_TIMEOUT also to 10 seconds.
2. Simulate a connection failure by disconnecting the SQL Server machine from the network.
3. Open the connection using SQLDriverConnect().

I don't get back the control before a minimum of 45 seconds. The result is the same when I set the timeout value to anything in the range 1 to 40 seconds. Any idea how I could get back the control (with the error) soon after the specified timeout time ?

See my code below :

bool ODBCSupport::OpenConnection()
{
     SQLRETURN   retcode;

     /*Allocate environment handle */
     retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
     if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
     {
          ProcessLogMessages(SQL_HANDLE_DBC, hdbc, "SQLDriverConnect Failed\n\n", TRUE);
          return false;
     }
     
     /* Set the ODBC version environment attribute */
     retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
     if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
     {
          ProcessLogMessages(SQL_HANDLE_DBC, hdbc, "SQLDriverConnect Failed\n\n", TRUE);
          return false;
     }
     
     /* Allocate connection handle */
     retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
     if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
     {
          ProcessLogMessages(SQL_HANDLE_DBC, hdbc, "SQLDriverConnect Failed\n\n", TRUE);
          return false;
     }

     /* Set Connection Timeout Attribute */
     retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_CONNECTION_TIMEOUT, (SQLPOINTER)10, SQL_IS_UINTEGER);
     if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
     {
          ProcessLogMessages(SQL_HANDLE_DBC, hdbc, "SQLDriverConnect Failed\n\n", TRUE);
          return false;
     }

     /* Set Login Timeout Attribute */
     retcode = SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT, (SQLPOINTER)10, SQL_IS_UINTEGER);
     if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
     {
          ProcessLogMessages(SQL_HANDLE_DBC, hdbc, "SQLDriverConnect Failed\n\n", TRUE);
          return false;
     }

     SQLCHAR      ConnStrIn[MAXBUFLEN] ="DRIVER={SQL Server};SERVER=hms68;UID=sa;PWD=12345678;DATABASE=Arch2030;";
     SQLCHAR      ConnStrOut[MAXBUFLEN];
     SQLSMALLINT   cbConnStrOut = 0;

     retcode = SQLDriverConnect(hdbc,      // Connection handle
            NULL,         // Window handle
            ConnStrIn,      // Input connect string
            SQL_NTS,         // Null-terminated string
            ConnStrOut,      // Address of output buffer
            MAXBUFLEN,      // Size of output buffer
            &cbConnStrOut,   // Address of output length
            SQL_DRIVER_NOPROMPT);

     if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
     {
          ProcessLogMessages(SQL_HANDLE_DBC, hdbc, "SQLDriverConnect Failed\n\n", TRUE);
          return false;
     }
}

Thanks in Advance.

Sudhir

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.