Help!!! Can I fetch output parameter value from stored procedure?

[weih]

I cant get any output parameters to work. I use CreateParameter on the _CommandPtr and append them all to the parameters collection ... values get passed in but output parameters come back as VT_NULL. I know the stored proc is fine coz it works when tested in the iSQLw environment (SQL Server).

Here is my code:
_CommandPtr pCmd;
_ParameterPtr pParam;
HRESULT hr;
try{
if(FAILED(hr=pCmd.CreateInstance(__uuidof(Command))))
_com_issue_error(hr);
pCmd->putref_ActiveConnection(pCnn);

pCmd->put_CommandText(_bstr_t("get_client_info2"));
pCmd->put_CommandType(adCmdStoredProc);

pParam=pCmd->CreateParameter(_bstr_t("@lSubjectID"),adInteger,
adParamInput,sizeof(long),_bstr_t(lsubjectID));
pCmd->Parameters->Append(pParam);

pParam=pCmd->CreateParameter(_bstr_t("@lSubjectNo"),adInteger,
adParamInput,sizeof(long),_bstr_t(lSubjectNo));
pCmd->Parameters->Append(pParam);

pParam=pCmd->CreateParameter(_bstr_t("@clientage"),adInteger,
adParamOutput,sizeof(long),_bstr_t("lsubjectno"));
pCmd->Parameters->Append(pParam);

pParam=pCmd->CreateParameter(_bstr_t("@clientsex"),adTinyInt,
adParamOutput,sizeof(BYTE),_bstr_t(pclient->m_nSex));
pCmd->Parameters->Append(pParam);

pCmd->Execute(NULL,NULL,adCmdStoredProc);

pParam = pCmd->Parameters->GetItem("@clientage");
temp = pParam->GetValue();
if(temp.vt != VT_NULL)
pclient->m_nAge = temp;

pParam = pCmd->Parameters->GetItem("@clientsex");
}
catch(_com_error &e)
{
dump_com_error(e);
return FALSE;
}


I never step into
"pclient->m_nAge = temp;"

Who can help me?

[cplussed]

Suggestion: Why don't you change your stored procedure to have the output param to take a certain value(maybe -ve value) when it is NULL?
I had similar problems once and I solved it this way.No headaches atleast.
But you may want to research abt other values vt may take. NULL is only one of the 8-10 possible values.

[senthu74]

Hi
I tried your code with my stored procedure in SQL Server 7.0, (which returns both recordset and otutput params) I found the following:

Case 1: Without Recordset
pCmd->Execute(NULL,NULL,adCmdStoredProc);
If the output is not directed to a recordset, I get the output param correctly.

Case 2: With Recordset
pRecordset = pCmd->Execute(NULL,NULL,adCmdStoredProc);
If I redirect the output to a recordset then the output param is NULL (VT_EMPTY).

Code:

Stored Proc:
CREATE PROC TestTest (@input integer, @Outparam INTEGER OUTPUT)
AS
BEGIN
SELECT @Outparam = @input*@input
SELECT @input*@input*@input as output
END

Visual C++:

_ConnectionPtr pCnn("ADODB.Connection");
pCnn-> Open("DSN=XXX;UID=xx;PWD=yy;", "", "", adConnectUnspecified);

VARIANT nInputVal, nOutputVal;

nOutputVal.vt = VT_I4; //Output param
nInputVal.vt = VT_I4; //Input param
V_I4(&nInputVal) = 5;

_ParameterPtr pParam;
_RecordsetPtr pRecordSet("ADODB.Recordset");
_CommandPtr pCmd("ADODB.Command");
HRESULT hr;

try
{
pCmd->putref_ActiveConnection(pCnn);

pCmd->put_CommandText(_bstr_t("TestTest"));
pCmd->put_CommandType(adCmdStoredProc);

pParam=pCmd->CreateParameter(_bstr_t("first"),adInteger,
adParamInput,sizeof(long), nInputVal);
pCmd->Parameters->Append(pParam);

pParam=pCmd->CreateParameter(_bstr_t("second"),adInteger,
adParamOutput,sizeof(long));
pCmd->Parameters->Append(pParam);

pCmd->Execute(NULL,NULL,adCmdStoredProc);

pParam = pCmd->Parameters->GetItem("second");
nOutputVal = pParam->GetValue();
}
catch(_com_error &e)
{
dump_com_error(e);
//Other handling....
}

[Slavo Hodul]

We are using combination like this and it works:

CString sProcName;
pParam = CMD->CreateParameter("", adVarChar, adParamOutput, MAX_IDNAMELEN*2+1, (LPCSTR) &sProcName); // stored procedure delivers back name of another SP
CMD->Parameters->Append(pParam);

// execute command
CMD->Execute(NULL, NULL, adExecuteNoRecords);

pParam = CMD->Parameters->GetItem(11l);
sProcName = (LPCSTR) static_cast(pParam->GetValue());