Solved

Check Database Connection

Posted on 2002-06-17
17
2,093 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:kukiya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +2
17 Comments
 

Author Comment

by:kukiya
ID: 7083973
I forgot to say the DB is SQL Server
0
 
LVL 7

Expert Comment

by:peterchen092700
ID: 7084168
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
 
LVL 6

Expert Comment

by:Triskelion
ID: 7084179
What about CDatabase::IsOpen();
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Accepted Solution

by:
peterchen092700 earned 50 total points
ID: 7084345
>> CDatabase::IsOpen()
AFAIK it only checks that the ODBC Handle != NULL...
0
 
LVL 32

Expert Comment

by:jhance
ID: 7084385
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
 

Author Comment

by:kukiya
ID: 7086009
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
 

Author Comment

by:kukiya
ID: 7088593
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
 
LVL 32

Expert Comment

by:jhance
ID: 7088721
See SQLSetConnectAttr and the SQL_ATTR_CONNECTION_TIMEOUT item there.
0
 

Author Comment

by:kukiya
ID: 7089051
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
 
LVL 32

Expert Comment

by:jhance
ID: 7089468
>>>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
 
LVL 32

Expert Comment

by:jhance
ID: 7089517
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
 

Author Comment

by:kukiya
ID: 7089835
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
 
LVL 32

Expert Comment

by:jhance
ID: 7089862
1) What is a "great time"?
2) What about the SQL_ATTR_LOGIN_TIMEOUT?
3) What statement above is causing the delay?
0
 

Author Comment

by:kukiya
ID: 7089920
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
 

Expert Comment

by:anandsudhir
ID: 7716085
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

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Importing Special Characters in Dynamics GP Through Integration Manager 3 96
if loop in java 3 174
sum67 challenge 35 128
matchUp  challenge 6 82
Introduction: Displaying information on the statusbar.   Continuing from the third article about sudoku.   Open the project in visual studio. Status bar – let’s display the timestamp there.  We need to get the timestamp from the document s…
In this post we will learn different types of Android Layout and some basics of an Android App.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question