MySQL – Page 2 – 0x2B|~0x2B

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

# 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;
}

#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

1	./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*

1	# 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.

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;
}

* # 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()

#!/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()

Tag Archives: MySQL

Database Access Layer in C++

MySQL with C++ and Python

1. C++

2. Python