Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Connection Timeout in ADO does not work!

Posted on 2003-02-22
9
Medium Priority
?
1,671 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
8 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
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!

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
Suggested Courses

579 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