MySQL Client

Table of Contents

  1. Packages
  2. Library
  3. License

Using this package you can execute SQL requests on a MySQL server directly from your mbed. It uses the Networking stack.

Packages

Precompiled version:

» Import this programMySQLClient

Last commit 05 Aug 2010 by user Donatien Garnier

Library

Architecture

This MySQL client implements a limited subset of the MySQL internal client/server protocol (including authentication), for server versions 4.1 and newer.

Includes

#include "MySQLClient.h"

Reference

» Import this program
NetServicesSource - MySQLClient Class Reference

Public Member Functions

  MySQLClient ()
  Instantiates the MySQL client.
MySQLResult   open ( Host &host, const string &user, const string &password, const string &db, void(*pMethod)( MySQLResult ))
  Opens a connection to a server.
template<class T >
MySQLResult   open ( Host &host, const string &user, const string &password, const string &db, T *pItem, void(T::*pMethod)( MySQLResult ))
  Opens a connection to a server.
MySQLResult   sql (string &sqlCommand)
  Executes an SQL command.
MySQLResult   exit ()
  Closes the connection to the server.
void  setTimeout (int ms)
  Setups timeout.
virtual void  poll ()
  This method can be inherited so that it is called on each Net::poll() call.

Protected Member Functions

void  close ()
  This flags the service as to be destructed if owned by the pool.

Example

This example inserts a record into a table named 'Test' containing one text column named 'Test'. Configure the parameters at the top of the example accordingly to your setup.

#include "mbed.h"
#include "EthernetNetIf.h"
#include "MySQLClient.h"

#define SQL_SERVER   ""
#define SQL_USER     ""
#define SQL_PASSWORD ""
#define SQL_DB       ""

EthernetNetIf eth; 
MySQLClient sql;

MySQLResult sqlLastResult;
void onMySQLResult(MySQLResult r)
{
  sqlLastResult = r;
}

int main()
{
  printf("Start\n");

  printf("Setting up...\n");
  EthernetErr ethErr = eth.setup();
  if(ethErr)
  {
    printf("Error %d in setup.\n", ethErr);
    return -1;
  }
  printf("Setup OK\n");
  
  Host host(IpAddr(), 3306, SQL_SERVER);
  
  //Connect
  sqlLastResult = sql.open(host, SQL_USER, SQL_PASSWORD, SQL_DB, onMySQLResult);
  while(sqlLastResult == MYSQL_PROCESSING)
  {
    Net::poll();
  }
  if(sqlLastResult != MYSQL_OK)
  {
    printf("Error %d during connection\n", sqlLastResult);
  }
  
  //SQL Command
  //Make command
  char cmd[128] = {0};
  const char* msg="Hello World!";
  sprintf(cmd, "INSERT INTO Test (Test) VALUES('%s')", msg);
  
  //INSERT INTO DB
  string cmdStr = string(cmd); 
  sqlLastResult = sql.sql(cmdStr);
  while(sqlLastResult == MYSQL_PROCESSING)
  {
    Net::poll();
  }
  if(sqlLastResult != MYSQL_OK)
  {
    printf("Error %d during SQL Command\n", sqlLastResult);
  }
  
  sql.exit();
  
  while(1)
  {
  
  }
  
  return 0;
}

This program can be imported from here:

» Import this programMySQLClientExample

Last commit 09 Jul 2010 by user Donatien Garnier

License



Post a comment Ask a question
Last modified 28 Jul 2010, by user Donatien Garnier. 10 comments

10 comments:

user Terry Neckar
01 Feb 2011

Any examples on retrieving rows on a SELECT statement? I've worked with MySQL before and would use:

while(MySQL.FetchNext()) { ulPermissions = MySQL.Field("AUTH").asLong(); }

user Deniz Akkaya
07 Aug 2011

I am also highly interested for Select statement. Any progress for Select?

user kyle fong
15 Sep 2011

