Return value from a Stored Procedure with ADO

[youssef]

Hi,

I use a stored procedure for write to a Database.
When I execute the Stored Procedure, this Stored Procedure acknoledge with a int value if all are OK.

I would like to know how can I do for reading this value.
The value must be extract from the "Status".

You find my sample code:


//Initialize the connection with the Database RTLSQL1 ARCHIVES_NUM
//COM initialization
CoInitialize (NULL);
_ConnectionPtr m_pConn2("ADODB.Connection"
_ParameterPtr pParam2,pParam3;

try
{
//Check if I can create a instance for a connection into a db
HRESULT hr = m_pConn2.CreateInstance (__uuidof (Connection));

if (FAILED (hr))
{
// Automat_LOG_WITH_TIME(_T("DB : Can't create intance of Connection&quot);
}
else
{
// Automat_LOG_WITH_TIME(_T("DB : I can create intance of Connection&quot);
}

CString strFileName = "JA001206";
//Check if I can Open a session in the RTLSQL1 ARCHIVES_TVI
if (FAILED (m_pConn2->Open("provider=sqloledb.1;database=ARCHIVES_TVI;server=1.1.1.1;uid=Automat;pwd=Automat;network=dbmssocn","","",adConnectUnspecified)))

{
// Automat_LOG_WITH_TIME(_T("DB : Can't open datasource &quot);
// Automat_Send_SMS(_T("DB : Can't open datasource &quot,_T("&quot);

}
else
{
//Automat_LOG_WITH_TIME(_T("DB : I can open datasource &quot);
CString sT;
sT.Format("%d",1);
_variant_t varLiReturn;
int liReturn = -99;

_CommandPtr pCmd2("ADODB.Command"
pCmd2->ActiveConnection = m_pConn2;
//Store procedure for the DATABASE
pCmd2->CommandText = "StartFileNumV3";

pParam2 = pCmd2->CreateParameter ( _bstr_t ("FileName&quot, adVarChar,
adParamInput, strFileName.GetLength (), (_bstr_t) strFileName);
pCmd2->Parameters->Append ( pParam2);


pParam2 = pCmd2->CreateParameter ( _bstr_t ("AutomatNr&quot, adInteger,
adParamInput, sizeof(int), _variant_t (sT));

pCmd2->Parameters->Append ( pParam2);
pParam2 = pCmd2->CreateParameter(_T("Status&quot,adInteger,adParamOutput,sizeof(int),varLiReturn);
pCmd2->Parameters->Append ( pParam2);

_RecordsetPtr pRecordset;
pRecordset.CreateInstance(__uuidof(Recordset));
pRecordset = pCmd2->Execute(NULL, NULL, adCmdStoredProc);
//******************
//!!!!!!!! I don't know if it is correct to receive the return value
varLiReturn = pCmd2->Parameters->Item[_T("Status&quot]->Value;

Sleep(100); //ms
//Automat_LOG_WITH_TIME(_T("DB : The Store Procedure have been execute with success (StartFileNum)&quot);

//Close the DATABASE
if ( (m_pConn2->State & adStateOpen) == adStateOpen)
{
m_pConn2->Close();
// Automat_LOG_WITH_TIME(_T("DB : I can close datasource RTLSQL1 ARCHIVES_TVI&quot);
}
else
{
// Automat_LOG_WITH_TIME(_T("DB : I can't close datasource RTLSQL1 ARCHIVES_TVI&quot);
}
}
}
catch ( _com_error &e )
{
_bstr_t bstrSource (e.Source());
_bstr_t bstrDescription (e.Description());
CString sErrorDB;
sErrorDB.Format("DB : ERROR !!!!!\r\nException thrown for classes generated by #import\r\nCode = %08lx\r\nCode meaning = %s\r\nSource = %s\r\nDescription = %s\r\n", e.Error (), e.ErrorMessage (), (LPCTSTR) bstrSource, (LPCTSTR) bstrDescription);
// Automat_LOG_WITH_TIME(sErrorDB);
MessageBox (sErrorDB, bstrDescription,MB_OK);
}
catch (...)
{
TRACE ( "*** Unhandled Exception ***" );
}

Sleep(100); //ms
CoUninitialize();

 

[williamu]

Hi,

This question has been asked before and the answer is to ensure that you flush the resultset.

pRecordset = pCmd2->Execute(NULL, NULL, adCmdStoredProc);
while( pRecordset.FlushResultSet( ) ) ;
varLiReturn = pCmd2->Parameters->Item[_T("Status&quot]->Value;

Only when this has been done will the return value of the Parameter will be available.

HTH
William
Software Engineer
ICQ No. 56047340