MySQL篇
写一套OA系统，为了方便进行数据库操作，特意抽出一周的时间来研究C/C++访问各种数据库的方法，并打算封装一套数据库操作类，现在奉上最简单的一部分：在Linux下访问MySQL数据库。
本文中使用的MySQL API代码为C语言，如果各位对C++有兴趣，可以考虑使用mysql++。
一、配置开发环境
首先需要编译、安装MySQL，安装完成后，将MySQL目录中的lib目录添加到环境变量中。
新建C/C++工程，把$MYSQL_ROOT/include添加到编译环境的包含路径下面。在编译选项中，增加$MYSQL_ROOT/lib目录。在Link选项中增加-lmysqlclient（已经把lib目录增加到系统环境变量中），或者直接引用libmysqlclient.so文件。
二、程序代码
不多说了，直接上代码，注释都很详细。
/*
* MySQLManager.h
*
*Created on: Feb 18, 2009
*Author: Steven Wee
*/
#ifndef MYSQLMANAGER_H_
#define MYSQLMANAGER_H_
#include "../Common/CheckStringTools.h"
#include <mysql.h>
#include <string>
#include <iostream>
#include <vector>
#include <string.h>
using namespace std;
class MySQLManager
{
public:
/*
* Init MySQL
* @param hosts: Host IP address
* @param userName:Login UserName
* @param password:Login Password
* @param dbName:Database Name
* @param port:Host listen port number
*/
MySQLManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port);
~MySQLManager();
void initConnection();
/*
* Making query from database
* @param mysql:MySQL Object
* @param sql:Running SQL command
*/
bool runSQLCommand(std::string sql);
/**
* Destroy MySQL object
* @param mysqlMySQL object
*/
void destroyConnection();
bool getConnectionStatus();
vector< vector<string> > getResult();
protected:
void setUserName(std::string userName);
void setHosts(std::string hosts);
void setPassword(std::string password);
void setDBName(std::string dbName);
void setPort(unsigned int port);
private:
bool IsConnected;
vector< vector<string> > resultList;
MYSQL mySQLClient;
unsigned int DEFAULTPORT;
char * HOSTS;
char * USERNAME;
char * PASSWORD;
char * DBNAME;
};
#endif /* MYSQLMANAGER_H_ */
/*
* MySQLManager.cpp
*
*Created on: Feb 18, 2009
*Author: Steven Wee
*/
#include "MySQLManager.h"
MySQLManager::MySQLManager(string hosts, string userName, string password, string dbName, unsigned int port)
{
IsConnected = false;
this ->setHosts(hosts);//设置主机IP地址
this ->setUserName(userName);//设置登录用户名
this ->setPassword(password);//设置登录密码
this ->setDBName(dbName);//设置数据库名
this ->setPort(port);//设置端口号
}
MySQLManager::~MySQLManager()
{
this ->destroyConnection();
}
void MySQLManager::setDBName(string dbName)
{
if ( dbName.empty() )
{//用户没有指定数据库名
std::cout << "DBName is null! Used default value: mysql" << std::endl;
this ->DBNAME = new char[5];
strcpy(this ->DBNAME, "mysql");
}
else
{
this ->DBNAME = new char[dbName.length()];
strcpy(this ->DBNAME, dbName.c_str());
}
}
void MySQLManager::setHosts(string hosts)
{
if ( hosts.empty() )
{//用户没有指定数据库IP地址
std::cout << "Hosts is null! Used default value: localhost" << std::endl;
this ->HOSTS = new char[9];
strcpy(this ->HOSTS, "localhost");
}
else
{
this ->HOSTS = new char[hosts.length()];
strcpy(this ->HOSTS, hosts.c_str());
}
}
void MySQLManager::setPassword(string password)
{//用户没有指定密码
if ( password.empty() )
{
std::cout << "Password is null! Used default value: " << std::endl;
this ->PASSWORD = new char[1];
strcpy(this ->PASSWORD, "");
}
else
{
this ->PASSWORD = new char[password.length()];
strcpy(this ->PASSWORD, password.c_str());
}
}
void MySQLManager::setPort(unsigned int port)
{//用户没有指定端口号，使用默认端口号
if ( port )
{
std::cout << "Port number is null! Used default value: 0" << std::endl;
this ->DEFAULTPORT = 0;
}
else
{
this ->DEFAULTPORT = port;
}
}
void MySQLManager::setUserName(string userName)
{//用户没有指定登录用户名
if ( userName.empty() )
{
std::cout << "UserName is null! Used default value: root" << std::endl;
this ->USERNAME = new char[4];
strcpy(this ->USERNAME, "root");
}
else
{
this ->USERNAME = new char[userName.length()];
strcpy(this ->USERNAME, userName.c_str());
}
}
void MySQLManager::initConnection()
{
if ( IsConnected )
{//已经连接到服务器
std::cout << "Is connected to server!" <<std::endl;
return;
}
mysql_init(&mySQLClient);//初始化相关对象
if ( !mysql_real_connect( &mySQLClient, HOSTS, USERNAME, PASSWORD, DBNAME, DEFAULTPORT, NULL, 0) )
{//连接到服务器
std::cout << "Error connection to database: %s\n" << mysql_error(&mySQLClient) << std::endl;
}
IsConnected = true;//修改连接标识
}
bool MySQLManager::runSQLCommand(string sql)
{
if ( !IsConnected )
{//没有连接到服务器
std::cout << "Not connect to database!" << std::endl;
return false;
}
if ( sql.empty() )
{//SQL语句为空
std::cout << "SQL is null!" << std::endl;
return false;
}
MYSQL_RES *res;
MYSQL_ROW row;
unsigned int i,j = 0;
StringTools stringTools;
sql = stringTools.filterString(sql);
i = mysql_real_query(&mySQLClient,sql.c_str(),(unsigned int)strlen(sql.c_str()));//执行查询
if ( i )
{
std::cout << "Error query from database: %s\n" << mysql_error(&mySQLClient) << std::endl;
return false;
}
res = mysql_store_result(&mySQLClient);
vector<string> objectValue;
while( (row = mysql_fetch_row(res)) )
{//遍历结果集
objectValue.clear();
for ( j = 0 ; j < mysql_num_fields(res) ; j++ )
{
objectValue.push_back(row[j]);
}
this ->resultList.push_back(objectValue);
}
mysql_free_result(res); //free result after you get the result
return true;
}
vector< vector<string> > MySQLManager::getResult()
{
return resultList;
}
void MySQLManager::destroyConnection()
{
mysql_close(&mySQLClient);
this ->IsConnected = false;
}
bool MySQLManager::getConnectionStatus()
{
return IsConnected;
}
三、修改建议
本人在以后的完善中，打算把runSQLCommand(char * sql)函数分解成两个或者三个函数，分别执行select和insert等语句。
在程序中，我并没有强制要求参数必须为const，可能会出现一些安全问题。
本文仅起抛砖引玉的作用，希望有高手可以指点我程序中的问题。
SQL Server篇
在Linux下连接MSSQL是一件很痛苦的事，因为微软同志没有提供任何接口给开发人员，大约他们认为要用MSSQL的，只可能是windows的操作系统。还好，MSSQL是从Sybase衍生出来的，有一些哥们做了一些Sybase的Linux下的连接库，这些连接库同时也能支持MSSQL，FreeTDS就是这样的一个东东。
这篇文章的受用读者，我想是那些希望在Linux或Unix下编写C或C++程序来连接MSSQL2000的兄弟们，因为我就是这样的。同时，那些写PHP的哥们也可以参考一下，当然如果你是用PHP的，你恐怕还要知道APACHE以及PHP脚本的安装，或者关于PHP自定义Module的开发，可以参考我以前写的一篇Blog（PHP5自定义Module开发）。
下面开始我们的探索之旅：
一、相关软件
首先我们需要FreeTDS的安装包
现在的最新版是0.82
其次就是大家需要自己搭建C++的开发环境了。
二、软件安装、配置
# tar zxvf freetds-stable.tgz（解压）
# ./configure --prefix=/usr/local/freetds \（指定FreeTDS安装路径）
--with-tdsver=8.0 --enable-msdblib （设置TDS版本，支持SQL Server 2000）
# make
# make install
将freetds的库文件所在路径配置到LD_LIBRARY_PATH参数中：
$ export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/freetds/lib/:
这么作的目的是为了避免加载FreeTds库文件加载不上的情况。
三、程序开发
不多说了，还是直接上代码：
/*
* SyBaseManager.h
*
*Created .: Feb 18, 2009
*Author: Steven Wee
*/
#ifndef SYBASEMANAGER_H_
#define SYBASEMANAGER_H_
#include "../Common/CheckStringTools.h"
#include <string>
#include <vector>
#include <iostream>
#include <assert.h>
#include <errno.h>
#include <stdlib.h>
#include <string.h>
#include <sybfront.h>
#include <sybdb.h>
using namespace std;
class SybaseManager
{
public:
SybaseManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port);
~SybaseManager();
/*
* Init SQL Server
* @param hosts: Host IP address
* @param userName:Login UserName
* @param password:Login Password
* @param dbName:Database Name
* @param port:Host listen port number
*/
void initConnection();
/*
* Making query from database
* @param mysql:MySQL Object
* @param sql:Running SQL command
*/
bool runSQLCommand(std::string sql);
/**
* Destroy MySQL object
* @param mysqlMySQL object
*/
void destroyConnection();
bool getConnectionStatus();
vector<vector<string> > getResult();
protected:
void setUserName(std::string userName);
void setHosts(std::string hosts);
void setPassword(std::string password);
void setDBName(std::string dbName);
void setPort(unsigned int port);
private:
bool IsConnected;
DBPROCESS *dbProcess;
vector< vector<string> > resultList;
unsigned int DEFAULTPORT;
char * HOSTS;
char * USERNAME;
char * PASSWORD;
char * DBNAME;
};
#endif /* SYBASEMANAGER_H_ */
/*
* SyBaseManager.cpp
*
*Created .: Feb 18, 2009
*Author: Steven Wee
*/
#include "SybaseManager.h"
SybaseManager::SybaseManager(std::string hosts, std::string userName, std::string password, std::string dbName, unsigned int port)
{
IsConnected = false;
this ->setHosts(hosts);
this ->setUserName(userName);
this ->setPassword(password);
this ->setDBName(dbName);
this ->setPort(port);
}
SybaseManager::~SybaseManager()
{
destroyConnection();
}
void SybaseManager::setDBName(string dbName)
{
if ( dbName.empty() )
{
std::cout << "DBName is null! Used default value: master" << std::endl;
this ->DBNAME = new char[5];
strcpy(this ->DBNAME, "master");
}
else
{
this ->DBNAME = new char[dbName.length()];
strcpy(this ->DBNAME, dbName.c_str());
}
}
void SybaseManager::setHosts(string hosts)
{
if ( hosts.empty() )
{
std::cout << "Hosts is null! Used default value: localhost" << std::endl;
this ->HOSTS = new char[9];
strcpy(this ->HOSTS, "localhost");
}
else
{
this ->HOSTS = new char[hosts.length()];
strcpy(this ->HOSTS, hosts.c_str());
}
}
void SybaseManager::setPassword(string password)
{
if ( password.empty() )
{
std::cout << "Password is null! Used default value: " << std::endl;
this ->PASSWORD = new char[1];
strcpy(this ->PASSWORD, "");
}
else
{
this ->PASSWORD = new char[password.length()];
strcpy(this ->PASSWORD, password.c_str());
}
}
void SybaseManager::setPort(unsigned int port)
{
if ( port )
{
std::cout << "Port number is null! Used default value: 0" << std::endl;
this ->DEFAULTPORT = 0;
}
else
{
this ->DEFAULTPORT = port;
}
}
void SybaseManager::setUserName(string userName)
{
if ( userName.empty() )
{
std::cout << "UserName is null! Used default value: sa" << std::endl;
this ->USERNAME = new char[4];
strcpy(this ->USERNAME, "sa");
}
else
{
this ->USERNAME = new char[userName.length()];
strcpy(this ->USERNAME, userName.c_str());
}
}
void SybaseManager::initConnection()
{
string Charset = "UTF-8";
dbinit();
LOGINREC *loginREC = dblogin();
DBSETLUSER(loginREC, this ->USERNAME);
DBSETLPWD(loginREC, this ->PASSWORD);
DBSETLCHARSET(loginREC, Charset.c_str());
dbProcess = dbopen(loginREC, this ->HOSTS);
if ( dbProcess == FAIL )
{
std::cout << "Connect to SQL Server failed!" << std::endl;
}
if ( dbuse( dbProcess, this ->DBNAME ) == FAIL )
{
std::cout << "Use table failed!" << std::endl;
}
}
bool SybaseManager::runSQLCommand( string sql )
{
dbcmd(dbProcess, sql.c_str());
if ( dbsqlexec(dbProcess) == FAIL )
{
std::cout << "Query from database failed!" << std::endl;
}
DBINT result_code;
vector<string> objectValue;
StringTools stringTools;
sql = stringTools.filterString(sql);
while ( (result_code = dbresults(dbProcess)) != NO_MORE_RESULTS )
{
struct Column
{
char* colName;
char* colBuffer;
int colType, colSize, colStatus;
} *columns, *pCol;
int nColumns;
int rowNo;
if ( result_code == SUCCEED )
{
nColumns = dbnumcols(dbProcess);
if ( (columns = (Column*)calloc(nColumns, sizeof(struct Column))) == NULL )
{
std::cout << "Error at bind data" << std::endl;
return false;
}
for ( pCol = columns; pCol - columns < nColumns; pCol++ )
{
int colNo = pCol - columns + 1;
pCol ->colName = dbcolname(dbProcess, colNo);
pCol ->colType = dbcoltype(dbProcess, colNo);
pCol ->colSize = dbcollen(dbProcess, colNo);
if ( SYBCHAR != pCol ->colType )
{
pCol ->colSize = dbwillconvert(pCol ->colType, SYBCHAR);
}
if ( (pCol ->colBuffer = (char*)calloc(1, pCol ->colSize + 1)) == NULL )
{
std::cout << "Check column buffer error!" << std::endl;
return false;
}
if ( dbbind(dbProcess, colNo, STRINGBIND, pCol ->colSize + 1, (BYTE*)pCol ->colBuffer) == FAIL )
{
std::cout << "Running dbbind() error!" << std::endl;
return false;
}
if ( dbnullbind(dbProcess, colNo, &pCol ->colStatus) == FAIL )
{
std::cout << "Running dbnullbind() error!" << std::endl;
return false;
}
}
while ( (rowNo = dbnextrow(dbProcess)) != NO_MORE_ROWS )
{
objectValue.clear();
switch ( rowNo )
{
case REG_ROW:
for ( pCol = columns; pCol - columns < nColumns; pCol++ )
{
const char* columnBuffer = pCol ->colStatus == -1 ? "NULL" : pCol ->colBuffer;
objectValue.push_back(stringTools.Trim(columnBuffer));//std::cout << columnBuffer << std::endl;
}
break;
case BUF_FULL:
assert( rowNo != BUF_FULL );
break;
case FAIL:
std::cout << "Get result error!" << std::endl;
break;
default:
std::cout << "Get result ignore, row number:" << rowNo << std::endl;
}
this ->resultList.push_back(objectValue);
}
for ( pCol = columns; pCol - columns < nColumns; pCol++ )
{
free( pCol ->colBuffer );
}
free( columns );
/*
if ( DBCOUNT(dbProcess) > -1 )
{
std::cout << "Affected rows:" << DBCOUNT(dbProcess) << std::endl;
}
*/
if ( dbhasretstat(dbProcess) == TRUE )
{
std::cout << "Procedure returned " << dbhasretstat(dbProcess) << std::endl;
}
}
}
return true;
}
void SybaseManager::destroyConnection()
{
dbclose(dbProcess);
}
bool SybaseManager::getConnectionStatus()
{
return IsConnected;
}
vector< vector<string> > SybaseManager::getResult()
{
return this ->resultList;
}
四、修改建议
本人在以后的完善中，打算把runSQLCommand(char * sql)函数分解成两个或者三个函数，分别执行select和insert等语句。
在程序中，我并没有强制要求参数必须为const，可能会出现一些安全问题。
本文仅起抛砖引玉的作用，希望有高手可以指点我程序中的问题。
Oracle之OCI篇
一、什么是OCI？
开发基于Oracle数据库的应用程序，我们可以选择多种工具，不仅可以用一般的数据库开发技术，诸如ADO（ActiveX Data Objects）、ODBC（Open DataBase Connectivity）等等，同时，也可以用Oracle公司提供的专门的开发工具，诸如Pro C_C++，OCI（Oracle Call Intedace）等等。比较这几种方式，前者因为是通用技术，开发起来比较容易，但是有一个致命的弱点就是诸如ADO之类的通用技术的速度太慢，如果我们要开发管理海量数据的数据库，比如影像数据库，那么，这种速度我们是不能忍受的。而OCI虽然开发起来难度大一些，但是它的速度极快，而且是一种底层接口，几乎可以操纵Oracle数据库的任何对象。
二、OCI简介
OCI(Oracle Call Intedace，即0racle调用层接口)是Oracle公司提供的由头文件和库函数等组成的一个访问Oracle数据库的应用程序编程接口（application programming interfaceAPI），它允许开发人员在第三代编程语言（包括C, C++, COBOL 与 FORTRAN）中通过SQL（Structure Query Language）来操纵Oracle数据库，而且OCI在一定程度上支持第三代编程语言（诸如C, C++, COBOL 与 FORTRAN）的数据类型、语法等等。OCI的显著特点是全面支持Oracle的面向对象技术，同时OCI还具有如下的一些特点：
1)非常有利于应用程序的设计；
2)高度控制应用程序的执行；
3)允许开发人员应用已熟悉的第三代程序设计语言来应用OCI；
4)支持动态SQL；
5)几乎所有的Oracle的开发工具都支持OCI；
6)通过回调技术（callbacks）来实现动态绑定与定义；
7)通过OCI的描述函数可以获取Oracle数据库的各种参数；
8)增强了数组在DML(data manipulation language)语言中的应用；
OCI接口支持Windows NT和Windows 95／98/2000/XP操作系统，它所支持的C语言编译器包括Borland C++和MiroSoft VisualC++等。在使用0CI开发Oralce数据库应用程序之前，应首先安装这些操作系统和C语言编译工具。在选择安装OCI开发工具包后，Oracle安装程序将0CI文件拷贝到oracle主目录内的以下子目录中：
..BIN\：执行文件和帮助文件：
..\OCIINCLUDE头文件；
三、开发前的注意事项
首先，为了防止某些动态链接库出问题，建议在安装了Oracle客户端的机器上进行开发、运行。
其次，使用OCI开发的程序，需要使用Oracle客户端的tnsnames.ora这个配置文件，所以在开发前需要使用netca来配置好相关内容。第三，Linux下的系统环境变量需要设置好。需要设置的环境变量包括ORACLE_HOME、ORACLE_SID、TNS_ADMIN，其中TNS_ADMIN指定到tnsnames.ora所在的文件夹。
四、程序开发：
还是直接用代码说话吧
/*
* Common.h
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#ifndef COMMON_H_
#define COMMON_H_
#include <unistd.h>
#include <oci.h>
#include <ctype.h>
#include <string>
#include <iostream>
#include <vector>
#include <string.h>
using namespace std;
#endif /* COMMON_H_ */
/*
* Exception.h
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#ifndef EXCEPTION_H_
#define EXCEPTION_H_
class Exception
{
public:
Exception(int errno);
virtual char * GetErrMsg() = 0;
virtual char * GetErrFunc() = 0;
virtual int GetErrNo() = 0;
protected:
int m_iErrNo;
char m_sErrBuff[512];
char m_sErrFunc[128];
};
#endif /* EXCEPTION_H_ */
/*
* Exception.cpp
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#include "Exception.h"
Exception::Exception(int errno)
{
this ->m_iErrNo = errno;
this ->m_sErrBuff[0] = 0;
this ->m_sErrFunc[0] = 0;
}
/*
* OCIException.h
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#ifndef OCIEXCEPTION_H_
#define OCIEXCEPTION_H_
#include "Common.h"
#include "Exception.h"
class OCIException : public Exception
{
public:
OCIException(sb4 errno);
OCIException(sb4 errno, char * errfunc);
OCIException(sb4 errno, dvoid * erroci);
OCIException(sb4 errno, dvoid * erroci, char * errfunc);
char * GetErrMsg();
char * GetErrFunc();
int GetErrNo();
private:
void CheckError(sb4 errno);
void CheckError(sb4 errno, dvoid * erroci);
};
#endif /* OCIEXCEPTION_H_ */
/*
* OCIException.cpp
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#include "OCIException.h"
void OCIException::CheckError(sb4 errno)
{
text errBuff[512];
switch ( errno )
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
sprintf( this ->m_sErrBuff, "OCI_SUCCESS_WITH_INFO" );
break;
case OCI_NEED_DATA:
sprintf( this ->m_sErrBuff, "OCI_NEED_DATA" );
break;
case OCI_NO_DATA:
sprintf( this ->m_sErrBuff, "OCI_NO_DATA" );
break;
case OCI_ERROR:
sprintf( this ->m_sErrBuff, "OCI_ERROR" );
break;
case OCI_INVALID_HANDLE:
sprintf( this ->m_sErrBuff, "OCI_INVALID_HANDLE" );
break;
case OCI_STILL_EXECUTING:
sprintf( this ->m_sErrBuff, "OCI_STILL_EXCUTING" );
break;
case OCI_CONTINUE:
sprintf( this ->m_sErrBuff, "OCI_CONTINUE" );
break;
default:
break;
}
}
void OCIException::CheckError(sb4 errno, dvoid * erroci)
{
text errBuff[512];
switch ( errno )
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
sprintf( this ->m_sErrBuff, "OCI_SUCCESS_WITH_INFO" );
break;
case OCI_NEED_DATA:
sprintf( this ->m_sErrBuff, "OCI_NEED_DATA" );
break;
case OCI_NO_DATA:
sprintf( this ->m_sErrBuff, "OCI_NO_DATA" );
break;
case OCI_ERROR:
OCIErrorGet( (dvoid*)erroci, (ub4)1, (text*)NULL, &this ->m_iErrNo, errBuff, (ub4)sizeof(errBuff), OCI_HTYPE_ERROR);
sprintf( this ->m_sErrBuff, "%.*s", strlen((char*) errBuff) - 1, errBuff);
break;
case OCI_INVALID_HANDLE:
sprintf( this ->m_sErrBuff, "OCI_INVALID_HANDLE" );
break;
case OCI_STILL_EXECUTING:
sprintf( this ->m_sErrBuff, "OCI_STILL_EXCUTING" );
break;
case OCI_CONTINUE:
sprintf( this ->m_sErrBuff, "OCI_CONTINUE" );
break;
default:
break;
}
}
OCIException::OCIException(sb4 errno) : Exception( (int)errno )
{
this ->CheckError(errno);
}
OCIException::OCIException(sb4 errno, char * errfunc) : Exception( (int)errno )
{
strcpy( this ->m_sErrBuff, errfunc);
this ->CheckError(errno);
}
OCIException::OCIException(sb4 errno, dvoid * erroci) : Exception( (int)errno )
{
this ->CheckError(errno, erroci);
}
OCIException::OCIException(sb4 errno, dvoid * erroci, char * errfunc) : Exception( (int)errno )
{
strcpy( this ->m_sErrBuff, errfunc);
this ->CheckError(errno, erroci);
}
char * OCIException::GetErrMsg()
{
return this ->m_sErrBuff;
}
char * OCIException::GetErrFunc()
{
return this ->m_sErrFunc;
}
int OCIException::GetErrNo()
{
return this ->m_iErrNo;
}
/*
* OCIError.h
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#ifndef OCIERROR_H_
#define OCIERROR_H_
#include "Common.h"
class OCIError
{
public:
static void PrintError(int errno);
static void PrintError(int errno, char * errfunc);
private:
static void CheckError();
static int m_iErrNo;
static char m_sErrBuff[512];
static char m_sErrFunc[128];
};
#endif /* OCIERROR_H_ */
/*
* OCIError.cpp
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#include "OCIError.h"
int OCIError::m_iErrNo = 0;
char OCIError::m_sErrBuff[512] = {0};
char OCIError::m_sErrFunc[128] = {0};
void OCIError::CheckError()
{
switch ( OCIError::m_iErrNo )
{
case OCI_SUCCESS:
break;
case OCI_SUCCESS_WITH_INFO:
sprintf( OCIError::m_sErrBuff, "OCI_SUCCESS_WITH_INFO" );
break;
case OCI_NEED_DATA:
sprintf( OCIError::m_sErrBuff, "OCI_NEED_DATA" );
break;
case OCI_NO_DATA:
sprintf( OCIError::m_sErrBuff, "OCI_NO_DATA" );
break;
case OCI_ERROR:
sprintf( OCIError::m_sErrBuff, "OCI_ERROR" );
break;
case OCI_INVALID_HANDLE:
sprintf( OCIError::m_sErrBuff, "OCI_INVALID_HANDLE" );
break;
case OCI_STILL_EXECUTING:
sprintf( OCIError::m_sErrBuff, "OCI_STILL_EXCUTING" );
break;
case OCI_CONTINUE:
sprintf( OCIError::m_sErrBuff, "OCI_CONTINUE" );
break;
default:
break;
}
}
void OCIError::PrintError(int errno)
{
OCIError::m_iErrNo = errno;
OCIError::CheckError();
std::cout << OCIError::m_sErrBuff << std::endl;
}
void OCIError::PrintError(int errno, char * errfunc )
{
OCIError::m_iErrNo = errno;
strcpy(OCIError::m_sErrFunc, errfunc);
OCIError::CheckError();
std::cout << OCIError::m_sErrFunc << OCIError::m_sErrBuff << std::endl;
}
/*
* OCIDB.h
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#ifndef OCIDB_H_
#define OCIDB_H_
#include "Common.h"
#include "../Common/CheckStringTools.h"
#define MAX_VAR_LEN 10
#define MAX_BUFFER_SIZE 32767
typedef struct {
ub2VarType;
char *VarName;
intVarLen;
union {
char * ValueChar;
float * ValueFloat;
int * ValueInt;
OCILobLocator *lobLocator;
};
} TBindVar;
typedef struct{
char VarName[MAX_VAR_LEN];
unsigned char VarType;
unsigned char VarSize;
unsigned char VarPrecision;
unsigned char VarScale;
unsigned char VarIsNull;
union {
char * ValueChar;
float * ValueFloat;
int * ValueInt;
OCILobLocator *lobLocator;
};
} TSelectVar;
#define MAX_BINDVAR_COUNT 10
#define MAX_SELECTVAR_COUNT 10
#define TYPE_INT 0
#define TYPE_FLOAT 1
#define TYPE_STRING 2
#define TYPE_BLOB 3
class OCIDB
{
private:
dvoid * indp;
OCIEnv * m_pOCIEnv;
OCIError * m_pOCIError;
OCISvcCtx * m_pOCISvcCtx;
OCIServer * m_pOCIServer;
OCISession * m_pOCISession;
OCIStmt * m_pOCIStmt;
OCIBind * m_pOCIBind;
OCIDefine * m_pOCIDefine;
OCIParam * m_pOCIParam;
TBindVar m_BindVars[MAX_BINDVAR_COUNT];
TSelectVar m_SelectVars[MAX_SELECTVAR_COUNT];
int m_iBindVarsCount;
int m_iSelectVarsCount;
char m_SUSEr[10];
char m_sPwd[10];
char m_sDBName[10];
int UserFetch();
int UserGetInt(int index);
int UserGetInt(char * name);
char * UserGetString(int index);
char * UserGetString(char * name);
char * UserGetBlob(int index);
char * UserGetBlob(char * name);
float UserGetFloat(int index);
float UserGetFloat(char * name);
void inline StrUpper(char *str);
bool inline StrCmp(const char *ori, const char *des);
vector< vector<string> > resultList;
protected:
char * getBlobValue();
public:
OCIDB();
~OCIDB();
/* Single User, Single Connection */
int Single_Conn();
void Single_Disc();
/* Multiple Sessions or Connections */
int Multiple_Conn();
void Multiple_Disc();
/* Execute SQL with none query */
int ExcuteSQL(char * sql);
/* Execute SQL with bind vars */
void BindInitVars();
void BindClearVars();
void BindAddVar(char * name, char * value);
void BindAddVar(char * name, int * value);
int BindSQL(char * sql);
/* Prepare SQL*/
int UserPrepare(char * sql);
int UserFree();
int UserBind(char * name, char * value);
int UserBind(char * name, int value);
int UserExecute();
int UserCommit();
int UserRollback();
int UserSelect(char * sql);
int UserSelectFree();
vector< vector<string> > getResult();
};
#endif /* OCIDB_H_ */
/*
* OCIDB.cpp
*
*Created .: Mar 1, 2009
*Author: Steven Wee
*/
#include "OCIDB.h"
#include "OCIException.h"
#include "OCIError.h"
OCIDB::OCIDB()
{
strcpy(this ->m_sUser, "webtest");
strcpy(this ->m_sPwd, "ns0528AO");
strcpy(this ->m_sDBName, "NSTEST");
this ->BindInitVars();
}
OCIDB::~OCIDB()
{
}
int OCIDB::Single_Conn()
{
try
{
sword errno;
/*
* OCIEnvCreate(&envhp, mode, (const dvoid *)0, 0, 0, 0, (size_t)0, (dvoid **)0 );
*/
errno = OCIEnvCreate( &this ->m_pOCIEnv, OCI_OBJECT, (dvoid *)0, (dvoid *(*)(dvoid *, size_t))0, (dvoid *(*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *))0, (size_t)0, (dvoid **)0);
if(errno)
{
throw OCIException(errno, (char *)"OCIDB::Single_Conn OCIEnvCreate");
}
/* Create Error Handle */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCIError, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
if(errno)
{
throw OCIException(errno, (char *)"OCIDB::Single_Conn OCIHandleAlloc");
}
/* Login */
errno = OCILogon(this ->m_pOCIEnv, this ->m_pOCIError, &this ->m_pOCISvcCtx, (const OraText*)this ->m_sUser,
strlen(this ->m_sUser), (const OraText*)this ->m_sPwd, strlen(this ->m_sPwd), (const OraText*)this ->m_sDBName,
strlen(this ->m_sDBName));
if(errno)
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::Single_Conn OCILogin2");
}
return 0;
}
catch ( OCIException &ex)
{
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
void OCIDB::Single_Disc()
{
sword errno;
/* Log off */
errno = OCILogoff(m_pOCISvcCtx, this ->m_pOCIError);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Single_Disc OCILogoff");
}
/* Free errno */
errno = OCIHandleFree((dvoid *)this ->m_pOCIError, (ub4)OCI_HTYPE_ERROR);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Single_Disc OCIHandleFree");
}
/* Free Env Handle */
errno = OCIHandleFree((dvoid *)this ->m_pOCIEnv, (ub4)OCI_HTYPE_ENV);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Single_Disc OCIHandleFree");
}
}
int OCIDB::Multiple_Conn()
{
try
{
sword errno;
errno = OCIInitialize( (ub4)OCI_DEFAULT, (dvoid *)0, (dvoid *(*)(dvoid *, size_t))0, (dvoid *(*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *))0 );
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIInitialize");
}
errno = OCIEnvInit( (OCIEnv **)&this ->m_pOCIEnv, OCI_DEFAULT, (size_t)0, (dvoid **)0);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIEnvInit");
}
/* Allocate Error Handle */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCIError, OCI_HTYPE_ERROR, (size_t)0, (dvoid **)0);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIHandleAlloc Error");
}
/* Allocate Server Context Handle */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCISvcCtx, OCI_HTYPE_SVCCTX, (size_t)0, (dvoid **)0);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIHandleAlloc SvcCtx");
}
/* Allocate Server Handle */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCIServer, OCI_HTYPE_SERVER, (size_t)0, (dvoid **)0);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIHandleAlloc Server");
}
/* Set Server into ServerContext */
errno = OCIAttrSet( (dvoid *)this ->m_pOCISvcCtx, OCI_HTYPE_SVCCTX, (dvoid *)this ->m_pOCIServer, 0, OCI_ATTR_SERVER, (OCIError *)this ->m_pOCIError);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIAttrSet Server");
}
/* Attach Server */
errno = OCIServerAttach(this ->m_pOCIServer, this ->m_pOCIError, (text *)this ->m_sDBName, strlen(this ->m_sDBName), OCI_DEFAULT);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIServerAttach");
}
/* Allocate Session Handle */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCISession, (ub4)OCI_HTYPE_SESSION, (size_t)0, (dvoid **)0);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIHandleAlloc Session");
}
/* Set Session into ServerContext */
errno = OCIAttrSet( (dvoid *)this ->m_pOCISvcCtx, (ub4)OCI_HTYPE_SVCCTX, (dvoid *)this ->m_pOCISession, (ub4)0, (ub4)OCI_ATTR_SESSION, (OCIError *)this ->m_pOCIError);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIAttrSet Session");
}
/* Set Session UserName */
errno = OCIAttrSet( (dvoid *)this ->m_pOCISession, OCI_HTYPE_SESSION, (dvoid *)this ->m_sUser, strlen(this ->m_sUser), OCI_ATTR_USERNAME, (OCIError *)this ->m_pOCIError);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIAttrSet UserName");
}
/* Set Session Password */
errno = OCIAttrSet( (dvoid *)this ->m_pOCISession, OCI_HTYPE_SESSION, (dvoid *)this ->m_sPwd, (ub4)strlen(this ->m_sPwd), (ub4)OCI_ATTR_PASSWORD, (OCIError *)this ->m_pOCIError);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::Multiple_Conn OCIAttrSet Password");
}
/* Session Begin */
errno = OCISessionBegin(this ->m_pOCISvcCtx, this ->m_pOCIError, this ->m_pOCISession, OCI_CRED_RDBMS, OCI_DEFAULT);
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::Multiple_Conn OCISessionBegin");
}
return 0;
}
catch (OCIException &ex)
{
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
void OCIDB::Multiple_Disc()
{
sword errno;
/* End Session */
errno = OCISessionEnd(this ->m_pOCISvcCtx, this ->m_pOCIError, this ->m_pOCISession, (ub4)OCI_DEFAULT);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Multiple_Disc OCISessionEnd");
}
/* Free Session */
errno = OCIHandleFree( (dvoid *)this ->m_pOCISession, (ub4)OCI_HTYPE_SESSION );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Multiple_Disc OCIHandleFree Session");
}
/* Detach Server */
errno = OCIServerDetach(this ->m_pOCIServer, this ->m_pOCIError, OCI_DEFAULT);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Multiple_Disc OCIServerDetach");
}
/* Free Server */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIServer, (ub4)OCI_HTYPE_SERVER );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Multiple_Disc OCIHandleFree Server");
}
/* Free ServerContext */
errno = OCIHandleFree( (dvoid *)this ->m_pOCISvcCtx, (ub4)OCI_HTYPE_SVCCTX );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Multiple_Disc OCIHandleFree ServerContext");
}
/* Free Error */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIError, (ub4)OCI_HTYPE_ERROR );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Multiple_Disc OCIHandleFree Error");
}
/* Free Env */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIEnv, (ub4)OCI_HTYPE_ENV );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::Multiple_Disc OCIHandleFree Env");
}
}
int OCIDB::ExcuteSQL(char * sql)
{
sword errno;
try
{
/* Allocate Stmt */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCIStmt, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::ExcuteSQL OCIHandleAlloc");
}
errno = OCIStmtPrepare(this ->m_pOCIStmt, this ->m_pOCIError, (const OraText *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::ExcuteSQL OCIStmtPrepare");
}
errno = OCIStmtExecute(this ->m_pOCISvcCtx, this ->m_pOCIStmt, this ->m_pOCIError, (ub4)1, (ub4)0, (const OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::ExcuteSQL OCIStmtExecute");
}
errno = OCITransCommit( this ->m_pOCISvcCtx, this ->m_pOCIError, 0);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::ExcuteSQL OCITransCommit");
}
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::ExcuteSQL OCIHandleFree");
}
return 0;
}
catch (OCIException &ex)
{
/* Rollback */
errno = OCITransRollback( this ->m_pOCISvcCtx, this ->m_pOCIError, 0);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::ExcuteSQL OCITransRollback");
}
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::ExcuteSQL OCIHandleFree");
}
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
void OCIDB::BindInitVars()
{
int i;
for( i = 0 ; i < MAX_BINDVAR_COUNT - 1 ; i++ )
{
this ->m_BindVars[i].VarType = 0;
this ->m_BindVars[i].VarName = 0;
this ->m_BindVars[i].VarLen = 0;
this ->m_BindVars[i].ValueChar = 0;
}
this ->m_iBindVarsCount = 0;
}
void OCIDB::BindClearVars()
{
int i;
for( i = 0 ; i < m_iBindVarsCount - 1 ; i++ )
{
delete [] this ->m_BindVars[i].VarName;
if ( this ->m_BindVars[i].VarType == SQLT_STR )
delete [] this ->m_BindVars[i].ValueChar;
if ( this ->m_BindVars[i].VarType == SQLT_INT )
delete [] this ->m_BindVars[i].ValueInt;
this ->m_BindVars[i].VarType = 0;
this ->m_BindVars[i].VarName = 0;
this ->m_BindVars[i].VarLen = 0;
this ->m_BindVars[i].ValueChar = 0;
}
this ->m_iBindVarsCount = 0;
}
void OCIDB::BindAddVar(char * name, char * value)
{
if ( this ->m_iBindVarsCount >= MAX_BINDVAR_COUNT )
return;
this ->m_BindVars[this ->m_iBindVarsCount].VarType = SQLT_STR;
this ->m_BindVars[this ->m_iBindVarsCount].VarName = new char[strlen(name) + 1];
strcpy( this ->m_BindVars[this ->m_iBindVarsCount].VarName, name);
this ->m_BindVars[this ->m_iBindVarsCount].ValueChar = new char[strlen(value) + 1];
strcpy( this ->m_BindVars[this ->m_iBindVarsCount].ValueChar, value);
this ->m_BindVars[this ->m_iBindVarsCount].VarLen = strlen(value) + 1;
this ->m_iBindVarsCount++;
}
void OCIDB::BindAddVar(char * name, int * value)
{
if ( this ->m_iBindVarsCount >= MAX_BINDVAR_COUNT )
return;
this ->m_BindVars[this ->m_iBindVarsCount].VarType = SQLT_INT;
this ->m_BindVars[this ->m_iBindVarsCount].VarName = new char[strlen(name) + 1];
strcpy( this ->m_BindVars[this ->m_iBindVarsCount].VarName, name);
this ->m_BindVars[this ->m_iBindVarsCount].ValueInt = new int;
this ->m_BindVars[this ->m_iBindVarsCount].ValueInt = value;
this ->m_BindVars[this ->m_iBindVarsCount].VarLen = sizeof(int);
this ->m_iBindVarsCount++;
}
int OCIDB::BindSQL(char * sql)
{
if( this ->m_iBindVarsCount == 0 )
return -1;
sword errno;
try
{
/* Allocate Stmt */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCIStmt, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0);
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::BindSQL OCIHandleAlloc Stmt");
}
/* Prepare Stmt */
errno = OCIStmtPrepare( this ->m_pOCIStmt, this ->m_pOCIError, (const OraText *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::BindSQL OCIStmtPrepare");
}
/* Bind Vars */
int i;
for ( i = 0 ; i < m_iBindVarsCount - 1 ; i++ )
{
/* Bind By Name */
errno = OCIBindByName( this ->m_pOCIStmt, &this ->m_pOCIBind, this ->m_pOCIError, (text *)this ->m_BindVars[i].VarName, -1,
(dvoid *)this ->m_BindVars[i].ValueChar, this ->m_BindVars[i].VarLen, this ->m_BindVars[i].VarType, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::BindSQL OCIBindByName");
}
/*
errno = OCIBindByPos( this ->m_pOCIStmt, &this ->m_pOCIBind, this ->m_pOCIError, i + 1, (dvoid *)this ->m_BindVars[i].ValueChar,
this ->m_BindVars[i].VarLen, this ->m_BindVars[i].VarType, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT);
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::BindSQL OCIBindByPos");
}
*/
}
/* Execute SQL */
errno = OCIStmtExecute( this ->m_pOCISvcCtx, this ->m_pOCIStmt, this ->m_pOCIError, (ub4)1, (ub4)0, (const OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT);
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::BindSQL OCIStmtExecute");
}
/*Commit */
errno = OCITransCommit( this ->m_pOCISvcCtx, this ->m_pOCIError, 0);
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::BindSQL OCITransCommit");
}
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::BindSQL OCIHandleFree");
}
/* Clear Vars */
this ->BindClearVars();
return 0;
}
catch ( OCIException &ex )
{
/* Rollback */
errno = OCITransRollback( this ->m_pOCISvcCtx, this ->m_pOCIError, 0);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::BindSQL OCITransRollback");
}
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::BindSQL OCIHandleFree");
}
this ->BindClearVars();
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
int OCIDB::UserPrepare(char * sql)
{
sword errno;
try
{
/* Allocate Stmt */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCIStmt, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0 );
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::UserPrepare OCIHandleAlloc");
}
/* Prepare Stmt */
errno = OCIStmtPrepare( this ->m_pOCIStmt, this ->m_pOCIError, (const OraText *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, (char *)"OCIDB::UserPrepare OCIStmtPrepare");
}
return 0;
}
catch ( OCIException &ex )
{
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::UserPrepare Ex OCIHandleFree");
}
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
int OCIDB::UserFree()
{
sword errno;
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::UserFree OCIHandleFree");
return -1;
}
return 0;
}
int OCIDB::UserBind(char * name, char * value)
{
sword errno;
try
{
errno = OCIBindByName( this ->m_pOCIStmt, &this ->m_pOCIBind, this ->m_pOCIError, (text *)name, -1,
(dvoid *)value, strlen(value) + 1, SQLT_STR, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserBind OCIBindByName");
}
return 0;
}
catch ( OCIException &ex )
{
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::UserBind Ex OCIHandleFree");
}
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
int OCIDB::UserBind(char * name, int value)
{
sword errno;
try
{
int val = value;
errno = OCIBindByName( this ->m_pOCIStmt, &this ->m_pOCIBind, this ->m_pOCIError, (text *)name, -1,
(dvoid *)&val, sizeof(int), SQLT_INT, (dvoid *)0, (ub2 *)0, (ub2 *)0, (ub4)0, (ub4 *)0, OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserBind OCIBindByName");
}
return 0;
}
catch ( OCIException &ex )
{
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::UserBind Ex OCIHandleFree");
}
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
int OCIDB::UserExecute()
{
sword errno;
try
{
errno = OCIStmtExecute( this ->m_pOCISvcCtx, this ->m_pOCIStmt, this ->m_pOCIError, (ub4)1, (ub4)0,
(const OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserExecute OCIBindByName");
}
return 0;
}
catch ( OCIException &ex )
{
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::UserExecute Ex OCIHandleFree");
}
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
int OCIDB::UserCommit()
{
sword errno;
errno = OCITransCommit( this ->m_pOCISvcCtx, this ->m_pOCIError, 0);
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::UserCommit OCITransCommit");
return -1;
}
return 0;
}
int OCIDB::UserSelect(char * sql)
{
sword errno;
try
{
/* Allocate Stmt */
errno = OCIHandleAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_pOCIStmt, OCI_HTYPE_STMT, (size_t)0, (dvoid **)0 );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIHandleAlloc");
}
/* Prepare Stmt */
errno = OCIStmtPrepare( this ->m_pOCIStmt, this ->m_pOCIError, (const OraText *)sql, (ub4)strlen(sql), (ub4)OCI_NTV_SYNTAX, (ub4)OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIStmtPrepare");
}
/* Execute Stmt */
errno = OCIStmtExecute( this ->m_pOCISvcCtx, this ->m_pOCIStmt, this ->m_pOCIError, (ub4)0, (ub4)0, (OCISnapshot *)NULL, (OCISnapshot *)NULL, OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIStmtExecute");
}
errno = OCIAttrGet( this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT, (dvoid *)&this ->m_iSelectVarsCount, (ub4 *)0, (ub4)OCI_ATTR_PARAM_COUNT, this ->m_pOCIError );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIAttrGet");
}
int iLen;
char * sName;
ub2 iSize, iPrecision, iType;
ub1 iScale, iIsNull;
int i;
for ( i = 0 ; i < this ->m_iSelectVarsCount ; i++ )
{
sName = 0;
errno = OCIParamGet( this ->m_pOCIStmt, OCI_HTYPE_STMT, this ->m_pOCIError, (dvoid **)&this ->m_pOCIParam, i+ 1 );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIParamGet");
}
//Column Name
errno = OCIAttrGet( (dvoid *)this ->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid **)&sName, (ub4 *)&iLen, OCI_ATTR_NAME, this ->m_pOCIError );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIAttrGet Column Name");
}
strncpy( this ->m_SelectVars[i].VarName, sName, iLen );
this ->m_SelectVars[i].VarName[iLen] = 0;
//Data Size;
errno = OCIAttrGet( (dvoid *)this ->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iSize, (ub4 *)0, OCI_ATTR_DATA_SIZE, this ->m_pOCIError );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIAttrGet Data Size");
}
this ->m_SelectVars[i].VarSize = iSize;
//Data Precision
errno = OCIAttrGet( (dvoid *)this ->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iPrecision, (ub4 *)0, OCI_ATTR_PRECISION, this ->m_pOCIError );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIAttrGet Data Precision");
}
this ->m_SelectVars[i].VarPrecision = iPrecision;
//Data Scale
errno = OCIAttrGet( (dvoid *)this ->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iScale, (ub4 *)0, OCI_ATTR_SCALE, this ->m_pOCIError );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIAttrGet Data Scale");
}
this ->m_SelectVars[i].VarScale = iScale;
//Data Is Null
errno = OCIAttrGet( (dvoid *)this ->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iIsNull, (ub4 *)0, OCI_ATTR_IS_NULL, this ->m_pOCIError );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIAttrGet Data Is Null");
}
this ->m_SelectVars[i].VarIsNull = iIsNull;
//Data Type
errno = OCIAttrGet( (dvoid *)this ->m_pOCIParam, OCI_DTYPE_PARAM, (dvoid *)&iType, (ub4 *)0, OCI_ATTR_DATA_TYPE, this ->m_pOCIError );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIAttrGet Data Type");
}
this ->m_SelectVars[i].VarType = iType;
switch ( this ->m_SelectVars[i].VarType )
{
case SQLT_NUM:
if ( this ->m_SelectVars[i].VarScale == 0 )
{
this ->m_SelectVars[i].ValueInt = new int;
this ->m_SelectVars[i].VarType = TYPE_INT;
errno = OCIDefineByPos( this ->m_pOCIStmt, &this ->m_pOCIDefine, this ->m_pOCIError, i + 1,
(dvoid *)this ->m_SelectVars[i].ValueInt,sizeof(int), SQLT_INT, this ->indp, (ub2 *)0, (ub2 *)0, OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIDefineByPos");
}
}
else
{
this ->m_SelectVars[i].ValueFloat = new float;
this ->m_SelectVars[i].VarType = TYPE_FLOAT;
errno = OCIDefineByPos( this ->m_pOCIStmt, &this ->m_pOCIDefine, this ->m_pOCIError, i + 1,
(dvoid *)this ->m_SelectVars[i].ValueFloat,sizeof(float), SQLT_FLT, this ->indp, (ub2 *)0, (ub2 *)0, OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIDefineByPos");
}
}
break;
case SQLT_CHR:
this ->m_SelectVars[i].ValueChar = new char[this ->m_SelectVars[i].VarSize + 1];
this ->m_SelectVars[i].VarType = TYPE_STRING;
errno = OCIDefineByPos( this ->m_pOCIStmt, &this ->m_pOCIDefine, this ->m_pOCIError, i + 1,
(dvoid *)this ->m_SelectVars[i].ValueChar,this ->m_SelectVars[i].VarSize + 1, SQLT_STR, this ->indp, (ub2 *)0, (ub2 *)0, OCI_DEFAULT );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIDefineByPos");
}
break;
case SQLT_BLOB:
/* Allocate lobLocator */
errno = OCIDe.orAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_SelectVars[i].lobLocator, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0 );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIDe.orAlloc");
}
this ->m_SelectVars[i].VarType = TYPE_BLOB;
errno = OCIDefineByPos( this ->m_pOCIStmt, &this ->m_pOCIDefine, this ->m_pOCIError, (ub4)i + 1, (dvoid *)&this ->m_SelectVars[i].lobLocator, (sb4)0,
(ub2)SQLT_BLOB, this ->indp, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT);
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIDefineByPos");
}
break;
case SQLT_CLOB:
/* Allocate lobLocator */
errno = OCIDe.orAlloc( (dvoid *)this ->m_pOCIEnv, (dvoid **)&this ->m_SelectVars[i].lobLocator, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0 );
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIDe.orAlloc");
}
this ->m_SelectVars[i].VarType = TYPE_BLOB;
errno = OCIDefineByPos( this ->m_pOCIStmt, &this ->m_pOCIDefine, this ->m_pOCIError, (ub4)i + 1, (dvoid *)&this ->m_SelectVars[i].lobLocator, (sb4)0,
(ub2)SQLT_BLOB, this ->indp, (ub2 *)0, (ub2 *)0, (ub4)OCI_DEFAULT);
if ( errno )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserSelect OCIDefineByPos");
}
break;
}
}
return 0;
}
catch ( OCIException &ex )
{
/* Free Stmt */
errno = OCIHandleFree( (dvoid *)this ->m_pOCIStmt, (ub4)OCI_HTYPE_STMT );
if ( errno )
{
OCIError::PrintError(errno, (char *)"OCIDB::UserExecute Ex OCIHandleFree");
}
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
int OCIDB::UserFetch()
{
sword errno;
try
{
errno = OCIStmtFetch( this ->m_pOCIStmt, this ->m_pOCIError, 1, OCI_FETCH_NEXT, OCI_DEFAULT );
if ( errno && errno != OCI_NO_DATA )
{
throw OCIException(errno, this ->m_pOCIError, (char *)"OCIDB::UserFetch OCIStmtFetch");
}
if ( errno == OCI_NO_DATA )
return 1;
return 0;
}
catch ( OCIException &ex )
{
std::cout << ex.GetErrFunc() << ex.GetErrMsg() << std::endl;
return -1;
}
}
int OCIDB::UserSelectFree()
{
int i;
for ( i = 0 ; i < this ->m_iSelectVarsCount ; i++ )
{
switch ( this ->m_SelectVars[i].VarType )
{
case TYPE_STRING:
delete [] this ->m_SelectVars[i].ValueChar;
break;
case TYPE_FLOAT:
delete this ->m_SelectVars[i].ValueFloat;
break;
case TYPE_INT:
delete this ->m_SelectVars[i].ValueInt;
break;
}
}
return this ->UserFree();
}
int OCIDB::UserGetInt(int index)
{
switch ( this ->m_SelectVars[index].VarType )
{
case TYPE_FLOAT:
return (int)*m_SelectVars[index].ValueFloat;
break;
case TYPE_INT:
return *m_SelectVars[index].ValueInt;
break;
default:
return 0;
}
}
int OCIDB::UserGetInt(char * name)
{
int i;
for ( i = 0 ; i < this ->m_iSelectVarsCount ; i++ )
{
if ( this ->StrCmp(m_SelectVars[i].VarName, name) )
{
switch ( this ->m_SelectVars[i].VarType )
{
case TYPE_FLOAT:
return (int)*m_SelectVars[i].ValueFloat;
break;
case TYPE_INT:
return *m_SelectVars[i].ValueInt;
break;
default:
return 0;
}
}
}
return 0;
}
float OCIDB::UserGetFloat(int index)
{
switch ( this ->m_SelectVars[index].VarType )
{
case TYPE_FLOAT:
return *m_SelectVars[index].ValueFloat;
break;
case TYPE_INT:
return (float)*m_SelectVars[index].ValueInt;
break;
default:
return 0;
}
}
float OCIDB::UserGetFloat(char * name)
{
int i;
for ( i = 0 ; i < this ->m_iSelectVarsCount ; i++ )
{
if ( this ->StrCmp(m_SelectVars[i].VarName, name) )
{
switch ( this ->m_SelectVars[i].VarType )
{
case TYPE_FLOAT:
return *m_SelectVars[i].ValueFloat;
break;
case TYPE_INT:
return (float)*m_SelectVars[i].ValueInt;
break;
default:
return 0;
}
}
}
return 0;
}
char * OCIDB::UserGetString(int index)
{
if ( this ->m_SelectVars[index].VarType == TYPE_STRING )
{
return this ->m_SelectVars[index].ValueChar;
}
else
{
return 0;
}
}
char * OCIDB::UserGetString(char * name)
{
int i;
for ( i = 0 ; i < this ->m_iSelectVarsCount ; i++ )
{
if ( this ->StrCmp(m_SelectVars[i].VarName, name) )
{
if ( this ->m_SelectVars[i].VarType == TYPE_STRING )
{
return this ->m_SelectVars[i].ValueChar;
}
}
}
return 0;
}
char * OCIDB::UserGetBlob(int index)
{
if ( this ->m_SelectVars[index].VarType == TYPE_BLOB )
{
return this ->m_SelectVars[index].ValueChar;
}
else
{
return 0;
}
}
char * OCIDB::UserGetBlob(char * name)
{
int i;
for ( i = 0 ; i < this ->m_iSelectVarsCount ; i++ )
{
if ( this ->StrCmp(m_SelectVars[i].VarName, name) )
{
if ( this ->m_SelectVars[i].VarType == TYPE_BLOB )
{
return this ->m_SelectVars[i].ValueChar;
}
}
}
return 0;
}
vector< vector<string> > OCIDB::getResult()
{
int i;
vector<string> objectValue;
StringTools stringTools;
while ( this ->UserFetch() == 0 )
{
objectValue.clear();
for ( i = 0 ; i < this ->m_iSelectVarsCount ; i++ )
{
switch ( this ->m_SelectVars[i].VarType )
{
case TYPE_STRING:
objectValue.push_back(this ->UserGetString(i));
break;
case TYPE_FLOAT:
objectValue.push_back(stringTools.ToString(this ->UserGetFloat(i)));
break;
case TYPE_INT:
objectValue.push_back(stringTools.ToString(this ->UserGetInt(i)));
break;
case TYPE_BLOB:
break;
}
}
resultList.push_back(objectValue);
}
return resultList;
}
void inline OCIDB::StrUpper(char * str)
{
int i;
int sLen = strlen(str);
for ( i = 0 ; i < sLen ; i++ )
{
str[i] = toupper(str[i]);
}
}
bool inline OCIDB::StrCmp(const char * ori, const char * des)
{
int iLenOri, iLenDes;
int j;
iLenOri = strlen(ori);
iLenDes = strlen(des);
if ( iLenOri != iLenDes )
return false;
for ( j = 0 ; j < iLenOri ; j++ )
{
if ( toupper(ori[j]) != toupper(des[j]) )
{
return false;
}
}
return true;
}
五、修改建议
本套代码只是处理了LOB类型的绑定，没有处理LOB类型数据的读取。
本人在以后的完善中，打算把runSQLCommand(char * sql)函数分解成两个或者三个函数，分别执行select和insert等语句。
在程序中，我并没有强制要求参数必须为const，可能会出现一些安全问题。
本文仅起抛砖引玉的作用，希望有高手可以指点我程序中的问题。