無法將字串值從Arduino發送到MySQL

我正在嘗試向 MySQL 發送一個字串值，但是我嘗試過的一切都失敗了，我不明白為什么。發送整數和浮點數從來都不是問題，但發送 String 或 char 永遠不會起作用。

我也沒有找到任何關于該錯誤的檔案——好像我需要一個特定的 MySQL 版本來處理 String 或 char，而 Integer 和 float 可以正常作業？

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

}

結果：

(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

uj5u.com熱心網友回復：

您缺少字符資料分隔符：Hello World 周圍的單引號，SQL 命令必須是：

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

您可以使用以下代碼：

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

另外，你應該可以使用s1，不需要先轉移到buf，因為sprintf無論如何都不會復制終止的null。

標籤：mysql sql Arduino

上一篇：根據另一個表中的特定行選擇特定列

下一篇：在Postgres中倒計時日期和月份