This article is obsolete or related to an obsolete product.
Please contact us at info@acmesystems.it for any further information.

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:

#include "stdio.h"

int main()
{
  printf("%s\n", mysql_get_client_info());
  return 0;
}

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:

CREATE TABLE IF NOT EXISTS `switch` (
`id` INT NOT NULL AUTO_INCREMENT ,
`time` TIMESTAMP NOT NULL ,
`switchid` VARCHAR( 20 ) NOT NULL ,
`description` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `id` )
) TYPE = MYISAM ;

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:

#include "stdio.h"
#include "stdlib.h"
#include "unistd.h"    
#include "sys/ioctl.h"
#include "fcntl.h"     
#include "asm/etraxgpio.h"
#include "include/mysql.h" 

#define def_host_name   "192.168.0.100" // Put here the IP address of your MySQL server
#define def_user_name   "fox"           // Put here your MySQL user
#define def_password    "fox"           // Put here your MySQL password
#define def_db_name     "logger"


MYSQL  *conn;
int fd;

void led_on() {
  int iomask;
  iomask=1<<25;
  ioctl(fd,_IO(ETRAXGPIO_IOCTYPE,IO_SETBITS),iomask);
}

void led_off() {
  int iomask;
  iomask=1<<25;
  ioctl(fd,_IO(ETRAXGPIO_IOCTYPE,IO_CLRBITS),iomask);
}

void led_blink(int number) {
  int i;
  for (i=0;i<number;i++) 
  {  
    led_on();
    usleep(100000);
    led_off();
    usleep(100000);
  }
}

int get_switch() { 
  int iomask;
  int value;

  iomask=1<<16;
  value=ioctl(fd, _IO(ETRAXGPIO_IOCTYPE, IO_READBITS));

  if ((value&iomask)==0) return 1;
  else return 0;
}

int main (int argc, char *argv[])
{
  if ((fd = open("/dev/gpiog", O_RDWR))<0) {
    printf("Open error on /dev/gpiog\n");
    exit(0);
  }
  
  conn = mysql_init (NULL);
  if (conn == NULL)
  {
   fprintf (stderr, "mysql_init() failed (probably out of memory)\n");
   led_blink(6);
   exit (1);
  }
  if (mysql_real_connect (
    conn,          /* pointer to connection handler */
    def_host_name, /* host to connect to */
    def_user_name, /* user name */
    def_password,  /* password */
    def_db_name,   /* database to use */
    0,             /* port (use default) */
    NULL,          /* socket (use default) */
    0)             /* flags (none) */
    == NULL)
  {
    fprintf (stderr, "mysql_real_connect() failed:\n");
    fprintf (stderr, "Error %u (%s)\n", mysql_errno (conn), mysql_error (conn));
    led_blink(5);
    exit (1);
  }

  while (1) {
    if (get_switch()) {
      led_on();
      if (mysql_query (conn, "INSERT INTO switch (switchid,description) VALUES ('IOG16','Switch pressed')") != 0)
      {
        printf("INSERT statement failed\n");
        led_blink(4);
        exit (1);
      }
      else
      {
        printf ("INSERT statement succeeded: %lu rows affected\n",(unsigned long) mysql_affected_rows (conn));
      }
      led_blink(1);
      while(get_switch());
    }
  }

  mysql_close (conn);
  close(fd);
  exit (0);
}

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

mysql> select * from switch;
+----+----------------+----------+----------------+
| id | time           | switchid | description    |
+----+----------------+----------+----------------+
|  1 | 20060530074807 | IOG16    | Switch pressed |
|  2 | 20060530074809 | IOG16    | Switch pressed |
|  3 | 20060530074811 | IOG16    | Switch pressed |
|  4 | 20060530074812 | IOG16    | Switch pressed |
|  5 | 20060530074813 | IOG16    | Switch pressed |
|  6 | 20060530074813 | IOG16    | Switch pressed |
|  7 | 20060530074814 | IOG16    | Switch pressed |
|  8 | 20060530074815 | IOG16    | Switch pressed |
|  9 | 20060530074815 | IOG16    | Switch pressed |
+----+----------------+----------+----------------+
9 rows in set (0.00 sec)