NODEMCU - SQL-Database

Hello,

i have some problem to manage my database. I use a NODEMCU who should send Data to the SQL-Database (Raspberry 4 and MariaDB). I can´t select or update etc… the record.

I use this script here:
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>
char ssid = “ABK14-24”; // Network Name
char pass = “password”; // Network Password
byte mac[6];
WiFiServer server(80);
IPAddress ip(192,168,X,X);
IPAddress gateway(192,168,X,X);
IPAddress subnet(255,255,255,0);
WiFiClient client;
MySQL_Connection conn((Client *)&client);
char INSERT_SQL = “INSERT adrutest.ardu_tabelle1(name,guthaben,rfid,datum) VALUES (Musterfrau, 130, now())”;
char query[128];
IPAddress server_addr(192,168,X,X); // MySQL server IP
char user = “SQLUSER”; // MySQL user
char password = “SQLPASSWORD”; // MySQL password
void setup() {
Serial.begin(9600);
Serial.println(“Initialising connection”);
Serial.print(F(“Setting static ip to : “));
Serial.println(ip);
Serial.println(””);
Serial.println("");
Serial.print(“Connecting to “);
Serial.println(ssid);
WiFi.config(ip, gateway, subnet);
WiFi.begin(ssid, pass);
while (WiFi.status() != WL_CONNECTED) {
delay(200);
Serial.print(”.”);
}
Serial.println("");
Serial.println(“WiFi Connected”);
WiFi.macAddress(mac);
Serial.print(“MAC: “);
Serial.print(mac[5],HEX);
Serial.print(”:”);
Serial.print(mac[4],HEX);
Serial.print(":");
Serial.print(mac[3],HEX);
Serial.print(":");
Serial.print(mac[2],HEX);
Serial.print(":");
Serial.print(mac[1],HEX);
Serial.print(":");
Serial.println(mac[0],HEX);
Serial.println("");
Serial.print(“Assigned IP: “);
Serial.print(WiFi.localIP());
Serial.println(””);
Serial.println(“Connecting to database”);
while (conn.connect(server_addr, 3306, user, password) != true) {
delay(200);
Serial.print ( “.” );
}
Serial.println("");
Serial.println(“Connected to SQL Server!”);
}
void loop() {

int soil_hum = 1024 - analogRead(sensorPin1);
delay(10000); //10 sec
sprintf(query, INSERT_SQL, soil_hum);
Serial.println(“Recording data.”);
Serial.println(query);
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
cur_mem->execute(query);
delete cur_mem;
}

The result in the Monitor:
Setting static ip to : 192.168.9.1

Connecting to ABK14-24
.
WiFi Connected
MAC: CD:2D:63:BC:FA:EC

Assigned IP: 192.168.9.1
Connecting to database

Connected to SQL Server!
Recording data.
INSERT adrutest.ardu_tabelle1(name,guthaben,rfid,datum) VALUES (Musterfrau, 130, now())

But there is no inster in the Database.

WHO can help me, what’s wrong?
(sorry for my english - i’m a german one)

best friendly reagards
Andy

The insert statement you are trying to execute:

INSERT adrutest.ardu_tabelle1(name,guthaben,rfid,datum) VALUES (Musterfrau, 130, now())

is flawed. Copy it and try to execute it in your SQL client - you should get errors.

There are a number of errors, the humidity data is a literal 130, not the value you measured. Musterfrau needs quotes around it. There is no rfid value provided. You may need to say INSERT INTO.

At first, thx for a quick reply.

so i try what you told me and you're right.

i change the insert line as follows:

INSERT adrutest.ardu_tabellle1(name,guthaben,rfid,datum) VALUES ('Musterfrau', 130, 0815,now())

there was a misstake in the table name and also Musterfrau must have '
now it works! thx.....

so you also can help me to select the record and display it in the serial monitor?

best regards andy

abernauer:
so you also can help me to select the record and display it in the serial monitor?

Why not try it, and show us your attempt first?

