使用OTL操作MySQL数据库

一、编写代码

注：以下代码来自OTL示例，略有改动

#include <iostream>
using namespace std;

#include <cstdio>
#include <cstring>
#include <cstdlib>

#define OTL_ODBC // Compile OTL 4.0/ODBC
// The following #define is required with MyODBC 3.51.11 and higher
#define OTL_ODBC_SELECT_STM_EXECUTE_BEFORE_DESCRIBE
#define OTL_ODBC_UNIX // uncomment this line if UnixODBC is used
#include <otlv4.h> // include the OTL 4.0 header file
otl_connect db; // connect object

void insert()
// insert rows into table
{
    otl_stream o(1, // buffer size should be == 1 always on INSERT
            "insert into test_tab values "
                " (:f1<int>,:f2<char[31]>), "
                " (:f12<int>,:f22<char[31]>), "
                " (:f13<int>,:f23<char[31]>), "
                " (:f14<int>,:f24<char[31]>), "
                " (:f15<int>,:f25<char[31]>) ",
            // INSERT statement. Multiple sets of values can be used
            // to work around the lack of the bulk interface
            db // connect object
            );

    // If the number of rows to be inserted is not known in advance,
    // another stream with the same INSERT can be opened
    otl_stream o2(1, // buffer size should be == 1 always on INSERT
            "insert into test_tab values "
                " (:f1<int>,:f2<char[31]>)", db // connect object
            );

    char tmp[32];
    int i;
    for (i = 1; i <= 100; ++i) {
        sprintf(tmp, "Name%d", i);
        o << i << tmp;
    }
    for (i = 101; i <= 103; ++i) {
        sprintf(tmp, "Name%d", i);
        o2 << i << tmp;
    }
}

void update(const int af1)
// insert rows into table
{
    otl_stream o(1, // buffer size should be == 1 always on UPDATE
            "UPDATE test_tab "
                "   SET f2=:f2<char[31]> "
                " WHERE f1=:f1<int>",
            // UPDATE statement
            db // connect object
            );
    o << "Name changed" << af1;
    o << otl_null() << af1 + 1; // set f2 to NULL

}

void select(const int af1) {
    otl_stream i(50, // buffer size may be > 1
            "select * from test_tab "
                "where f1>=:f11<int> "
                "  and f1<=:f12<int>*2",
            // SELECT statement
            db // connect object
            );
    // create select stream

    int f1;
    char f2[31];

    i << af1 << af1; // :f11 = af1, :f12 = af1
    while (!i.eof()) { // while not end-of-data
        i >> f1;
        cout << "f1=" << f1 << ", f2=";
        i >> f2;
        if (i.is_null())
            cout << "NULL";
        else
            cout << f2;
        cout << endl;
    }

}

int main() {
    otl_connect::otl_initialize(); // initialize ODBC environment
    try {

        // db.rlogon("UID=xuanyuan;PWD=xuanyuan;DSN=examples"); // connect to ODBC
        db.rlogon("xuanyuan/xuanyuan@examples"); // connect to ODBC, alternative format of connect string 

        otl_cursor::direct_exec(db, "use examples");  // 此行在原示例代码中没有，必须使用use xxx切换数据库
        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 int, f2 varchar(30)) type=innoDB"); // create table

        insert(); // insert records into the table
        update(10); // update records in the table
        select(8); // select records from the 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.sqlstate << endl; // print out SQLSTATE message
        cerr << p.var_info << endl; // print out the variable that caused the error
    }

    db.logoff(); // disconnect from ODBC

    return 0;

}

二、编译代码

g++ -o"otl_test2" otl_test2.cpp -lmyodbc3

三、运行程序 otl_test

$ ./otl_test2

结果如下：

f1=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name changed
f1=11, f2=NULL
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16

四、关于OTL更多的信息

请访问OTL的主页：http://otl.sourceforge.net/

专注 Linux下C/C++、ACE、Boost、高性能服务器端应用 开发技术培训

http://www.xuanyuan-soft.cn/