摘要
本文詳細記錄了，使用VS2013，VC++ Windows Console 程式運作 OTL 一個示例代碼 Example 185 （ http://otl.sourceforge.net/otl4_ex185.htm）的過程，希望能幫到首次接觸OTL的朋友。
1. OTL簡介
OTL 是 Oracle, Odbc and DB2-CLI Template Library 的縮寫，是一個C++操控關系資料庫的模闆庫，它目前幾乎支援所有的目前各種主流資料庫，例如Oracle, MS SQL Server, Sybase, Informix, MySQL, DB2, Interbase / Firebird, PostgreSQL, SQLite, SAP/DB, TimesTen, MS ACCESS等等。OTL中直接操作Oracle主要是通過Oracle提供的OCI接口進行，進行操作DB2資料庫則是通過CLI接口來進行，至于MS的資料庫和其它一些資料庫，則OTL隻提供了ODBC來操作的方式。當然Oracle和DB2也可以由OTL間接使用ODBC的方式來進行操縱。OTL最新版本為4.0，項目首頁：http://otl.sourceforge.net/
優點：
a. 跨平台
b. 運作效率高，與C語言直接調用API相當
c. 開發效率高，起碼比ADO.net使用起來更簡單，更簡潔
d. 部署容易，不需要ADO元件，不需要.net framework 等
缺點：
我不知道，是以我不說:)
2. OTL下載下傳
從首頁：http://otl.sourceforge.net/可以進入下載下傳頁面，使用OTL開發，隻需要下載下傳其一個頭檔案就可以了，下載下傳下來的頭檔案名為：otlv4.h。在下載下傳頁面也同時提供了幫助文檔和例子程式的下載下傳。
3. 編譯調試運作示例 Example 185
3.1 打開VS2013，建立一個VC++空白的Win32 Console Application工程，Solution name：OTL Example，Project Name： Example185_Unicode_NVarChar2_otl_refcur_stream，添加cpp檔案，main.cpp， 将示例代碼（http://otl.sourceforge.net/otl4_ex185.htm）拷貝到main.cpp中，就向這樣
别着急編譯，一大堆錯誤呢，接着往下看。
3.2 配置x64 Solution Platform
系統環境是 Windows 8.1 Pro，64-bitOperating System, x64-based processor。OracleClient 64-bit, Release 11.2.0.3.0。
是以必須，建立x64 SolutionPlatform
點選OK，然後将編譯平台設定為x64
3.3 添加引用otlv4.h
在解決方案檔案所在的目錄下，建立檔案夾OTL_Include，将下載下傳下來的otlv4.h檔案放入其中，然後在工程屬性中，添加剛才建立的目錄作為otlv4.h引用路徑：
$(SolutionDir)\OTL_Include
3.4 添加引用oci.h和oci.lib
從Oracle的安裝路徑中，找到oci.h和oci.lib所在的檔案夾，将檔案夾oci其拷貝到解決方案所在檔案夾下。
然後依次修改下面3個工程屬性：
1) oci.h引用路徑：$(SolutionDir)\oci\include
2) Oci.lib引用路徑：$(SolutionDir)\oci\lib\msvc
3) 指定oci.lib依賴
4 其它編譯錯誤
當遇到以下編譯錯誤時：
1>main.cpp(26): error C4996: 'sprintf': This function orvariable may be unsafe. Consider using sprintf_s instead. To disabledeprecation, use _CRT_SECURE_NO_WARNINGS. See online help for details.
1> C:\Program Files (x86)\Microsoft Visual Studio12.0\VC\include\stdio.h(356) : see declaration of 'sprintf'
使用sprintf_s替換掉函數sprintf。
至此，編譯應該通過了。
5. 修改連接配接字元串
從例子代碼中，找到rlogon函數調用，修改其連接配接到測試資料庫，例如，我的就修改如下：
db.rlogon("fmuser_syy/[email protected]:1521/fmorcl"); // connectto Oracle
6. 運作效果
7. 示例工程下載下傳
http://download.csdn.net/detail/sunyeyi/8515193
資源分分很貴的！是以附上完整源代碼一份，你也可以不必去下載下傳！
//
// http://otl.sourceforge.net/otl4_ex185.htm
#include <iostream>
using namespace std;
#include <stdio.h>
#define OTL_ORA8I // Compile OTL 4.0/OCI8i
#define OTL_UNICODE // Enable Unicode OTL for OCI8i
#include <otlv4.h> // include the OTL 4.0 header file
otl_connect db; // connect object
void insert()
// insert rows into table
{
otl_stream o(50, // buffer size
"insert into test_tab values(:f1<float>,:f2<char[31]>)",
// SQL statement
db // connect object
);
char tmp[32];
unsigned short tmp2[32]; // Null terminated Unicode character array.
for (int i = 1; i <= 100; ++i){
sprintf_s(tmp, "Name%d", i);
unsigned short* c2 = tmp2;
char* c1 = tmp;
// Unicode's first 128 characters are ASCII (0..127), so
// all is needed for converting ASCII into Unicode is as follows:
while (*c1){
*c2 = (unsigned char)*c1;
++c1; ++c2;
}
*c2 = 0; // target Unicode string is null terminated,
// only the null terminator is a two-byte character,
// not one-byte
o << (float)i;
o << (unsigned char*)tmp2;
// overloaded operator<<(const unsigned char*) in the case of Unicode
// OTL accepts a pointer to a Unicode character array.
// operator<<(const unsigned short*) wasn't overloaded
// in order to avoid ambiguity in C++ type casting.
}
}
void select()
{
otl_stream i(1, // buffer size
"begin "
" open :cur<refcur,out[50]> for "
// :cur is a bind variable name, refcur -- its type,
// out -- output parameter, 50 -- the buffer size when this
// reference cursor will be attached to otl_refcur_stream
" select * "
" from test_tab "
" where f1>=:f<int,in> and f1<=:f*2; "
"end;", // PL/SQL block returns a referenced cursor
db // connect object
);
// create select stream with referenced cursor
i.set_commit(0); // set stream "auto-commit" to OFF.
float f1;
unsigned short f2[32];
otl_refcur_stream s; // reference cursor stream for reading rows.
i << 8; // assigning :f = 8
i >> s; // initializing the refrence cursor stream with the output
// reference cursor.
while (!s.eof()){ // while not end-of-data
s >> f1;
s >> (unsigned char*)f2;
// overloaded operator>>(unsigned char*) in the case of Unicode
// OTL accepts a pointer to a Unicode chracter array.
// operator>>(unsigned short*) wasn't overloaded
// in order to avoid ambiguity in C++ type casting.
cout << "f1=" << f1 << ", f2=";
// Unicode's first 128 characters are ASCII, so in order
// to convert Unicode back to ASCII all is needed is
// as follows:
for (int j = 0; f2[j] != 0; ++j){
cout << (char)f2[j];
}
cout << endl;
}
s.close(); // closing the reference cursor
i << 4; // assigning :f = 4
i >> s;
while (!s.eof()){ // while not end-of-data
s >> f1;
s >> (unsigned char*)f2;
// overloaded operator>>(unsigned char*) in the case of Unicode
// OTL accepts a pointer to a Unicode chracter array.
// operator>>(unsigned short*) wasn't overloaded
// in order to avoid ambiguity in C++ type casting.
cout << "f1=" << f1 << ", f2=";
// Unicode's first 128 characters are ASCII, so in order
// to convert Unicode back to ASCII all is needed is
// as follows:
for (int j = 0; f2[j] != 0; ++j){
cout << (char)f2[j];
}
cout << endl;
}
// there is no need to explicitly calls s.close() since s's destructor
// will take care of closing the stream
}
int main()
{
otl_connect::otl_initialize(); // initialize OCI environment
try{
db.rlogon("fmuser_syy/[email protected]:1521/fmorcl"); // connect to Oracle
otl_cursor::direct_exec
(
db,
"drop table test_tab",
otl_exception::disabled // disable OTL exceptions
); // drop table
otl_cursor::direct_exec
(
db,
"create table test_tab(f1 number, f2 nvarchar2(60))"
); // create table
db.set_character_set(SQLCS_NCHAR);
insert(); // insert records into table
select(); // select records from table
}
catch (otl_exception& p){ // intercept OTL exceptions
cerr << p.msg << endl; // print out error message
cerr << p.stm_text << endl; // print out SQL that caused the error
cerr << p.var_info << endl; // print out the variable that caused the error
}
db.logoff(); // disconnect from Oracle
return 0;
}