Insert data from arduino into MYSQL Database problem

Hello!

For a cuple of days, I am strugling with this… and I almoust made it, but… It is always a but…:frowning:
I want to put my temp and humidity sensors from 5 devices into a MYSQL database.

I will show you the code.
I am trying to achieve this with Mysql connector for arduino library, instead of the PHP solution, wich didn’t work for me, no matter how many examples from the internet I tryed. So, here I am with mysql connector library, wich apparently does the job.

My problems is: I have 5 voids sections (for inserting data in MYSQL) When I activate all of them, somehow, and somewhere it hangs, and I have no insertion in database. It only works with 2 at the same time, wich is not enough. Why is this happening?

I will show you the code:

#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

float memoldtmpb1=20.25;
float memoldhb1=80.78;
float memoldtmpc2=10.56;
float memoldhc2=55.08;
float memoldtmpbc=20.10;
float memoldhbc=54.00;
float memoldtmpgj=12.40;
float memoldhgj=54.00;
float memoldtmppi=28.10;
float memhpi=0;


char tmpb1[6];
char hb1[6];
char tmpc2[6];
char hc2[6];
char tmpbc[6];
char hbc[6];
char tmpgj[6];
char hgj[6];
char tmppi[6];
char hpi[6];

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
byte arduinoIP[] = { 192, 168, 0, 100 };    
byte gatewayIP[] = { 192, 168, 0, 1 };
byte subnetIP[] = { 255, 255, 255, 0 };
byte dnsIP[] = { 8, 8, 8, 8 };
IPAddress server_addr(5, 15, 54, 88);  // IP of the MySQL *server* here

char user[] = "arduino";              // MySQL user login username
char password[] = "myarduino";        // MySQL user login password

EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  Serial.begin(115200);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr, arduinoIP, dnsIP, gatewayIP, subnetIP);
  Serial.println("Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
 
    delay(1000);
  }
  else
    Serial.println("Connection failed.");
}


void loop() {
  delay(2000);
dtostrf(memoldtmppi, 5 , 2, tmppi);
dtostrf(memhpi, 5, 2, hpi);

dtostrf(memoldtmpb1, 5 , 2, tmpb1);
dtostrf(memoldhb1, 5, 2, hb1);

dtostrf(memoldtmpc2, 5 , 2, tmpc2);
dtostrf(memoldhc2, 5, 2, hc2);

dtostrf(memoldtmpbc, 5 , 2, tmpbc);
dtostrf(memoldhbc, 5, 2, hbc);

dtostrf(memoldtmpgj, 5 , 2, tmpgj);
dtostrf(memoldhgj, 5, 2, hgj);

Serial.println(memoldtmpb1);
      Serial.println(memoldhb1);
      Serial.println("Values after converting to string:");
      Serial.println(tmpb1);
      Serial.println(hb1);
      Serial.println(tmpc2);
      Serial.println(hc2);
      
      Serial.println("Recording data.");

     insertb1();
     insertc2();
     insertbc();
    // insertgj();
    // insertpi();
}

void insertb1() {
      const char INSERT_SQL[] = {" INSERT INTO arduino_roomtemp.roomtemp (LOCATION, TEMPERATURE, HUMIDITY) VALUES ('ETAJ-DORMITOR', %s , %s)" };
      char query[255];
      sprintf(query, INSERT_SQL, tmpb1, hb1);
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      cur_mem->execute(query);
      delete cur_mem;
      Serial.println("Data saved!");
      
}
 
void insertc2() {      
      const char INSERT_SQL[] = {"INSERT INTO arduino_roomtemp.roomtemp (LOCATION, TEMPERATURE, HUMIDITY) VALUES ('ETAJ-CAMERA', %s , %s)"};
      char query[255];
      sprintf(query, INSERT_SQL, tmpc2, hc2);
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      cur_mem->execute(query);
      delete cur_mem;
      
      }

void insertbc() {      
      delay(1200);
      const char INSERT_SQL[] = {"INSERT INTO arduino_roomtemp.roomtemp (LOCATION, TEMPERATURE, HUMIDITY) VALUES ('BUCATARIE', %s , %s)"};
      char query[255];
      sprintf(query, INSERT_SQL, tmpbc, hbc);
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      cur_mem->execute(query);
      delete cur_mem;
      
      }

void insertgj() {      
      const char INSERT_SQL[] = {"INSERT INTO arduino_roomtemp.roomtemp (LOCATION, TEMPERATURE, HUMIDITY) VALUES ('GARAJ', %s , %s)"};
      char query[255];
      sprintf(query, INSERT_SQL, tmpgj, hgj);
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      cur_mem->execute(query);
      delete cur_mem;
      
      }
      void insertpi() {      
      const char INSERT_SQL[] = {"INSERT INTO arduino_roomtemp.roomtemp (LOCATION, TEMPERATURE, HUMIDITY) VALUES ('POOL WATER TEMP.', %s , %s)"};
      char query[255];
      sprintf(query, INSERT_SQL, tmppi, hpi);
      MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
      cur_mem->execute(query);
      delete cur_mem;
      
      }

Hope anyone can help me… because I am exhausted now :slight_smile:

Thank you!
Adrian

I guess you simply run out of memory. Compiling your sketch shows that globals use over 1200bytes of RAM, you then allocate 255 bytes buffers and and a MYSQL cursor also uses at least around 400bytes. The 2kB RAM a UNO have (you didn't specify the type so we expect the default UNO) is filled very fast this way. A simple way to save memory is to use the F() macro in all print() and println() methods with fixed strings. That saves you more than 100 bytes. I'm quite sure the buffers for the SQL commands can be made smaller too. If you have simple tables in the MYSQL DB you can lower the MAXFIELDS constant in the library to save additional memory.

This is bad news if it's memory problem. Yes, sorry I forgot to mention, it's UNO.
Maybe I will have to abandon this solution, since this mysql library takes some resources. So, I have only the php solution.

What you don't know is, that besides this code, I will have more on this arduino. This sketch was just for testing to see if it works. Those float variables that represents temperature and humidity, arrived there by the help of some wifi modules... so I must add some aditional libraryies: VirtualWire, LiquidCrystal_I2C.

So I am preety sure the space it's too low... though the IDE showed me that I have left some memory for program space and variables to spare...

Thank you!

So I am preety sure the space it's too low... though the IDE showed me that I have left some memory for program space and variables to spare...

The IDE just shows you the RAM required by global variables, it cannot correctly calculate the complete memory requirements at compile time. And I guess you have more than enough program space (flash memory) for your sketch, but that's another type of memory.

I made some changes to the sketch, to make insertion in one void, instead of 5… but that does not save too much memory… If the sketch leaves belloy 1000 bytes of memory, then problems appear. It is clear to me now, that it’s a memory problem. If I will not managed to achieve my goal with PHP… then I will buy a MEGA. I have the whole weekend ahead.

I will not get mad if anyone cand help me with arduino code for the PHP thing…

Right not I have those 3 PHP files.

review_data.php - for displaying the data in a browser and this works and looks nice:

<?php
    // Start MySQL Connection
    include('dbconnect.php');
//


$selected = mysql_select_db($Database, $dbh);
?>

<html>
<head>
    <title>Temperature and Humidity Sensors:</title>
    <style type="text/css">
        .table_titles, .table_cells_odd, .table_cells_even {
                padding-right: 20px;
                padding-left: 20px;
                color: #000;
        }
        .table_titles {
            color: #FFF;
            background-color: #009ACD;
        }
        .table_cells_odd {
            background-color: #CCC;
        }
        .table_cells_even {
            background-color: #FAFAFA;
        }
        table {
            border: 2px solid #333;
        }
        body { font-family: "Trebuchet MS", Arial; }
    </style>
</head>

    <body>
        <h1>Temperatures and humidity data:</h1>
    <table border="0" cellspacing="0" cellpadding="4">
      <tr>
            <td class="table_titles">ID</td>
            <td class="table_titles">Date and Time</td>
            <td class="table_titles">LOCATION</td>
            <td class="table_titles">TEMPERATURE</td>
            <td class="table_titles">HUMIDITY</td>
          </tr>
<?php
    // Retrieve all records and display them
    $result = mysql_query("SELECT * FROM roomtemp ORDER BY DATETIME DESC");

    // Used for row color toggle
    $oddrow = true;

    // process every record
    while( $row = mysql_fetch_array($result) )
    {
        if ($oddrow)
        {
            $css_class=' class="table_cells_odd"';
        }
        else
        {
            $css_class=' class="table_cells_even"';
        }

        $oddrow = !$oddrow;

        echo '<tr>';
        echo '   <td'.$css_class.'>'.$row["ID"].'</td>';
        echo '   <td'.$css_class.'>'.$row["DATETIME"].'</td>';
        echo '   <td'.$css_class.'>'.$row["LOCATION"].'</td>';
        echo '   <td'.$css_class.'>'.$row["TEMPERATURE"].'</td>';
 echo '   <td'.$css_class.'>'.$row["HUMIDITY"].'</td>';
        echo '</tr>';
    }
?>
    </table>
    </body>
</html>

then i have: dbconnect.php - where are the data connection for mysql server:

<?php
$Username = "arduino";  // enter your username for mysql
$Password = "myarduino";  // enter your password for mysql
$Hostname = "localhost";      // this is usually "localhost" unless your database resides on a different server
$Database = "arduino_roomtemp"; //database name

$dbh = mysql_connect($Hostname , $Username, $Password) or die (mysql_error());;
@mysql_select_db($Database) or die (mysql_error());
?>

and the most important part is: add_data.php, and of course the arduino code, and this is very unclear to me.

add_data.php looks like:

<?php
   include("connect.php");
   
   $link=Connection();

 //$temp1=$_POST["temp1"];
 //$hum1=$_POST["hum1"];

 $query = "insert into roomtemp (LOCATION, TEMPERATURE, HUMIDITY)  values ('ETAJ-DORMITOR', '".$_GET["memoldtmpb1"]."', '".$_GET["memoldthb1"]."')"; 
$query = "insert into roomtemp (LOCATION, TEMPERATURE, HUMIDITY:)  values ('ETAJ-CAMERA', '".$_GET["memoldtmpc1"]."', '".$_GET["memoldhc1"]."')"; 
$query = "insert into roomtemp (LOCATION, TEMPERATURE, HUMIDITY)  values ('BUCATARIE', '".$_GET["memoldtmpbc"]."', '".$_GET["memoldhbc"]."')"; 
$query = "insert into roomtemp (LOCATION, TEMPERATURE, HUMIDITY)  values ('GARAJ', '".$_GET["memoldtmpgj"]."', '".$_GET["memoldhgj1"]."')"; 
$query = "insert into roomtemp (LOCATION, TEMPERATURE, HUMIDITY)  values ('POOL WATER TEMP.', '".$_GET["memoldpi"]."', '-')"; 
   
   mysql_query($query,$link);
 mysql_close($link);

   header("Location: index.php");
?>

Now… the information I found on google, are very contradictory. Some say, that for the variable field it has to be something like: .$_GET[“variablename”] and some say it should be: .$_POST["variablename].

The Arduino code, the same… POST or GET…

the arduino code looks like this:

...

if (client.connect("www.*****.*************.com",80)) { // REPLACE WITH YOUR SERVER ADDRESS
 client.println("POST /add_data.php HTTP/1.1"); 
 client.println("Host: *****.*************.com"); // SERVER ADDRESS HERE TOO
 client.println("Content-Type: application/x-www-form-urlencoded"); 
 client.print("Content-Length: "); 
 client.println(data.length()); 
 client.println(); 
 client.print(data); 
 } 

 if (client.connected()) { 
 client.stop(); // DISCONNECT FROM THE SERVER
 }

 delay(3000);

Thank you for your help!
Adrian.

Okey. I made it work with PHP. Actually it was pretty simple. Anyway, fate does that at the end maybe I must go with the MEGA… because of this:

VirtualWire library wich I use for , Wifi modules, use pins 10 11 12…I changed that to 6 7 8.
Ethernet shield uses 10 11 12 13. and 4 for SDcard.
Receiver pin for the wifi module use also a pin, 9.

So the pins left are: 2,3,5.

And there it must be an LCD, without a LCD Shield I need 6 pins… unavailable.
with LCD shield It doesen’t work. After I put the #include <LiquidCrystal_I2C.h> the ethernet shield doesen’t work anymore. So there is a conflict there.

Any help or solution?

Thank you!
Adrian.

adiculiniute:
So the pins left are: 2,3,5.

And A0, A1, A2, A3, A4, A5.

adiculiniute:
And there it must be an LCD, without a LCD Shield I need 6 pins... unavailable.

My I2C LCD only uses SDA and SCL which happen to be on A4 and A5 on an UNO.

Yes.
Somehow I did it... there are a lot of lcd i2c libraries out there. One of them works with eth shield. All working great. Thank you!

Adrian.