Arduino Forum

Products => Arduino Yún => Topic started by: bramhalt on Oct 15, 2013, 10:44 pm

Title: Yún write and read from MySQL database
Post by: bramhalt on Oct 15, 2013, 10:44 pm
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.



Title: Re: Yún write and read from MySQL database
Post by: hugo007 on Oct 16, 2013, 12:00 am
You will need a mysql connector in arduino side.
http://drcharlesbell.blogspot.pt/2013/04/introducing-mysql-connectorarduino_6.html (http://drcharlesbell.blogspot.pt/2013/04/introducing-mysql-connectorarduino_6.html)
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

Title: Re: Yún write and read from MySQL database
Post by: federicofissore on Oct 16, 2013, 01:02 pm
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
Title: Re: Yún write and read from MySQL database
Post by: brunialti on Oct 19, 2013, 04:43 pm
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
Title: Re: Yún write and read from MySQL database
Post by: bramhalt on Nov 12, 2013, 07:08 pm
i think that sqlite its gonna be, but how to instal that on the yun?
Title: Re: Yún write and read from MySQL database
Post by: Erni on Nov 12, 2013, 07:44 pm
Take a look at this

http://www.ernstc.dk/arduino/Yun_start.htm
Title: Re: Yún write and read from MySQL database
Post by: Roque Campos on Nov 12, 2013, 11:50 pm
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.
Title: Re: Yún write and read from MySQL database
Post by: Roque Campos on Nov 13, 2013, 09:21 am
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:

Code: [Select]

//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:

Code: [Select]

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:

Code: [Select]

<?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: http://forum.arduino.cc/index.php?topic=196687.msg1460998#msg1460998

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.
Title: Re: Yún write and read from MySQL database
Post by: spirit1 on Nov 28, 2013, 11:44 am
Guys, I've been trying to write in a mysqli base with my Yun for a few days now, but without success.

Arduino code
Code: [Select]
#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
Code: [Select]
<?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?
Title: Re: Yún write and read from MySQL database
Post by: Erni on Nov 28, 2013, 04:15 pm
I have done something similar.
I just used the Process example sketch and sprintf to make the URL:

