关注我
_variant_t RecordsAffected;
string szSql = (string)"P_KS_ADDUSER '" + m_szName+ "','" + m_szPasswd +"';";
try
{
_CommandPtr ptrCmd;
ptrCmd.CreateInstance(__uuidof(Command));
ptrCmd->ActiveConnection = pContext->GetConnection();
VARIANT vtRoyalty;
vtRoyalty = COleVariant((long)0);
ptrCmd->Parameters->Append(
ptrCmd->CreateParameter("Return", adInteger, adParamReturnValue,sizeof(int),vtRoyalty));
vtRoyalty = COleVariant(m_szName.c_str());
ptrCmd->Parameters->Append(
ptrCmd->CreateParameter("Name", adChar, adParamInput,m_szName.size(),vtRoyalty));
vtRoyalty = COleVariant(m_szPasswd.c_str());
ptrCmd->Parameters->Append(
ptrCmd->CreateParameter("Pass", adChar, adParamInput,m_szPasswd.size(),vtRoyalty));
ptrCmd->CommandText = "P_KS_ADDUSER";
ptrCmd->CommandType = adCmdStoredProc;
ptrCmd->Execute(NULL,NULL,adCmdStoredProc);
cout<<"添加用户成功，id="<<(unsigned int)ptrCmd->Parameters->GetItem("Return")->GetValue()<<endl;
//cout<<"add id:"<<(unsigned int)ptrCmd->GetParameters()->GetItem(0)<<endl;
//pContext->GetNetworkSender()->SendPack();
}
catch(_com_error e)
{
cout<<"增加用户的时候发生异常:"<<(LPCSTR)e.Description()<<endl;
return;
}
要是有多个值要返回，那么可以使用输出参数(output parameter)的方法；要是需要返回一个数组，可以使用返回数据集的方法。
按MSDN的例子，一般都是下面的调用方法（节选自MSDN）：
HRESULT hr = S_OK;
// Define ADO object pointers.
// Initialize pointers on define.
// These are in the ADODB:: namespace.
_RecordsetPtr pRstByRoyalty = NULL;
_RecordsetPtr pRstAuthors = NULL;
_CommandPtr pcmdByRoyalty = NULL;
_ParameterPtr pprmByRoyalty = NULL;
_ConnectionPtr pConnection = NULL;
_bstr_t strCnn("Provider=sqloledb;Data Source=srv;"
"Initial Catalog=Pubs;User Id=sa;Password=;");
_bstr_t strMessage, strAuthorID;
int intRoyalty;
VARIANT vtRoyalty;
try
{
//Open a Connection.
TESTHR(pConnection.CreateInstance(__uuidof(Connection)));
hr = pConnection->Open(strCnn,"","",NULL);
pConnection->CursorLocation = adUseClient;
//Open Command Object with one Parameter
TESTHR(pcmdByRoyalty.CreateInstance(__uuidof(Command)));
pcmdByRoyalty->CommandText = "byroyalty";
pcmdByRoyalty->CommandType = adCmdStoredProc;
//Define Integer/variant.
vtRoyalty.vt = VT_I2;
vtRoyalty.iVal = intRoyalty;
pprmByRoyalty = pcmdByRoyalty->CreateParameter(
"percentage",adInteger,adParamInput,sizeof(int),vtRoyalty);
pcmdByRoyalty->Parameters->Append(pprmByRoyalty);
pprmByRoyalty->Value = vtRoyalty;
//Create Recordset by executing the command
pcmdByRoyalty->ActiveConnection = pConnection;
pRstByRoyalty = pcmdByRoyalty->Execute(
NULL,NULL,adCmdStoredProc);
//执行结果的处理，省略...
pRstByRoyalty->Close();
pRstAuthors->Close();
pConnection->Close();
}
catch(_com_error &e)
{
//意外处理...
}
}
必须指定执行SQL语句的CommandType是adCmdStoredProc，存储过程有参数就必须CreateParameter生成参数，这样调用存储过程就成了很麻烦的一件事情，必须针对不同的存储过程生成不同的参数。一个两个还可以接受，如果项目的业务逻辑比较复杂，需要依赖大量的存储过程，并且项目的需求或功能可能要经常变动或扩展，那就成了一个噩梦了。有没有通用的方法呢？当然有，只需要通用的查询就可以实现。
_ConnectionPtr pConnection = NULL;
m_Conn.CreateInstance(__uuidof(Connection));
m_Conn->ConnectionString = "Provider=sqloledb;Data Source=srv;"Initial Catalog=Pubs;User Id=sa;Password=;";
m_Conn->Open("","","",NULL);
_variant_t m_param;
_bstr_t m_bstr;
int index = 0,nFieldCount;
_RecordsetPtr m_Rs;
m_Rs.CreateInstance(__uuidof(Recordset));
m_Rs->Open("select * from titles",(IDispatch*)(m_Conn->m_Conn),adOpenStatic, adLockReadOnly,adCmdText);
nFieldCount = m_Rs->Fields->Count;
while(!m_Rs->EndOfFile)
{
for(index=0;index < nFieldCount;index ++)
{
m_param.Clear();
m_param = (short)index;
m_param = m_Rs->Fields->GetItem(m_param);
if(m_param.vt != VT_NULL)
m_bstr = m_param;
else
m_bstr = "";
printf("%s\n",(char*)m_bstr);
}
m_Rs->MoveNext();
}
m_Rs->Close();
m_Conn->Close();
以上一段就是普通的执行查询SQL语句的代码，简单起见，去掉了所有防护和判断代码，对于MS SQLServer，如果是执行返回结果集的存储过程，比如Pubs的byroyalty，只需要把上面代码中的"select * from titles"替换成"byroyalty 100"就可以了，即"过程名 参数1,参数2,..."形式。
这样一来，不需要生成参数，只需要改变SQL语句，就实现了MS SQLServer存储过程的调用，可以通用。但是对于返回参数的存储过程，这段代码就不行了，不过还是可以通过SQL语句解决：
“declare @q int exec checkpwd 'user','pwd',@ret = @q OUTPUT select @q”
checkpwd是一个存储过程，两个输入参数，用户名和密码，第三个是输出参数@ret，返回用户验证的结果。通过执行上面的SQL语句，把输出参数作为结果集返回，就可以适应上面的VC代码了。返回值的存储过程也是一样，执行“declare @q int,@return int exec @return = checkpwd 'user','pwd',@ret = @q OUTPUT select @q,@return”就可以了。
这里讲的是SQLServer的存储过程调用，这段代码同样可以调用ORACLE的存储过程，不过需要对SQL语句作出一些调整。
调用sql server 存储过程方法1如下：
strSQL1="EXEC proc_getdianmingsonnode '"+AuthorName+"','"+FileName+"','"+DateTime+"','"+NQANum+"'";
conn.pCon->Execute(strSQL,&RecordsAffected,adCmdText);
其中AuthorName,FileName等是CString类的变量
调用存储过程(有外传参数)方法2如下：
_variant_t vFileName,vSonFileName,vTmp;
CString FileName,SonFileName;
FieldPtr pfldFileName;
_ParameterPtr parm,parm1;
conn.CreateConnection();
conn.pRs->MoveFirst();
pfldFileName = conn.pRs->Fields->GetItem("FileName");
///向存储过程传入参数-------
parm=conn.pCmd->CreateParameter("input",adVarChar,adParamInput,60,vFileName);
conn.pCmd->Parameters->Append(parm);
parm1=conn.pCmd->CreateParameter("output",adVarChar,adParamOutput,1024,vTmp);
conn.pCmd->Parameters->Append(parm1);
/////执行存储过程--------------
pRs=conn.pCmd->Execute(NULL,NULL,-1);
/////得到存储过程的参数--------------------
vSonFileName=conn.pCmd->Parameters->GetItem(long(1))->GetValue();
如果在循环中使用一定要
////删除所传的参数，以以便下一条记录传参数
conn.pCmd->Parameters->Delete(parm->Name);
conn.pCmd->Parameters->Delete(parm1->Name);
HRESULT hr;
VARIANT vNull;
_ConnectionPtr pCnn;
_CommandPtr pCmd;
_ParameterPtr pParam1, pParam2;
hr = pCnn.CreateInstance("ADODB.Connection");
if (SUCCEEDED(hr))
{
hr = pCnn->Open("Provider=MSDASQL.1;Persist Security Info=False;User ID=sa;Data Source=CW111;Initial Catalog=CW111", "", "", adModeUnknown);
if (SUCCEEDED(hr))
{
hr = pCmd.CreateInstance("ADODB.Command");
if (SUCCEEDED(hr))
{
VariantInit(&vNull);
vNull.vt = VT_ERROR;
vNull.scode = DISP_E_PARAMNOTFOUND;
pCmd->ActiveConnection = pCnn;
pCmd->CommandText = "procGetBooks";
pCmd->CommandType = adCmdStoredProc;
pParam1 = pCmd->CreateParameter("@BookMark", adChar, adParamInput, 32, "CR-006");
pParam2 = pCmd->CreateParameter("@Ret", adChar, adParamOutput, 32);
pCmd->Parameters->Append(pParam1);
pCmd->Parameters->Append(pParam2);
pCmd->Execute(&vNull, &vNull, adCmdStoredProc);
AfxMessageBox ((char *)_bstr_t(pParam2->Value));
}
pCnn->Close();
}
}
pCnn.Release();
有参无返回存储过程
OleInitialize(NULL);
_ConnectionPtr _pConn(__uuidof(Connection));
_variant_t _vRes;
_pConn->Open("Provider=sqloledb; Server=192.168.1.155; Initial Catalog=SMSMessage; User ID=sa; Password=tendency",(BSTR) NULL, (BSTR) NULL, -1);
CString strSQL;
strSQL.Format("EXEC SMSMessage_Insert '4001','1','2001','2002145'");
_pConn->Execute((_bstr_t)strSQL,&_vRes,adCmdText);
if(_pConn->GetState() == adStateOpen)
_pConn->Close();
if(_pConn != NULL)
_pConn.Release();
OleUninitialize();
无参无返回存储过程
OleInitialize(NULL);
_ConnectionPtr _pConn(__uuidof(Connection));
_variant_t _vRes;
_pConn->Open("Provider=sqloledb; Server=192.168.1.155; Initial Catalog=SMSMessage; User ID=sa; Password=tendency",(BSTR) NULL, (BSTR) NULL, -1);
_pConn->Execute("SMSMessage_Insert",&_vRes,adCmdStoredProc);
if(_pConn->GetState() == adStateOpen)
_pConn->Close();
if(_pConn != NULL)
_pConn.Release();
OleUninitialize();
//有参有返回值的存储过程
_CommandPtr pIDCommand;
pIDCommand.CreateInstance(__uuidof(Command));
pIDCommand->ActiveConnection = m_pConnection;//连接字符串
pIDCommand->CommandText = "pr_AddNew";//存储过程名
pIDCommand->CommandType = adCmdStoredProc;//标记该操作为存储过程
_ParameterPtr m_pParam;
m_pParam = pIDCommand->CreateParameter("@reid",adInteger,adParamInputOutput,sizeof(int),0);//添加参数该参数为一个inputoutput类型的int参数
pIDCommand->Parameters->Append(m_pParam);
m_pParam = pIDCommand->CreateParameter("@ReallyName",adVarChar,adParamInputOutput,128,ReallyName); //添加参数该参数为一个inputoutput类型的VarChar(128)参数
pIDCommand->Parameters->Append(m_pParam);
m_pParam = pIDCommand->CreateParameter("@nodeid",adInteger,adParamInput,sizeof(int),root);// 该参数为input类型的int参数，参数的值在root中
pIDCommand->Parameters->Append(m_pParam);
m_pParam = pIDCommand->CreateParameter("@ShowName",adChar,adParamInput,ShowName.GetLength(),ShowName.GetBuffer(0)); //数为input类型的char,参数的值在,ShowName.GetBuffer(0)中，该用法要回收
ShowName.ReleaseBuffer();//回收
pIDCommand->Parameters->Append(m_pParam);
pIDCommand->Execute(NULL,NULL,adCmdStoredProc);//执行存储过程
Id = pIDCommand->Parameters->GetItem((long)0)->Value;//根据参数在参数表中的位置取值，当前参数是第一个，所以值为0
CString _reallyname= pIDCommand->Parameters->GetItem((long)1)->Value;
//pIDCommand.Detach();//使用完毕后释放资源
pIDCommand.Release();//使用完毕后释放资源