利用mfc类和odbcapi执行存储过程

最新推荐文章于 2021-03-23 21:53:10 发布

lionzl

最新推荐文章于 2021-03-23 21:53:10 发布

阅读量765

点赞数

分类专栏：数据库文章标签： mfc 存储 sql report output diagnostics

数据库专栏收录该内容

140 篇文章 2 订阅

订阅专栏

在vc中通过ODBC（不要ADO)调用
CREATE PROCEDURE Test
@Para1 int ,
@Para2 int ,
@Para3 int ,
@Ret int OUTPUT
AS
insert into test1 values (@Para1,@Para2,@Para3)
set @Ret = @Para1;
GO
///
jnxulei(石头) ( ) 信誉：111
//------------------------------------------------------------------------------
CDatabase * pDatabase = new CDatabase;

pDatabase->OpenEx("DSN=ODBCName;UID=***;PWD=***", 0 );

CRecordset rs( pDatabase );

SQL.Format("{Test %d,%d,%d,%d}",Time0, Time1, Time2, Time3);

rs.Open( CRecordset::dynamic, SQL, CRecordset::none );

rs.GetFieldValue( (long)0, strReturn );

rs.Close();

pDatabase->Close();
delete pDatabase;
///
lsp5i5j(星辰->女人在哪) ( ) 信誉：87
//------------------------------------------------------------------------------
ODBC有点麻烦,输入输出参数都有
int SimpleGetAccordOtherprocedure(CDatabase *dbCust,char *spstring,char *resultp,int *resultplen,int *ireturn,int *nIndex,char *sUserTel,char *sOtherId,char *sOtherTel,char *sOtherSex,char *sOtherOld)
{
char spstm[512];
int ret;
HSTMT hStmt;
int totallen=0;
SDWORD cbIn1=SQL_NTS, cbIn2=SQL_NTS, cbOut=1024,cbRet=0;

ret=SQLAllocStmt(dbCust->m_hdbc, &hStmt);
if(ret!=SQL_SUCCESS && ret!=SQL_SUCCESS_WITH_INFO)
return(-3503);

if(sizeof(spstm)<=strlen(spstring))
return(-3504);
strcpy(spstm,spstring);
ret=SQLPrepare(hStmt, (UCHAR *)spstm, SQL_NTS);
if(ret!=SQL_SUCCESS && ret!=SQL_SUCCESS_WITH_INFO)
return(-3504);

ret=SQLBindParameter(hStmt,1,SQL_PARAM_OUTPUT,SQL_C_SSHORT,SQL_INTEGER, 0, 0, ireturn, 0,&cbRet);
ret=SQLBindParameter(hStmt,2,SQL_PARAM_INPUT,SQL_C_SSHORT,SQL_INTEGER,0, 0, nIndex, 0, &cbIn1);
ret=SQLBindParameter(hStmt,3,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, DBACCESS_MAXPARAMLEN, 0, sUserTel, DBACCESS_MAXPARAMLEN,&cbIn1);
ret=SQLBindParameter(hStmt,4,SQL_PARAM_OUTPUT,SQL_C_CHAR,SQL_CHAR, DBACCESS_MAXPARAMLEN, 0, sOtherId, DBACCESS_MAXPARAMLEN,&cbIn2);
ret=SQLBindParameter(hStmt,5,SQL_PARAM_OUTPUT,SQL_C_CHAR,SQL_CHAR, DBACCESS_MAXPARAMLEN, 0, sOtherTel, DBACCESS_MAXPARAMLEN,&cbIn1);
ret=SQLBindParameter(hStmt,6,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, DBACCESS_MAXPARAMLEN, 0, sOtherSex, DBACCESS_MAXPARAMLEN,&cbIn1);
ret=SQLBindParameter(hStmt,7,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR, DBACCESS_MAXPARAMLEN, 0, sOtherOld, DBACCESS_MAXPARAMLEN,&cbIn1);

ret=SQLExecute(hStmt);

if(ret!=SQL_SUCCESS && ret!=SQL_SUCCESS_WITH_INFO)
return(-3505); // printf("Error in execute!/n");
ret=TBSDBProcWU(hStmt,resultp,resultplen);
return ret;
}

参考:
---------------------------------------------------------------

http://www.vchelp.net/wyy/tour/odbc_api.asp#_Toc50053547
---------------------------------------------------------------

http://tech.ccidnet.com/pub/article/c1060_a58457_p1.html
:p
Top

问】CRecordset类如何访问存储过程取得返回值？

答】
用MFC ODBC
重载crecordset:
//chcode.h
class chcode : public CRecordset
{
public:
void Move( long nrows, WORD wfetchtype );
chcode(CDatabase* pDatabase = NULL);
DECLARE_DYNAMIC(chcode)

// Field/Param Data
//{{AFX_FIELD(chcode, CRecordset)
long m_retreturn_value;
CString m_newpassword;
CString m_oldpassword;
CString m_username;
//}}AFX_FIELD

// Overrides
// ClassWizard generated virtual function overrides
//{{AFX_VIRTUAL(chcode)
public:
virtual CString GetDefaultConnect(); // Default connection string
virtual CString GetDefaultSQL(); // Default SQL for Recordset
virtual void DoFieldExchange(CFieldExchange* pFX); // RFX support
//}}AFX_VIRTUAL

// Implementation
#ifdef _DEBUG
virtual void AssertValid() const;
virtual void Dump(CDumpContext& dc) const;
#endif
};

//{{AFX_INSERT_LOCATION}}
// Microsoft Visual C++ will insert additional declarations immediately before the previous line.

#endif // !defined(AFX_CHCODE_H__FF9F8501_31F2_4794_B697_B7FFB5A15C30__INCLUDED_)
//chcode.cpp
// chcode.cpp : implementation file
//

#include "stdafx.h"
#include "chcode.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

/
// chcode
void AFXAPI RFX_Textout(CFieldExchange * pfx, LPCTSTR szname,
CString& value, int nmaxlength, int ncolumntype, short nscale);
IMPLEMENT_DYNAMIC(chcode, 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
}

/
// chcode diagnostics

#ifdef _DEBUG
void chcode::AssertValid() const
{
CRecordset::AssertValid();
}

void chcode::Dump(CDumpContext& dc) const
{
CRecordset::Dump(dc);
}
#endif //_DEBUG
//Move(long nRows, WORD wFetchType)
void chcode::Move(long nrows, WORD wfetchtype)
{
if (m_nFields)
CRecordset ::Move(nrows, wfetchtype);
else
m_bBOF = m_bEOF = true;
}

调用:

CDatabase db1;
s1.Format("ODBC;UID=sa;PWD=%s","");
db1.Open("report",false,false,s1);
chcode chrs(&db1);
//CRecordset rs(&db1);
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.Open(AFX_DB_USE_DEFAULT_TYPE,"{call sp_password('report','report','report')}");
//chrs.m_retreturn_value;这就是返回值
chrs.Close();
db1.Close();

你也可以去看看下面的例子：
http://www.codeproject.com/database/mssqltutorial.asp
http://www.codeproject.com/database/MyRecordset.asp
http://www.codeproject.com/database/spcw.asp