使用OTL操作Oracle数据库
OTL是一个轻量级的数据库操作工具；可以访问多种数据库（oracle，db2,mysql, sql server等等）,但是它只能用于c++。
基本使用
OTL的一般使用步骤包括：
(1) 使用宏指明底层数据库API类型和控制编译器对OTL的编译。例如：
#define OTL_ORA9I // Compile OTL 4.0/OCI9i
#define OTL_UNICODE //Enable Unicode OTL for OCI9i
(2) 创建otl_connect对象，该对象一般为全局共享的。
(3) 调用otl_connect的静态方法otl_initialize()初始化OTL环境。
(4) 调用otl_connect的rlogon()方法连接数据库。
(5) 创建otl_stream()对象，该对象一般为局部的。
(6) 调用otl_stream的open()方法打开SQL进行解析。
(7) 使用otl_stream的<<操作符绑定SQL中的变量。
(8) 使用otl_stream的>>操作符读取返回结果。
(9) 调用otl_connect的logoff()方法从数据库断开。
下面将通过一个较为全面的示例说明使用OTL连接数据库、创建表和存储过程、调用存储过程、查询记录以及插入记录、从数据库断开的具体代码实现。
| #include <stdio.h> #include <string.h> #include <iostream> #include <vector>
#define OTL_ORA9I // Compile OTL 4.0/OCI9i //#define OTL_UNICODE // Enable Unicode OTL for OCI9i #include "otlv4.h" // include the OTL 4.0 header file
using namespace std;
/** *连接数据库 */ int OTLConnect (const char* pszConnStr, otl_connect& db) { try { otl_connect::otl_initialize(); // initialize OCI environment db.rlogon(pszConnStr); db.auto_commit_off ( ); printf ( "CONNECT: OK!\n" ); } catch(otl_exception& p) { // intercept OTL exceptions printf ( "Connect Error: (%s) (%s) (%s)\n",p.msg, p.stm_text, p.var_info ); return -1; } return 0; }
/** *从数据库断开 */ int OTLDisconnect (otl_connect& db) { db.commit ( ); db.logoff();
printf ( "DISCONNECT: OK!\n" ); return 0; }
/** *创建数据库表和存储过程 */ int OTLExec ( otl_connect& db) { try { int nCnt = 0; char strSql[] = "SELECT count(0) FROM user_tables " " WHERE table_name = 'TEST_FTP' ";
otl_stream otlCur (1, (const char*)strSql, db ); otlCur >> nCnt;
if ( nCnt == 0 ) { char strDDL[] = "create table TEST_FTP " "( " " AREA_ID VARCHAR2(100) not null, " " FTP_FILE_NAME VARCHAR2(100) not null, " " FTP_TIME VARCHAR2(14), " " FTP_BEGIN_TIME VARCHAR2(14), " " FTP_END_TIME VARCHAR2(14), " " FTP_MOD_TIME date, " " FTP_SIZE NUMBER(8), " " FTP_SOURCE_PATH VARCHAR2(100), " " FTP_LOCAL_PATH VARCHAR2(100), " " FTP_RESULT VARCHAR2(4), " " FTP_REDO VARCHAR2(1) )";
otl_cursor::direct_exec ( db, (const char*)strDDL ); }
char strSqlProc[] = "SELECT count(0) from user_objects " " WHERE object_type = 'PROCEDURE' and object_name = 'PR_REMOVE_FTP' "; otl_stream otlCurProc (1, (const char*)strSqlProc, db ); otlCurProc >> nCnt;
if ( nCnt == 0 ) { char strProc[] = "CREATE OR REPLACE procedure pr_remove_ftp " " ( area in varchar2, out_flag out varchar ) " "AS " "strtmp varchar2(32); " "BEGIN " " strtmp := area||'%'; " " DELETE FROM TEST_FTP where area_id LIKE strtmp; " " out_flag := 'OK'; " "END; ";
otl_cursor::direct_exec ( db, (const char*)strProc ); }
} catch(otl_exception& p) { // intercept OTL exceptions printf ( "EXECUTE Error: (%s) (%s) (%s)\n",p.msg, p.stm_text, p.var_info ); } return 0; }
/** *调用存储过程 */ int OTLProcedure (otl_connect& db ) { try {
char szData[64], szData1[64], szData2[64], szData3[64]; int nSize = 0; char strSql[] = " BEGIN " " pr_remove_ftp ( :area<char[100],in>, :out<char[100],out> ); " " END; "; otl_stream otlCur (1, (const char*)strSql, db ); otlCur.set_commit ( 0 );
strcpy ( szData, "AREA" ); memset ( szData1, 0, sizeof(szData1) ); memset ( szData2, 0, sizeof(szData2) ); memset ( szData3, 0, sizeof(szData3) );
otlCur << szData; otlCur >> szData1;
printf ( "PROCEDURE: %s!\n", szData1 ); } catch(otl_exception& p) { // intercept OTL exceptions printf ( "PROCEDURE Error: (%s) (%s) (%s)\n",p.msg, p.stm_text, p.var_info ); } return 0; }
/** *查询记录 */ int OTLSelect (otl_connect& db) { try { char szData[64], szData1[64], szData2[64], szData3[64], szRedo[2]; int nSize; char strSql[] = " SELECT area_id, ftp_time, ftp_file_name, " " to_char(ftp_mod_time, 'YYYY-MM-DD HH24:MI:SS'), ftp_size " " FROM TEST_FTP " " WHERE ftp_redo = :ftp_redo<char[2]>" ; otl_stream otlCur (1, (const char*)strSql, db );
strcpy ( szRedo, "Y" ); otlCur << szRedo; while ( !otlCur.eof() ) { memset ( szData, 0, sizeof(szData) ); otlCur >> szData; otlCur >> szData1; otlCur >> szData2; otlCur >> szData3; otlCur >> nSize; printf ( "SELECT: (%s %s %s %s %d)\n", szData, szData1, szData2, szData3, nSize ); } } catch(otl_exception& p) { // intercept OTL exceptions printf ( "Select Error: (%s) (%s) (%s)\n",p.msg, p.stm_text, p.var_info ); } return 0; }
/** *插入记录 */ int OTLInsert (otl_connect& db) { try { char szData[64], szData1[64], szData2[9], szData3[64], szRedo[2]; int nSize; char strSql[] = " INSERT into TEST_FTP " " ( area_id, ftp_file_name, ftp_time, ftp_mod_time, ftp_size, ftp_redo )" " VALUES ( :area_id<char[100]>, " " :ftp_file_name<char[100]>, " " to_char(sysdate,'YYYYMMDDHH24MISS'), " " to_date(:ftp_mod_time<char[20]>,'YYYYMMDD'), " " :ftp_size<int>, " " :ftp_redo<char[2]> ) "; otl_stream otlCur (1, (const char*)strSql, db );
otlCur.set_commit ( 0 );
for ( int i = 1; i < 10; i ++ ) { sprintf ( szData, "AREA_%d", i ); sprintf ( szData1, "FILE_NAME_%d", i ); if ( i < 5 ) { sprintf ( szData2, "20070415" ); strcpy ( szRedo, "Y" ); } else { sprintf ( szData2, "20070416" ); strcpy ( szRedo, "N" ); }
memset ( szData3, 0, sizeof(szData3) ); nSize = i * 100;
otlCur << szData << szData1 << szData2 << nSize << szRedo; }
printf ( "INSERT: OK!\n" ); } catch(otl_exception& p) { // intercept OTL exceptions printf ( "INSERT Error: (%s) (%s) (%s)\n",p.msg, p.stm_text, p.var_info ); } return 0; }
/** *主函数 */ int main ( int argc, char *argv[] ) { otl_connect db; char szConn[64];
if ( argc >= 2 ) strcpy ( szConn, argv[1] ); else { printf ( "otltest conn_str" ); return -1; }
if ( OTLConnect ( szConn, db ) < 0 ) return 0; OTLExec ( db ); OTLProcedure ( db ); OTLInsert ( db ); OTLSelect ( db ); OTLDisconnect ( db );
return 0; } |