how to get the host's ip_addr?????

user Klaus Kastler
21 Jan 2012

I got everytime 2 failures...

Error 2 during connection and Error 3 during SQL Command.

I don't know why and what does this mean? I use MySQL Server 5.1

user HACK KUET
20 Feb 2012

Important:

Host host(IpAddr(), 3306, SQL_SERVER);

In this line what is meant by IpAddr() ? I have to give the Ip address of the host? Or just keep that line unchanged?

user nick karakotas
16 Mar 2012

Hi,

After converting the Code to C I found some problems in the Send Auth:

if( m_len - (m_pPos - m_buf) != 44)
{
   //We only support protocol >= mysql-4.1
    DBG("Message after pcsz has wrong len (%d != 44)...\n", m_len - (m_pPos - m_buf));
    onResult(MYSQL_PRTCL);
    return;
  }

Should be changed to:

        uint16_t serverFlags = *((uint16_t*)&m_pPos[13]);

	if( ! (serverFlags & (CLIENT_RESERVED | CLIENT_PROTOCOL_41	| CLIENT_SECURE_CONNECTION  )) )   //  New 4.1 protocol
	{
		//We only support protocol >= mysql-4.1
		DEBUGF(LOG,("We only support protocol >= mysql-4.1\n"));
		onResult(MYSQL_PRTCL);
                return;
	}

A server might send server capabilities of : 0xf7ff and not 0x2c82 as expected in the code.

Regards, Nick

user Fergal O'Hanlon
14 Oct 2012

Hi Guys,

I am hoping you may be able to help me. I cannot get past the error 2 when using this program no matter what server I try to connect to. (I have created the same table on several servers and keep getting an error 2). I created a LAMP server on a Raspeberry Pi and get an error 2 when also trying the above code. I have created and rechecked several times users to the mySQL database through the privileges section of phpmyadmin and still am encountering the error 2 code. Do you have any suggestions of where to look next?

Many thanks

Fergal.

user Zakaria Yahi
05 Nov 2012

user Fergal O'Hanlon wrote:

Hi Guys,

I am hoping you may be able to help me. I cannot get past the error 2 when using this program no matter what server I try to connect to. (I have created the same table on several servers and keep getting an error 2). I created a LAMP server on a Raspeberry Pi and get an error 2 when also trying the above code. I have created and rechecked several times users to the mySQL database through the privileges section of phpmyadmin and still am encountering the error 2 code. Do you have any suggestions of where to look next?

Many thanks

Fergal.

I got the same error, can someone help please?

user Adriano Lopes
17 Dec 2012

user Zakaria Yahi wrote:

user Fergal O'Hanlon wrote:

Hi Guys,

I am hoping you may be able to help me. I cannot get past the error 2 when using this program no matter what server I try to connect to. (I have created the same table on several servers and keep getting an error 2). I created a LAMP server on a Raspeberry Pi and get an error 2 when also trying the above code. I have created and rechecked several times users to the mySQL database through the privileges section of phpmyadmin and still am encountering the error 2 code. Do you have any suggestions of where to look next?

Many thanks

Fergal.

I got the same error, can someone help please?

user Zakaria Yahi wrote:

user Fergal O'Hanlon wrote:

Hi Guys,

I am hoping you may be able to help me. I cannot get past the error 2 when using this program no matter what server I try to connect to. (I have created the same table on several servers and keep getting an error 2). I created a LAMP server on a Raspeberry Pi and get an error 2 when also trying the above code. I have created and rechecked several times users to the mySQL database through the privileges section of phpmyadmin and still am encountering the error 2 code. Do you have any suggestions of where to look next?

Many thanks

Fergal.

I got the same error, can someone help please?

Me too, got the same error.

It seems that this code doesn't work anymore.

user Awstriker _
01 Feb 2013

Does this work with MySQL 5.5.24? Keep getting Error 2 (Protocol)