Use MySQL database in your C++ projects

How to use MySQL DB in your C++ projects. This tutorial is written for Debian Lenny/Ubuntu 9.10. With a few minor changes, it should work on other distributions also.

What will you need?

- gcc <-- c++ compiler

- MySQL server 5.0 installed

- MySQL C API

To install above programs run this command as root:

apt-get install build-essential mysql-server mysql-client libsoci-mysql-gcc

When you have everything installed you need to create database and add user (MySQL Add User).

Now you will need to add a new table. The easiest approach is to use phpMyAdmin.

Simple program to test MySQL C API (copy it and save as filename.cpp):

#include <iostream>
#include <mysql.h>

using namespace std;

//MySQL variables
MYSQL *connection, mysql;
MYSQL_RES *result;
MYSQL_ROW row;
int query_state;

int main() {
//Connects to MySQL DB
mysql_init(&mysql);

connection = mysql_real_connect(&mysql, "localhost", "username", "password", "database", 0, 0, 0);

//Checks if there was a problem with connection
if(connection == NULL) {
cout <<mysql_error(&mysql);
return 1;
}

//Execute query
query_state = mysql_query(connection, "SELECT * FROM table");

if(query_state != 0) {
cout << mysql_error(connection);
}

//Loads query data
result = mysql_use_result(connection);

//Writes data to stdio from first column on all rows in table
while((row = mysql_fetch_row(result)) != NULL) {
cout << row[0] << endl;
}

mysql_free_result(result);
mysql_close(connection);
}

You can compile it with following command:

g++ filename.cpp -L/usr/include/mysql -lmysqlclient -I/usr/include/mysql

If there was no error displayed then your program compiled successfully. You can run by typing:

./a.out

And now you made your first C++ program that gets data from MySQL DB. You can do a lot more than just that.

You will probably need to read MySQL C API Manual to learn others commands.

Next time I will write about preparing Apple Xcode and Microsoft Visual Studio for using MySQL C API.

mod_python and MySQLdb

How to install mod_python and MySQLdb in Debian Lenny?

MySQL is one of greatest databases for web sites, and for applications and it's probably most used with PHP. Python is a good language for a lot of things. In my case, I needed some daemons, to do certain tasks. Firstly, I tried with PHP, but it is language for web pages, and it don't perform well as a daemon. So then I have to choose which language to use. C++ is powerful but very complex and there are other languages, that could fit me better for this project. I don't have a lot of idea about Perl and so I tried programming Python.

I faced two problems in last two days:

1. I had problems with installing MySQLdb

2. How to make and publish a simple website in Python?

MySQLdb

I got a few errors while trying to install MySQLdb by using python file.py install. As far as I searched the Internet, there are some workarounds but neither of them worked for me. And then it hit me. Doesn't Debian comes with apt, a great package tool?

Installing this plug-in is very easy with apt. You just need to run following command:

apt-get install python-mysqldb

And now you can use MySQL from Python.

Example:

#! /usr/bin/python

import MySQLdb
import sys #for exit

try:
conn = MySQLdb.connect(host="localhost", user="root", passwd="", db = "database", charset = "utf8", use_unicode = True)
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit(1)

cursor = conn.cursor()
cursor.execute("SET NAMES utf8;SET CHARACTER SET utf8;SET character_set_connection=utf8;")
cursor.close()

cursor = conn.cursor()

while(1):
cursor.execute("SELECT * FROM table")
if cursor.rowcount > 0:
rows = cursor.fetchall()
for row in rows:
if row == None:
break
print "Data: %s %s" % (row[0], row[1])

This is an example for UTF-8 charset encoding. I still don't know why I needed to close cursor after executing SET NAMES utf8, but this code should work without a problem.
After my daemon feetched all needed data, I need to display it through web browser. PHP came to my mind, but if one thing is in Python, why not make other thing to? So for that I needed mod_python.

How to install mod_python and use it?

It can be installed by typing following command:

apt-get install libapache2-mod-python

You need to put following code in .htaccess or your Apache's configuration file to use Python:

AddHandler mod_python .py
PythonHandler mod_python.publisher

Now just write Python code, save it as something.py and put it in your website's root.

MySQL Add User

The simplest way to add user to MySQL and grant privileges for specific database.

1. Open terminal and type "mysql -u root -p" to start MySQL client and use your root account to connect to database.

2. Type "create database my_database;" to create a database with name my_database.

3. Type:

GRANT ALL PRIVILEGES
ON my_database.*
TO 'my_user'@'localhost'
IDENTIFIED BY 'my_password'
WITH GRANT OPTION;

With this command you've created a user with name my_user and password my_password with all privileges on my_database.