Sorry, sure! I try it with this code:

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>
char ssid = “ABK14-24”; // Network Name
char pass = “password”; // Network Password
byte mac[6];
WiFiServer server(80);
IPAddress ip(192,168,9,1);
IPAddress gateway(192,168,9,1);
IPAddress subnet(255,255,255,0);
WiFiClient client;
MySQL_Connection conn((Client *)&client);
char query = “SELECT name, guthaben FROM adrutest.ardu_tabellle1 WHERE rfid = ‘2712’”;
//char query[128];
IPAddress server_addr(192,168,9,214); // MySQL server IP
char user = “sqluser”; // MySQL user
char password = “sqlpassword”; // MySQL password
void setup() {
Serial.begin(9600);
Serial.println(“Initialising connection”);
Serial.print(F(“Setting static ip to : “));
Serial.println(ip);
Serial.println(””);
Serial.println("");
Serial.print(“Connecting to “);
Serial.println(ssid);
WiFi.config(ip, gateway, subnet);
WiFi.begin(ssid, pass);
while (WiFi.status() != WL_CONNECTED) {
delay(200);
Serial.print(”.”);
}
Serial.println("");
Serial.println(“WiFi Connected”);
WiFi.macAddress(mac);
Serial.print(“MAC: “);
Serial.print(mac[5],HEX);
Serial.print(”:”);
Serial.print(mac[4],HEX);
Serial.print(":");
Serial.print(mac[3],HEX);
Serial.print(":");
Serial.print(mac[2],HEX);
Serial.print(":");
Serial.print(mac[1],HEX);
Serial.print(":");
Serial.println(mac[0],HEX);
Serial.println("");
Serial.print(“Assigned IP: “);
Serial.print(WiFi.localIP());
Serial.println(””);
Serial.println(“Connecting to database”);
while (conn.connect(server_addr, 3306, user, password) != true) {
delay(200);
Serial.print ( “.” );
}
Serial.println("");
Serial.println(“Connected to SQL Server!”);
}
void loop() {
row_values *row = NULL;
long head_count = 0;
delay(1000);
Serial.println(“1) Demonstrating using a cursor dynamically allocated.”);
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);// Initiate the query class instance
cur_mem->execute(query); // Execute the query
column_names *columns = cur_mem->get_columns(); // Fetch the columns (required) but we don’t use them.
do { // Read the row (we are only expecting the one)
row = cur_mem->get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
delete cur_mem; // Deleting the cursor also frees up memory used
Serial.println(query);
// Show the result
Serial.print("Name/Guthaben = ");
Serial.println(head_count);
delay(500);

Serial.println(“2) Demonstrating using a local, global cursor.”);
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);// Initiate the query class instance
cur_mem->execute(query); // Execute the query
// Fetch the columns (required) but we don’t use them.
cur_mem->get_columns();
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
// Now we close the cursor to free any memory
delete cur_mem;

// Show the result but this time do some math on it
Serial.print("Name/Guthaben: ");
Serial.println(head_count);
}

This is the message from the Serial Monitor:

Setting static ip to : 192.168.9.1

Connecting to ABK14-24
.
WiFi Connected
MAC: CD:2D:63:BC:FA:EC

Assigned IP: 192.168.9.1
Connecting to database

Connected to SQL Server!

  1. Demonstrating using a cursor dynamically allocated.
    SELECT name, guthaben FROM adrutest.ardu_tabellle1 WHERE rfid = ‘2712’
    Name = 0
  2. Demonstrating using a local, global cursor.

Exception (9):
epc1=0x40201561 epc2=0x00000000 epc3=0x00000000 excvaddr=0x6563697e depc=0x00000000

stack>>>

ctx: cont
sp: 3ffffdc0 end: 3fffffc0 offset: 01a0
3fffff60: 00000000 0000002e 3ffee5d0 40203b94
3fffff70: 00000000 3ffee5d0 3ffee5d0 3ffe84d6
3fffff80: 00000000 3ffee5d0 3fff0a04 402012fa
3fffff90: feefeffe feefeffe feefeffe 3ffee648
3fffffa0: 3fffdad0 00000000 3ffee608 4020437c
3fffffb0: feefeffe feefeffe 3ffe8554 40100b95
<<<stack<<<

P.S. How can I insert here a code?

Best regards
Andy

Again, take the query from the serial monitor and try it in your SQL client. I suspect that it will barf on the backticks around rfid.

I try that before and it works…see pic

Bizarre syntax, but OK, I'm no MySQL expert.

I'd add a bunch of Serial.prints to see which line is causing the crash.

Also, what do you see in your MySQL logs?

Where can I find the log?

No idea. I had assumed from your code that you were using MySQL, but I see now that it's actually MariaDb. Is that MySQL library compatible?

That said, purely guessing, is there a /var/log/MariaDb directory on your Pi?

pls remember (see above) the insert part work fine!

