Ado中如何调用存储过程返回结果集（不是单条纪录）？

[andy_lau]

如题！

[yintongshun]

http://otn.oracle.com/global/cn/sample_code/tech/windows/ole_db/index.html

http://www.zdnet.com.cn/developer/tech/story/0,2000081602,39139044-2,00.htm

http://www.weiw.com/article/list.asp?id=639

[AkiraChing]

the following codes is copied:

CString strTmp;

CString m_sdatasource; // Data source name
CString m_sUserID; // User Id
CString m_sPassword; // Password

// GET the above values from the user
//Without creating Datasource we can use database by the following code
/* strTmp.Format( "driver={sql server};"
"server=%s;"
"Database=%s;""UID=%s;""PWD=%s;",
m_server,m_sdatabase,m_sUserID,m_sPassword );*/

strTmp.Format( "dsn=%s;""UID=%s;""PWD=%s;",m_sdatasource,m_sUserID,m_sPassword );
_bstr_t bstrSQLServerConnect;
_bstr_t bstrProc =( L"sp_StartByteImport" );; //Stored procedure name
_variant_t Final;
bstrSQLServerConnect = (LPCTSTR) strTmp;
m_status="Empty File";
_ConnectionPtr Conn1; // connection object pointer
_CommandPtr Cmd1; // command object pointer
_RecordsetPtr Rs1; // recordset object pointer
bool bvalid = false;
try
{
Conn1.CreateInstance( __uuidof( Connection ) ); // Instantiating connection object
Conn1->ConnectionString = bstrSQLServerConnect; // giving the sqlconnection
Conn1->Open( bstrEmpty, bstrEmpty, bstrEmpty ); // open the connection object
Cmd1.CreateInstance( __uuidof( Command ) ); // creating command object
Cmd1->ActiveConnection = Conn1; // giving the connection handle
Cmd1->CommandText = _bstr_t( bstrProc ); // passing the stored procedue
Cmd1->CommandType = adCmdStoredProc; // type
Cmd1->Parameters->Refresh(); // passing string value as argument to stored procedure
Cmd1->Parameters->Item[ _variant_t( (long) 1 ) ]->Value = _variant_t( (LPCTSTR)m_sfilename );
Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, adCmdUnknown ); // executing the stored procedure and storing the recordset value
bvalid = true;
Final = Rs1->Fields->GetItem( _variant_t( 0L ) )->Value; // getting the first column value of the result row
strTmp.Format( "%s", CrackStrVariant( Final) ); // to see the value
// put your code to see all column values
}
catch( CException *e ) // trapping all error messages
{
TCHAR szCause[255];
e->GetErrorMessage(szCause, 255);
m_status=szCause;
}
catch( _com_error &e )
{
m_status=e.ErrorMessage( );
}
catch(...)
{
m_status="Error while executing the Import";

}
//we need to create the stored procedures below before running the application
//CREATE PROCEDURE sp_AddAccountingInfo @nfinal int, @pcDate datetime,
//@pcURL varchar (250), @pcTop varchar (250),
//@pcQueryString varchar (250), @pcBytes int, @pcRequests int AS
/*
Do your operation here
*/
//CREATE PROCEDURE sp_AddAccountingInfo
//@nfinal int,
//@pcDate datetime,
//@pcURL varchar (250),
//@pcTop varchar (250),
//@pcQueryString varchar (250),
//@pcBytes int,
//@pcRequests int
//AS
/*
Put your code here
*/


see in
http://www.codeguru.com/mfc_database/stored_proc_using_ado.shtml

[andy_lau]

谢谢楼上，我要的是返回结果集的

[hq1978]

给你两个封装好了ado类，和其返回结果集的用法
/*########################################################################
Filename: ado.h
----------------------------------------------------
-- -- [e-mail]
-- -- 20/1/2003 19:08
----------------------------------------------------
【注意事项】:
1. 必须在下面指明msado15.dll的全路径,一般在"C:\Program Files\Common
Files\System\ADO\"目录下.
2. 程序必须要初始化COM环境,可以调用CAdoConnection::OleInit()函数初
始化COM环境.
3. 在使用记录集对象前必须先调用CAdoConnection::Connect()连接数据库,
连接后可给多个CAdoRecordSet对象使用, 用完后请关闭数据库.
5. 打开记录集前, 先调用CAdoRecordSet::SetAdoConnection()设置连接对
象, 用完后请关闭记录集.
6. 在编译过程中不用理会下面的编译警告:
warning: unary minus operator applied to unsigned type, result
still unsigned
如果不想此警告出现,可以在StdAfx.h文件中加入这样一行代码以禁止此
警告: #pragma warning(disable:4146)
########################################################################*/
#if !defined(_ANYOU_COOL_ADOCONNETION_H)
#define _ANYOU_COOL_ADOCONNETION_H

#if _MSC_VER > 1000
#pragma once
#endif

#import "C:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF","adoEOF")

/*########################################################################
------------------------------------------------
CAdoConnection class
------------------------------------------------
########################################################################*/
class CAdoConnection
{
//构建-----------------------------------------------
public:
static BOOL OleInit();
CAdoConnection();
virtual ~CAdoConnection();

//属性-----------------------------------------------
public:
BOOL SetConnectTimeOut(long lTime);
BOOL IsOpen();
_ConnectionPtr GetConnection();

//操作-----------------------------------------------
public:
BOOL Connect(LPCTSTR strConnect, long lOptions = adConnectUnspecified);
BOOL Disconnect();

BOOL Cancel();

long BeginTrans();
BOOL RollbackTrans();
BOOL CommitTrans();

_RecordsetPtr OpenSchema(SchemaEnum QueryType);
CString GetLastError();

//数据-----------------------------------------------
protected:
CString m_strConnect;
_ConnectionPtr m_pConnection;
};

/*########################################################################
------------------------------------------------
CAdoRecordSet class
------------------------------------------------
########################################################################*/

class CAdoRecordSet
{
//构建------------------------------------------------
public:
CAdoRecordSet();
CAdoRecordSet(CAdoConnection *pConnection);
virtual ~CAdoRecordSet();
HRESULT Open(LPCTSTR strSQL, long lOption = adCmdText, CursorTypeEnum CursorType = adOpenStatic, LockTypeEnum LockType = adLockOptimistic);
void SetAdoConnection(CAdoConnection *pConnection);
BOOL Close();

//属性------------------------------------------------
public:
BOOL IsEOF();
BOOL IsBOF();

long GetState();
long GetStatus();

long GetRecordCount();
long GetFieldsCount();

CString GetLastError();

CString GetFieldName(long lIndex);
DWORD GetFieldType(long lIndex);
DWORD GetFieldAttributes(long lIndex);
long GetFieldDefineSize(long lIndex);
long GetFieldActualSize(long lIndex);

Fields* GetFields();
FieldPtr GetField(long lIndex);
const _RecordsetPtr& GetRecordset();

//记录集更新操作--------------------------------------
public:
BOOL AddNew();
BOOL Update();
BOOL CancelUpdate();
BOOL CancelBatch(AffectEnum AffectRecords = adAffectAll);
BOOL Delete(AffectEnum AffectRecords = adAffectCurrent);

//记录集导航操作--------------------------------------
public:
BOOL MoveFirst();
BOOL MovePrevious();
BOOL MoveNext();
BOOL MoveLast();
BOOL Move(long lRecords, BookmarkEnum Start = adBookmarkCurrent);

//其他方法--------------------------------------------
public:
BOOL Cancel();
BOOL PutCollect(long index, const _variant_t &pver);
BOOL GetValueString(CString &strValue, CString strFieldName);
BOOL GetValueString(CString &strValue, long lFieldIndex);

_RecordsetPtr operator =(_RecordsetPtr &pRecordSet);
CString Format(_variant_t var);

//成员变量--------------------------------------------
protected:
CAdoConnection *m_pConnection;
_RecordsetPtr m_pRecordset;
CString m_strSQL;
};

#endif // !defined(_ANYOU_COOL_ADOCONNETION_H)

[hq1978]

续 一
/*####################################################################
Filename: ado.cpp
----------------------------------------------------
Remarks: ...
----------------------------------------------------
Author: [e-mail]
Created: 20/1/2003 19:19
####################################################################*/
#include "stdafx.h"
#include "resource.h"
#include "ADO.h"

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

/*####################################################################
------------------------------------------------
CAdoConnection class
------------------------------------------------
####################################################################*/

CAdoConnection::CAdoConnection()
{
m_pConnection = NULL;
}

CAdoConnection::~CAdoConnection()
{
Disconnect();
}

BOOL CAdoConnection::OleInit()
{
return AfxOleInit();
}

