Solved

Check Database Connection

Posted on 2002-06-17
17
2,072 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
  • 6
  • 5
  • 2
  • +2
17 Comments
 

Author Comment

by:kukiya
Comment Utility
I forgot to say the DB is SQL Server
0
 
LVL 7

Expert Comment

by:peterchen092700
Comment Utility
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
Comment Utility
What about CDatabase::IsOpen();
0
 
LVL 7

Accepted Solution

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

Expert Comment

by:jhance
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 32

Expert Comment

by:jhance
Comment Utility
See SQLSetConnectAttr and the SQL_ATTR_CONNECTION_TIMEOUT item there.
0
 

Author Comment

by:kukiya
Comment Utility
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
Comment Utility
>>>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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ADO Memory leak with DELPHI 2007 37 153
scoreUp challenge 14 47
Problem to event 3 48
Can not remove SSL certificate on iPhone 6 - iOS10.2 12 81
Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
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.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now