?
Solved

Connection Timeout in ADO does not work!

Posted on 2003-02-22
9
Medium Priority
?
1,616 Views
Last Modified: 2007-12-19
Ok, guys, here is a question that I really need answered. Who ever can help me with this one will receiving a whooping 280 points.

I have wrote a program in C++ using ADO, where I connect to SQL Server using ODBC, execute a stored procedure and then get the results. The program is compiled as DLL and is used on W2K machine.

The problem that I noticed is that ConnectionTimeout and CommandTimeout properties do not have an efect on the connection timeout. For example, I have SQL server running on the remote machine and so, I unplug that machine from the network. Then, I start my program, and guess what -^ the timeout value for the connection.open request is always somewhere between 45 secs ad 2 minutes.
I am using MDAC 2.6 (and cannot upgrade).
What I need help with:
1. Be able to set the timeout value for the connection.open in ADO
2. be able to set the timeout value for the command.Execute in ADO

Here is the sample of the code where I do it:


     try    
     {
          HRESULT hr = m_pConn.CreateInstance (__uuidof (Connection));

          if (FAILED (hr))
          {
               _com_error er(hr);
               
               return er.Error();
          }
               
                m_pConn->ConnectionTimeout = 10; // set timeout to 10 seconds
          if (FAILED (m_pConn->Open (_bstr_t (m_connect_str),
                         _bstr_t (""), _bstr_t (""), adConnectUnspecified)))
          {
               _com_error er(hr);
               return er.Error();
          }


// and later on in the code:
          _CommandPtr pCommand;
          _ParameterPtr pParam1;
          hr = pCommand.CreateInstance (__uuidof (Command));


          if (FAILED (hr))
          {
               _com_error er(hr);
               
               return er.Error();
          }
         
          pCommand->ActiveConnection = m_pConn;

                pConnand->CommandTimeout = 10;
          pCommand->CommandText = (_bstr_t)m_SQL;


                    pCommand->CommandType = adCmdStoredProc; // this is a stored procedure

// and later on in the code
         pCommand->Execute(NULL, NULL, adCmdStoredProc);


I was able to get the timeout to work in VB, but not in VC. Please help me to get it to work!

Thank you!

0
Comment
Question by:jeff12345678
[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
9 Comments
 
LVL 49

Expert Comment

by:DanRollins
ID: 7998261
I tried it with Ms SqlServer 2000 running on the same computer as the app, and the timeout works as advertized (actually it throws an exception after the specified number of seconds).

So, I'd say you are experiencing a built-in delay that is related to the network.  One way to work around that would be to use adAsyncConnect in the Open call.  Then poll the State periodically with your own timeout loop.

This sequence is not ready for primetime, but it shows the basics:

    if(FAILED(::CoInitialize(NULL)))
        return;

     CString sConn= "DSN=Pubs;UID=sa;PWD=;";

     _ConnectionPtr pConn= NULL;
     HRESULT hr;
     try {      
          hr= pConn.CreateInstance(__uuidof(Connection));
          pConn->ConnectionString = (LPCSTR)sConn;
          // pConn->ConnectionTimeout= 3L; // not used in this example

          hr= pConn->Open("","","",adAsyncConnect);
          if ( FAILED(hr) ) {
               MessageBox("problem with that hr thingy");              
          }
          DWORD nTimeoutTick= ::GetTickCount()+ (CNUM_ConnTimeoutSecs * 1000);
          while ( (::GetTickCount() < nTimeoutTick )  && (pConn->State == adStateConnecting) ) {
               Sleep(1000);
          }
     }
     catch(...) {  //catch(_com_error &e)
          CString sMsg= "Some other exception";
          if ( pConn->Errors->Count > 0 ) {
               ErrorPtr pErr= pConn->Errors->GetItem(0L);
               sMsg.Format("error %x %s", pErr->Number, (LPCSTR)pErr->Description );
          }
          MessageBox( sMsg, "ooopsidaisy" );              
          return;
     }

     if ( pConn->State != adStateOpen ) {
          MessageBox("BUMMER!");              
     }
     else {
          MessageBox("neato!");              
     }

-- Dan
0
 
LVL 10

Expert Comment

by:makerp
ID: 7998334
if you unplug the network cable then when your client tries to look up the other machines name it will timeout in the time gethostbyname takes to fail. try shutting down sql server instead.


remeber the connection time out is the time ADO will block on a connect call and other things it has to do to establish a connection with sql server. if when it attempts to connect it can not even find the machine then what would be the point hanging around any longer than necessary.
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8000600
makerp may have identfied the specific network-related delay to which I alluded.  You may be able to test this by modifying your ODBC connection settings on the client.  Rather than specifying the server name, specify its IP number.  That may make the network timeout occur more quickly, so the connection timeout will be closer to what you excpect.

-- Dan
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:jeff12345678
ID: 8006543
MakeRP - I checked for what you said by:
1. adding a hosts file to my windows with ip of the destination SQL Server, and it still did not work.
2. trying to use ip address instead of hostname in DSN definition.

Here is something interesting I noticed: if I set ConnectionTimeout = 10 then try to use my ADO, the first attempt times out at 72 seconds. The second one times out at 48 seconds...

If I shut SQL Server down, then Connection.Open returns immediately. The return error codes are the same:-2147467259
0
 
LVL 49

Expert Comment

by:DanRollins
ID: 8011129
That
   Open(...,adAsyncConnect)
followed by time-limited, status-checking polling loop technique seems to be the logical way.  Do you have any other questions?

-- Dan
0
 

Author Comment

by:jeff12345678
ID: 8012766
Dan,

thank you for all your comments, but I tried adAsyncConnect and then my Cmd.Execute would not work!Do I have to change there something as well?

Why would timeout not work on my w2k with ADO 2.6?

Thanks,
Vic
0
 
LVL 49

Accepted Solution

by:
DanRollins earned 1120 total points
ID: 8012888
You would use that technique ONLY for making the connection.  Actually, you should write a function named "TestConnection" using that technique.  If it worked, you'd know there was a good connection.  Immediately close it and then make the conection normally.  Call TestConnection just once -- when the program first starts up.  Thereafter, use normal connection logic.

>>Why would timeout not work on my w2k with ADO 2.6?
It does.  It just takes longer than expected becasue of external, unrelated, network issues.

-- Dan
0
 
LVL 11

Expert Comment

by:bcladd
ID: 9784159
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:

Answered: Points to DanRollins

Please leave any comments here within the next seven days. Experts: Silence
means you don't care.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

-bcl (bcladd)
EE Cleanup Volunteer

0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Article by: SunnyDark
This article's goal is to present you with an easy to use XML wrapper for C++ and also present some interesting techniques that you might use with MS C++. The reason I built this class is to ease the pain of using XML files with C++, since there is…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Suggested Courses
Course of the Month8 days, 20 hours left to enroll

765 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