Hello guys,
I need to comunicate with MySQL to store a variable from arduino. When I use just a Char statement I can get it to work, but when I use sprintf to create a statement from a variable, the arduino stucks trying to connect to MySQL. Can anyone help to solve this problem?
The codes are shown bellow and the serial responses are attached. Any help is apreciated, thanks in advance!
This code works perfectly:
#include "Ultrasonic.h"
#include <SPI.h>
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192,168,10,135); // IP of the MySQL *server* here
char user[] = "MyUser"; // MySQL user login username
char password[] = "MyPassword"; // MySQL user login password
// Sample query
char INSERT_SQL[] = "INSERT INTO rafael.eventos (nivel,estado) VALUES (1,'teste')";
EthernetClient clientEmail;
EthernetClient client;
MySQL_Connection conn((Client *)&client);
int HTTP_PORT = 80;
String HTTP_METHOD = "GET";
char HOST_NAME[] = "maker.ifttt.com";
String PATH_NAME = "/trigger/problemaCaixa/with/key/f9WuiRFn8Dj4ufPo5hgKMIgYfnio3Vu9biAIEmYr-Ni"; // change your EVENT-NAME and YOUR-KEY
String queryString = "?value1=26&value2=70";
void setup() {
Serial.begin(9600);
while (!Serial); // wait for serial port to connect
Serial.println("Connecting...");
if (Ethernet.begin(mac_addr)==0){
Serial.println("Failed to configure Ethernet using DHCP");
}
Serial.println(Ethernet.localIP());
}
void loop() {
delay(2000);
sendData();
email();
}
void email(){
// connect to web server on port 80:
if(clientEmail.connect(HOST_NAME, HTTP_PORT)) {
// if connected:
Serial.println("Connected to server");
// make a HTTP request:
// send HTTP header
clientEmail.println("GET " + PATH_NAME + queryString + " HTTP/1.1");
clientEmail.println("Host: " + String(HOST_NAME));
clientEmail.println("Connection: close");
clientEmail.println(); // end HTTP header
while(clientEmail.connected()) {
if(clientEmail.available()){
// read an incoming byte from the server and print it to serial monitor:
char c = clientEmail.read();
Serial.print(c);
}
}
// the server's disconnected, stop the client:
clientEmail.stop();
Serial.println();
Serial.println("disconnected");
} else {// if not connected:
Serial.println("connection failed");
}
}
void sendData(){
if (conn.connect(server_addr, 3306, user,password)) {
Serial.println("Recording data.");
}
else
Serial.println("Connection failed.");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(INSERT_SQL);
// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
conn.close();
client.stop();
}
This code does not work:
#include "Ultrasonic.h"
#include <SPI.h>
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192,168,10,135); // IP of the MySQL *server* here
char user[] = "domoticaSamsung"; // MySQL user login username
char password[] = "Rafa#rafa1"; // MySQL user login password
// Sample query
char bigBuf[100] = "";
char INSERT_SQL[] = "INSERT INTO rafael.eventos (nivel,estado) VALUES (%d,%s);";
int media =5;
EthernetClient clientEmail;
EthernetClient client;
MySQL_Connection conn((Client *)&client);
int HTTP_PORT = 80;
String HTTP_METHOD = "GET";
char HOST_NAME[] = "maker.ifttt.com";
String PATH_NAME = "/trigger/problemaCaixa/with/key/f9WuiRFn8Dj4ufPo5hgKMIgYfnio3Vu9biAIEmYr-Ni"; // change your EVENT-NAME and YOUR-KEY
String queryString = "?value1=26&value2=70";
void setup() {
Serial.begin(9600);
while (!Serial); // wait for serial port to connect
Serial.println("Connecting...");
if (Ethernet.begin(mac_addr)==0){
Serial.println("Failed to configure Ethernet using DHCP");
}
Serial.println(Ethernet.localIP());
}
void loop() {
delay(2000);
sprintf(bigBuf, INSERT_SQL, media,"'diminuindo'");
sendData();
email();
}
void email(){
// connect to web server on port 80:
if(clientEmail.connect(HOST_NAME, HTTP_PORT)) {
// if connected:
Serial.println("Connected to server");
// make a HTTP request:
// send HTTP header
clientEmail.println("GET " + PATH_NAME + queryString + " HTTP/1.1");
clientEmail.println("Host: " + String(HOST_NAME));
clientEmail.println("Connection: close");
clientEmail.println(); // end HTTP header
while(clientEmail.connected()) {
if(clientEmail.available()){
// read an incoming byte from the server and print it to serial monitor:
char c = clientEmail.read();
Serial.print(c);
}
}
// the server's disconnected, stop the client:
clientEmail.stop();
Serial.println();
Serial.println("disconnected");
} else {// if not connected:
Serial.println("connection failed");
}
}
void sendData(){
if (conn.connect(server_addr, 3306, user,password)) {
Serial.println("Recording data.");
}
else
Serial.println("Connection failed.");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(bigBuf);
// Note: since there are no results, we do not need to read any data
// Deleting the cursor also frees up memory used
delete cur_mem;
conn.close();
client.stop();
}
