mysql - Getting varchar value in select with MySQL Connector/Arduino

Question

I'm setting up an automatic weighing system for quality control. For this, I am using the MySQL Connector/Arduino library.

Everything is ready, and I only have the following steps left:

1. Consult a database which is the batch number I am going to evaluate (the batch number is stored in a VARCHAR into the qcInsecta_get data table).

2. Include the value obtained above, in an INSERT with the value of the weight that the load cell returns to me.

I have worked with the examples of basic_insert and basic_select provided by the author of the library. Inserting values in the datable I haven't had any problems (I had done it with probe values before). However, when I do SELECT, the value retrieved is always a numeric value, if the database field is numeric, for example, INT. But if the database field is a VARCHAR, it returns 0.

I'm not an expert in C code, but I've seen it included in the example:

head_count = atol(row->values[0]);

And atol converts a string to its numeric value. I have tried to use other forms or converters to be able to make a SELECT and get the value from the database, but it has been impossible for me. How could I tackle this problem?

The code doing the SELECT, using a NODEMCU V3 board is:

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

char ssid[] = ""; // SSID NAME
char pass[] = ""; // SSID PASSWORD

IPAddress server_addr(xxx, xxx, x, xxx);
char user[] = "";
char password[] = "";
char query[] = "SELECT batchnumber FROM registro.qcInsecta_get ORDER BY id DESC LIMIT 1";
WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  delay(500);
  Serial.begin(115200);
  WiFi.begin(ssid, pass);
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(100);
    Serial.println(F("Connected"));    
  }
  else {
      Serial.println();
      conn.close();  
      if (conn.connect(server_addr, 3306, user, password)) {
      delay(500);
      } 
      else {
        Serial.println("...");
      }
  }
}

void loop() {
  delay(1000);
  row_values *row = NULL;
  long head_count = 0;

  Serial.println("1) Demonstrating using a cursor dynamically allocated.");
  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Execute the query
  cur_mem->execute(query);
  // Fetch the columns (required) but we don't use them.
  column_names *columns = 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);
  // Deleting the cursor also frees up memory used
  delete cur_mem;

  // Show the result
  Serial.print("  Batch number = ");
  Serial.println(head_count);

  delay(500);
} 

About the database:

| qcInsecta | CREATE TABLE `qcInsecta` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batchnumber` varchar(30) NOT NULL,
  `weight` float NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `batchnumber` (`batchnumber`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=utf8mb4 |

MariaDB [registro]> SELECT * FROM qcInsecta;
+----+-------------+--------+---------------------+
| id | batchnumber | weight | created_at          |
+----+-------------+--------+---------------------+
|  5 | COL-01-05   |     35 | 2019-06-06 17:04:38 |
| 16 | COL-01-01   |     22 | 2019-06-06 18:57:02 |
| 17 | COL-01-01   |     22 | 2019-06-06 18:57:02 |
| 18 | COL-01-01   |     25 | 2019-06-06 21:52:02 |

标签： mysqlarduino

Answer 1

随着head_count = atol(row->values[0]);您将输出的每个字符转换为一个长值，它是一个数字。因此，您应该只对 NUMERIC 字段执行此操作。对于字符串字段，您只需要row->values它是一个 C 字符串数组。

基于此 row->values，包含一个具有SELECT列结果大小的数组。例如，如果您选择一列（就像您当前的选择查询一样），row->values大小为 1，您的结果将在row->values[0].

如果您需要分隔每一列，请尝试为每一列进行选择，或者您可以按照complex_select.ino示例并迭代列而不是行，然后分隔每种列类型并将它们转换为所需的值。

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

char ssid[] = ""; // SSID NAME
char pass[] = ""; // SSID PASSWORD

IPAddress server_addr(xxx, xxx, x, xxx);
char user[] = "";
char password[] = "";
char query[] = "SELECT batchnumber FROM registro.qcInsecta_get ORDER BY id DESC LIMIT 1";
WiFiClient client;
MySQL_Connection conn((Client *)&client);

void setup()
{
    delay(500);
    Serial.begin(115200);
    WiFi.begin(ssid, pass);
    if (conn.connect(server_addr, 3306, user, password))
    {
        delay(100);
        Serial.println(F("Connected"));
    }
    else
    {
        Serial.println();
        conn.close();
        if (conn.connect(server_addr, 3306, user, password))
        {
            delay(500);
        }
        else
        {
            Serial.println("...");
        }
    }
}

void loop()
{
    delay(1000);

    long head_count = 0;

    Serial.println("1) Demonstrating using a cursor dynamically allocated.");
    // Initiate the query class instance
    MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
    // Execute the query
    cur_mem->execute(query);
    // Fetch the columns (required) but we don't use them.
    Serial.println("Fetching with Columns");
    column_names *columns = cur_mem->get_columns();

    for (int f = 0; f < columns->num_fields; f++)
    {
        Serial.print(columns->fields[f]->name);
        if (f < columns->num_fields - 1)
        {
            Serial.print(',');
        }
    }
    Serial.println("Done");

    // Read the rows and print them
    Serial.println("Fetching with Rows");
    row_values *row = NULL;
    do
    {
        row = cur_mem->get_next_row();
        if (row != NULL)
        {
            //e.g convert to a float value
            // float batchnumber = 0;
            //   batchnumber = atol(row->values[0]);
            //   Serial.print("float value: ");
            //   Serial.println(batchnumber,2);
            String batchnumber_str = "";
            for (int f = 0; f < columns->num_fields; f++)
            {
                //just print the String value
                // Serial.print(row->values[f]);
                // convert value to String
                batchnumber_str = String(row->values[f]);
                Serial.print("batchnumber_str: ");
                Serial.println(batchnumber_str);
                if (f < columns->num_fields - 1)
                {
                    Serial.print(',');
                }
            }
            Serial.println();
        }
    } while (row != NULL);
    Serial.println("Done");
    // Deleting the cursor also frees up memory used
    delete cur_mem;

    // Show the result
    Serial.print("  Batch number = ");
    Serial.println(head_count);

    delay(500);
}