1.//下载Instant Client
https://www.oracle.com/database/technologies/instant-client/downloads.html
下载basic和sdk,前者是dll,后者是库文件
2.//程序依赖的dll文件有
a)当前版本10.2
b)应用程序依赖的DLL有
oci.dll //oracle call interface
oraociei10.dll //oracle call interface instant clinet
程序中打包上面的两个文件就可以了
c)注意事项
环境变量可以不使用,缺少oraociei10.dll会无法连接数据库
3.//封装库
//OCIHelper.h
#pragma once
#include "stdafx.h"
#include <Inc\DB\oracle2\Instant Client\sdk\include\oci.h>
#include "vector"
#pragma warning(disable:4251)
#pragma warning(disable:4101)
#pragma warning(disable:4267)
using namespace ATL;
#ifndef _OCIHELPER_STR
#ifdef _UNICODE
#define PLStrLen wcslen
#else
#define PLStrLen strlen
#ifdef _X86_
extern "C" { int _afxForceUSRDLL; }
#else
extern "C" { int __afxForceUSRDLL; }
#endif
#endif
#else
#define _OCIHELPER_STR
#endif // !_OCIHELPER_STR
#ifdef DLLDIR_EX
#define DLLDIR __declspec(dllexport)
#else
#define DLLDIR __declspec(dllimport)
#endif
#define OH_NA -1 //not available
#define OH_SUCCESS 0 //success
#define OH_ERROR 1 //error
//
typedef struct
{
char* ColName; //字段名
ub2 collen; //字段长度
ub2 coltyp; //字段类型
ub1 precision;
sb1 scale;
void* pValue; //值
sb4 value_size; //值的长度
ub2 rlenp;
sb2 indp;
OCIDefine* defnpp;
} OHCell;
//
typedef struct
{
std::vector<CString> head;
std::vector<std::vector<CString>> cells;
} OHDataTable;
typedef CMap<CString, LPCWSTR, CString, LPCWSTR> OHMapFieldAndValue;
namespace PingLibrary
{
class DLLDIR COCIHelper
{
public:
COCIHelper(void);
~COCIHelper(void);
//opening and closing
virtual bool connect(CString strServerIP, CString strDBName, CString strUser, CString strPassWord);
virtual bool isConnect();
virtual int disconnect();
//query all data
virtual int query(CString strSQL, bool select = true);
virtual int query(CString strSQL, OHDataTable* dataTable);
inline int executeSelect(CString strSQL, OHDataTable* dataTable) { return query(strSQL, dataTable); };
int executeSelect(CString strTable, CString strField, CString strValue, OHDataTable* dataTable);
int executeSelectBind(CString strTable, CString strField, CString strValue, OHDataTable* dataTable);
int executeSelect(CString strTable, OHMapFieldAndValue& fieldAndValueWhere, OHDataTable* dataTable);
int executeSelectBind(CString strTable, OHMapFieldAndValue& fieldAndValueWhere, OHDataTable* dataTable);
int executeSelect(CString strTable, CStringArray& field, OHMapFieldAndValue& fieldAndValueWhere, OHDataTable* dataTable);
inline int executeInsert(CString strSQL) { return query(strSQL, false); };
int executeInsert(CString strTable, OHMapFieldAndValue& fieldAndValue);
inline int executeUpdate(CString strSQL) { return query(strSQL, false); };
int executeUpdate(CString strTable, CString strField, CString strValue);
int executeUpdate(CString strTable, OHMapFieldAndValue& fieldAndValueSet);
int executeUpdate(CString strTable, OHMapFieldAndValue& fieldAndValueSet, OHMapFieldAndValue& fieldAndValueWhere);
inline int executeDelete(CString strSQL) { return query(strSQL, false); };
int executeDelete(CString strTable, CString strField, CString strValue);
int executeDelete(CString strTable, OHMapFieldAndValue& fieldAndValueWhere);
int fetch();
//row and col info
virtual long getNumRows();
virtual int getNumCols();
virtual int getColName(int col, CString* string);
virtual int getColType(int col, int* type); //0-string 1-bool 2-short 3-long 4-float
//5-double 6-currency 7-date 8-time
//8-memo 9-blob 10-ole
int getAllTables(OHDataTable* dataTable);
int getTableColmns(CString strTable, OHDataTable* dataTable);
CString getOciError();
BOOL SetEnv(CString key, CString value, BOOL append = FALSE);
CString GetApplicatiobDir();
private:
// OCI Internal functions
int initialize(CString strTNSName);
int logon(CString strUser, CString strPassWord);
int geColumnAttr(int numcols);
void ErrorProc(dvoid* err, sword status);
char* CStrToChar(CString str);
long m_rows;
int m_cols;
bool m_connected;
std::vector<CString> ColHead;//字段
std::vector<std::vector<CString>> ColVal;//值
OCIEnv* m_envhp;
OCIServer* m_srvhp;
OCIError* m_errhp;
OCISvcCtx* m_svchp;
OCIStmt* m_stmhp;
OCIDescribe* m_dschp;
OCISession* m_authp;
OHCell* m_Parms;
};
}
//OCIHelper.cpp
#include "stdafx.h"
#include "OCIHelper.h"
#include <string.h>
#include <crtdbg.h>
using namespace PingLibrary;
COCIHelper::COCIHelper(void)
{
//
m_rows = 0;
m_cols = 0;
m_Parms = NULL;
m_connected = FALSE;
//
m_envhp = NULL;
m_srvhp = NULL;
m_errhp = NULL;
m_svchp = NULL;
m_stmhp = NULL;
m_dschp = NULL;
m_authp = NULL;
}
COCIHelper::~COCIHelper(void)
{
if (m_connected) {
disconnect();
}
}
bool COCIHelper::connect(CString strServerIP, CString strDBName, CString strUser, CString strPassWord)
{
CString str1;
str1.Format(_T("(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = %s)(PORT = 1521)))\
(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = %s)))"), strServerIP, strDBName);
m_connected = false;
sword rc = OCI_SUCCESS;
rc = initialize(str1);
if (rc == OCI_SUCCESS) {
rc = logon(strUser, strPassWord);
if (rc == OCI_SUCCESS) {
m_connected = TRUE;
}
}
return (rc == OCI_SUCCESS) ? true : false;
}
bool COCIHelper::isConnect()
{
return m_connected;
}
int COCIHelper::disconnect()
{
m_connected = FALSE;
try
{
// Logout
OCIHandleFree((dvoid*)m_stmhp, (ub4)OCI_HTYPE_STMT);
OCISessionEnd(m_svchp, m_errhp, m_authp, (ub4)0);
// Cleanup
if (m_srvhp) OCIServerDetach(m_srvhp, m_errhp, (ub4)OCI_DEFAULT);
if (m_srvhp) OCIHandleFree((dvoid*)m_srvhp, (CONST ub4) OCI_HTYPE_SERVER);
if (m_svchp) OCIHandleFree((dvoid*)m_svchp, (CONST ub4) OCI_HTYPE_SVCCTX);
if (m_errhp) OCIHandleFree((dvoid*)m_errhp, (CONST ub4) OCI_HTYPE_ERROR);
if (m_authp) OCIHandleFree((dvoid*)m_authp, (CONST ub4) OCI_HTYPE_SESSION);
if (m_dschp) OCIHandleFree((dvoid*)m_dschp, (ub4)OCI_HTYPE_DESCRIBE);
if (m_envhp) OCIHandleFree((dvoid*)m_envhp, (ub4)OCI_HTYPE_ENV);
//Free memory allocated for buffers
for (int x = 0; x < m_cols; x++)
{
free(m_Parms[x].ColName);
free(m_Parms[x].pValue);
}
if (m_Parms != NULL) {
free(m_Parms);
}
//
ColHead.clear();
ColVal.clear();
//vector必须使用swap来清空,否则会内存泄漏
std::vector<CString> v1;
ColHead.swap(v1);
std::vector<std::vector<CString>> v2;
ColVal.swap(v2);
//sword rc = OCITerminate(OCI_DEFAULT);//需要和OCIInitialize成对使用
}
catch (CException& ex)
{
}
return OH_SUCCESS;
}
long COCIHelper::getNumRows()
{
return m_rows;
}
int COCIHelper::getNumCols()
{
return m_cols;
}
int COCIHelper::getColName(int col, CString* string)
{
if (col < 0 || col > m_cols)
return OH_ERROR;
string->Format(_T("%s"), m_Parms[col].ColName);
return OH_SUCCESS;
}
/*
col - column of interest
type - ponter to an integer that stores information
about the data type. Possible data types can be:
UGCELLDATA_STRING (1) string
UGCELLDATA_NUMBER (2) number
UGCELLDATA_BOOL (3) boolean
UGCELLDATA_TIME (4) date/time
UGCELLDATA_CURRENCY (5) currency
*/
int COCIHelper::getColType(int col, int* type)
{
if (col < 0 || col > m_cols)
return OH_ERROR;
switch (m_Parms[col].coltyp)
{
case SQLT_INT:
case SQLT_BDOUBLE:
*type = 2;
break;
default:
*type = 1;
}
return OH_SUCCESS;
}
int COCIHelper::initialize(CString strTNSName)
{
ub4 service_len = (ub4)PLStrLen(strTNSName);
sword rc = OCI_ERROR;
//需要拷贝oraociei.dll到程序目录下,否则初始化失败
//ErrorProc(m_errhp, OCIEnvInit(&m_envhp, OCI_DEFAULT, 0, 0));
ErrorProc(m_errhp, OCIEnvCreate((OCIEnv**)&m_envhp, (ub4)OCI_DEFAULT,
(dvoid*)0, (dvoid * (*)(dvoid*, size_t)) 0,
(dvoid * (*)(dvoid*, dvoid*, size_t)) 0,
(void(*)(dvoid*, dvoid*)) 0, (size_t)0, (dvoid**)0));
ErrorProc(m_errhp, OCIHandleAlloc(m_envhp, (void**)&m_errhp, OCI_HTYPE_ERROR, 0, 0));
ErrorProc(m_errhp, OCIHandleAlloc(m_envhp, (void**)&m_srvhp, OCI_HTYPE_SERVER, 0, 0));
ErrorProc(m_errhp, OCIHandleAlloc(m_envhp, (void**)&m_svchp, OCI_HTYPE_SVCCTX, 0, 0));
ErrorProc(m_errhp, OCIHandleAlloc(m_envhp, (void**)&m_authp, OCI_HTYPE_SESSION, 0, 0));
ErrorProc(m_errhp, OCIHandleAlloc((dvoid*)m_envhp, (dvoid**)&m_dschp, OCI_HTYPE_DESCRIBE, 0, 0));
ErrorProc(m_errhp, OCIHandleAlloc((dvoid*)m_envhp, (dvoid**)&m_stmhp, OCI_HTYPE_STMT, 0, 0));
text* tns = (text*)CStrToChar(strTNSName);
rc = OCIServerAttach(m_srvhp, m_errhp, tns, service_len, OCI_DEFAULT);
delete tns;
if (rc == OCI_SUCCESS)
{
/* set attribute server context in the service context */
(void)OCIAttrSet((dvoid*)m_svchp, OCI_HTYPE_SVCCTX, (dvoid*)m_srvhp,
(ub4)0, OCI_ATTR_SERVER, (OCIError*)m_errhp);
}
else
rc = OCI_ERROR;
ErrorProc(m_errhp, rc);
return rc;
}
int COCIHelper::logon(CString strUser, CString strPassWord)
{
int rc = 0;
(void)OCIHandleAlloc((dvoid*)m_envhp, (dvoid**)&m_authp,
(ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid**)0);
char* usr = CStrToChar(strUser);
char* pwd = CStrToChar(strPassWord);
(void)OCIAttrSet((dvoid*)m_authp, (ub4)OCI_HTYPE_SESSION,
(dvoid*)usr, (ub4)PLStrLen(strUser),
OCI_ATTR_USERNAME, m_errhp);
(void)OCIAttrSet((dvoid*)m_authp, (ub4)OCI_HTYPE_SESSION,
(dvoid*)pwd, (ub4)PLStrLen(strPassWord),
OCI_ATTR_PASSWORD, m_errhp);
delete usr;
delete pwd;
rc = OCISessionBegin(m_svchp, m_errhp, m_authp, OCI_CRED_RDBMS, (ub4)OCI_DEFAULT);
if (rc == OCI_SUCCESS)
{
(void)OCIAttrSet((dvoid*)m_svchp, (ub4)OCI_HTYPE_SVCCTX,
(dvoid*)m_authp, (ub4)0,
(ub4)OCI_ATTR_SESSION, m_errhp);
}
else
rc = OCI_ERROR;
ErrorProc(m_errhp, rc);
return rc;
}
int COCIHelper::getAllTables(OHDataTable* dataTable)
{
sword status = OCI_ERROR;
try
{
CString strSQL;
strSQL.Format(_T("select table_name from user_tables order by table_name"));
if (OCI_SUCCESS == (status = query(strSQL)))
{
if (OCI_SUCCESS == (status = fetch()))
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
}
catch (CException& ex)
{
}
return status;
}
int COCIHelper::getTableColmns(CString strTable, OHDataTable* dataTable)
{
sword status = OCI_ERROR;
try
{
CString strSQL;
strSQL.Format(_T("select t.column_name from user_tab_columns t where t.table_name='%s'"), strTable);
if (OCI_SUCCESS == (status = query(strSQL)))
{
if (OCI_SUCCESS == (status = fetch()))
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
}
catch (CException& ex)
{
}
return status;
}
CString COCIHelper::getOciError()
{
text errbuf[512];
sb4 errcode = 0;
CString OciErrorMsg;
(void)OCIErrorGet((dvoid*)m_errhp, (ub4)1, (text*)NULL, &errcode,
errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
if (errcode == 1406) // Data Truncation
{
for (int x = 0; x < m_cols; x++)
{
if (m_Parms[x].indp == -2)
OciErrorMsg.Format(_T("Data Truncation on column %d, exceeds max size"), x);
else
if (m_Parms[x].indp > 0)
OciErrorMsg.Format(_T("Data Truncation on column %d, actual size %d"),
x, m_Parms[x].indp);
else
OciErrorMsg.Format(_T("Data truncation on column %d reported, but no size indicator"), x);
}
}
else {
OciErrorMsg.Format(_T("%s"), (char*)errbuf);
}
return OciErrorMsg;
}
BOOL COCIHelper::SetEnv(CString key, CString value, BOOL bAppend)
{
BOOL bResult = FALSE;
HKEY hRegKey;
CString str = _T("SYSTEM\\ControlSet001\\Control\\Session Manager\\Environment");
if (::RegOpenKey(HKEY_LOCAL_MACHINE, str, &hRegKey) != ERROR_SUCCESS)
bResult = FALSE;
else
{
if (bAppend) {
CString oldValue = _T("");
DWORD size;
::RegQueryValueEx(hRegKey, key, NULL, NULL, (BYTE*)oldValue.GetBuffer(1024), &size);
oldValue.ReleaseBuffer();
if (oldValue.Find(value) != -1) {
return TRUE;
}
value = value + _T(";") + oldValue;
}
bResult = (::RegSetValueEx(hRegKey,
key,
0,
REG_SZ,
(CONST BYTE*)value.GetBuffer(0),
value.GetLength() * sizeof(TCHAR)) != ERROR_SUCCESS);
}
return bResult;
}
CString COCIHelper::GetApplicatiobDir()
{
CString m_FilePath;
TCHAR exeFullPath[MAX_PATH] = { NULL };
GetModuleFileName(NULL, exeFullPath, MAX_PATH);
m_FilePath.Format(_T("%s"), exeFullPath);
int m_iPosIndex = m_FilePath.ReverseFind('\\');
m_FilePath = m_FilePath.Left(m_iPosIndex);
return m_FilePath;
}
/*
oracle field max length=30
*/
int COCIHelper::geColumnAttr(int numcols)
{
sword retval = OH_ERROR;
text* namep;
ub4 sizep;
OCIParam* parmdp;
ub4 parmcnt = numcols;
for (ub4 pos = 1; pos <= parmcnt; pos++)
{
/* get the parameter descriptor for each column */
if ((retval = OCIParamGet((void*)m_stmhp, (ub4)OCI_HTYPE_STMT, m_errhp,
(void**)&parmdp, (ub4)pos)) != OCI_SUCCESS)
return OCI_ERROR;
/* column length */
if ((retval = OCIAttrGet((dvoid*)parmdp, (ub4)OCI_DTYPE_PARAM,
(dvoid*)&m_Parms[pos - 1].collen, (ub4*)0,
(ub4)OCI_ATTR_DATA_SIZE, (OCIError*)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
/* column name */
if ((retval = OCIAttrGet((dvoid*)parmdp, (ub4)OCI_DTYPE_PARAM,
(dvoid*)&namep, (ub4*)&sizep,
(ub4)OCI_ATTR_NAME, (OCIError*)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
/*oracle field max length=30*/
if (sizep > 30) {
sizep = 30;
}
if (sizep)
{
strncpy((char*)m_Parms[pos - 1].ColName, (char*)namep, (size_t)sizep);
m_Parms[pos - 1].ColName[sizep] = '\0';
}
/* data type */
if ((retval = OCIAttrGet((dvoid*)parmdp, (ub4)OCI_DTYPE_PARAM,
(dvoid*)&m_Parms[pos - 1].coltyp, (ub4*)0,
(ub4)OCI_ATTR_DATA_TYPE, (OCIError*)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
/* precision */
if ((retval = OCIAttrGet((dvoid*)parmdp, (ub4)OCI_DTYPE_PARAM,
(dvoid*)&m_Parms[pos - 1].precision, (ub4*)0,
(ub4)OCI_ATTR_PRECISION, (OCIError*)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
/* scale */
if ((retval = OCIAttrGet((dvoid*)parmdp, (ub4)OCI_DTYPE_PARAM,
(dvoid*)&m_Parms[pos - 1].scale, (ub4*)0,
(ub4)OCI_ATTR_SCALE, (OCIError*)m_errhp)) != OCI_SUCCESS)
return OCI_ERROR;
// Release memory associated with handle, or we leak memory on each column.
OCIDescriptorFree((void*)parmdp, OCI_DTYPE_PARAM);
}
return retval;
}
int COCIHelper::fetch()
{
sword rc = OCI_ERROR;
ub4 num_rows = 0;
int numcols = 0;
// Get column count
rc = OCIAttrGet((const char*)m_stmhp, (ub4)OCI_HTYPE_STMT, (int*)&numcols,
(ub4*)0, (ub4)OCI_ATTR_PARAM_COUNT, m_errhp);
if (rc != OCI_SUCCESS)
return rc;
m_cols = numcols;
m_Parms = (OHCell*)calloc(m_cols, sizeof(OHCell));
if (m_Parms == NULL)
return 16;
// Allocate memory for column names
//oracle field max length=30
for (int x = 0; x < m_cols; x++)
{
m_Parms[x].ColName = (char*)malloc(31);
memset(m_Parms[x].ColName, 0, 31);
m_Parms[x].defnpp = (OCIDefine*)0;
}
rc = geColumnAttr(m_cols);
if (rc != OCI_SUCCESS)
return rc;
// Allocate memory to store cell values
for (int x = 0; x < m_cols; x++)
{
switch (m_Parms[x].coltyp)
{
case SQLT_INT:
m_Parms[x].pValue = (int*)malloc(sizeof(int));
m_Parms[x].value_size = sizeof(int);
break;
case SQLT_DAT:
// Environment variable NLS_DATE_FORMAT has been set to MM/DD/YYYY
// Coerce to character format
m_Parms[x].coltyp = SQLT_STR;
m_Parms[x].pValue = (char*)malloc(11);
m_Parms[x].value_size = 11;
break;
case SQLT_TIMESTAMP:
// Environment variable NLS_TIMESTAMP_FORMAT has been set to
// MM/DD/YYYY HH24:MI:SS.FF
// Coerce to character format
m_Parms[x].coltyp = SQLT_STR;
m_Parms[x].pValue = (char*)malloc(30);
m_Parms[x].value_size = 30;
break;
case SQLT_NUM:
if ((m_Parms[x].precision == 0) && (m_Parms[x].scale == -127))
{
m_Parms[x].coltyp = SQLT_BDOUBLE;
m_Parms[x].pValue = (double*)malloc(sizeof(double));
m_Parms[x].value_size = sizeof(double);
m_Parms[x].scale = 5;
}
else
{
if (m_Parms[x].scale == 0)
{
m_Parms[x].coltyp = SQLT_INT;
m_Parms[x].pValue = (int*)malloc(sizeof(int));
m_Parms[x].value_size = sizeof(int);
}
else
{
m_Parms[x].coltyp = SQLT_BDOUBLE;
m_Parms[x].pValue = (double*)malloc(sizeof(double));
m_Parms[x].value_size = sizeof(double);
}
}
break;
default:
// Must be a character format, lets force to null terminated string
m_Parms[x].coltyp = SQLT_STR;
m_Parms[x].pValue = (char*)malloc(m_Parms[x].collen + 1);
m_Parms[x].value_size = m_Parms[x].collen + 1;
break;
}
}
// Define buffers to store cell values, they will be filled by OCIStmtFetch2
for (int x = 0; x < m_cols; x++)
{
if ((rc = OCIDefineByPos(m_stmhp, &m_Parms[x].defnpp, m_errhp, x + 1, m_Parms[x].pValue,
m_Parms[x].value_size, m_Parms[x].coltyp,
&m_Parms[x].indp, &m_Parms[x].rlenp, (ub2*)0, OCI_DEFAULT)) != OCI_SUCCESS)
{
// Can be either OCI_INVALID_HANDLE or OCI_ERROR
break;
}
}
//
ColHead.clear();
for (int i = 0; i < (int)m_cols; i++)
{
ColHead.emplace_back((char*)m_Parms[i].ColName);
}
rc = OCIStmtExecute(m_svchp, m_stmhp, m_errhp, 1, 0,
(OCISnapshot*)0, (OCISnapshot*)0, OCI_STMT_SCROLLABLE_READONLY);
//
m_rows = 0;
ColVal.clear();
rc = OCIStmtFetch(m_stmhp, m_errhp, 1, OCI_FETCH_CURRENT, OCI_DEFAULT);
while (OCI_SUCCESS == rc)
{
std::vector<CString>temp;
for (int i = 0; i < (int)m_cols; i++)
{
temp.emplace_back((char*)m_Parms[i].pValue);
}
ColVal.emplace_back(temp);
m_rows++;
rc = OCIStmtFetch(m_stmhp, m_errhp, 1, OCI_FETCH_NEXT, OCI_DEFAULT);
}
if (rc != OCI_ERROR) {
rc = OCI_SUCCESS;
}
return rc;
}
void COCIHelper::ErrorProc(dvoid* err, sword status)
{
CString str;
sb4 errcode;
text errbuf[512];
switch (status)
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
(void)printf("Error - OCI_SUCCESS_WITH_INFO\n");
break;
case OCI_NEED_DATA:
(void)printf("Error - OCI_NEED_DATA\n");
break;
case OCI_NO_DATA:
(void)printf("Error - OCI_NODATA\n");
break;
case OCI_ERROR:
(void)OCIErrorGet((dvoid*)m_errhp, (ub4)1, (text*)NULL, &errcode,
errbuf, (ub4)sizeof(errbuf), OCI_HTYPE_ERROR);
(void)printf("Error - %.*s\n", 512, errbuf);
str.Format(_T("error:%d\nerror info:%s\n"), errcode, CString(errbuf));
AfxMessageBox(str);
break;
case OCI_INVALID_HANDLE:
(void)printf("Error - OCI_INVALID_HANDLE\n");
break;
case OCI_STILL_EXECUTING:
(void)printf("Error - OCI_STILL_EXECUTE\n");
break;
case OCI_CONTINUE:
(void)printf("Error - OCI_CONTINUE\n");
break;
default:
break;
}
}
int COCIHelper::query(CString strSQL, bool select)
{
sword rc = OCI_ERROR;
char* sql_select = CStrToChar(strSQL);
ErrorProc(m_errhp, rc = OCIStmtPrepare(m_stmhp, m_errhp, (text*)sql_select, (ub4)strlen(sql_select) + 1,
OCI_NTV_SYNTAX, OCI_DEFAULT));
if (select) {
ErrorProc(m_errhp, rc = OCIStmtExecute(m_svchp, m_stmhp, m_errhp, (ub4)0, (ub4)0,
(OCISnapshot*)0, (OCISnapshot*)0, OCI_DESCRIBE_ONLY));
}
else {
ErrorProc(m_errhp, rc = OCIStmtExecute(m_svchp, m_stmhp, m_errhp, (ub4)0, (ub4)0,
(OCISnapshot*)0, (OCISnapshot*)0, OCI_DEFAULT));
}
delete sql_select;
return rc;
}
int COCIHelper::query(CString strSQL, OHDataTable* dataTable)
{
sword rc = OCI_ERROR;
if (isConnect()) {
if (OCI_SUCCESS == (rc = query(strSQL))) {
if (OCI_SUCCESS == (rc = fetch()))
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
}
return rc;
}
int COCIHelper::executeSelect(CString strTable, CString strField, CString strValue, OHDataTable* dataTable)
{
sword status = OCI_ERROR;
try
{
CString strSQL;
strSQL.Format(_T("select * from %s where %s='%s'"), strTable, strField, strValue);
if (OCI_SUCCESS == (status = query(strSQL)))
{
if (OCI_SUCCESS == (status = fetch()))
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
}
catch (CException& ex)
{
}
return status;
}
int COCIHelper::executeSelectBind(CString strTable, CString strField, CString strValue, OHDataTable* dataTable)
{
/*
目标数据库 变量名规则
Oracle :Parameter
Sql Server @Parameter
OleDb.net ?
*/
sword status = OCI_ERROR;
try
{
OCIBind* bidhp;
text* txtSQL;
CString strSQL;
strSQL.Format(_T("select * from %s where %s=:aa"), strTable, strField);
txtSQL = (text*)CStrToChar(strSQL);
if (OCI_SUCCESS != (status = OCIStmtPrepare(m_stmhp, m_errhp, txtSQL,
strlen((char*)txtSQL), OCI_NTV_SYNTAX, OCI_DEFAULT)))
{
ErrorProc(m_errhp, status);
delete txtSQL;
return status;
}
delete txtSQL;
//绑定变量1
//if (OCI_SUCCESS != (status = OCIBindByPos(m_stmhp, &bidhp, m_errhp, 1,
// (dvoid*)strValue.GetString(), sizeof(strValue), SQLT_STR, (dvoid*)0, (ub2*)0, (ub2)0, (ub4)0, (ub4*)0, OCI_DEFAULT)))
//{
// ErrorProc(m_errhp, status);
// return status;
//}
//绑定变量2
if (OCI_SUCCESS != (status = OCIBindByName(m_stmhp, &bidhp, m_errhp, (text*)":aa", strlen(":aa"),
(dvoid*)strValue.GetString(), PLStrLen(strValue) + 1, SQLT_STR, 0, 0, 0, 0, 0, OCI_DEFAULT)))
{
ErrorProc(m_errhp, status);
return status;
}
if (OCI_SUCCESS != (status = OCIStmtExecute(m_svchp, m_stmhp, m_errhp, (ub4)0, 0, NULL, NULL, OCI_DEFAULT)))
{
ErrorProc(m_errhp, status);
return status;
}
if (OCI_SUCCESS == fetch())
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
catch (CException& ex)
{
}
return status;
}
int COCIHelper::executeSelectBind(CString strTable, OHMapFieldAndValue& fieldAndValueWhere, OHDataTable* dataTable)
{
/*
目标数据库 变量名规则
Oracle :Parameter
Sql Server @Parameter
OleDb.net ?
*/
sword status = OCI_ERROR;
try
{
//1.
std::vector<char*> pp;
OCIBind** bidhp = (OCIBind**)malloc(fieldAndValueWhere.GetCount() * sizeof(OCIBind*));
CString strSQL;
CString s1;
POSITION po = fieldAndValueWhere.GetStartPosition();
int num = 0;
while (po)
{
CString _key, _val;
fieldAndValueWhere.GetNextAssoc(po, _key, _val);
s1.Format(_T("%s and %s=:aa%d"), s1, _key, num);
num++;
}
strSQL.Format(_T("select * from %s where %s"), strTable, s1.Mid(5));
char* txtSQL = CStrToChar(strSQL);
if (OCI_SUCCESS != (status = OCIStmtPrepare(m_stmhp, m_errhp, (text*)txtSQL,
strlen((char*)txtSQL), OCI_NTV_SYNTAX, OCI_DEFAULT)))
{
}
pp.push_back(txtSQL);
//2.
if (OCI_SUCCESS == status)
{
//绑定变量1
po = fieldAndValueWhere.GetStartPosition();
num = 0;
while (po)
{
CString _key, _val;
fieldAndValueWhere.GetNextAssoc(po, _key, _val);
char* val = CStrToChar(_val);
if (OCI_SUCCESS != (status = OCIBindByPos(m_stmhp, &bidhp[num], m_errhp, num + 1,
(dvoid*)val, PLStrLen(_val) + 1, SQLT_STR, (dvoid*)0, (ub2*)0, (ub2)0, (ub4)0, (ub4*)0, OCI_DEFAULT)))
{
ErrorProc(m_errhp, status);
break;
}
pp.push_back(val);
num++;
}
}
//3.
if (OCI_SUCCESS == status)
{
ub2 stmt_type;
status = OCIAttrGet(m_stmhp, (ub4)OCI_HTYPE_STMT, (dvoid*)&stmt_type, (ub4*)0, (ub4)OCI_ATTR_STMT_TYPE, m_errhp);
if (OCI_SUCCESS == (status = OCIStmtExecute(m_svchp, m_stmhp, m_errhp, (ub4)0, 0, NULL, NULL, OCI_DEFAULT)))
{
if (OCI_SUCCESS == (status = fetch()))
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
}
//4.
for (int i = 0; i < (int)pp.size(); i++)
{
char* a1 = pp.at(i);
delete a1;
}
std::vector<char*> pp1;
pp.swap(pp1);
pp.clear();
free(bidhp);
ErrorProc(m_errhp, status);
return status;
}
catch (CException& ex)
{
}
return status;
}
int COCIHelper::executeSelect(CString strTable, OHMapFieldAndValue& fieldAndValueWhere, OHDataTable* dataTable)
{
sword status = OCI_ERROR;
if (fieldAndValueWhere.GetCount() > 0)
{
CString strSQL;
CString s1;
POSITION po = fieldAndValueWhere.GetStartPosition();
while (po)
{
CString _key, _val;
fieldAndValueWhere.GetNextAssoc(po, _key, _val);
s1.Format(_T("%s and %s='%s'"), s1, _key, _val);
}
strSQL.Format(_T("select * from %s where %s"), strTable, s1.Mid(5));
if (OCI_SUCCESS == (status = query(strSQL)))
{
if (OCI_SUCCESS == (status = fetch()))
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
}
return status;
}
int COCIHelper::executeSelect(CString strTable, CStringArray& field, OHMapFieldAndValue& fieldAndValueWhere, OHDataTable* dataTable)
{
sword status = OCI_ERROR;
if (field.GetCount() > 0 && fieldAndValueWhere.GetCount() > 0)
{
CString strSQL;
CString s1, s2;
POSITION po = fieldAndValueWhere.GetStartPosition();
while (po)
{
CString _key, _val;
fieldAndValueWhere.GetNextAssoc(po, _key, _val);
s1.Format(_T("%s and %s='%s'"), s1, _key, _val);
}
for (int i = 0; i < field.GetCount(); i++)
{
s2.Format(_T("%s,%s"), s2, field.GetAt(i));
}
strSQL.Format(_T("select %s from %s where %s"), s2.Mid(1), strTable, s1.Mid(5));
//
if (OCI_SUCCESS == (status = query(strSQL)))
{
if (OCI_SUCCESS == (status = fetch()))
{
dataTable->head = ColHead;
dataTable->cells = ColVal;
}
}
}
return status;
}
int COCIHelper::executeInsert(CString strTable, OHMapFieldAndValue& fieldAndValue)
{
sword status = OCI_ERROR;
if (fieldAndValue.GetCount() > 0)
{
CString strSQL;
CString s1, s2;
POSITION po = fieldAndValue.GetStartPosition();
while (po)
{
CString _key, _val;
fieldAndValue.GetNextAssoc(po, _key, _val);
s1.Format(_T("%s,%s"), s1, _key);
s2.Format(_T("%s,'%s'"), s2, _val);
}
strSQL.Format(_T("insert into %s (%s) values (%s)"), strTable, s1.Mid(1), s2.Mid(1));
//
if (OCI_SUCCESS == (status = query(strSQL, false)))
{
}
}
return status;
}
int COCIHelper::executeUpdate(CString strTable, CString strField, CString strValue)
{
sword status = OCI_ERROR;
try
{
CString strSQL;
strSQL.Format(_T("update %s set %s='%s'"), strTable, strField, strValue);
if (OCI_SUCCESS == (status = query(strSQL)))
{
}
}
catch (CException& ex)
{
}
return status;
}
int COCIHelper::executeUpdate(CString strTable, OHMapFieldAndValue& fieldAndValueSet)
{
sword status = OCI_ERROR;
if (fieldAndValueSet.GetCount() > 0)
{
CString strSQL;
CString s1;
POSITION po = fieldAndValueSet.GetStartPosition();
while (po)
{
CString _key, _val;
fieldAndValueSet.GetNextAssoc(po, _key, _val);
s1.Format(_T("%s and %s='%s'"), s1, _key, _val);
}
strSQL.Format(_T("update %s set %s"), strTable, s1.Mid(5));
//
if (OCI_SUCCESS == (status = query(strSQL)))
{
}
}
return status;
}
int COCIHelper::executeUpdate(CString strTable, OHMapFieldAndValue& fieldAndValueSet, OHMapFieldAndValue& fieldAndValueWhere)
{
sword status = OCI_ERROR;
if (fieldAndValueSet.GetCount() > 0 && fieldAndValueWhere.GetCount() > 0)
{
CString strSQL;
CString s1, s2;
POSITION po = fieldAndValueSet.GetStartPosition();
while (po)
{
CString _key, _val;
fieldAndValueSet.GetNextAssoc(po, _key, _val);
s1.Format(_T("%s and %s='%s'"), s1, _key, _val);
}
POSITION po2 = fieldAndValueWhere.GetStartPosition();
while (po2)
{
CString _key, _val;
fieldAndValueWhere.GetNextAssoc(po, _key, _val);
s2.Format(_T("%s and %s='%s'"), s2, _key, _val);
}
strSQL.Format(_T("update %s set %s where %s"), strTable, s1.Mid(5), s2.Mid(5));
//
if (OCI_SUCCESS == (status = query(strSQL)))
{
}
}
return status;
}
int COCIHelper::executeDelete(CString strTable, CString strField, CString strValue)
{
sword status = OCI_ERROR;
try
{
CString strSQL;
strSQL.Format(_T("delete from %s where %s='%s'"), strTable, strField, strValue);
if (OCI_SUCCESS == (status = query(strSQL)))
{
}
}
catch (CException& ex)
{
}
return status;
}
int COCIHelper::executeDelete(CString strTable, OHMapFieldAndValue& fieldAndValueWhere)
{
sword status = OCI_ERROR;
if (fieldAndValueWhere.GetCount() > 0)
{
CString strSQL;
CString s1;
POSITION po = fieldAndValueWhere.GetStartPosition();
while (po)
{
CString _key, _val;
fieldAndValueWhere.GetNextAssoc(po, _key, _val);
s1.Format(_T("%s and %s='%s'"), s1, _key, _val);
}
strSQL.Format(_T("delete from %s where %s"), strTable, s1.Mid(5));
//
if (OCI_SUCCESS == (status = query(strSQL)))
{
}
}
return status;
}
char* COCIHelper::CStrToChar(CString str)
{
#ifdef _UNICODE
int n = str.GetLength(); //按字符计算,str的长度
int len = WideCharToMultiByte(CP_ACP, 0, str, n, NULL, 0, NULL, NULL); //按Byte计算str长度
char* pChStr = new char[len + 1]; //按字节为单位
WideCharToMultiByte(CP_ACP, 0, str, n, pChStr, len, NULL, NULL); //宽字节转换为多字节编码
pChStr[len] = '\0'; //不要忽略末尾结束标志
//用完了记得delete []pChStr,防止内存泄露
return pChStr;
#else
return str.GetString();
#endif // _UNICODE
}
如有疑问加微信:q547414230