Insert into statement -> passing variables

Other Hardware Home Automation
1

Hi all,

I'm using a ESP8266 to connect to my WiFi and insert data into MySql database.

However inserting variables don't work.

char INSERT_SQL[] = "INSERT INTO home.kitchen(Temperature, Humidity, Date) VALUES (%d,%d, Now())";

This generates the error, error: 57 = data truncated for column arduino float insert statement

What works is:

char INSERT_SQL[] = "INSERT INTO home.kitchen(Temperature, Humidity, Date) VALUES ('123','4', Now())";

But i want to insert the content of the variables Temperature and Humidity, type float.

If I change it to %f, it does not change anything.

I guess i need to tell the variables Temperature and Humidity must be used, but cannot make it happen. Replace the %d by Temperature or @Temperature (like in SQL) the error is gone, but NULL data is inserted except date and time.

Can anyone get me on the right direction? Thanks in advance!

2

Since you can pass values manually/hard coded, have you printed to console the variable values to insure what is being passed? If it's expecting a float what is the variable value and format

3

Variable definition is:

float Temperature = 21.4; 
float Humidity = 99.6;

If i do this:

Serial.println((float)(Temperature),1);

it outputs 21.4 over the serial port, as expected.

tried to change it to,

char INSERT_SQL[] = "INSERT INTO home.kitchen(Temperature, Humidity, Date) VALUES (((float)(Temperature),1),((float)(Humidity),1), Now())";

It compiles, but it generetes an error during inserting.

Error message is:
10:24:13.511 -> Error: 207 = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'float)(Temperature),1),((float)(Humidity),1), Now())' at line 1.

Changed the column datatype from float to decimal. But this doesn't make any difference

4

It looks to me as though you have some example code that was intended to be used with sprintf. Can you post the code that does the insert as well as your char array?

5

mmm, still stuck..

I now have used some code on this site:

My code, it converts the float/double values to char and then insert it. It is not the way how i would like it, but getting a bit desperete now.

#include <ESP8266WiFi.h>           // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

IPAddress server_addr(192,168,178,14);  // IP of the MySQL *server* here
char user[] = "kitchen_node";              // MySQL user login username
char password[] = "Kitchen123";        // MySQL user login password


double Temperature = 21.4; 
double Humidity = 99.6; 

//char INSERT_SQL[] = "INSERT INTO home.kitchen(Temperature, Humidity, Date) VALUES ("Temperature + ", " + Humidity + ", " + Now() ")";
char INSERT_SQL[] = "INSERT INTO home.kitchen(Temperature, Humidity, Date) VALUES ('%s','%s','Now())";
 
 char query[128];
 


// WiFi card example
char ssid[] = "Verborgen Netwerk";         // your SSID
char pass[] = "Not gonna tell this";     // your SSID Password

WiFiClient client;               
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

void setup()
{
  Serial.begin(115200);
  while (!Serial);   

  // Begin WiFi section
  Serial.printf("\nConnecting to %s", ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  // print out info about the connection:
  Serial.println("\nConnected to network");
  Serial.print("My IP address is: ");
  Serial.println(WiFi.localIP());

  Serial.print("Connecting to SQL...  ");
  if (conn.connect(server_addr, 3306, user, password))
    Serial.println("OK.");
  else
    Serial.println("FAILED.");
  
  // create MySQL cursor object
  cursor = new MySQL_Cursor(&conn);
}

void loop()
{
  char TemperatureChar[6];
  char HumidityChar[5];
   Serial.println("Looping");

  if (conn.connected())

  dtostrf(Temperature, 5 , 2, TemperatureChar);
  dtostrf(Humidity, 5, 2, HumidityChar);

   
  sprintf(query, INSERT_SQL, TemperatureChar, HumidityChar);
  

  delay(1000);
}
6

You're nearly there I think. Print your query to serial - you have one quote too many.

7

Gottem!

  1. No '' around the %s in the insert query
  2. Need to execute the query, the print function doesn't do this cur_mem->execute(query);
  3. the ' as you mentioned.

Thanks for the help! 8)