Solved

Check Database Connection

Posted on 2002-06-17
17
2,074 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
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
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
has77  challenge 9 71
Execute multiple curl cmds with sleep and send output to file 10 88
Detect file exist or not 3 128
Can not remove SSL certificate on iPhone 6 - iOS10.2 12 232
This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
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 is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

932 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

12 Experts available now in Live!

Get 1:1 Help Now