MySQL Forums
Forum List  »  Connector/Arduino

Arduino IDE and MYSQL_Connector library
Posted by: Gwyllym Jones
Date: May 08, 2019 10:24AM

Question: Is there a limit in the size of query that can be returned using the MYSQL_Connector library for the Arduino IDE?

Background: I am trying to access a field in a MYSQL database table which is approx 2Kb in size. I'm using the Arduino MYSQL_Connector library and for testing purposes have simplified the field so that it only contains the character 'a', i.e there are no special characters or escape characters in the data. Experimenting with the field size (i.e no. of characters) results in the following:

Field size Returned data size from Query
<=250 <=250
>=251 and <=255 1
=256 0
>256 252

In all cases, the query returns the initial characters from the field, anything after character# 252 is being truncated.

If I run the query using mysql cli client, Dbeaver or MySQL Workbench I get the full field contents returned in the query exactly as expected.

I've read that truncation can occur if charsets between the server and client do not match and have experimented using different CHARSET and COLLATE options on the server (utf8,utf8mb4, Latin1) etc. but it doesn't change the output results described above.

Has anyone else seen this behaviour or know what I am doing wrong?


Additional info:

The test script is pretty much taken straight from the examples that are provided with the library i.e.
-----------------
char query[1000] = "SELECT myData FROM myTable WHERE myId = 'esp14c8dd';";


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

void setup() {
Serial.begin(115200);
WiFi.mode(WIFI_STA);
WiFi.disconnect();
delay(100);
WiFi.begin(ssid, wifiPass);

while (WiFi.status() != WL_CONNECTED) {
delay(1000);
Serial.print(".");
}
Serial.println();
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Connection failed.");
}


void loop() {
delay(1000);

// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

// Execute the query
cur_mem->execute(query);

delay(2000);
// Fetch the columns and print them
column_names *cols = cur_mem->get_columns();
for (int f = 0; f < cols->num_fields; f++) {
Serial.print(cols->fields[f]->name);
if (f < cols->num_fields-1) {
Serial.print(',');
}
}
Serial.println();
// Read the rows and print them
row_values *row = NULL;
do {
row = cur_mem->get_next_row();
if (row != NULL) {
for (int f = 0; f < cols->num_fields; f++) {
Serial.println(row->values[f]);
Serial.printf("Size of data is %lu\n",strlen(row->values[f]));
//Serial.println(strlen(row->values[f]));
if (f < cols->num_fields-1) {
Serial.print(',');
}
}
Serial.println();
}
} while (row != NULL);
// Deleting the cursor also frees up memory used

delete cur_mem;
}
-------------------------


MySQL Community Server v8.0.15 for Linux on x86_64
NodeMCU v3 ESP8266
Arduino IDE v1.8.9
MYSQL_Connector library v1.1.1

Options: ReplyQuote


Subject
Views
Written By
Posted
Arduino IDE and MYSQL_Connector library
6008
May 08, 2019 10:24AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.