Yún write and read from MySQL database

Hello

Before I start this is the first time that I work whit c++
My question is how can I write and read from a MySQL database through WiFi because i need the database for my webpage.
At the moment I have an local database of MySQL on xampp, and in that database I would see all the values of my sensors (analog and digital) and control outputs of the Yún board.
is there someone who had a sketch for this that not communicate through Temboo.
I already source on Google but i didn't find any usable.

You will need a mysql connector in arduino side.

I don´t know if you could use wifi for this, give a try
You could also use php script running in your xamp with get method to pass or read values for MySql

You can also install a mysql server instance on the yun itself (beware of the disk space utilization)
Or you can have your sketch to call a python script that connects to your mysql server
Or (as we do in Officine Arduino for our doorphone/access control system) you can use sqlite, that, as the name implies, is lighter

I used chuck's connector with ethernet shield.
I tried to use Yun as well, replacing the Ethernetclient calls with YunClient function but unfortunately I did'nt succeded.
The lib halts on the first available() function call. I did'nt go deeper for now.
Did any of you gents succeded ?
thanks

i think that sqlite its gonna be, but how to instal that on the yun?

Take a look at this

http://www.ernstc.dk/arduino/Yun_start.htm

I'm developing a similar protect those days. My arduino yun has a temperature sensor and sends the temperature data every few seconds to a mysql database. The mysql database is in an remote vps server, out in the internet (in another country I think).

