FOX Board G20
FOX Board LX832
Easy Guardian
SMS FoxBox
Acme Systems srl

FOX Board LX832 is discontinued
To be informed about its availability and prices please CONTACT US
To know more about the new FOX Board G20 GO HERE

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:

	mysqltest1.c
#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:

logger.sql

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:

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: