Go Down

Topic: Yún write and read from MySQL database (Read 24106 times) previous topic - next topic

bramhalt

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.




hugo007

You will need a mysql connector in arduino side.
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

Debian,Mint,Ubuntu
Arduino Mega 2560
Arduino Nano
Arduino Duemilanove
MAC OS Montain Lion
Raspberry PI Model B

federicofissore

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

brunialti

#3
Oct 19, 2013, 04:43 pm Last Edit: Oct 19, 2013, 05:54 pm by brunialti Reason: 1
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

bramhalt

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

Erni

Take a look at this

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

Roque Campos

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.

Roque Campos

#7
Nov 13, 2013, 09:21 am Last Edit: Nov 13, 2013, 09:24 am by Roque Campos Reason: 1
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.

spirit1

#8
Nov 28, 2013, 11:44 am Last Edit: Nov 28, 2013, 11:52 am by spirit1 Reason: 1
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?

Erni

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();
}



federicofissore

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)

spirit1

#11
Nov 28, 2013, 05:36 pm Last Edit: Nov 28, 2013, 05:38 pm by spirit1 Reason: 1

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?

alnitak

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

spirit1

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.

federicofissore

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?

Go Up