I tried a lot of ways (mysql connector and so on) but after a lot of test, the easier way to me is to have a little php script in my vps which can receive http post requests and it works as an rest interface between the arduino yun and the mysql database ( I don't like the idea of opening my dB to non local connections).

So, in your sketch you use the process library and you execute a curl command in the linino side of the arduino yun. This curl command is a http post request. For example http://my-remote-webserver/my-script.php and you can pass some parameters in the header of the post request like temperatures and so on.

The php script receives the request, inserts the data in the mysql database and can send data back to, in json or a simple text.

This works very well. I use the same script to do the same thing with a sqlite database in the lining only changing a a couple of lines. I'm writing from my bed with my tablet now, but if you are interested tomorrow I can publish the php script.
Best regards.

As I promised yesterday, here is the snipet how I do the access to a remote database.

First, in the sketch, use the following function to do a curl call:

//This function executes a curl command. You can use it to make a rest request.
String doCurl(String COM) {
  Process p;
  p.runShellCommand(COM);
  while (p.running());//Wait for the process to finish
  String result;

  if (p.exitValue() == 0) {
    while (p.available() > 0) {
      char c = p.read();
      result += c;
    }
  }
  else {
    resultado = F("ErrorCurl:");
    result += p.exitValue();
  }
  return result;
}

For example, you want to send to your server a temperature:

float temp=12.2;

//The curl command is curl --data "db=m&t=i_t&te=12.2" http://my_web_server/my_script.php
//We must escape the "
//db=m this is for switching between a mysql database and a sqlite database
//t=i_t this means "task=insert temperature"
//te=12.2 this is the temperature we want to insert

 String  s = F("curl --data \"db=m&t=i_t&te=");
 s += tem_act;
 s +="\" http://my_web_Server/my_script.php";

 String result=doCurl(command);

Finally, the php script that is on your server:

<?php
try {
    if (isset($_POST["t"]) && $_POST["t"] != "") {

        #t is the task we want to do
        $task = $_POST["t"];

        #bd is the database (m for mysql, s for sqlite)
        if (isset($_POST["db"]) && $_POST["db"] != "") {
            $db_type = $_POST["db"];
            if ($type_db === "m") {
                $link = new \PDO('mysql:host=localhost;dbname=db_name', 'db_user', 'password', array(
                    \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
                    \PDO::ATTR_PERSISTENT => false,
                    \PDO::MYSQL_ATTR_INIT_COMMAND => 'set names utf8mb4'
                ));
            } else {
                $link = new \PDO('sqlite:/mnt/sd/sqlite_file_in_the_yun_sd');
            }

             #Task i_t= insert temperature in the temperature table
            if ($task === "i_t") {
                $temp = $_POST["te"]; 

                $query = "INSERT INTO  temperatures (temperature)VALUES (?)";
                $handle = $link->prepare($query);
                $handle->bindValue(1, $temp);
                $handle->execute();
                echo 'OK';
            }
            $link = null;
        } else {
            echo "DB type missing";
        }
    } else {
        echo "Task missing";
    }
    exit;
} catch (\PDOException $ex) {
    echo $ex->getMessage();
}
?>

The php script works for a sqlite database or for a mysql database. Of course the structure of the tables have to be equivalent if you want to reuse your different querys.

Yo can add more tasks or antoher taks, such as deleting, retrieving data form a table, etc. But be carefull, you can not handle in the arduino yun a resultset in Json format with 100 rows. I work only with small results (words like OK or data like strings with ten characters and so on).

There are other ways, like don't using a sketch and do all the work in a php script on the linino side, and run the php script periodically with a cron job, like discussed here: Program in the sketch side vs program in the linino side... - #17 by roquec - Arduino Yún - Arduino Forum

Of course if you use the php script on the linino to work with a sqlite database, you need to install php and sqlite.

Hope this helps you in some way.

Best regards

R. Campos.

Guys, I've been trying to write in a mysqli base with my Yun for a few days now, but without success.

Arduino code

#include <Bridge.h>
#include <Console.h>
#include <YunServer.h>
#include <YunClient.h> 
#include <DHT22.h>

DHT22 dht22(12);

YunServer server; 

void setup() {

  Bridge.begin();
  server.listenOnLocalhost();
  server.begin();
  Console.begin();
    while (!Console);
    Console.println("Ready");
}
 
void loop() {
    
  YunClient client = server.accept();
  
  dht22.readData();
  float temp = dht22.getTemperatureC();
  
  if (client) {
      Console.println("connected");
      Console.println("sending request");
    
      client.print("GET/ addmysql.php?temp=");
      Console.print("GET/ addmysql.php?temp=");
      client.print(temp);
      Console.print(temp);
      client.println(" HTTP/1.1");
      Console.println(" HTTP/1.1");
      client.println("Host: 192.168.1.111/sd/dht22mysql");
      client.println("Connection: close");
      client.println();
      Console.println("Done!");
      client.stop();
  }
  else {
  Console.println("cannot connect");
  }
  
  delay(3000);

}

PHP code

<?php

$cxn = mysqli_connect('localhost','root','xxxx','termostat')
	or die("Can't connect!");

mysqli_select_db($cxn,'termostat')
	or die ("Unable to select a database!");

$temp = ($_GET['temp']);	
	
$qry = "INSERT INTO temperatura (temp) VALUES ($temp)";
mysqli_query($cxn,$qry);
?>

I think is a "client" problem in arduino sketch. Because if a run manualy an URL like "192.168.1.111/sd/dht22mysql/addmysql?temp=22.34" the values ​​are saved in the database.

Can somebody please help me?

I have done something similar.
I just used the Process example sketch and sprintf to make the URL:

/*
  Running process using Process class. 
 
 This sketch demonstrate how to run linux processes
 using an Arduino Yún. 
 
 created 5 Jun 2013
 by Cristian Maglie
 
 This example code is in the public domain.
 
 http://arduino.cc/en/Tutorial/Process

 */

#include <Process.h>
char buffer[50];
int val=1200;


void setup() {
  // Initialize Bridge
  Bridge.begin();
  
  // Initialize Serial
  Serial.begin(9600);
  
  // Wait until a Serial Monitor is connected.
  while (!Serial);

  // run various example processes
  //runCurl(val);
  //runCpuInfo();
}

void loop() {
  Serial.println("Starting");
  sprintf (buffer, "192.168.62.52/sd/test/upd_testdb.php?tekst=%d", val);  
   val++;
   Serial.println(buffer);
   runCurl();
   delay(10000);
  // Do nothing here.
}

void runCurl() {
  // Launch "curl" command and get Arduino ascii art logo from the network
  // curl is command line program for transferring data using different internet protocols
  Process p;		// Create a process and call it "p"
  p.begin("curl");	// Process that launch the "curl" command
  p.addParameter(buffer); // Add the URL parameter to "curl"

  p.run();		// Run the process and wait for its termination

  // A process output can be read with the stream methods
  while (p.available()>0) {
    char c = p.read();
    Serial.print(c);
  }
  // Ensure the last bit of data is sent.
  Serial.flush();
}

If that IP address is the one of the yun where the sketch is running, I strongly suggest you to use "localhost" instead. Otherwise, next time you restart your home router, your sketch will stop working (because the router will likely give the yun a different IP address)

Erni:
I have done something similar.

Thank you, Erni!
But I'm not familiar with Process. What is a difference between Process and Arduino sketches?

Federico Fissore, I have a static ip :wink:
Did you have any idea why my sketch isn't working?

Here's how I ended up doing it:

cheers,

Pete

Something new to work on it, thank you Pete! :wink:

But even so I would like to ask someone to take a look at my sketch. I would like to know where I made a mistake.

I haven't. Try using the "echo" command instead of curl in order to see what the linino side is actually receiving
Is that fixed ip address the yun one?

alnitak:
Here's how I ended up doing it:

Adventures with Arduino Yun: Setting up the Yun to write pin data to an onboard database

cheers,

Pete

How did you create the database on Arduino? I've already installed sqlite and php5 but i don't know how to open the shell to create a database ( i use windows o.s.)

Installing and configuring MySQL serverat Arduino Yun, To enable MySQL in PHP install:

http://wiki.openwrt.org/doc/howto/lamp

Thank you...
I've created a database called "test1.db" at /mnt/sda1/arduino/test/www and i'm using this arduino code

/*get time and pin1 and pin2 state and run a linux process on the linino server to write to database table
*/
#include <Bridge.h>
#include <Process.h>

// how often to run the pin read (in milliseconds)
const unsigned long RUN_INTERVAL_MILLIS = 60000; 

// the last time we ran the pin read (initialized to 60 seconds ago,
// so the pins are read immediately when we start up)
unsigned long lastRun = (unsigned long)-60000;
// variables for setting up time 
Process date;                 // process used to get the date
String timeString;


void setup() {
  // put your setup code here, to run once:
 
  // for debugging, wait until a serial console is connected. This can be removed after the code is debugged, along with all the Serial. commands
  Serial.begin(9600);
  delay(4000);
  while(!Serial);

  Serial.print("Initializing the bridge...");
  Bridge.begin();
  Serial.println("OK");

  // run an initial date process. Should return:
  // hh:mm:ss :
  if (!date.running())  {
    date.begin("date");
    date.addParameter("+%D +%T");
    date.run();
    Serial.println(date.readString());
  }

}//end setup

void loop() {
  // put your main code here, to run repeatedly:
 // get the number of milliseconds this sketch has been running
  unsigned long now = millis();

  // run again if it's been RUN_INTERVAL_MILLIS milliseconds since we last ran
  if (now - lastRun >= RUN_INTERVAL_MILLIS) {

    // remember 'now' as the last time we checked the pins
    lastRun = now;
    // get the date and time
    if (!date.running())  {
      date.begin("date");
      date.addParameter("+%D +%T");
      date.run();
    }//end if
    
    // put the date and time into a string variable
    while (date.available()>0) {
      timeString = date.readString(); 
    }//end while
    
    //get the pin states and add them to the database
    String pin1 = String(getSensorValue(1));
    String pin2 = String(getSensorValue(2));
    runSqlQuery(timeString,pin1,pin2);
    } //end if
}// end loop
 // function to run the appending of the data to the database
 unsigned int runSqlQuery(String time, String pin1, String pin2){
   Process p;
   String cmd = "sqlite3 ";
   String paramstring1 = "-line ";
   // set the path and name of your database here
   String paramstring2 ="/mnt/sda1/arduino/www/test/test1.db ";
   // insert a row with time and sensor data 
   String paramstring3 ="'insert into testdata (\"time\",\"pin1\",\"pin2\") Values (\""+time+"\","+pin1+","+pin2+");'";
   // get the error code
   String paramstring4 =" ; echo $?";
   p.runShellCommand(cmd + paramstring1 + paramstring2 + paramstring3+ paramstring4);
   Serial.println("process run");
     // A process output can be read with the stream methods
   while (p.available()>0) {
    char c = p.read();
    Serial.print(c);
  }
  // Ensure the last bit of data is sent.
  Serial.flush();

  }
//function to get sensor values with debugging information
int getSensorValue(int pinNum) {
  Serial.print("Reading pin "+ String(pinNum)+ "value...");
  int value = analogRead(pinNum);
  Serial.println("OK");
  return value;
}

but the table "testdata" is always empty....where did I go wrong?

You are working on sqlite not mysql server.

http://forum.arduino.cc/index.php?topic=204451.msg1505801#msg1505801

I've solved it, it was just a problem with the serial

Serial.begin(9600);
  delay(4000);
  while(!Serial);

Now i'm trying to insert data in the database with a my function but i don't know how to insert a variable instead of a string....
for example String paramstring3 ="'insert into allarme (\"Evento\",\"Data\") Values (\"A\",\"B\");'";
i want to use variables A and B instead of strings "A" and "B" but i don't know how.