Arduino UNO + Mysql_Connector : Problem with another query after running one

Dear expert,
Im using Uno, RFID reader and LAN Shield with mysql_connector library.

Everything working fine if I only run 1 or 2 query as long as it doesnt run in between.

In my test, I had 1 table which have RFID cardno, Qty and ID (integer, auto increament, primary key)

the 1st query return ID of said RFID card no and pass it to another query to update its Qty
The 2nd one, use 1st query result and pass it to decrease it Qty.

As long as I didnt run 2nd query, it work perfectly fine.

But, as soon as cur_mem->execute(query); run, it either freeze, disconnect from server, the ID didnt catch any values

Here is the code for the queries :

void proses_db() {
  row_values *row = NULL;
  long dataID = 0;
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  sprintf(query, QUERY_POP, read_rfid.c_str());
  Serial.println(query);
  cur_mem->execute(query);
  column_names *columns = cur_mem->get_columns();
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {      
      dataID = row->values[0];
	  open_lock();
       sprintf(query, QUERY_POP2, dataID);
	  cur_mem->execute(query);  // **<< this is where it freeze / error** 	  Serial.println(query);
      delay(1000);
	  
    }
  } while (row != NULL);
  delay(1000);
  Serial.println("done!!");
  delete cur_mem; // free memory
}

here is the full code

/* * ----------------------------------
  RST/Reset   RST          9  
  SPI SS      SDA(SS)      10 
  SPI MOSI    MOSI         11 / ICSP-4
  SPI MISO    MISO         12 / ICSP-1
  SPI SCK     SCK          13 / ICSP-3

*/
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <SPI.h>
#include <MFRC522.h>

#define RST_PIN         8 //9           
#define SS_PIN          9 //10          
MFRC522 mfrc522(SS_PIN, RST_PIN);
String read_rfid;                   //

const int lock = 6;                // relay
byte mac[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(192, 168, 5, 249);  
IPAddress ip(192, 168, 5, 71);
IPAddress dns(8, 8, 8, 8);
IPAddress gateway(192, 168, 5, 1);
IPAddress subnet(255, 255, 255, 0);

char user[] = "root";              
char password[] = "password";        
char db[] = "database";

const char QUERY_POP[] = "select id from jajal where jml>0 and nocard='%s' LIMIT 1";
const char QUERY_POP2[] = "update jajal set jml=jml-1 where id=%s and jml>0";
char query[128];

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


void setup() {

  Serial.begin(115200);
  while (!Serial);
  SPI.begin();
  mfrc522.PCD_Init();
  pinMode(lock, OUTPUT);
  digitalWrite(lock, HIGH);
  delay(5000);
  Ethernet.begin(mac, ip, dns, gateway, subnet);
  Serial.print("IP LAN :");
  Serial.println(Ethernet.localIP());
  Serial.print("IP Database :");
  Serial.println(server_addr);
  if (conn.connect(server_addr, 3307, user, password, db)) {
    delay(3000);
  }
  else
    Serial.println("gagal konek bos");
}
void dump_byte_array(byte *buffer, byte bufferSize) {
  read_rfid = "";
  for (byte i = 0; i < bufferSize; i++) {
    read_rfid = read_rfid + String(buffer[i], HEX);
  }
}

void open_lock() {
  digitalWrite(lock, LOW);
  delay(1000); 
  digitalWrite(lock, HIGH);
}

void loop() {
  
  if ( ! mfrc522.PICC_IsNewCardPresent())
    return;


  if ( ! mfrc522.PICC_ReadCardSerial())
    return;
  dump_byte_array(mfrc522.uid.uidByte, mfrc522.uid.size);

  proses_db();


}

void proses_db() {
  row_values *row = NULL;
  long dataID = 0;
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  sprintf(query, QUERY_POP, read_rfid.c_str());
  Serial.println(query);
  cur_mem->execute(query);
  column_names *columns = cur_mem->get_columns();
  do {

    row = cur_mem->get_next_row();
    if (row != NULL) {      
      dataID = row->values[0];
	  open_lock();
 
      sprintf(query, QUERY_POP2, dataID);
	  cur_mem->execute(query);  // error in here
	  Serial.println(query);
      delay(1000);

	  
    }
  } while (row != NULL);
  delay(1000);
  Serial.println("done!!");
  delete cur_mem; // free memory
}

here’s the serial output :

Connected to server version 8.0.24
select id from jajal where jml>0 and nocard=‘171f1e1e’ LIMIT 1
update jajal set jml=jml-1 where id=999999999 and jml>0
Error: 164 = 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 ‘and jml>0’ at line 1.
update jajal set jml=jml-1 where id= and jml>0
ERROR: Timeout waiting for client.
done!!

notice that 2nd query is executed twice and the 2nd query being pass without ID passed.

If I commented cur_mem->execute(query); , the it work fine (tho it doesnt update the record obviously)

IP LAN :192.168.5.71
IP Database :192.168.5.249
…trying…
Connected to server version 8.0.24
select id from jajal where jml>0 and nocard=‘171f1e1e’ LIMIT 1
update jajal set jml=jml-1 where id=999999999 and jml>0
done!!
select id from jajal where jml>0 and nocard=‘171f1e1e’ LIMIT 1
update jajal set jml=jml-1 where id=999999999 and jml>0
done!!

thank you in advance.

ps : this is my 1st project with arduino and c++, so please bear with me :slight_smile:

Using dynamic memory allocation (heap allocation) should be avoided on AVR Arduinos. Same applies for using the String class (as it uses dynamic allocation).

I guess you’re running out of memory. You can try to save some memory by using the F() macro on string literals.

Thats Im initially suspect for, because whenever I commented that particular line, it work fine.

However, if I move that block code to new function (thats what it called in cpp?) and call that after the 1st one (with different query) it works too.

About dynamic memory allocation (heap allocation), can you point on which, so I can make the code better?

Thankyou very much

You are using the new operator, which allocates on the heap.

However you are freeing the memory again using delete, so all ought to be well.

However perhaps you should be holding on the the MySQL_Cursor object for the
duration of the connection, not creating multiple ones. Another issue is you may
have to do something to close down the cursor before freeing it - check the docs for
the library.

This topic was automatically closed 120 days after the last reply. New replies are no longer allowed.