Atmel© Certified Partner

PLEASE NOTE: This article is obsolete or related to a discontinued product.

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

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.

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
in
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)

Related links