/*===================================================================
Name: 连接到数据源.
-----------------------------------------------------
Params: strConnect: 连接字符串,包含连接信息.
lOptions: 可选。决定该方法是在连接建立之后(异步)还是连接
建立之前(同步)返回。可以是如下某个常量：
常量 说明
adConnectUnspecified (默认)同步方式打开连接。
adAsyncConnect 异步方式打开连接。Ado用 ConnectComplete 事
件来通知何时完成连接。
=====================================================================*/
BOOL CAdoConnection::Connect(LPCTSTR strConnect, long lOptions)
{
m_strConnect = strConnect;
try
{
///创建 Connection 对象---------------------------
HRESULT hr = m_pConnection.CreateInstance("ADODB.Connection");
if (SUCCEEDED(hr))
{
// 连接数据库---------------------------------------------
if (SUCCEEDED(m_pConnection->Open(strConnect, "", "", lOptions)))
{
return TRUE;
}
}
}
catch (_com_error e)
{
TRACE(_T(":( 连接数据库发生错误: %s\n"), e.ErrorMessage());
return FALSE;
}
catch (...)
{
TRACE(_T(":( 连接数据库时发生未知错误:"));
}
return FALSE;
}

/*===================================================================
Name: 关闭与数据源的连接.
-----------------------------------------------------
Remarks: 使用 Close 方法可关闭 Connection 对象以便释放所有关联的系统资
源。关闭对象并非将它从内存中删除，可以更改它的属性设置并且在此后再
次打开。要将对象从内存中完全删除，可将对象变量设置为 Nothing。
=====================================================================*/
BOOL CAdoConnection::Disconnect()
{
try
{
if (m_pConnection != NULL && m_pConnection->State == adStateOpen)
{
if (SUCCEEDED(m_pConnection->Close()))
{
return TRUE;
}
}
}
catch(_com_error e)
{
TRACE(_T(":( 关闭数据库发生错误: %s\n"), e.ErrorMessage());
return FALSE;
}
return FALSE;
}

[hq1978]

续二
/*========================================================================
Remarks: 请考参 CAdoRecordSet 类 Cancel 方法.
==========================================================================*/
BOOL CAdoConnection::Cancel()
{
ASSERT(m_pConnection != NULL);
try
{
return m_pConnection->Cancel();
}
catch (_com_error e)
{
TRACE(_T(":( Cancel发生错误: %s\n"), e.ErrorMessage());
return FALSE;
}
return FALSE;
}

_ConnectionPtr CAdoConnection::GetConnection()
{
return m_pConnection;
}

/*===================================================================
Name: 取得最后发生的错误信息.
-----------------------------------------------------
Remarks: 任何涉及 ADO 对象的操作都可以产生一个或多个提供者错误。产生错
误时,可以将一个或多个 Error 对象置于 Connection 对象的 Errors 集合
中。其他 ADO 操作产生错误时，将清空 Errors 集合，并且将新的 Error
对象置于 Errors 集合中。
每个 Error 对象代表特定的提供者错误，而不是 ADO 错误。ADO 错误
被记载在运行时的异常处理机制中。
没有产生错误的 ADO 操作对 Errors 集合没有影响。使用 Clear 方式
可手工清除 Errors 集合。
Errors 集合中的 Error 对象集合描述响应单个语句时产生的所有错误。
列举 Errors 集合中指定错误可使错误处理例程更精确地确定产生错误的原
因及错误来源，并执行适当还原步骤。
某些属性和方法将返回作为 Errors 集合中的 Error 对象显示的警告，
但不会中止程序的执行。在调用 Recordset 对象上的 Resync、UpdateBatch
或 CancelBatch 方法，Connection 的 Open 方法或者设置 Recordset 对象
上的 Filter 属性前，请调用 Errors 集合上的 Clear 方法。这样您就可以
阅读 Errors 集合的 Count 属性以测试返回的警告。
=====================================================================*/
CString CAdoConnection::GetLastError()
{
CString strErrors = "";
try
{
if (m_pConnection != NULL)
{
ErrorsPtr pErrors = m_pConnection->Errors;
CString strError;
for (long n = 0; n < pErrors->Count; n++)
{
ErrorPtr pError = pErrors->GetItem(n);
strError.Format(_T("Description: %s\r\n\r\nState: %s, Native: %d, Source: %s\r\n"),
(LPCTSTR)pError->Description,
(LPCTSTR)pError->SQLState,
pError->NativeError,
(LPCTSTR)pError->Source);
strErrors += strError;
}
}
return strErrors;
}
catch(_com_error e)
{
TRACE(_T(":( GetLastError发生错误: %s\n"), e.ErrorMessage());
return strErrors;
}
return strErrors;
}

BOOL CAdoConnection::IsOpen()
{
try
{
return (m_pConnection != NULL && m_pConnection->State == adStateOpen);
}
catch (_com_error e)
{
TRACE(_T(":( IsOpen方法发生错误: %s\n"), e.ErrorMessage());
return FALSE;
}
return FALSE;
}

