Home About us Questions Send money Contacts Terms


http://acme.systems

Please note:This article is obsolete or related to an obsolete 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




Acme Systems srl
Via Aldo Moro 53 - 00055 Ladispoli (RM) - Italy
P.IVA/C.F. 08114831004
Tel +39.06.99.12.187 - Fax +39.06.622.765.31
http://www.acmesystems.it
Iscritta al Registro delle Imprese di Roma al n. 08114831004


Atmel© Certified Partner

Documentation Terms of Use
Acme Systems provides this documentation "as is" without warranty or guarantees of any kind. The maintainer of this site (Sergio Tanzilli), has gone to a great deal of effort into making this documentation as correct as possible. Acme Systems does not provide any direct support for the Open Source preinstalled software but does provide, through these pages and forum posts, all of the information required to obtain the sources, install, use and update the Open Source software that can be run on the Acme platforms. Please note that all of the preinstalled software, used on the Acme Systems products, are Open Source and you will need to check the license terms provided (usually the GPL) by each author before using it in any commercial or non-commercial product, by yourself. Before sending emails or calling the Acme staff please note that WE ARE MAINLY HARDWARE DESIGNERS and NOT LINUX GURUS and therefore ask that you post your questions directly to the forum listed below and ensure that all of the site contributors and large software community will read and reply to your questions.

This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License.
Creative Commons License