ADO调用存储过程(ORACLE),用CommandText的问题

Question

在使用CommandText时,怎样表示清楚存储过程的入参和出参?
m_pCommand->CommandText=_bstr_t("pr_vitual_query(?,{resultset 20 out_cus_value},{resultset 20 out_result})");
上面的格式编译没错,但执行时会报出IDispatch #3092
原因是resultset的这种写法是错误的,哪位高手可以告诉我正确的格式?
谢谢!!!

Answer 1

//导入ADO库文件
#import "d:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF","EndOfFile")
#include "icrsint.h"int _tmain(int argc, TCHAR* argv[], TCHAR* envp[])
{
_RecordsetPtr m_pRs;
CString m_strConnection;
CString m_strCmdText;CoInitialize(NULL);m_strConnection = _T("Provider=SQLOLEDB.1;Password=;Persist Security Info=True;User ID=test;Initial Catalog=;Data Source=db");
m_strCmdText = _T("select * from Request"); HRESULT hr;
m_pRs = NULL; try{
hr=m_pRs.CreateInstance(_uuidof(Recordset));
if (FAILED(hr)){
printf("err");
}
}
catch(_com_error &e)
{
printf("database error:%s",e.Error());
}
IADORecordBinding* piAdoRecordBinding = NULL;
CCustomRs customRs;
//调用不带参数的存储过程
m_pRs->Close();
m_strCmdText = _T("spSelRequest");
m_pRs->Open((LPCTSTR)m_strCmdText,(LPCTSTR)m_strConnection,adOpenStatic,adLockReadOnly,adCmdStoredProc);
piAdoRecordBinding->Release();
m_pRs->QueryInterface(_uuidof(IADORecordBinding),(LPVOID*)&piAdoRecordBinding);
piAdoRecordBinding->BindToRecordset(&customRs);
printf("\n%s",customRs.m_szreqcontent);
//调用带输入参数的存储过程
m_pRs->Close();
m_strCmdText = _T("exec spSelRequest2 '9999'");
m_pRs->Open((LPCTSTR)m_strCmdText,(LPCTSTR)m_strConnection,adOpenStatic,adLockReadOnly,adCmdUnknown);
piAdoRecordBinding->Release();
m_pRs->QueryInterface(_uuidof(IADORecordBinding),(LPVOID*)&piAdoRecordBinding);
piAdoRecordBinding->BindToRecordset(&customRs);
printf("\n%s",customRs.m_szreqcontent); //调用带输出参数和返回值的存储过程
_ConnectionPtr piConnection; //定义一个连接变量
piConnection.CreateInstance("ADODB.Connection");
CString strCn;
strCn.Empty();
strCn=_T("Provider=SQLOLEDB.1;Password=pw;Persist Security Info=True;User ID=uid;Initial Catalog=s;Data Source=db");
_variant_t bcnstr=_variant_t(strCn);

_variant_t bunstr=_variant_t("dbmodify");
_variant_t bpwdstr=_variant_t("changedata");

//打开一个连接
piConnection->Open(_bstr_t(bcnstr),_bstr_t(bunstr),_bstr_t(bpwdstr),-1);
_CommandPtr cmd;
cmd.CreateInstance("ADODB.Command");

//返回值
_ParameterPtr pParamRet;
pParamRet.CreateInstance("ADODB.Parameter");
//pParamRet->Name="rowcount2"; //
pParamRet->Type=adInteger; //
pParamRet->Direction=adParamReturnValue; //声明是
cmd->Parameters->Append(pParamRet); //输入参数
_ParameterPtr pParamRk;
pParamRk.CreateInstance("ADODB.Parameter");
//pParamRk->Name="conetent"; //存储过程的参数1
pParamRk->Type=adChar; //字符串
pParamRk->Size=20; //
pParamRk->Direction=adParamInput;//表明是输入参数
pParamRk->Value=_variant_t("a");//int->CString->_variant_t后赋值
cmd->Parameters->Append(pParamRk); //输出参数
_ParameterPtr pParamOk;
pParamOk.CreateInstance("ADODB.Parameter");
//pParamOk->Name="rowcount"; //参数2名称
pParamOk->Type=adInteger; //整型
pParamOk->Direction=adParamOutput; //声明是输出参数
cmd->Parameters->Append(pParamOk); cmd->ActiveConnection = piConnection;
cmd->CommandText="spSelRequest3"; //存储过程的名字
cmd->CommandType=adCmdStoredProc;//表示为存储过程adCmdStoredProc
    //执行,获得结果
_RecordsetPtr m_pRecordset;
    m_pRecordset = cmd->Execute(NULL, NULL, adCmdStoredProc);
m_pRecordset->QueryInterface(_uuidof(IADORecordBinding),(LPVOID*)&piAdoRecordBinding);
piAdoRecordBinding->BindToRecordset(&customRs); printf("\n(4)%d",customRs.m_lid);
//_variant_t TheValue; //VARIANT数据类型
//TheValue = m_pRecordset->GetCollect("id");//
//printf("\nID=%s",(char*)_bstr_t(TheValue));
m_pRecordset->Close(); //关闭后才能取得返回值
printf("\n(1)%s",(char*)_bstr_t(pParamRk->Value));
printf("\n(2)%s",(char*)_bstr_t(pParamOk->Value));
printf("\n(3)%s",(char*)_bstr_t(pParamRet->Value));
printf("\n*******"); m_pRs->Close(); piAdoRecordBinding->Release();

CoUninitialize();
return nRetCode;
}
///////////////总结：VC使用AD0调用存储过程要注意参数顺序，先是返回值，再是存储过程的参数列表顺序

Answer 2

我自己写的是这样的,我觉得和楼上的兄弟差不多,我是对ORACLE的存储过程进行调用.
但是在执行Execute的时候总是报出IDispatch #3092,说是存储过程没有被声明,不知道为什么?
请高手指点,急!!!!!
::CoInitialize(NULL);
_ConnectionPtr m_pConnection;
    HRESULT hr;
m_pConnection.CreateInstance (__uuidof(Connection));
m_pConnection->CommandTimeout =30;
_bstr_t strConnect = _T("Provider=MSDAORA.1;Password=test2000;User ID=billing;Data Source=ora8i;Persist Security Info=True");
    hr = m_pConnection->Open (strConnect,"billing","test2000",-1);
    _CommandPtr m_pCommand;
    CString cvar1;
int cvar2;
CString cvar3;
cvar1="123456";
cvar2=0;
    cvar3="";
    try
{
        m_pCommand.CreateInstance(__uuidof(Command));
m_pCommand->ActiveConnection=m_pConnection;
        
        m_pCommand->CommandText="pr_vitual_query"; m_pCommand->CommandType=adCmdStoredProc;
             _variant_t vvar1,vvar2,vvar3;
    vvar1=_variant_t(_bstr_t(cvar1));
char twoout[10];
        sprintf(twoout,"%d",cvar2);
    vvar2=_variant_t(_bstr_t(LPCTSTR(twoout)));
    vvar3=_variant_t(_bstr_t(cvar3));
    _ParameterPtr mp_var1,mp_var2,mp_var3;
    mp_var1.CreateInstance("ADODB.Parameter");;
    mp_var2.CreateInstance("ADODB.Parameter");
    mp_var3.CreateInstance("ADODB.Parameter"); mp_var1=m_pCommand->CreateParameter(_bstr_t(cvar1),adVarChar,adParamInput,20,vvar1);
// mp_var1->Name = "in_cus_id";
// mp_var1->Type = 
m_pCommand->Parameters->Append(mp_var1);  mp_var2=m_pCommand->CreateParameter(_bstr_t(LPCTSTR(twoout)),adInteger,adParamOutput,20,vvar2);
m_pCommand->Parameters->Append(mp_var2);  mp_var3=m_pCommand->CreateParameter(_bstr_t(cvar3),adVarChar,adParamOutput,20,vvar3);
m_pCommand->Parameters->Append(mp_var3); 
        

_variant_t vNull;
vNull.vt=VT_ERROR;
vNull.scode=DISP_E_PARAMNOTFOUND;
m_pCommand->Execute(&vNull,&vNull,adCmdStoredProc);
cvar2=mp_var2->Value.intVal;
  cvar3=mp_var3->Value.bstrVal;
}
catch(_com_error &error)
{
    MessageBox(error.ErrorMessage(),"ADO错误!");
    MessageBox(error.Description(),"ADO错误!");
}