新建一个dialog工程,工程名为oracle上面放一个button;
在stdafx.h最后加上:
#include <comdef.h>
#import "c:\program files\common files\system\ado\msado20.tlb"\
no_namespace rename ("EOF", "adoEOF")
在oracle.cpp最后加上:
long COracleApp::InitOracle()
{
try
{
m_connectionptr.CreateInstance (__uuidof(Connection));
_bstr_t strConnect = _T("Provider=MSDAORA.1;Password=manager;User ID=system;Data Source=cdwz;Persist Security Info=True");
m_connectionptr->Open (strConnect,"system","manager",-1);
}
catch(...)
{
DisplayAdoError();
}
return 0;
}
long COracleApp::DisplayAdoError()
{
long errorcount=m_connectionptr->GetErrors ()->GetCount ();
_bstr_t add;
CString errormsg;
for (short i=0;i<errorcount;i++)
{
add=m_connectionptr->GetErrors ()->GetItem (COleVariant((short)i))->GetDescription ();
errormsg.Insert (0,(char*)add);
errormsg+="\r";
}
AfxMessageBox(errormsg);
return 0;
}
在oracle.h中加上
public:
long DisplayAdoError();
long InitOracle();
COracleApp();
修改BOOL COracleApp::InitInstance()
加上:
AfxOleInit();
InitOracle();
COracleDlg dlg;
m_pMainWnd = &dlg;
int nResponse = dlg.DoModal();
在oracledlg.cpp中修改onbutton1:
COracleApp *papp=(COracleApp*)AfxGetApp();
try
{
_bstr_t sqltext="{call for_result.pro_test(?,?,{resultset 0, p_cur})}";
_CommandPtr m_commandptr;
_RecordsetPtr m_pUserSet;
m_commandptr.CreateInstance (__uuidof(Command));
m_pUserSet.CreateInstance (__uuidof(Recordset));
m_commandptr->ActiveConnection =papp->m_connectionptr ;
m_commandptr->CommandText =sqltext;
m_commandptr->CommandType =adCmdText;
_ParameterPtr ParameterPtr1=NULL,ParameterPtr2=NULL;
ParameterPtr1=m_commandptr->CreateParameter ("id",adInteger,adParamInput,4,COleVariant((short)1));
m_commandptr->Parameters ->Append (ParameterPtr1);
ParameterPtr2=m_commandptr->CreateParameter ("tablename",adVarChar,adParamInputOutput,255,COleVariant("ff" ));
m_commandptr->Parameters ->Append (ParameterPtr2);
m_pUserSet=m_commandptr->Execute (NULL,NULL,adCmdUnknown);
//如果说没有结果集,则换成
//m_commandptr->Execute (NULL,NULL,adCmdUnknown);
// 至于你说的返回成功标志,可以放在输出参数里
_bstr_t result=m_commandptr->Parameters->GetItem("tablename")->GetValue();
if (!m_pUserSet->adoEOF )
{
long count=m_pUserSet->Fields ->GetCount ();
long iii=m_pUserSet->GetCollect (COleVariant((short)0)).intVal ; }
}
catch(...)
{
papp->DisplayAdoError();
}oralce的存储过程源程序:
create table a (id integer,addr varchar2);
create or replace package for_result is
type TypCur is ref cursor;
procedure pro_test(p_id number,result in out varchar2, p_cur out TypCur);
end for_result;
create or replace package body for_result is
procedure pro_test(p_id number,result in out varchar2, p_cur out TypCur)
is
begin
open p_cur for select * from a where id>p_id;
result :='pretty pretty gril';
end;
end for_result;
在stdafx.h最后加上:
#include <comdef.h>
#import "c:\program files\common files\system\ado\msado20.tlb"\
no_namespace rename ("EOF", "adoEOF")
在oracle.cpp最后加上:
long COracleApp::InitOracle()
{
try
{
m_connectionptr.CreateInstance (__uuidof(Connection));
_bstr_t strConnect = _T("Provider=MSDAORA.1;Password=manager;User ID=system;Data Source=cdwz;Persist Security Info=True");
m_connectionptr->Open (strConnect,"system","manager",-1);
}
catch(...)
{
DisplayAdoError();
}
return 0;
}
long COracleApp::DisplayAdoError()
{
long errorcount=m_connectionptr->GetErrors ()->GetCount ();
_bstr_t add;
CString errormsg;
for (short i=0;i<errorcount;i++)
{
add=m_connectionptr->GetErrors ()->GetItem (COleVariant((short)i))->GetDescription ();
errormsg.Insert (0,(char*)add);
errormsg+="\r";
}
AfxMessageBox(errormsg);
return 0;
}
在oracle.h中加上
public:
long DisplayAdoError();
long InitOracle();
COracleApp();
修改BOOL COracleApp::InitInstance()
加上:
AfxOleInit();
InitOracle();
COracleDlg dlg;
m_pMainWnd = &dlg;
int nResponse = dlg.DoModal();
在oracledlg.cpp中修改onbutton1:
COracleApp *papp=(COracleApp*)AfxGetApp();
try
{
_bstr_t sqltext="{call for_result.pro_test(?,?,{resultset 0, p_cur})}";
_CommandPtr m_commandptr;
_RecordsetPtr m_pUserSet;
m_commandptr.CreateInstance (__uuidof(Command));
m_pUserSet.CreateInstance (__uuidof(Recordset));
m_commandptr->ActiveConnection =papp->m_connectionptr ;
m_commandptr->CommandText =sqltext;
m_commandptr->CommandType =adCmdText;
_ParameterPtr ParameterPtr1=NULL,ParameterPtr2=NULL;
ParameterPtr1=m_commandptr->CreateParameter ("id",adInteger,adParamInput,4,COleVariant((short)1));
m_commandptr->Parameters ->Append (ParameterPtr1);
ParameterPtr2=m_commandptr->CreateParameter ("tablename",adVarChar,adParamInputOutput,255,COleVariant("ff" ));
m_commandptr->Parameters ->Append (ParameterPtr2);
m_pUserSet=m_commandptr->Execute (NULL,NULL,adCmdUnknown);
//如果说没有结果集,则换成
//m_commandptr->Execute (NULL,NULL,adCmdUnknown);
// 至于你说的返回成功标志,可以放在输出参数里
_bstr_t result=m_commandptr->Parameters->GetItem("tablename")->GetValue();
if (!m_pUserSet->adoEOF )
{
long count=m_pUserSet->Fields ->GetCount ();
long iii=m_pUserSet->GetCollect (COleVariant((short)0)).intVal ; }
}
catch(...)
{
papp->DisplayAdoError();
}oralce的存储过程源程序:
create table a (id integer,addr varchar2);
create or replace package for_result is
type TypCur is ref cursor;
procedure pro_test(p_id number,result in out varchar2, p_cur out TypCur);
end for_result;
create or replace package body for_result is
procedure pro_test(p_id number,result in out varchar2, p_cur out TypCur)
is
begin
open p_cur for select * from a where id>p_id;
result :='pretty pretty gril';
end;
end for_result;
解决方案 »
- 移植别人的程序,出现字符数组的参数出问题
- Debug Assertion failed!
- 孙鑫VC++深入讲解p583的一个问题
- 怎么用mfc的对话框显示超大图片?
- 利用OpenCV怎么对图像添加幻灯片的效果如逐渐消失,百叶窗之类的
- 多线程问题求教:请问怎样编写一个用与打印的打印线程,就象Word中的“后台打印”那样子?
- DLL调用时出现内存错误,是为什么?很急!
- CMyView::OnPrepareDC()究竟是有何用啊!在线等待!
- 如何使用InstallShield?
- error C2106: '=' : left operand must be l-value 是什么意思?
- 为什么不能用SetThreadPriority提高线程优先级?
- 菜鸟的问题,快速结帖,送分!!!!
有什么错误的话早点告诉我哈。
我一加
#import "c:\program files\common files\system\ado\msado20.tlb"\
no_namespace rename ("EOF", "adoEOF")
就说重复定义。
SQL> desc a;
名称 空值? 类型
ID NUMBER(38)
NAME VARCHAR2(20)
SQL> select * from a;
ID NAME
--------- --------------------
17 pretty pretty gril
包的定义:
create or replace package for_result is
type TypCur is ref cursor;
procedure pro_test(p_id number,result out varchar2, p_cur out TypCur);
end for_result;
包体的定义:
create or replace package body for_result is
procedure pro_test(p_id number,result out varchar2, p_cur out TypCur)
is
begin
open p_cur for select * from a where id>p_id;
result :='pretty pretty gril';
end;
end for_result;新建一个win32 console project.加入文件oledb.cpp:
#include <atldbcli.h>
#include <iostream.h>
#include <comdef.h>
#define RETURNHR(hr) if(FAILED((HRESULT)hr)) { AtlTraceErrorRecords((HRESULT)hr); return E_FAIL; }
class cdwzparam
{
public:
int id;
char name[50];
char result[20];
BEGIN_COLUMN_MAP(cdwzparam)
COLUMN_ENTRY(1,id)
COLUMN_ENTRY(2,name)
END_COLUMN_MAP()
BEGIN_PARAM_MAP(cdwzparam)
SET_PARAM_TYPE(DBPARAMIO_INPUT)
COLUMN_ENTRY(1, id)
SET_PARAM_TYPE(DBPARAMIO_OUTPUT)
COLUMN_ENTRY(2,result)
END_PARAM_MAP()
};int main(void)
{
CoInitialize(NULL);
HRESULT hr;
CDataSource connection;
CSession session;
hr = connection.OpenFromInitializationString(L"Provider=MSDAORA.1;Password=manager;User ID=system;Data Source=cdwz");
hr = session.Open(connection);
CCommand<CAccessor< cdwzparam> > command;
hr=command.Create(session,"{call for_result.pro_test(?,?)}");
hr=command.Prepare (1);
command.id=1;
_tcscpy(command.result,"");
hr=command.Open (NULL,NULL,true);
RETURNHR(hr);
_bstr_t mmm(command.result); while( command.MoveNext() == S_OK)
{
cout<<command.id <<" "<<command.name <<endl;
}
command.Close ();
session.Close ();
connection.Close ();
CoUninitialize();
return S_OK;
}
今天偷偷到公司加了一会儿班.顺便写了一下,应该是你想要的吧哈.
不会再要我写一个odbc访问oracle的吧哈.
存储过程test1:
create or replace procedure test1(i in number,result out varchar2) as
begin
/*dbms_output.put_line('输入参数是' || to_char(i));*/
delete from a;
insert into a values (17,'pretty pretty gril');
result :='pretty pretty gril';
end;
在控制面板里odbc数据源新建一个user dsn.add->orace odbc driver->确定.data source name为oracle,desciption为 oracle.service name为oracle数据库的名字,我的cdwz.userid为system.(根据你自己的机子更改一下)
新建一个win32 console project,加入文件odbc.cpp;
#include <stdio.h>
#include <windows.h>
#include <sqlext.h>
#include <sql.h>
#include <odbcss.h>#define MAXBUFLEN 256
SQLHENV henv = SQL_NULL_HENV;
SQLHDBC hdbc = SQL_NULL_HDBC;
void ProcessLogMessages(SQLSMALLINT plm_handle_type,
SQLHANDLE plm_handle, char *logstring,
int ConnInd);
int main()
{
SQLHSTMT hstmt = SQL_NULL_HSTMT;
RETCODE retcode;
retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);
retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
retcode = SQLConnect(hdbc, (SQLCHAR*) "oracle", SQL_NTS, (SQLCHAR*) "system", SQL_NTS,(SQLCHAR*) "manager", SQL_NTS);
retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
retcode = SQLPrepare(hstmt, (SQLCHAR *)"{call test1(?,?)}", SQL_NTS);
SQLINTEGER inparm;
SQLCHAR outparm[30];
ZeroMemory(outparm, 30);
inparm=1;
long length=SQL_NTS; retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG ,
SQL_INTEGER, 1, 0, &inparm, 0, &length);
retcode = SQLBindParameter(hstmt,2, SQL_PARAM_OUTPUT, SQL_C_CHAR,
SQL_CHAR, 30, 0, outparm, 30, &length); retcode = SQLExecute(hstmt);
if ( (retcode != SQL_SUCCESS) &&(retcode != SQL_SUCCESS_WITH_INFO) ) {
ProcessLogMessages(SQL_HANDLE_STMT, hstmt,"SQLExecute() Failed\n\n", TRUE);
}
printf("%s\n", outparm);
SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
retcode = SQLDisconnect(hdbc);
retcode = SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
return 0;
}
void ProcessLogMessages(SQLSMALLINT plm_handle_type,
SQLHANDLE plm_handle,
char *logstring, int ConnInd)
{
RETCODE plm_retcode = SQL_SUCCESS;
UCHAR plm_szSqlState[MAXBUFLEN] = "",
plm_szErrorMsg[MAXBUFLEN] = "";
SDWORD plm_pfNativeError = 0L;
SWORD plm_pcbErrorMsg = 0;
SQLSMALLINT plm_cRecNmbr = 1;
SDWORD plm_SS_MsgState = 0, plm_SS_Severity = 0;
SQLINTEGER plm_Rownumber = 0;
while (plm_retcode != SQL_NO_DATA_FOUND) {
plm_retcode = SQLGetDiagRec(plm_handle_type, plm_handle,
plm_cRecNmbr, plm_szSqlState, &plm_pfNativeError,
plm_szErrorMsg, MAXBUFLEN - 1, &plm_pcbErrorMsg);
}
plm_cRecNmbr++;
}
谢谢,我回去研究一下。
我是用CManualAccessor做的,但访问时总是有问题,提示我参数个数不对,你能帮我看看吗?
create or replace package t3 is
type TypCur is ref cursor;
procedure test(p_id in number,result1 in varchar2, result2 in varchar2, ret_cur out TypCur);
end t3;
/
create or replace package body t3 is
procedure test(p_id in number,result1 in varchar2, result2 in varchar2, ret_cur out TypCur)
is
begin
open ret_cur for select * from system.test;
end test;
end t3;
//下面是一个调用函数:
USES_CONVERSION;
CCommand<CManualAccessor, CRowset, CNoMultipleResults>* prs;
struct MYBIND* pParams = NULL;
ULONG ulParams = 0;
DWORD arrType[3]; //创建存储过程语句
CString strCall;
//add at 2002-11-21
//只能是带有三个参数的定式
ulParams = 3;
// 在Oracle中char和varchar2类型对应于DBTYPE中的类型都是DBTYPE_WSTR;而int 和numeric对应当则是DBTYPE_NUMERIC;
// 输入三个参数,分别是:int ,char,char;//使用exec t3.test 1,'3','34fg'调用,
strCall.Format(_T("{call %s(?, ?, ?)}"), pszProcName);
arrType[0] = DBTYPE_NUMERIC;
arrType[1] = DBTYPE_WSTR;
arrType[2] = DBTYPE_WSTR; prs = new CCommand<CManualAccessor, CRowset, CNoMultipleResults>;
prs->Create(m_session, strCall);
prs->Prepare(1); if (ulParams > 0)
{
DBPARAMBINDINFO* pBindInfo = new DBPARAMBINDINFO[ulParams];
ULONG* pOrdinals = new ULONG[ulParams];
pParams = new MYBIND[ulParams]; // 帮定输入参数,以DBTYPE_STR类型为基础
prs->CreateParameterAccessor(ulParams, &pParams[0], sizeof(MYBIND)*ulParams);
for (ULONG l=0; l<ulParams; l++)
{
//设置参数的属性
pOrdinals[l] = l+1;
pBindInfo[l].pwszDataSourceType = T2OLE(_T("DBTYPE_CHAR"));
pBindInfo[l].pwszName = NULL;
pBindInfo[l].ulParamSize = 40;
pBindInfo[l].bPrecision = 0;
pBindInfo[l].bScale = 0; //改成全部是输入
pBindInfo[l].dwFlags = DBPARAMFLAGS_ISINPUT;// new add
// 设置帮定值
if(l < aryItem.GetSize())
{
_tcsncpy(pParams[l].szValue, aryItem[l], 40);
}
//帮定 add at 2002-11-21
prs->AddParameterEntry(l+1, arrType[l], sizeof(TCHAR)*40,
&pParams[l].szValue, NULL, NULL, DBPARAMIO_INPUT);
} // 调用 ICommandWithParameters::SetParameterInfo 这样我们可以告诉 provider把参数类型转换成 DBTYPE_STR
prs->SetParameterInfo(ulParams, pOrdinals, pBindInfo);
delete pOrdinals;
delete pBindInfo;
}
/*
CDBPropSet propset(DBPROPSET_ROWSET);
//Oracle要求要加上这句话,加上了确不好使,shit!
propset.AddProperty(ORAPROP_PLSQLRSet, true);
propset.AddProperty(DBPROP_IRowsetChange, true);
propset.AddProperty(DBPROP_CLIENTCURSOR, VARIANT_TRUE);
propset.AddProperty(DBPROP_CANSCROLLBACKWARDS, true);
propset.AddProperty(DBPROP_CANFETCHBACKWARDS, true);
propset.AddProperty(DBPROP_UPDATABILITY, DBPROPVAL_UP_INSERT | DBPROPVAL_UP_CHANGE | DBPROPVAL_UP_DELETE);
*/
if (prs->Open(NULL, NULL, true) != S_OK)
{
GetLastError(m_szLastErrorDesc);
SetStatus(m_szLastErrorDesc);
delete prs;
prs = NULL;
delete pParams;
return FALSE;
}
BOOL bRet = FALSE;
if (prs->m_spRowset != NULL)
{
//显示记录集
bRet = ShowTableData((CCommand<CManualAccessor>*)prs);
}
else
{
SetStatus(_T("储存过程正常返回,但没有记录集。"));
bRet = TRUE;
}
if (pParams != NULL)
delete pParams;
prs->Close();
return bRet;
你不知道你运行了你的程序(OLE DB)没有,我按你的方法编了一个,但是不行,后来我干脆按你的原封不动的抄上,结果还是不对,虽然他们都可以编译通过,但运行时会出现command.m_spRowset等于空的现象,也就是说没有返回记录集。为什么会出现这种现象呢?
不要说我的表有毛病:表已创建。create table a (ID int, name char(20))
*
ERROR 位于第 1 行:
ORA-00955: 名称已由现有对象使用已创建10行。
程序包已创建。
程序包主体已创建。
class CMyCmd: public CCommand< CManualAccessor, CNoRowset >
{public: void AddParameterEntry(ULONG nOrdinal, DBTYPE wType, ULONG nColumnSize,
void* pData, void* pLength = NULL, void* pStatus = NULL,
DBPARAMIO eParamIO = DBPARAMIO_INPUT)
{
ATLASSERT(m_nCurrentParameter < m_nParameters);
ULONG nLengthOffset, nStatusOffset; if (pStatus != NULL)
nStatusOffset = (BYTE*)pStatus - m_pParameterBuffer;
else
nStatusOffset = 0; if (pLength != NULL)
nLengthOffset = (BYTE*)pLength - m_pParameterBuffer;/*m_pBuffer*/
else
nLengthOffset = 0; CManualAccessor::Bind(m_pParameterEntry + m_nCurrentParameter, nOrdinal, wType, nColumnSize, 0, 0,
eParamIO, (BYTE*)pData - m_pParameterBuffer, nLengthOffset, nStatusOffset); m_nCurrentParameter++;
}
};
do like following: CSession session ;
hr = session.Open( ds ) ;
CCommand< CManualAccessor, CNoRowset > cmd ;<BR/>
cmd.Create( session, NULL);
cmd.CreateParameterAccessor( 2, &buf, sizeof(buf) );
cmd.AddParameterEntry( 1, DBTYPE_I4,0, &buf, NULL, NULL, DBPARAMIO_INPUT );
cmd.AddParameterEntry( 2, DBTYPE_STR, 32, (PBYTE)&buf + 4, (PBYTE)&buf + 36 , NULL, DBPARAMIO_OUTPUT ); if( SUCCEEDED( hr = cmd.Open(session, "{call sp_myproc(?,?)}", NULL, &lRows, DBGUID_DEFAULT, false) ) )
{
你的那个程序的确是不能执行,运行到while( command.MoveNext() == S_OK)
一句是因为没有判断command.m_spRowset,所以会出错,因为我运行时更本就没有记录集返回。不是机器的原因吧,我也是Win2K server + vc6 +oracle9i.
_bstr_t mmm(command.result);mmm有没有变成'pretty pretty gril';
要不你先在sql plus中测试一下,for_result.pro_test(?,?);
我的是win2k pro+oracle 8.1+mdac 2.7.因为以前用ado访问oracle老出问题所以给改成2.7.参数少的情况我也遇见过当时是因为,"{call for_result.pro_test(?,?)}");这儿忘了加"{"和"}"号.
CCommand<CManualAccessor, CRowset, CNoMultipleResults>* prs;
struct MYBIND* pParams = NULL;
ULONG ulParams = 0;
DWORD arrType[3]; //创建存储过程语句
CString strCall;
ulParams = 3;
strCall.Format(_T("{call %s(?, ?, ?)}"), pszProcName);
arrType[0] = DBTYPE_NUMERIC;
arrType[1] = DBTYPE_WSTR;
arrType[2] = DBTYPE_WSTR; prs = new CCommand<CManualAccessor, CRowset, CNoMultipleResults>; if (ulParams > 0)
{
pParams = new MYBIND[ulParams]; // 帮定输入参数,以DBTYPE_STR类型为基础
prs->CreateParameterAccessor(ulParams, &pParams[0], sizeof(MYBIND)*ulParams);
for (ULONG l=0; l<ulParams; l++)
{
if(l < aryItem.GetSize())
{
_tcsncpy(pParams[l].szValue, aryItem[l], 40);
}
//帮定 add at 2002-11-21
prs->AddParameterEntry(l+1, arrType[l], sizeof(TCHAR)*40,
&pParams[l].szValue, NULL, NULL, DBPARAMIO_INPUT);
} }
if (prs->Open(m_session, strCall, NULL, NULL, DBGUID_DEFAULT, false) != S_OK)
{
GetLastError(m_szLastErrorDesc);
SetStatus(m_szLastErrorDesc);
delete prs;
prs = NULL;
delete pParams;
return FALSE;
}
BOOL bRet = FALSE;
if (prs->m_spRowset != NULL)
{
//显示记录集
bRet = ShowTableData((CCommand<CManualAccessor>*)prs);
}
else
{
SetStatus(_T("储存过程正常返回,但没有记录集。"));
bRet = TRUE;
}
if (pParams != NULL)
delete pParams;
prs->Close();
return bRet;