【Oracle】VC6.0使用 odbc 访问 Oracle 存储过程

环境说明

系统环境

• 系统：Windows XP
• IDE： Microsoft Visual C++ 6.0

使用的对象 msado15.tlh

• _ConnectionPtr m_pConnection;

  HRESULT hr = m_pConnection.CreateInstance(“ADODB.Connection”);

• _RecordsetPtr m_pRecordset;

  m_pRecordset.CreateInstance(“ADODB.Recordset”);

• _CommandPtr m_pCommand;

  m_pCommand.CreateInstance(“ADODB.Command”);

• _ParameterPtr pInputParam;

  pInputParam.CreateInstance(__uuidof(Parameter));

Oracle 存储过程

Procedures

PRO_DISPENSER_DISPENSING { 
I_CHFM VARCHAR2,
ERR_NO NUMBER OUT,
ERR_MSG VARCHAR2 OUT}

VC6.0 完整代码

注意事项：以下代码可以会出现第一次调用成功，第二次调用失败的情况，建议每次调用都关闭链接，再重新打开链接再调用存储过程。

	CADOManage* adb = new CADOManage();LONG errorCode = adb->open();if (errorCode !=0){continue;}// 调用存储过程errorCode = adb->execSQL2('入参1');if (errorCode !=0){adb->freeRecord();adb->close();continue;}adb->freeRecord();adb->close();

long CADOManage::open()								
{//return open(param);string connectMode = configUtil.getValue(cfghis_unMysql_DBConnectMode);try{rootLogger->trace("function open start..............");CoInitialize(0);HRESULT hr = m_pConnection.CreateInstance("ADODB.Connection");///创建Connection对象if(SUCCEEDED(hr)){//dbType = "MYSQL";_bstr_t connStr;m_pConnection->CursorLocation=adUseClient;for(int i=0;i< DBCMORA_NUM;i++){if (egOracleCoonectUI[i] == connectMode){string strConnect;CString cstrConnect;cstrConnect =stringToCString(egOracleCoonect[i]) ;cstrConnect.Replace("myHost",stringToCString(ADODBparam.m_server));cstrConnect.Replace("myUser",stringToCString(ADODBparam.m_uid));cstrConnect.Replace("myPassword",stringToCString(ADODBparam.m_pwd));cstrConnect.Replace("myDSName",stringToCString(ADODBparam.m_data));if(ADODBparam.m_port == "")ADODBparam.m_port = 1433;cstrConnect.Replace("myPort",stringToCString(ADODBparam.m_port));strConnect = CStringTostring(cstrConnect);if (i== DBCMORA_ODBC1 || i==DBCMORA_OLEDBMS13 || i==DBCMORA_OLEDBOracle1){m_pConnection->Open(_bstr_t(strConnect.c_str()),(_bstr_t)ADODBparam.m_uid.c_str(),(_bstr_t)ADODBparam.m_pwd.c_str(),adConnectUnspecified);}else if (i== DBCMORA_ODBC3){m_pConnection->Open(_bstr_t(strConnect.c_str()),(_bstr_t)_T(""),(_bstr_t)_T(""),adModeUnknown);}else{m_pConnection->Open(_bstr_t(strConnect.c_str()),(_bstr_t)_T(""),(_bstr_t)_T(""),adConnectUnspecified);}break;}}	bOpen= true;m_pRecordset.CreateInstance("ADODB.Recordset");m_pCommand.CreateInstance("ADODB.Command");m_pCommand->ActiveConnection = m_pConnection;}}catch(_com_error e)///捕捉异常{string errormessage;errormessage = "连接ADO数据库失败!\r错误信息:";errormessage +=e.ErrorMessage();rootLogger->fatal(errormessage);//	AfxMessageBox(errormessage.c_str());///显示错误信息//bOpen= false;return ER_DB_CONNECT;}rootLogger->trace("function open end..............");return 0;
}		

long CADOManage::execSQL2(const string &sqlText)
{try{	rootLogger->trace("function execSQL start..............");mute->Lock();int nRet = 0;if (sqlText == ""){rootLogger->warn("no SQL text!..............");mute->Unlock();return WM_NO_SQLTEXT;}freeRecord();rootLogger->debug("sqlText = " + sqlText);//设置命令类型m_pCommand->CommandType = adCmdStoredProc;//设置存储过程名称m_pCommand->CommandText =_bstr_t("portal56_his.pro_dispenser_dispensing");m_pCommand->put_CommandTimeout(60);_ParameterPtr pInputParam,pOutputParam1,pOutputParam2;pInputParam.CreateInstance(__uuidof(Parameter));pOutputParam1.CreateInstance(__uuidof(Parameter));pOutputParam2.CreateInstance(__uuidof(Parameter));//设置入参 i_cfhm 名称、数据类型、输入/输出、长度、内容pInputParam = m_pCommand->CreateParameter(_bstr_t("i_cfhm"), adVarChar, adParamInput, sqlText.length(), sqlText.c_str());pInputParam->Value = _variant_t(sqlText.c_str());//设置出参1 err_no 名称、数据类型、输入/输出、长度pOutputParam1 = m_pCommand->CreateParameter(_bstr_t("err_no"), adInteger, adParamOutput,10);//设置出参2 err_msg 名称、数据类型、输入/输出、长度pOutputParam2 = m_pCommand->CreateParameter(_bstr_t("err_msg"), adVarChar, adParamOutput, 1024);pOutputParam2->Size = 1024;//添加入参1 到 命令m_pCommand->Parameters->Append(pInputParam);//添加入出参1 到 命令m_pCommand->Parameters->Append(pOutputParam1);//添加入出参2 到 命令m_pCommand->Parameters->Append(pOutputParam2);//以存储过程的形式adCmdStoredProc 执行命令m_pRecordset = m_pCommand->Execute(NULL, NULL,adCmdStoredProc); m_pCommand->Parameters->Release();pInputParam->Release();pOutputParam1->Release();pOutputParam2->Release();mute->Unlock();rootLogger->trace("function execSQL end..............");}catch (_com_error e) {string errormessage = e.ErrorMessage();        rootLogger->error("failed to execute SQL:" + errormessage);rootLogger->error("failed sql = " + sqlText);mute->Unlock();return ER_EXECUTE_SQL;}return 0;
}