Code: [Select]
/*
  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();
}


Title: Re: Yún write and read from MySQL database
Post by: federicofissore on Nov 28, 2013, 04:23 pm
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)
Title: Re: Yún write and read from MySQL database
Post by: spirit1 on Nov 28, 2013, 05:36 pm

I have done something similar.


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


If that IP address is the one of the yun where the sketch is running, I strongly suggest you to use "localhost" instead.


Federico Fissore, I have a static ip ;)
Did you have any idea why my sketch isn't working?
Title: Re: Yún write and read from MySQL database
Post by: alnitak on Nov 28, 2013, 06:31 pm
Here's how I ended up doing it:

http://yunadventures.blogspot.com/2013/11/setting-up-yun-to-write-pin-data-to.html

cheers,

Pete
Title: Re: Yún write and read from MySQL database
Post by: spirit1 on Nov 28, 2013, 06:52 pm
Something new to work on it, thank you Pete! ;)

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.
Title: Re: Yún write and read from MySQL database
Post by: federicofissore on Dec 03, 2013, 09:49 am
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?
Title: Re: Yún write and read from MySQL database
Post by: Sansu on Apr 05, 2014, 04:00 pm

Here's how I ended up doing it:

http://yunadventures.blogspot.com/2013/11/setting-up-yun-to-write-pin-data-to.html

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.)
Title: Re: Yún write and read from MySQL database
Post by: sonnyyu on Apr 05, 2014, 04:41 pm
Installing and configuring MySQL serverat Arduino Yun, To enable MySQL in PHP install:

http://wiki.openwrt.org/doc/howto/lamp (http://wiki.openwrt.org/doc/howto/lamp)
Title: Re: Yún write and read from MySQL database
Post by: Sansu on Apr 06, 2014, 11:14 am
Thank you...
I've created a database called "test1.db" at  /mnt/sda1/arduino/test/www and i'm using this arduino code
Code: [Select]
/*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?
Title: Re: Yún write and read from MySQL database
Post by: sonnyyu on Apr 06, 2014, 04:19 pm
You are working on sqlite not mysql server.

http://forum.arduino.cc/index.php?topic=204451.msg1505801#msg1505801 (http://forum.arduino.cc/index.php?topic=204451.msg1505801#msg1505801)
Title: Re: Yún write and read from MySQL database
Post by: Sansu on Apr 06, 2014, 07:55 pm
I've solved it, it was just a problem with the serial
Code: [Select]
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
Code: [Select]
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.
Title: Re: Yún write and read from MySQL database
Post by: sonnyyu on Apr 06, 2014, 08:23 pm
Your own code:

Code: [Select]
String paramstring3 ="'insert into testdata (\"time\",\"pin1\",\"pin2\") Values (\""+time+"\","+pin1+","+pin2+");'";
Title: Re: Yún write and read from MySQL database
Post by: Sansu on Apr 06, 2014, 08:29 pm
I've already tried this but it doesn't work...
Code: [Select]
String paramstring3 ="'insert into testdata (\"time\",\"pin1\",\"pin2\") Values (\""+A+"\","+B+");'";
Title: Re: Yún write and read from MySQL database
Post by: sonnyyu on Apr 06, 2014, 08:32 pm
Missing  one parameter C;-
time=> A
pin1=>B
pin2=>C

Title: Re: Yún write and read from MySQL database
Post by: Sansu on Apr 06, 2014, 08:58 pm
I use a table with 2 columns
Title: Re: Yún write and read from MySQL database
Post by: chetto983 on Apr 13, 2014, 04:15 pm
i do like this:

send data:


arduino code:
Code: [Select]

if (   get_status() == true &&   togglesensore  == true) {




#ifdef debug
   Serial.println ("start Send");
#endif
   digitalWrite(status_led, ON);
   settemp = String ( temperature, DEC);
   setumid = String (umidity, DEC);
   String nowdb = data_ora();


   Send.begin("curl");  // Process that launch the "curl" command

   Send.addParameter( sito + "/inserisci.php?tempo=" + nowdb + "&temperatura=" + settemp + "&umidita=" + setumid); // Add the URL parameter to "curl"

   Send.runAsynchronously();// Run the process and wait for its termination
  delay(2000);

   String risp;
#ifdef debug
   Serial.println ("send run");
   Serial.println(Send.exitValue());
#endif

   if (Send.exitValue() == 0 && Send.available() > 0) {

     risp = Send.readString();
     Send.close();
   }



   else
   {
#ifdef debug
     Serial.println("send timeout");
#endif
Send.close();

   }


PHP:

Code: [Select]


<?php


include(
"configurazione.php");



if(@$_REQUEST['temperatura']!="")
{

        
$tempo=mysql_real_escape_string($_REQUEST['tempo']);
$temperatura=mysql_real_escape_string($_REQUEST['temperatura']);
$umidita=mysql_real_escape_string($_REQUEST['umidita']);
$stato=mysql_real_escape_string($_REQUEST['stato']);
$setpoint=mysql_real_escape_string($_REQUEST['setpoint']);


mysql_query("INSERT INTO `temperatura`(`ID`, `time`, `temperatura`, `umidita`,`Stato`, `Setpoint` ) VALUES (\"\",'$tempo', $temperatura,$umidita,'$stato',$setpoint);");

echo "dati inseriti";

}
else
{
echo "errore";
echo $tempo;
echo $temperatura;
echo $umidita;
die();
}

?>

Title: Re: Yún write and read from MySQL database
Post by: Sansu on Apr 16, 2014, 01:42 pm
Solved, the correct syntax is
Code: [Select]
   String paramstring3 ="'insert into consumi (\"Data\",\"Ora\",\"Consumo\") Values (\""+data+"\",\""+ora+"\",\""+consumo+"\");'";