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();//使用完毕后释放资源