OLEDB Connection - need help to resolve "Timeout Expired" Error

[poshyradha]

HI all,

In the small Application what I am developing, I am estabilishing a OLE DB connection to acces the data base

It is very big query which is being fired and I always get “Timeout Expired” error at the catch statement
Following is the code what I use

try
	{
		HRESULT hResult;
		hResult = m_pRecordset.CreateInstance(__uuidof(ADODB::Recordset));
		if (FAILED(hResult))
		{
			throw _com_error(hResult);
		}
		else
		{
			
			m_pPIConnection->CursorLocation = ADODB::adUseClient;
			_variant_t varQuery;
			varQuery = cStrSQLQuery.AllocSysString();
			hResult = m_pRecordset->Open(varQuery,m_pPIConnection.GetInterfacePtr(),ADODB::adOpenForwardOnly, ADODB::adLockReadOnly,ADODB::adCmdText);
			if (FAILED(hResult))
			{
				throw _com_error(hResult);
			}
			else
			{
// process the data 
				
		      }		
	}
	catch(_com_error &e)
	{
		nStatusFlag = N_FAILED;
		BSTR bStrErrorDesc;
		bStrErrorDesc = (BSTR)e.Description();
		CString cStrErrorDesc(bStrErrorDesc);
		gcIntfMessage.AddMessage(STR_PI_ERROR_CODE_1006 + STR_SEMICOLON + cStrErrorDesc);
	}
	catch(...)
	{
		nStatusFlag = N_FAILED;
		AfxMessageBox(_T("Unhandlled exception"));
	};

the database connection is established using the following lines of code

cStrConnectionString.Format(_T("Provider=PIOLEDB;Data Source=%s;Initial Catalog=PIARCHIVE;Connect Timeout=100;User Id=%s;Password=%s;"),m_sProsPIServer.cStrDSN,m_sProsPIServer.cStrUserName,m_sProsPIServer.cStrPassword);
			_bstr_t bStrConnectionString = cStrConnectionString;
			hr = m_pPIConnection->Open(bStrConnectionString, L"",L"", ADODB::adConnectUnspecified);
			if (SUCCEEDED(hr))
			{
				bConnectionStatus = TRUE;
			}

I tried adjusting the “Connect Timeout” property in the above connect string …. But still I Get the same
Error can some one help me to get trough?

Thanks and Regards
RK

[g_gili]

Just for a simple information . Does a simple 'small' query works? I mean I want to know that here is a general problem or the problem is for that big query.

Best regards,
Gili

[TheCPUWizard]

Also be careful with your use of a Client Cursor.

If you go directly to the DBMS box, and execute the query interactively, how long does it take?

[poshyradha]

Hi all,
as g_gili said, the simple query works,
when the query request for more data then it fails with time out expired
I was searching through internet, one place I found a guy saying me to set property called commandTimeout as zero and it worked

cStrConnectionString.Format(_T("Provider=PIOLEDB;Data Source=%s;Initial Catalog=PIARCHIVE;User Id=%s;Password=%s;"),m_sProsPIServer.cStrDSN,m_sProsPIServer.cStrUserName,m_sProsPIServer.cStrPassword);
			_bstr_t bStrConnectionString = cStrConnectionString;
			hr = m_pPIConnection->Open(bStrConnectionString, L"",L"", ADODB::adConnectUnspecified);
			m_pPIConnection->CommandTimeout =0;

but he hasn't expalined the funda behind it?
what this proprty is for? and why it should be set to zero....
I did searched in the internet but i could not locate the correct explanation..... Can some one help me? please....

[poshyradha]

as an additional information

the declaration of m_piConnection is as follows

	ADODB::_ConnectionPtr m_pPIConnection;

[jdbdjdbd]

Does your big query work if you directly write your SQL in database ? This will tell if the problem is in your query or in your program.

[poshyradha]

HI all,
I Found the answer.....

setting the property to zerotells the connection to never time out until the query gets executed.

but the best thing to do is to optimize the query such that it does not take such a long time......

that is what i have read from one of the guys..... []