BOOL CAdoConnection::SetConnectTimeOut(long lTime)
{
try
{
return SUCCEEDED(m_pConnection->put_ConnectionTimeout(lTime));
}
catch (_com_error e)
{
TRACE(_T(":( SetConnectTimeOut方法发生错误: %s\n"), e.ErrorMessage());
return FALSE;
}
return FALSE;
}


/*========================================================================
Name: 从数据源获取数据库信息.
-----------------------------------------------------
Params: QueryType 所要运行的模式查询类型,可以为下列任意常量.
adSchemaAsserts CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaCatalogs CATALOG_NAME
adSchemaCharacterSets CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
adSchemaCheckConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaCollations COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
adSchemaColumnDomainUsage DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
COLUMN_NAME
adSchemaColumnPrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
GRANTOR
GRANTEE
adSchemaColumns TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintColumnUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaConstraintTableUsage TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaForeignKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
FK_TABLE_CATALOG
FK_TABLE_SCHEMA
FK_TABLE_NAME
adSchemaIndexes TABLE_CATALOG
TABLE_SCHEMA
INDEX_NAME
TYPE
TABLE_NAME
adSchemaKeyColumnUsage CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
adSchemaPrimaryKeys PK_TABLE_CATALOG
PK_TABLE_SCHEMA
PK_TABLE_NAME
adSchemaProcedureColumns PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
COLUMN_NAME
adSchemaProcedureParameters PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PARAMTER_NAME
adSchemaProcedures PROCEDURE_CATALOG
PROCEDURE_SCHEMA
PROCEDURE_NAME
PROCEDURE_TYPE
adSchemaProviderSpecific 参见说明
adSchemaProviderTypes DATA_TYPE
BEST_MATCH
adSchemaReferentialConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
adSchemaSchemata CATALOG_NAME
SCHEMA_NAME
SCHEMA_OWNER
adSchemaSQLLanguages <无>
adSchemaStatistics TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
adSchemaTableConstraints CONSTRAINT_CATALOG
CONSTRAINT_SCHEMA
CONSTRAINT_NAME
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
CONSTRAINT_TYPE
adSchemaTablePrivileges TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
GRANTOR
GRANTEE
adSchemaTables TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
adSchemaTranslations TRANSLATION_CATALOG
TRANSLATION_SCHEMA
TRANSLATION_NAME
adSchemaUsagePrivileges OBJECT_CATALOG
OBJECT_SCHEMA
OBJECT_NAME
OBJECT_TYPE
GRANTOR
GRANTEE
adSchemaViewColumnUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViewTableUsage VIEW_CATALOG
VIEW_SCHEMA
VIEW_NAME
adSchemaViews TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
-----------------------------------------------------
returns: 返回包含数据库信息的 Recordset 对象. Recordset 将以只读、静态
游标打开.
-----------------------------------------------------
Remarks: OpenSchema方法返回与数据源有关的信息，例如关于服务器上的表
以及表中的列等信息, 上述数据仅供参考, 视具体的数据源可能会有不同。
==========================================================================*/
_RecordsetPtr CAdoConnection::OpenSchema(SchemaEnum QueryType)
{
ASSERT(m_pConnection != NULL);
try
{
return m_pConnection->OpenSchema(QueryType, vtMissing, vtMissing);
}
catch(_com_error e)
{
TRACE(_T(":( OpenSchema方法发生错误: %s\n"), e.ErrorMessage());
return NULL;
}
return NULL;
}

[klbt]

超长的答案，学习

[axiaowin]

在vckbase上有例子已经封装好了，解决了Ado数据库操作的大部分问题，超强，推荐。

[checkyvc6]

我要的是返回结果集的
//存储过程(返回记录集)
Create Procedure proc_sel
as
select id,name from tt_info

///////////////////////////////////////////
上面的存储过程执行后会返回一个记录集，你用Command的Execute执行后返回在Rs1里面
Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, adCmdUnknown );
然后你可以用RS1来取得你想要的东西

[zmpcoming]

mark

[bluebohe]

大家是被200分烧的吧呵呵
可是你有什么问题呢？就象你说的：

上面的存储过程执行后会返回一个记录集，你用Command的Execute执行后返回在Rs1里面
Rs1 = Cmd1->Execute( &vtEmpty, &vtEmpty2, adCmdUnknown );
然后你可以用RS1来取得你想要的东西

这样子是可以的，这还有什么问题呢？

[边学边用边创造]

up

[cdwy411]

估计他要的是 调用的存储过程的返回值是一个 Cursor 类型的!
即,Recordset里的每一条记录就是一个Cursor!
是吗?

[m0772]

顶！