[大数据]金仓数据库KingbaseES qodbc编译使用指南

一、linux编译Qt4?带odbc驱动

1）编译qodbc

./configure --prefix=`pwd`/release -no-phonon -no-phonon-backend -no-opengl -no-gtkstyle -no-xinput -no-xkb -no-webkit -no-multimedia -no-script -no-scripttools -no-javascript-jit -no-declarative -no-qt3support -no-audio-backend -no-svg -nomake demos -nomake docs -nomake examples -fast?-qt-sql-odbc -plugin-sql-odbc -I/home/xing/xjjia_odbc/unixODBC-2.3.7/release/include -L/home/xing/xjjia_odbc/unixODBC-2.3.7/release/lib

备注：

-I/home/xing/xjjia_odbc/unixODBC-2.3.7/release/include?该参数为unixodbc的头文件所在路径

-L/home/xing/xjjia_odbc/unixODBC-2.3.7/release/lib?该参数为unixodbc的库文件所在路径

2）编译之后，在/release/plugins/sqldrivers下，可见libqsqlodbc.so即可为qodbc驱动

二、linux下使用QODBC

1、配置odbc连接

1）安装unixodbc，使用 odbcinst –j 查看读取的配置文件所在的路径

2） 修改odbcinst.ini，配置驱动所在路径，如下图

3）在odbc.ini中，配置连接数据库信息

2、下面是一个测试程序用例

#include <QCoreApplication>
#include <iostream>
#include <QtDebug>
#include <QByteArray>
#include <QDate>
#include <QFile>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QStringList>

const QString host = "10.10.11.152";
const int port = 54332;
const QString dbName = "kdbodbc_test_dsn";
const QString user = "xjjia";
const QString password = "123456";

static int failed = 0;
static int cases = 0;

void check(bool b, const char *f)
{
    if (!b) {
        failed++;
    }
    cases++;
    std::cout << "test " << f << (b ? " success!" : " failed!") << std::endl;
    std::cout << "---------------------------------" << std::endl;
}

bool testConnect(QSqlDatabase &db)
{
    db = QSqlDatabase::addDatabase("QODBC");
    db.setHostName(host);
    db.setDatabaseName(dbName);
    db.setUserName(user);
    db.setPassword(password);
    db.setPort(port);
    db.setConnectOptions("connect_timeout=2");

    db.open();

    if (!db.isOpen()) {
        qDebug() << db.lastError();
        return false;
    }

    QSqlQuery q(db);
    q.exec("select * from pg_type");
    while (q.next()) {
        qDebug() << q.value(0).toInt();
    }

    q.exec("select version()");
    while (q.next()) {
        qDebug() << q.value(0).toString();
    }
    return true;
}

bool testCreate(QSqlDatabase &db)
{
    QSqlQuery q(db);
    bool ret;

    ret = q.exec("create temp table Q_test_v8r6(id int, val text)");
    if (!ret) {
        qDebug() << q.lastError();
        return false;
    }

    ret = q.exec("insert into Q_test_v8r6(id, val) values(100, '中文测试')");
    if (!ret) {
        qDebug() << q.lastError();
        return false;
    }

    return true;
}

bool testPrepare(QSqlDatabase &db)
{
    QSqlQuery q(db);
    bool ret;

    ret = q.prepare("insert into Q_test_v8r6(id) values(?)");
    if (!ret)
    {
        qDebug()<<q.lastError();
        return false;
    }

    q.bindValue(0, 200);
    ret = q.exec();
    if (!ret)
    {
        qDebug()<<q.lastError();
        return false;
    }

    return true;
}

bool testBatch(QSqlDatabase &db)
{
    QSqlQuery q(db);
    QVariantList ints;
    bool ret;

    ints << 500 << 2 << 3 << 400 << 20000 << -100;
    q.addBindValue(ints);

    ret = q.execBatch();
    if (!ret)
    {
        qDebug()<<q.lastError();
        return false;
    }

    return true;
}

bool testLob(QSqlDatabase &db)
{
    QSqlQuery q(db);
    bool ret;

    ret = q.exec("create temp table Q_test_lob(id int, b blob, c clob)");
    if (!ret)
    {
        qDebug()<<q.lastError();
        return false;
    }

    ret = q.prepare("insert into Q_test_lob(id,b,c) values(?,?,?)");
    if (!ret)
    {
        qDebug()<<q.lastError();
        return false;
    }

    QByteArray ba("中文测试数据ABCDEF12345!@#$%");
    QString str("中文测试数据ABCDEF12345!@#$%");

    qDebug() << "byte data:" << ba;

    for (int i = 0; i < 3; i++) {
        q.bindValue(0, i);
        q.bindValue(1, ba);
        q.bindValue(2, str);
        q.exec();
    }

    ret = q.exec("select * from Q_test_lob");
    if (!ret)
    {
        qDebug()<<q.lastError();
        return false;
    }

    while (q.next()) {
        qDebug() << q.value(0).toInt();
        qDebug() << q.value(1).toByteArray();
        qDebug() << q.value(2).toString();
    }
    return true;
}


bool testQuery(QSqlDatabase &db)
{
    QSqlQuery q(db);
    q.exec("select * from Q_test_v8r6");

    while (q.next()) {
        qDebug() << q.value(0).toInt();
        qDebug() << q.value(1).toString();
    }

    q.exec("select version()");
    while (q.next()) {
        qDebug() << q.value(0).toString();
    }

    return true;
}

int main()
{
    QCoreApplication::addLibraryPath("./");

    qDebug() << "The driver should be put in this path:" << QCoreApplication::libraryPaths();

    qDebug() << "Available drivers:" << QSqlDatabase::drivers() << "\n\n";

    QSqlDatabase db;

    check(testConnect(db), "testConnect");

    check(testCreate(db), "testCreate");

    check(testPrepare(db), "testPrepare");

    check(testQuery(db), "testQuery");

    check(testLob(db), "testLob");

    std::cout << "\nAll test cases hava been done!" << std::endl;
    std::cout << "All cases:" << cases << ", failed cases:" << failed << std::endl;

    return 0;
}