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 