Unable to send String value from Arduino to MySQL-CodePudding

I'm trying to send a String value to MySQL, but everything I've tried failed and I don't understand why. Sending integers and floats is never a problem, but sending String or char never works.

I also haven't found any documentation on the error - as if I need a specific MySQL version to handle String or char, while Integer and float works fine?

//--------wifi-------------
#include "defines.h"
#define WIFI_FIRMWARE_LATEST_VERSION        "1.4.8"
#include <SPI.h>
#include <WiFiNINA_Generic.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(*, *, *, *);
char user[] = ******;
char password[] = *****;
WiFiClient client;
char ssid[] = ****;
char pass[] = ****;
int status_wifi = WL_IDLE_STATUS;

// -------- MySQL -----------
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
MySQL_Connection conn((Client *)&client);
int status_mysql;
String hostName = ****;
char query[255];

void setup() {
  Serial.begin(9600); //initialise serial monitor

  //--- wifi
  while (!Serial && millis() < 5000);
  Serial.print(F("\n(Setup) Start WiFiPing on ")); //Serial.println(BOARD_NAME);
  Serial.println(WIFININA_GENERIC_VERSION);
  if (WiFi.status() == WL_NO_MODULE) {
    Serial.println(F("Communication with WiFi module failed!"));
    while (true);
  }
  String fv = WiFi.firmwareVersion();
  if (fv < WIFI_FIRMWARE_LATEST_VERSION) {
    Serial.print(F("(Setup) Your current firmware NINA FW v"));
    Serial.println(fv);
    Serial.print(F("(Setup) Please upgrade the firmware to NINA FW v"));
    Serial.println(WIFI_FIRMWARE_LATEST_VERSION);
  }
  while (status_wifi != WL_CONNECTED) {
    Serial.print(F("(Setup) Attempting to connect to open SSID: "));
    Serial.println(ssid);
    status_wifi = WiFi.begin(ssid, pass);
  }
  Serial.println(F("(Setup) You're connected to the network"));

  //-- mysql
  Serial.println("(Setup) Connecting to MySQL...");
  status_mysql = conn.connect(server_addr, 3306, user, password);
  if (status_mysql == true) {
    delay(1000);
  }

}

void loop() {

  Serial.println("(Looping) Sketch: MySQL_insert_string");

  //--- MySQL
  Serial.println("(Looping) Connecting to MySQL...");
  status_mysql = conn.connect(server_addr, 3306, user, password);

  Serial.println("(Looping) Trying to send data");
  String s1 = "Hello World!";
  char buf[30];
  s1.toCharArray(buf, s1.length());
  Serial.println(buf);

  sprintf(query, "INSERT INTO pyntiqlv_webhooks.new_table VALUES (%s)", buf);
  MySQL_Cursor *cur_mem2 = new MySQL_Cursor(&conn);
  cur_mem2->execute(query);

  delete cur_mem2;
  Serial.println("(Looping) Done sending");

  Serial.println(" ");
  delay(2000);

}

Result:

(Looping) Sketch: MySQL_insert_string
(Looping) Connecting to MySQL...
...trying...
Connected to server version 8.0.29-cll-lve
(Looping) Trying to send data
Hello World
Error: 161 = 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 'World)' at line 1.
(Looping) Done sending

CodePudding user response：

You are missing the character data delimiters: single quotes around Hello World, the SQL command must be:

INSERT INTO pyntiqlv_webhooks.new_table VALUES ('Hello World!')
but yours:
INSERT INTO pyntiqlv_webhooks.new_table VALUES (Hello World!)

You could use the code:

sprintf(query, "INSERT INTO pyntiqlv_webhooks.new_table VALUES ('%s')", buf);

Also, you should be able to use s1, no need to first transfer to buf, because sprintf will not copy the terminating null, anyway.