Go Down

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

Sansu


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.)

sonnyyu

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

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

Sansu

#17
Apr 06, 2014, 11:14 am Last Edit: Apr 06, 2014, 11:23 am by Sansu Reason: 1
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?

sonnyyu


Sansu

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.

sonnyyu

Your own code:

Code: [Select]
String paramstring3 ="'insert into testdata (\"time\",\"pin1\",\"pin2\") Values (\""+time+"\","+pin1+","+pin2+");'";

Sansu

I've already tried this but it doesn't work...
Code: [Select]
String paramstring3 ="'insert into testdata (\"time\",\"pin1\",\"pin2\") Values (\""+A+"\","+B+");'";

sonnyyu

Missing  one parameter C;-
time=> A
pin1=>B
pin2=>C


Sansu


chetto983

#24
Apr 13, 2014, 04:15 pm Last Edit: Apr 13, 2014, 04:17 pm by chetto983 Reason: 1
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();
}

?>


Sansu

Solved, the correct syntax is
Code: [Select]
   String paramstring3 ="'insert into consumi (\"Data\",\"Ora\",\"Consumo\") Values (\""+data+"\",\""+ora+"\",\""+consumo+"\");'";

Go Up