How to compile MySQL C client API

This tutorial shows how to compile the MySQL 5.0.21 C client API for the FOX Board

MySQL (http://www.mysql.org) is one of the most used and faster SQL server in the world.
MySQL C client API ( http://dev.mysql.com/doc/refman/5.0/en/c.html ) are a set of functions in C that enable your C applications to make SQL queries to a remote MySQL Server across a TCP/IP connection.

Using the MySQL C client API on the FOX Board it is possible to build a powerful gateway between external devices like lights, switches, display, etc. and the values stored in a SQL database.

An example is to generate an SQL "INSERT" every time a switch is pressed or show the results of a complex SQL "SELECT" directly on a LCD display to see the live values of total sales amounts from your ecommerce site.

How to compile step by step the MySQL C API on the FOX SDK

Go to your SDK directory and type the usual:
```
$ source init_env
```
Download the MySQL 5.0.21 sources from http://dev.mysql.com/downloads/mysql/5.0.html then type:
```
$ tar zxvf mysql-5.0.21.tar.gz
$ cd mysql-5.0.21
```
Download in this directory (mysql-5.0.21) the file mysql-5.0.21-fox.patch and type:
```
$ patch -p1 < mysql-5.0.21-fox.patch
```
Open the file configure.fox and check if ROOTFS is set to the right SDK directory path. Then type:
```
$ autoconf
$ sh configure.fox
$ make
```
If autoconf doesn't run, check if the autotools are already installed on your Linux box.
Download this simple C program: @source='mysqltest1.c'

and compile it with:

$ gcc-cris -mlinux -isystem  /sdk_path/target/cris-axis-linux-gnu/ 
-lm -o mysqltest1  mysqltest1.c
/sdk_path/mysql-5.0.21/libmysql/.libs/libmysqlclient.a /sdk_path/mysql-5.0.21/zlib/.libs/libz.a 
$ cris-strip mysqltest1

You will obtain a file of about 400Kb because the mysql library are linked statically.

Copy the executable file on the FOX Board:
```
$ scp mysqltest1root@192.168.0.90:/var
```
Login to the FOX Board and run the test:
```
[root@axis-00408c012220 /var]97# ./mysqltest1
5.0.21
```
If it works, you are ready to try a connection to a MySQL server.

Example 1 - An event logger

In this example we'll generate on the MySQL server one record each time we push an external switch. The record will have a timestamp to know when the switch was pressed.

A led will indicate if the new log record has generated on the MySQL server with a single blink. More blinks indicate an error condition.

Build the hardware

The external switch is connected to the FOX Board as shown on the following schematic diagram:

Schematic to connect an external switch
Schematic to connect an external LED

Create database and table on the MySQL server

Create a new database named logger on your MySQL server and a table named switch inside it. Create inside the table switch four fields: id, time, switchid and description.

id will containt the unique record id
time will contain the switch pressed timestamp
switchid will containt a string with a switch identifier
description will containt a string with an event description

To create all of them you can use directly this SQL commands file: @source='logger.sql'

Be shure that your MySQL server is enabled to receive external transactions. To do that modify this line on /etc&msql/my.cnf:

#skip-external-locking

skip-external-locking

Be shure also that you server can accept connection on default MySQL port 3306.

This is the source of logger.c: @source='logger.c'

This is the result thet we obtain in the switch table: