VC++环境设置
1加include files项:%ORACLE_HOME%\oci\include
%ORACLE_HOME% = HKEY_LOCALMACHINE_SOFTWARE\SOFTWARE\ORACLE\KEY_OraDb10g_home 注册表键
2、加library file
项:%ORACLE_HOME%\oci\lib\msvc\vc6 %ORACLE_HOME%\oci\lib
3、加lib文件oraocci10.lib (VS10的链表-强制依赖项)
4、在preprocessor definitions 下面去掉_DEBUG宏,加入WIN32COMMON _DLL
_MT 3个宏;或在#include <occi.h>前面加以下代码
#inndef WIN32COMMON
#define WIN32COMMON
#endif
#inndef _DLL
#define _DLL
#endif
#inndef _MT
#define _MT
#endif
5、在project options 里将MLd去掉d,去掉/GZ。
或者在build->set active configouration 打开set active project configouration对话
框,选择win32 release。
使用方法:
#include <occi.h>
using namespace oracle::occi;
环境变量Environment
Environment *env;
Env=Environment::creatEnvironment(Enviroment::DEFAULT);
Environment::terminateEnvironment(env);
连接对象:Connection
Connection *conn;
Conn=env->createConnection(user,passwd,db);
Env->terminateConnection(conn);
普通连接池:
ConnectionPool *connPool;
connPool=env->creatConnectionPool(poolusername,poolpasswd,connectstring,minco
nn,maxconn,incrconn);
普通连接:
con=connPool->createConnection(username,password);
代理连接:
ConectionPool->createProxyConnection(
Const string &username,
Connetion::ProxyType proxtType=Connection::PROXY_DEFAULT);
或者
ConnectionPool->createProxyConnection(
Const string &username,
String roles[],
Int numRoles,
Connection::ProxyType proxyType=Connection::PROXY_DEFAULT);
无状态连接池:(连接池用完后可自动增加)
StatlessConnectionPool *spool=
Enviroment::CreatStatlessConnectionPool(username,passwd,connectstring,maxco
nn,minconn,incrconn,HOMOGENOUS);
Connection *conn=spool->getConnection();//获取连接对象
spool->releaseConnection(conn,”CN”);//释放连接,同时标记
env->terminateConnectionPool(spool); //销毁连接池
statement对象:
1、标准
String sqlstmt = “SELECT author_id,author_name FROM author_tab order by
author_id;//occi数据类型
stmt=conn->createStatement(sqlstmt); //创建标准类型
ResultSet *rset=stmt->executeQuery(); //返回结果
Cout<<rset->getInt(1)<<rset->getSting(2); //第1、2列
Stmt->closeResultSet(rset); //关闭
Conn->terminateStatement(stmt); //关闭
执行SQL语句时可执行以下函数:
execute():一般SQL语句,create\insert
executeUpdate():执行DDL\DML语句
executeArrayUpdate():复杂的SQL语句
executeQuery():查询
setSQL()
getSQL():获取当前执行的SQL语句
SQLException ex:捕捉发生错误的异常;ex.getErrorCode() ex.getMessage()
2、带参数
Void insertBind(int c1,string c2)
{
String sqlStmt=”INSERT INTO author_tab VALUES(:x,:y);
Stmt=conn->createStatement(sqlStmt); try{
stmt->setInt(1,c1); stmt->setString(2,c2);stmt->executeUpdate();
}catch(SQLException ex)
{cout<<ex.getErrorCode()<<ex.getMessage();}
Conn->terminateStatement;
}
3、带存储过程的
A:调用过程:
Statement *stmt=con->createStatement(“BEGIN demo_proc(:v1,:v2,:v3)
END;”);//3参数
Cout<<stmt->getSQL();
stmt->setInt(1,10); stmt->setMaxParamSize(2,30); stmt->String(2,”IN”);
stmt->registerOutParam(3,OCCISTRING,30,””);
int updateCount= stmt->executeUpdate();
B:调用函数:
Statement *stmt=con->createStatement(“BEGIN:a:=demo_proc(:v1,:v2,:v3)
END;”);//4参数
Cout<<stmt->getSQL();
stmt->setInt(2,10); stmt->setMaxParamSize(3,30); stmt->String(2,”IN”);
stmt->registerOutParam(1,OCCISTRING,30,””);
stmt->registerOutParam(4,OCCISTRING,30,””);
int updateCount= stmt->executeUpdate();
批量编辑数据
Statement *stmt=conn->createStatement(“INSERT INTO emp(empno,ename) VALUES(:1,:2)”);
stmt->setMaxIteration(10); //指定最多有多少个记录时执行executeUpdate
stmt->setMaxParamSize(2,100); //指定最大的数据缓冲区
stmt->setInt(1,10001); stmt->setString(2,”john”); stmt->setIteartion();//加入缓冲区
stmt->executeUpdate();
操作流数据(getStream(),writeBuffer(),writeLastBuffer(),readBuffer(),
readLastBuffer())
数据类型BLOB、CLOB、LONG、LONG RAW 、RAW 、VARCHAR2的数据
为流数据
Statement *stmt=conn->creatStatement(“INSERT INTO testtab(longeo1) VALUES(:1)”);
stmt->setCharacterStreamMode(1,10000); stmt->executeUpdate();
Stream *instream= stmt->getStream(1); char buffer[10000];
instream->writeBuffer(buffer,len);instream->writeLastBuffer(buffer,len);
stmt->closeStream(instream);
事务的提交与回滚 Connection::commit();Connection::rollback()
自动提交Statement::setAutoCommit(TRUE/FALSE);
Statement对象的缓冲区:
1、连接对象的缓冲区:
conn->setStmtCacheSize(10); int csize=conn->getStmtSize();
conn->terminateStatemen(stme); stmt->disableCaching();
2、连接池缓冲区:
conPool->setStmtCacheSize();
Metadata对象:查看数据库对象的属性与元数据
MetaData md=conn->getMetadata(“emp”,PTYPE_UNK);//建立对象
int objectType=md.getInt(Metadata::ATTY_OBJ_PTYPE);//对象类型
int columnCount=md.getInt(Metadata:ATTR_OBJ_NUM_COLS);//对象的字段数
Timestamp objts=md.getTimestamp(Metedata::ATTR_OBJ_TIMESTAMP);//时区
vector<MetaData>cols=md.getVector(Metadata::ATTR_LIST_COLUMS);//返回集
合对象
cols[0].getString(Metedata::ATTR_NAME);//第一个字段名
cols[0].getInt(Metedata::ATTR_DATA_TYPE);//第一个字段类型
Statement *stmt =conn->creatStatement(“select * from emo”);
ResultSet *rs=stmt->executeQuery();
Vector(MeteData) selectcols=rs->getColumnListMetaData();
int columnCount=selectcols.size();
for(int i=0;i<columnCount;i==)
{
cout<<selectcols[i].getString(MetaData::ATTR_NAME);
cout<<selectcols[i].getInt(MetaData::ATTR_DATA_TYPE);
}