[Oracle] VC6.0 uses odbc to access Oracle stored procedures

Environmental description

System environment

• System: Windows XP
• IDE: Microsoft Visual C++ 6.0
• msado15.dll:
  • File version: 6.2.19041.3570
  • Product version: 10.0.19041.3570
• Data source driver: Oracle in OraClient11g_home1 – Oracle Datebase Client 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-Bit)
  
  

Object used 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 stored procedures

Procedures

CREATE OR REPLACE PROCEDURE PRO_DISPENSER_DISPENSING
(
  I_CHFM IN VARCHAR2
,ERR_NO OUT NUMBER
,ERR_MSG OUT VARCHAR2
)AS
BEGIN
  ERR_NO := 1;
  ERR_MSG := I_CHFM;
END PRO_DISPENSER_DISPENSING;

Use Oracle SQL Developer to call stored procedures

 DECLARE
    v_err_no NUMBER;
    v_err_msg VARCHAR2(100);
  BEGIN
    PRO_DISPENSER_DISPENSING('Shao Mo Qianhua', v_err_no, v_err_msg);
    DBMS_OUTPUT.PUT_LINE('Error number: ' || v_err_no);
    DBMS_OUTPUT.PUT_LINE('Error message: ' || v_err_msg);
  END;

VC6.0 complete code

Note: The following code may succeed in the first call but fail in the second call. It is recommended to close the link each time it is called, reopen the link and then call the stored procedure.

 CADOManage* adb = new CADOManage();
    LONG errorCode = adb->open();
if (errorCode !=0)
{<!-- -->
continue;
}
//Call stored procedure
errorCode = adb->execSQL2('Input parameter 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");///Create Connection object
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)///Catch exception
{<!-- -->
string error message;
errormessage = "Failed to connect to ADO database!\rError message:";
errormessage + =e.ErrorMessage();
rootLogger->fatal(errormessage);
// AfxMessageBox(errormessage.c_str());///Display error message
//bOpen= false;
        return ER_DB_CONNECT;
}
rootLogger->trace("function open end............");
return 0;
}

long CADOManage::execSQL2(const string & amp;sqlText)
{<!-- -->
try{<!-- -->
rootLogger->trace("function execSQL start............");
mute->Lock();
int nRet = 0;
\t\t
if (sqlText == "")
{<!-- -->
rootLogger->warn("no SQL text!............");
mute->Unlock();
return WM_NO_SQLTEXT;
}
\t\t
freeRecord();
rootLogger->debug("sqlText = " + sqlText);
//Set command type
m_pCommand->CommandType = adCmdStoredProc;
//Set the stored procedure name
m_pCommand->CommandText =_bstr_t("pro_dispenser_dispensing");
m_pCommand->put_CommandTimeout(60);
_ParameterPtr pInputParam,pOutputParam1,pOutputParam2;
pInputParam.CreateInstance(__uuidof(Parameter));
pOutputParam1.CreateInstance(__uuidof(Parameter));
pOutputParam2.CreateInstance(__uuidof(Parameter));
//Set input parameters i_cfhm name, data type, input/output, length, content
        pInputParam = m_pCommand->CreateParameter(_bstr_t("i_cfhm"), adVarChar, adParamInput, sqlText.length(), sqlText.c_str());
        pInputParam->Value = _variant_t(sqlText.c_str());
        //Set the output parameter 1 err_no name, data type, input/output, length
pOutputParam1 = m_pCommand->CreateParameter(_bstr_t("err_no"), adInteger, adParamOutput,10);
//Set the output parameter 2 err_msg name, data type, input/output, length
        pOutputParam2 = m_pCommand->CreateParameter(_bstr_t("err_msg"), adVarChar, adParamOutput, 1024);
pOutputParam2->Size = 1024;
//Add parameter 1 to command
m_pCommand->Parameters->Append(pInputParam);
//Add input and output parameters 1 to the command
        m_pCommand->Parameters->Append(pOutputParam1);
        //Add input and output parameters 2 to the command
        m_pCommand->Parameters->Append(pOutputParam2);
        //Execute the command in the form of stored procedure adCmdStoredProc
m_pRecordset = m_pCommand->Execute(NULL, NULL,adCmdStoredProc);
\t\t
_variant_t errNoValue = pOutputParam1->Value;
_variant_t errMsgValue = pOutputParam2->Value;

//Convert to specific type
long errNo = errNoValue.lVal;
CString temp;
temp.Format(_T("%ld"), errNo);
MessageBox(NULL, temp, _T("err_no"), MB_OK);
if (errMsgValue.bstrVal != NULL)
{<!-- -->
_bstr_t errMsg = errMsgValue.bstrVal;
string temp(errMsg);
}

m_pCommand
->Parameters->Release();
pInputParam->Release();
pOutputParam1->Release();
pOutputParam2->Release();
\t\t
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;
}

Error reporting

An exception was encountered when calling the Oracle stored procedure using m_pCommand->Execute(NULL, NULL,adCmdStoredProc).

• Data source driver: Oracle in OraClient12Home1_32bit

This situation may be caused by compatibility issues between different versions of the Oracle driver and msado15.dll. Different versions of drivers and libraries may have different interfaces and behaviors, so incompatibilities may occur when using different versions of drivers.
One way to resolve this issue is to ensure that the Oracle driver used is compatible with a version of msado15.dll. You can try the following solutions:

1. Replace msado library – Confirm that your code is using the msado15.dll library file that matches the Oracle driver version. If you are using the Oracle in OraClient12Home1_32bit driver, try using the msado15.dll library file that corresponds to that driver version.
2. If you cannot find the msado15.dll library file that exactly matches the Oracle driver version, you can try to upgrade or downgrade the Oracle driver version, such as: Oracle in OraClient11g_home1 to ensure compatibility with the available msado15.dll library file.
3. In addition, you can also consider using other database connection libraries or methods to connect and call Oracle stored procedures. For example, you can try to use the official ODBC driver provided by Oracle or other third-party database connection library to avoid problems with msado15 .dll version incompatibility issue.

Microsoft C++ exception: long at memory location

Exception raised at 0x75FOD982 (in OracleTest.exe): Microsoft C++ exception: long, located at memory location 0x00E6D88C.

Exception raised at 0x75FOD982 (in OracleTest.exe): Microsoft C++ exception: long, located at memory location 0x0133D9A4.