i hope i find the rigth log (errorlog)

2020-02-29 20:31:53 215 [Warning] Aborted connection 215 to db: 'unconnected' user: 'rfid' host: '192.168.9.1' (Got an error reading communication packe$
2020-02-29 20:31:53 216 [Warning] IP address '192.168.9.1' could not be resolved: Temporary failure in name resolution

Hello,

so i get from a other one the solution. Here is what i change:
remove that:

Serial.println("2) Demonstrating using a local, global cursor.");
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);// Initiate the query class instance
  cur_mem->execute(query);                   // Execute the query
  // Fetch the columns (required) but we don't use them.
  cur_mem->get_columns();
  // Read the row (we are only expecting the one)
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      head_count = atol(row->values[0]);
    }
  } while (row != NULL);
  // Now we close the cursor to free any memory
  delete cur_mem;

  // Show the result but this time do some math on it
  Serial.print("Name/Guthaben: ");
  Serial.println(head_count);

change that:

Serial.println("1) Demonstrating using a cursor dynamically allocated.");
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);// Initiate the query class instance
    cur_mem->execute(query);                        // Execute the query
    column_names *columns = cur_mem->get_columns();   // Fetch the columns (required) but we don't use them.
do {                                               // Read the row (we are only expecting the one)
    row = cur_mem->get_next_row();
    if (row != NULL) {
      Serial.print("Name: ");
      Serial.println(row->values[0]);  // <--- der Name als Zeichenkette
      Serial.print("Guthaben: ");
      Serial.println(atof(row->values[1])); // <---- das Guthaben umwandeln in float (du wirst damit rechen wollen)
    }
    } while (row != NULL);

So here the complet working code:

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>
#include <WiFiClient.h>
char ssid[] = "SSID";                 // Network Name
char pass[] = "SSIDPassword;                 // Network Password
byte mac[6];
WiFiServer server(80);
IPAddress ip(192,168,X,X);
IPAddress gateway(192,168,X,X);
IPAddress subnet(255,255,255,0);
WiFiClient client;
MySQL_Connection conn((Client *)&client);
char query[] = "SELECT name, guthaben FROM adrutest.ardu_tabellle1 WHERE `rfid` = '2712'";
//char query[128];
IPAddress server_addr(192,168,X,X);          // MySQL server IP
char user[] = "SQLUSER";           // MySQL user
char password[] = "SQLUSERPASSWORD";       // MySQL password
void setup() {
  Serial.begin(9600);
  Serial.println("Initialising connection");
  Serial.print(F("Setting static ip to : "));
  Serial.println(ip);
  Serial.println("");
  Serial.println("");
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.config(ip, gateway, subnet); 
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(200);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi Connected");
  WiFi.macAddress(mac);
  Serial.print("MAC: ");
  Serial.print(mac[5],HEX);
  Serial.print(":");
  Serial.print(mac[4],HEX);
  Serial.print(":");
  Serial.print(mac[3],HEX);
  Serial.print(":");
  Serial.print(mac[2],HEX);
  Serial.print(":");
  Serial.print(mac[1],HEX);
  Serial.print(":");
  Serial.println(mac[0],HEX);
  Serial.println("");
  Serial.print("Assigned IP: ");
  Serial.print(WiFi.localIP());
  Serial.println("");
  Serial.println("Connecting to database");
  while (conn.connect(server_addr, 3306, user, password) != true) {
    delay(200);
    Serial.print ( "." );
  }
  Serial.println("");
  Serial.println("Connected to SQL Server!");  
}
void loop() {
  row_values *row = NULL;
  long head_count = 0;
  delay(1000);
  Serial.println("1) Demonstrating using a cursor dynamically allocated.");
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);// Initiate the query class instance
    cur_mem->execute(query);                        // Execute the query
    column_names *columns = cur_mem->get_columns();   // Fetch the columns (required) but we don't use them.
  do {                                               // Read the row (we are only expecting the one)
    row = cur_mem->get_next_row();
    if (row != NULL) {
      Serial.print("Name: ");
      Serial.println(row->values[0]);  // <--- der Name als Zeichenkette
      Serial.print("Guthaben: ");
      Serial.println(atof(row->values[1])); // <---- das Guthaben umwandeln in float (du wirst damit rechen wollen)
    }
    } while (row != NULL);
   delete cur_mem;                                   // Deleting the cursor also frees up memory used
  Serial.println(query);
   delay(500);
}

Thx for your support!

Andy