Arduino analogread to mysql

Projects Programming
1

Hi guys. I want to send arduino analog pin data to mysql server, but it gives the following error:
Error: 163 = You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '')')')' at line 1.

Screenshot 2022-01-05 180626
Screenshot 2022-01-05 1806261310×594 236 KB

can someone help me ? here the project sketch:

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

#define ANALOG_PIN_1 1
int val;

byte mac_addr[] = { 0xFE, 0xBD, 0xCD, 0xCB, 0xA8, 0xAA };

IPAddress server_addr(10, 5, 51, 13); // IP of the MySQL *server* here
char user[] = "arduino";              // MySQL user login username
char password[] = "12345678";        // MySQL user login password

// Sample query
char INSERT_SQL[] = "INSERT INTO hello.test (message) VALUES ('%s')";

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

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


void loop() {
  delay(1000);

  val = analogRead(ANALOG_PIN_1);
  char query[80];
  sprintf(query, INSERT_SQL, val);

  Serial.println("Recording data.");

  // 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;
}

and sql script:

  CREATE DATABASE hello;
  CREATE TABLE hello.test (
    num integer primary key auto_increment,
    message char(40),
    recorded timestamp
  );
2

When you use this string for your sprint() function, %s specifies you are passing in a string, but

sprintf(query, INSERT_SQL, val);
is passing in an int

use
char INSERT_SQL[] = "INSERT INTO hello.test (message) VALUES ('%d')";

you could also help yourself with a debug message

 Serial.println("Recording data.");
Serial.print("SQL: " ); Serial.println(query);
3

thanks for reply. but I get %d value in mysql table

Screenshot 2022-01-05 181930

4

Did you take the other advice and print the query?

5

yes with this example https://github.com/ChuckBell/MySQL_Connector_Arduino/blob/master/examples/basic_insert/basic_insert.ino I can read the Hello, Arduino! in mysql table.

6

The idea is to print the result of the sprintf to serial so you can see what's wrong with it.

7

OK I do it and it's always printing number 48

Screenshot 2022-01-05 184114

8

Nope. Print the query variable.

10

showing this: INSERT INTO hello.test (message) VALUES ('%s')

11

Don't forget to look at the Web site's logs, they are a source for some of the reasons of failure.

I know with MySQL dB, I had to see which IP address were being refused connections to the dB and open those address up to being allowed to connect to the dB.

I found using HTTP POST to work quite well, ESP32 HTTP GET and HTTP POST with Arduino IDE | Random Nerd Tutorials.

12

Time to post your latest code I think.

13

Well, the problem is, I do not know what to do

14

Then you didn't edit your INSERT_SQL variable.
With this code:

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

  char INSERT_SQL[] = "INSERT INTO hello.test (message) VALUES ('%d')";

  int val = 100;
  char query[80];

  sprintf(query, INSERT_SQL, val);
  Serial.println("Recording data.");
  Serial.print("SQL: " ); Serial.println(query);
}

void loop() {
}

I get:

Recording data.
SQL: INSERT INTO hello.test (message) VALUES ('100')
15

You need to be using query here, not INSERT_SQL

16

WoW thanks. I replaced the query with INSERT_SQL now working like a charm. thanks you saved my day. :heart_eyes: