Archive

Posts Tagged ‘MySQL’

Database Access Layer in C++

April 15th, 2015 No comments

We have JDBC in Java… and SOCI in C++… Well, it’s not so easy as it should be. To build with cmake:

# mkdir ../build
# cd ../build
# set ORACLE_HOME=C:\oraclexe\app\oracle\product\10.2.0\server
# cmake -G "Visual Studio 9 2008" -DMYSQL_INCLUDE_DIR="C:\Program Files\MySQL\MySQL Server 5.5\include" -DMYSQL_LIBRARIES="C:\Program Files\MySQL\MySQL Server 5.5\lib\libmysql.lib" ../soci-3.2.3

The documents seem outdated, many options do not work. Just managed to figure out from the *.cmake source files. You can also download the oracle instant client SDK, and re-arrange the directory structure for build.

Code snippet I extracted from its unit tests:

#include "soci.h"
#include "soci-mysql.h"
//#include "soci-oracle.h"
#include <ctime>
#include <string>
#include <iostream>
#include <sstream>
using namespace std;

int main()
{
    try {
        soci::session sql(soci::mysql, "host=192.168.1.101 db=mysql user=root password=111111");
        //soci::session sql(soci::oracle, "service=192.168.1.102/ORCL user=sys password=111111");
        soci::row v;
        /* comma operator is overloaded here.. */
        soci::statement st = (sql.prepare << "SELECT * FROM user", into(v));
        //soci::statement st = (sql.prepare << "SELECT * FROM SYS.USER$", into(v));
        st.execute(true);  /* with data exchange */
        unsigned int num_fields = v.size();
        cout << "fields: " << num_fields << endl;
        num_fields = (num_fields <= 9) ? num_fields : 9;
        unsigned long num_rows = (unsigned long)st.get_affected_rows();
        cout << "rows: " << num_rows << endl;
        for (size_t i = 0; i < num_fields; ++i) {
            const soci::column_properties &props = v.get_properties(i);
            cout << props.get_name() << '\t';
        }
        cout << endl;
        do {
            stringstream ss;
            for (size_t i = 0; i < num_fields; ++i) {
                if (v.get_indicator(i) == soci::i_null) {
                    ss << "NULL";
                    break;
                }
                const soci::column_properties &props = v.get_properties(i);
                switch (props.get_data_type()) {
                case soci::dt_string:
                    ss << v.get<string>(i);
                    break;
                case soci::dt_double:
                    ss << v.get<double>(i);
                    break;
                case soci::dt_integer:
                    ss << v.get<int>(i);
                    break;
                case soci::dt_long_long:
                    ss << v.get<long long>(i);
                    break;
                case soci::dt_unsigned_long_long:
                    ss << v.get<unsigned long long>(i);
                    break;
                case soci::dt_date:
                    tm dt = v.get<tm>(i);
                    ss << asctime(&dt);
                    break;
                }
                ss << '\t';
            }
            cout << ss.str() << endl;
        } while (st.fetch());
    } catch (soci::soci_error &e) {
        cerr << "Error: " << e.what() << endl;
    }
    return 0;
}

Updated Apr 20, 2015:

1. Under RHEL5/CentOS5, I got errors like:

./test_oracle: error while loading shared libraries: /home/gonwan/oracle11_64/lib/libnnz11.so: cannot restore segment prot after reloc: Permission denied

It’s due to SELinux security feature. Simply workaround it with:

# chcon -t texrel_shlib_t *.so*

2. Oracle uses oraociei11.dll or libociei.so for client data. They are both large files(110+MB), since they support multiple languages. Instead, you can use oraociicus11.dll(30+MB) or libociicus.so(10-MB). These files contain only English support.

Categories: C/C++ Tags: , ,

MySQL with C++ and Python

March 26th, 2015 No comments

Backup here:

1. C++

/*
 * # gcc test_mysql.c -o test_mysql -lmysqlclient -pedantic -Wall -Wextra
 */
#include <stdio.h>
#include <mysql/mysql.h>

int main()
{
    const char *host = "192.168.1.101";
    const char *user = "root";
    const char *pass = "111111";
    const char *dbname = "mysql";
    MYSQL mysql;
    MYSQL_RES *mysql_res;
    MYSQL_FIELD *mysql_field;
    MYSQL_ROW mysql_row;
    unsigned int num_fields;
    unsigned long num_rows;
    int i, rc;

    mysql_init(&mysql);
    /* connect */
    if (!mysql_real_connect(&mysql, host, user, pass, dbname, 0, NULL, 0)) {
        fprintf(stderr, "ERROR: %s\n", mysql_error(&mysql));
        return -1;
    }
    /* query */
    rc = mysql_query(&mysql, "SELECT * FROM user");
    if (rc) {
        fprintf(stderr, "ERROR: %s\n", mysql_error(&mysql));
        mysql_close(&mysql);
        return -1;
    }
    /* display */
    mysql_res = mysql_store_result(&mysql);
    if (!mysql_res) {
        fprintf(stderr, "ERROR: %s\n", mysql_error(&mysql));
        mysql_close(&mysql);
        return -1;
    }
    num_fields = mysql_num_fields(mysql_res);
    printf("fields: %u\n", num_fields);
    num_rows = (unsigned long)mysql_num_rows(mysql_res);
    printf("rows: %lu\n", num_rows);
    if (num_rows != 0) {
        num_fields = (num_fields <= 9) ? num_fields : 9;
        for (i = 0; i < (int)num_fields; i++) {
            mysql_field = mysql_fetch_field_direct(mysql_res, i);
            printf("%s\t", mysql_field->name);
        }
        printf("\n");
        while ((mysql_row = mysql_fetch_row(mysql_res)) != NULL) {
            for (i = 0; i < (int)num_fields; i++) {
                printf("%s\t",  mysql_row[i]);
            }
            printf("\n");
        }
    }
    mysql_free_result(mysql_res);
    mysql_close(&mysql);
    return 0;
}

2. Python

#!/usr/bin/python
# sudo apt-get install python-mysql.connector
# sudo apt-get install python3-mysql.connector
from __future__ import print_function
import mysql.connector

cur = None
con = None

try:
    con = mysql.connector.connect(host='192.168.1.101', user='root', password='111111', database='mysql')
    cur = con.cursor()
    cur.execute("SELECT * FROM user")
    num_fields = len(cur.column_names)
    print('fields: %d' % num_fields)
    if num_fields > 9:
        num_fields = 9
    rows = cur.fetchall()  # fetchall() before get rowcount
    print('rows: %d' % cur.rowcount)
    for i in range(num_fields):
        print('%s\t' % (cur.column_names[i]), end='')
    print()
    for row in rows:
        for i in range(num_fields):
            print('%s\t' % (row[i]), end='')
        print()
except mysql.connector.Error as e:
    print(e)
finally:
    if cur:
        cur.close()
    if con:
        con.close()
Categories: Database Tags: