// SQL Server, stored procedure , Command , Recordset, input parameter, output parameter, returned recordset from stored procedure //execute stored procedure , get returned value, output value, and returned recordset. #include "stdafx.h" #import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" rename( "EOF", "adoEOF" ) struct InitOle { InitOle() { ::CoInitialize(NULL); } ~InitOle() { ::CoUninitialize(); } } _init_InitOle_; void PrintProviderError(ADODB::_ConnectionPtr pConnection);int main(int argc, char* argv[]) { printf("Create a adParamReturnValue for Command object to get return value of stored procedure\n"); printf("Reference :MSDN---Append and CreateParameter Methods Example (VC++)\n"); ADODB::_ConnectionPtr Conn1; ADODB::_CommandPtr Cmd1; ADODB::_RecordsetPtr Rs1; ADODB::_ParameterPtr retParam= NULL; ADODB::_ParameterPtr inParam=NULL; ADODB::_ParameterPtr outParam=NULL; _variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR); _variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR); _bstr_t bstrConnect( L"driver={sql server};server=Cell;Database=zhg;UID=sa;PWD=;" ); // the following stored procedure return 12345 int value _bstr_t bstrCreate ( L"create proc sp_AdoTest( @OutParam int OUTPUT,@InParam int ) " L"as " L"select @OutParam = @InParam + 10 " L"select * from Table1" L"return 12345" ); _bstr_t bstrSP(L"sp_Adotest" ); try { _bstr_t bstrEmpty; Conn1.CreateInstance( __uuidof( ADODB::Connection ) ); Cmd1.CreateInstance( __uuidof( ADODB::Command ) ); Rs1.CreateInstance(__uuidof(ADODB::Recordset)); // Establish connection. Conn1->ConnectionString = bstrConnect; Conn1->Open( bstrConnect, bstrEmpty, bstrEmpty, -1 ); // Open recordset. Cmd1->ActiveConnection = Conn1; Cmd1->CommandText = bstrSP; Cmd1->CommandType = ADODB::adCmdStoredProc; retParam=Cmd1->CreateParameter(_bstr_t("Return"),ADODB::adInteger,ADODB::adParamReturnValue,sizeof(int)); Cmd1->Parameters->Append(retParam); outParam = Cmd1->CreateParameter(_bstr_t("OutParam"),ADODB::adInteger,ADODB::adParamOutput,sizeof(int)); Cmd1->Parameters->Append(outParam); inParam = Cmd1->CreateParameter(_bstr_t("InParam"),ADODB::adInteger,ADODB::adParamInput,sizeof(int),_variant_t( (long) 10 )); inParam->Value=_variant_t( (long) 10 ); Cmd1->Parameters->Append(inParam); Cmd1->Parameters->Refresh(); Cmd1->Parameters->Item[ _variant_t( _bstr_t("@InParam") ) ]->Value =_variant_t( (long) 11 ); Rs1->put_CursorLocation(ADODB::adUseClient); Rs1->Open((_variant_t((IDispatch *) Cmd1)),vtEmpty,ADODB::adOpenStatic, ADODB::adLockReadOnly, -1); // Get return value of the stored procedure.adCmdUnknown TCHAR tcbuf[1024]; long retvalue=Cmd1->Parameters->Item[(short)0]->Value; long p2=Cmd1->Parameters->Item[(short)1]->Value; long p3=Cmd1->Parameters->Item[(short)2]->Value; int recordcount=0; if(Rs1->State==ADODB::adStateClosed) MessageBox(NULL,"no recordset is returned from the stored procedure","Information",MB_OK); else recordcount = Rs1->GetRecordCount(); wsprintf(tcbuf,"retvalue of the stored procedure:%d,input value:%d,output value:%d,record count :%d",retvalue,p2,p3,recordcount); MessageBox(NULL,tcbuf,"Output",MB_OK); } catch(_com_error &e) { _bstr_t bstrSource(e.Source()); _bstr_t bstrDescription(e.Description()); printf("\nCOM error occurred, Source : %s \n Description : %s \n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription); PrintProviderError(Conn1); } return 0; } VOID PrintProviderError(ADODB::_ConnectionPtr pConnection) { // Print Provider Errors from Connection object. // pErr is a record object in the Connection's Error collection. ADODB::ErrorPtr pErr = NULL; long nCount = 0; long i = 0; if( (pConnection->Errors->Count) > 0) { nCount = pConnection->Errors->Count; // Collection ranges from 0 to nCount -1. for(i = 0; i < nCount; i++) { pErr = pConnection->Errors->GetItem(i); printf("\n\t Error number: %x\t%s", pErr->Number, (LPCSTR)pErr->Description); } } }
ODBC: class chcode : public CRecordset chcode::chcode(CDatabase* pdb) : CRecordset(pdb) { //{{AFX_FIELD_INIT(chcode) m_oldpassword=""; m_newpassword=""; m_username=""; //}}AFX_FIELD_INIT m_nDefaultType = snapshot; m_nParams=4; } CString chcode::GetDefaultConnect() { return _T("ODBC;DSN="); }CString chcode::GetDefaultSQL() { return _T(""); }void chcode::DoFieldExchange(CFieldExchange* pFX) { //{{AFX_FIELD_MAP(chcode) pFX->SetFieldType(CFieldExchange ::outputParam); //set the field type to outputParam for the return value RFX_Long(pFX, _T("return_value"), m_retreturn_value); //bind the return value to the variable pFX->SetFieldType(CFieldExchange ::inputParam); //reset the field type to inputParam RFX_Text(pFX, "@old", m_oldpassword);//,255,SQL_CHAR,0); RFX_Text(pFX, "@new", m_newpassword);//,255,SQL_CHAR,0); //call the new rfx_Text to get the character output params RFX_Text(pFX, "@loginame", m_username);//,255,SQL_CHAR,0); //}}AFX_FIELD_MAP }//调用存储过程修改用户密码 CDatabase db1; s1.Format("ODBC;UID=sa;PWD=%s","11111"); db1.Open("report1",false,false,s1); chcode chrs(&db1); //从CRecordSet 继承的新类 chrs.m_newpassword=in.m1; chrs.m_oldpassword=s3; chrs.m_username="report"; chrs.Open( AFX_DB_USE_DEFAULT_TYPE ,_T("{?=CALL sp_password(?,?,?)}")); chrs.Close(); db1.Close();
_commandptr m_comd;
_connection m_conn;m_rec.createinstance(__uuidof(recordset));
...
//建立m_comd和m_conn对象m_comd->activeconnection = m_conn;
m_comd->comandtext="mysp";
m_comd->comandtype = adCmdStoredProc;m_rec->CursorType = adOpenKeyset;
m_rec = m_comd->execute(NULL,NULL,adCmdStoredProc);
//execute stored procedure , get returned value, output value, and returned recordset.
#include "stdafx.h"
#import "C:\PROGRA~1\COMMON~1\System\ado\msado15.dll" rename( "EOF", "adoEOF" )
struct InitOle
{
InitOle() { ::CoInitialize(NULL); }
~InitOle() { ::CoUninitialize(); }
} _init_InitOle_;
void PrintProviderError(ADODB::_ConnectionPtr pConnection);int main(int argc, char* argv[])
{
printf("Create a adParamReturnValue for Command object to get return value of stored procedure\n");
printf("Reference :MSDN---Append and CreateParameter Methods Example (VC++)\n");
ADODB::_ConnectionPtr Conn1;
ADODB::_CommandPtr Cmd1;
ADODB::_RecordsetPtr Rs1;
ADODB::_ParameterPtr retParam= NULL;
ADODB::_ParameterPtr inParam=NULL;
ADODB::_ParameterPtr outParam=NULL;
_variant_t vtEmpty (DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t vtEmpty2 (DISP_E_PARAMNOTFOUND, VT_ERROR);
_bstr_t bstrConnect( L"driver={sql server};server=Cell;Database=zhg;UID=sa;PWD=;" );
// the following stored procedure return 12345 int value
_bstr_t bstrCreate ( L"create proc sp_AdoTest( @OutParam int OUTPUT,@InParam int ) "
L"as "
L"select @OutParam = @InParam + 10 "
L"select * from Table1"
L"return 12345" );
_bstr_t bstrSP(L"sp_Adotest" );
try
{
_bstr_t bstrEmpty;
Conn1.CreateInstance( __uuidof( ADODB::Connection ) );
Cmd1.CreateInstance( __uuidof( ADODB::Command ) );
Rs1.CreateInstance(__uuidof(ADODB::Recordset));
// Establish connection.
Conn1->ConnectionString = bstrConnect;
Conn1->Open( bstrConnect, bstrEmpty, bstrEmpty, -1 );
// Open recordset.
Cmd1->ActiveConnection = Conn1;
Cmd1->CommandText = bstrSP;
Cmd1->CommandType = ADODB::adCmdStoredProc;
retParam=Cmd1->CreateParameter(_bstr_t("Return"),ADODB::adInteger,ADODB::adParamReturnValue,sizeof(int));
Cmd1->Parameters->Append(retParam);
outParam = Cmd1->CreateParameter(_bstr_t("OutParam"),ADODB::adInteger,ADODB::adParamOutput,sizeof(int));
Cmd1->Parameters->Append(outParam);
inParam = Cmd1->CreateParameter(_bstr_t("InParam"),ADODB::adInteger,ADODB::adParamInput,sizeof(int),_variant_t( (long) 10 ));
inParam->Value=_variant_t( (long) 10 );
Cmd1->Parameters->Append(inParam);
Cmd1->Parameters->Refresh();
Cmd1->Parameters->Item[ _variant_t( _bstr_t("@InParam") ) ]->Value =_variant_t( (long) 11 );
Rs1->put_CursorLocation(ADODB::adUseClient);
Rs1->Open((_variant_t((IDispatch *) Cmd1)),vtEmpty,ADODB::adOpenStatic,
ADODB::adLockReadOnly, -1);
// Get return value of the stored procedure.adCmdUnknown
TCHAR tcbuf[1024];
long retvalue=Cmd1->Parameters->Item[(short)0]->Value;
long p2=Cmd1->Parameters->Item[(short)1]->Value;
long p3=Cmd1->Parameters->Item[(short)2]->Value;
int recordcount=0;
if(Rs1->State==ADODB::adStateClosed)
MessageBox(NULL,"no recordset is returned from the stored procedure","Information",MB_OK);
else
recordcount = Rs1->GetRecordCount();
wsprintf(tcbuf,"retvalue of the stored procedure:%d,input value:%d,output value:%d,record count :%d",retvalue,p2,p3,recordcount);
MessageBox(NULL,tcbuf,"Output",MB_OK);
}
catch(_com_error &e)
{
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
printf("\nCOM error occurred, Source : %s \n Description : %s \n",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);
PrintProviderError(Conn1);
}
return 0;
}
VOID PrintProviderError(ADODB::_ConnectionPtr pConnection)
{
// Print Provider Errors from Connection object.
// pErr is a record object in the Connection's Error collection.
ADODB::ErrorPtr pErr = NULL;
long nCount = 0;
long i = 0; if( (pConnection->Errors->Count) > 0)
{
nCount = pConnection->Errors->Count;
// Collection ranges from 0 to nCount -1.
for(i = 0; i < nCount; i++)
{
pErr = pConnection->Errors->GetItem(i);
printf("\n\t Error number: %x\t%s", pErr->Number, (LPCSTR)pErr->Description);
}
}
}
class chcode : public CRecordset
chcode::chcode(CDatabase* pdb)
: CRecordset(pdb)
{
//{{AFX_FIELD_INIT(chcode)
m_oldpassword="";
m_newpassword="";
m_username="";
//}}AFX_FIELD_INIT
m_nDefaultType = snapshot;
m_nParams=4;
}
CString chcode::GetDefaultConnect()
{
return _T("ODBC;DSN=");
}CString chcode::GetDefaultSQL()
{
return _T("");
}void chcode::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(chcode)
pFX->SetFieldType(CFieldExchange ::outputParam); //set the field type to outputParam for the return value
RFX_Long(pFX, _T("return_value"), m_retreturn_value); //bind the return value to the variable
pFX->SetFieldType(CFieldExchange ::inputParam); //reset the field type to inputParam
RFX_Text(pFX, "@old", m_oldpassword);//,255,SQL_CHAR,0);
RFX_Text(pFX, "@new", m_newpassword);//,255,SQL_CHAR,0); //call the new rfx_Text to get the character output params
RFX_Text(pFX, "@loginame", m_username);//,255,SQL_CHAR,0);
//}}AFX_FIELD_MAP
}//调用存储过程修改用户密码 CDatabase db1;
s1.Format("ODBC;UID=sa;PWD=%s","11111");
db1.Open("report1",false,false,s1);
chcode chrs(&db1); //从CRecordSet 继承的新类
chrs.m_newpassword=in.m1;
chrs.m_oldpassword=s3;
chrs.m_username="report";
chrs.Open( AFX_DB_USE_DEFAULT_TYPE ,_T("{?=CALL sp_password(?,?,?)}"));
chrs.Close();
db1.Close();
老兄如果用的觉得好,给我几分。
我还没挣过分呢。
CDatabase db;
strTemp.Format("ODBC;UID=admin;PWD=admin");
db.Open("cc",false,false,strTemp); DailyCheck rs(&db);
rs.m_tradedateParam = strDate;
rs.m_merchantParam = strBizCode;
rs.m_resultParam = 0;
rs.Open(AFX_DB_USE_DEFAULT_TYPE);
时表,然后再在VC中打开该表,用ADO,ODBC都可实现。
1、如果使用ODBC,由于CDatabase::ExecuteSQL并没有提供存储过程输出的接受方法,所以不要寄希望于MFC封装类,只能求助于ODBC API了,但API编程相当复杂,你可能不会为此而学习ODBC API编程。另外,有一种算是比较“歪门邪道"的方法,就是把返回结果当作一个记录集,使用CRecordSet::Open("mysp")的方法,在一个CRecordSet对象中承载返回值,当然这就需要所编写的存储过程返回一个记录集。
另外,上面建立临时表的方法也不妨一试。
2、如果使用ADO编程,则比较简单,直接绑定参数就行了,上面已经有实例。但如果不熟悉ADO在VC中的使用,尤其是其中涉及到COM 指针的概念,则也有一定难度,另外,在一个原有的ODBC访问程序中要支持ADO,则还要增加对OLE的支持,可能对程序的效率有影响。(我感觉,ADO好像不是为VC设计的,它只偏爱VB)。
至于怎样在前台程序中如何知道SQL server数据库的特定表的某行数据修改,我想着涉及到SQL Server的内部事务处理机制,可能有接口的存储过程和编程方法供开发使用,可以查查BOL(Books Online)
Item[_variant_t((long)0)]->Value;
//访问取得第一个参数的值,从0开始计数,也可以直接给出列的名称,如下一行:
lReturnValue=pCommand->Parameters-> Item[_bstr_t("Return")]->Value;
//访问参数Return
Cstring lInputValue;
lInputValue=(LPSTR)(_bstr_t)pCommand-> Parameters->Item[_bstr_t("InParam")]->Value;
//访问参数InParam
long lOutputValue =pCommand->Parameters-> Item[_bstr_t("OutParam")]->Value;
//访问参数OutParam
Select语句的结果集可以通过Recordset对象来访问:
_variant_t vtEmpty(DISP_E_PARAMNOTFOUND, VT_ERROR);
pRecordset->Open((_variant_t((IDispatch *) pCommand)),vtEmpty,adOpenStatic, adLockReadOnly, -1);
//执行存储过程并获得返回的结果集
CString strFirstName=pRecordset-> GetCollect("fname").bstrVal;
//读取返回记录集fname字段的值
CString strLastName=pRecordset-> GetCollect("lname").bstrVal;
//读取返回记录集lname字段的值
另外,如果存储过程有多个参数,只要先创建Parameter参数对象,在利用Append方法依次加入Parameters集合即可。
class CSP_Parameter
{
public:
enum PARATYPE{spPARA_IN,spPARA_OUT}; LPCTSTR para;
PARATYPE pt;
int size;
LPTSTR pVal; CSP_Parameter(LPCTSTR p,PARATYPE t,int s,LPSTR pv):pVal(NULL),size(0)
{
para = p;
pt = t;
size = s;
pVal = pv;
} CSP_Parameter():pVal(NULL),size(0){} void Set(LPCTSTR p,PARATYPE t,int s,LPSTR pv)
{
para = p;
pt = t;
size = s;
pVal = pv;
}
};
class CSP_Invoker
{
private:
CSP_Parameter * para;
int ParaNumber;
TCHAR SP_Name[255];
public:
void SetSPName(LPCTSTR name)
{
strcpy(SP_Name,name);
} void SetPara(CSP_Parameter * p,int num)
{
ASSERT(p);
para = p;
ParaNumber = num;
} CSP_Invoker():para(NULL),ParaNumber(0){}
int Execute();
};
int CSP_Invoker::Execute()
{
ASSERT(para);
ASSERT(ParaNumber > 0);
_CommandPtr Cmd;
_ParameterPtr retParam= NULL;
_ParameterPtr inParam=NULL;
_ParameterPtr outParam=NULL;
try
{
Cmd.CreateInstance(__uuidof(Command));
Cmd->ActiveConnection = GetMyApplication()->m_pConnection;
Cmd->CommandType = adCmdStoredProc;
Cmd->CommandText = this->SP_Name;
retParam = Cmd->CreateParameter(_bstr_t("Return"),adInteger,adParamReturnValue,sizeof(int));
Cmd->Parameters->Append(retParam);
int i;
for(i = 0; i < ParaNumber ;i++)
{
switch(para[i].pt)
{
case CSP_Parameter::spPARA_IN:
inParam = Cmd->CreateParameter(_bstr_t(para[i].para),adVarChar,adParamInput,para[i].size,_variant_t(para[i].pVal));
Cmd->Parameters->Append(inParam);
break;
case CSP_Parameter::spPARA_OUT:
outParam = Cmd->CreateParameter(_bstr_t(para[i].para),adVarChar,adParamOutput,para[i].size);
Cmd->Parameters->Append(outParam);
break;
default:
ASSERT(FALSE);
}
}
Cmd->Execute(NULL,NULL,adCmdStoredProc);
long retvalue = Cmd->Parameters->Item[(short)0]->Value;
for(i = 0; i < ParaNumber ;i++)
{
switch(para[i].pt)
{
case CSP_Parameter::spPARA_OUT: _variant_t val = Cmd->Parameters->Item[(short)(i+1)]->Value;
CString str; if(val.vt==VT_NULL) str.Empty();
else str.Format((char *)_bstr_t(val)); strcpy(para[i].pVal,(LPCTSTR)str); break;
}
}
return retvalue /*?1:0*/;
} catch(_com_error e)///捕捉异常
{
AfxMessageBox(_T("查询数据库失败!7"));///显示错误信息
AfxMessageBox(e.ErrorMessage());
return -1;
}
return -1;
}
CSP_Parameter类和CSP_Invoker类是用来实现调用SQLServer中存储程序的两个类
原始的调用方法比较复杂,我把它们封起来.
CSP_Parameter:参数类,成员含义分别为
para:参数名称
pt 输入或输出
size 大小
pVal 值,对于输入参数,通过它将值传入,对于输入参数,用它把值传回
CSP_Invoker调用类
para 是 CSP_Parameter 型指针,用来接收参数数组
ParaNumber 参数个数
SP_Name 存储程序名调用方法:
我们先构造参数数组如:CSP_Parameter p[5];
然后调用每个成员的Set函数给成员赋值:
p[0].Set(_T("账号"),CSP_Parameter::spPARA_IN,50,user);
其中user TCHAR user[125];
strcpy(user,_T("the str u want")); CSP_Invoker invoker;
invoker.SetSPName(_T("增加用户账号"));
invoker.SetPara(p,5);
int result = invoker.Execute();
if(result == 1)
{
InfoBox(_T("操作成功!!!"));
}
else
{
InfoBox(_T("操作失败!!!"));
return;
}
*/