I have tried many ways and followed the example but cannot insert into mysql :(( hope everyone can help me
#include <Arduino.h>
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <rdm6300.h>
byte mac_addr[] = {0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED};
IPAddress ip_addr(10, 84, 3, 30); // Arduino static IP address
IPAddress server_addr(10, 84, 3, 91); // MySQL server IP address
char user[] = "test"; // MySQL username
char password[] = "123456789"; // MySQL password
char db[] = "datatool"; // Database name
EthernetClient client;
MySQL_Connection conn((Client *)&client);
#define RDM6300_RX_PIN 5
Rdm6300 rdm6300;
MySQL_Cursor *cur_mem; // Declare MySQL cursor at a higher scope
void setup() {
Serial.begin(115200);
while (!Serial); // Wait for Serial connection
Ethernet.begin(mac_addr, ip_addr);
Serial.println("Connecting...");
delay(1000);
if (conn.connect(server_addr, 3306, user, password, db)) {
Serial.println("Connection success!.");
rdm6300.begin(RDM6300_RX_PIN);
cur_mem = new MySQL_Cursor(&conn); // Create MySQL cursor
} else {
Serial.println("Connection failed.");
}
}
void loop() {
// Check if a new RFID tag is detected
if (rdm6300.get_new_tag_id()) {
// Get the tag ID as an unsigned long
unsigned long tagId = rdm6300.get_tag_id();
// Construct the SQL query
String query = "SELECT employeeName, employeeID, section FROM employee WHERE IDcard = '" + String(tagId) + "'";
// Execute the query
cur_mem->execute(query.c_str());
// Fetch the columns
column_names *cols = cur_mem->get_columns();
// Fetch the rows
row_values *row = cur_mem->get_next_row();
if (row != NULL) {
// Access and print the values of employeeName, employeeID, and section directly
Serial.print("employeeName: ");
Serial.println(row->values[0]);
Serial.print("employeeID: ");
Serial.println(row->values[1]);
Serial.print("section: ");
Serial.println(row->values[2]);
String insertQuery = "INSERT INTO ricedata (Timestamp, employeeID, employeeName, Section) VALUES (NOW(),'" + String(row->values[1]) + "', '" + String(row->values[0]) + "', '" + String(row->values[2]) + "')";
cur_mem->execute(insertQuery.c_str())
row = cur_mem->get_next_row();
} else {
// Print the RFID tag ID when the employee is not found
Serial.print("Employee not found for RFID tag ID: ");
Serial.println(tagId);
}
}
}
I'm sure the DB connection was successful because I can get information when swiping the RIFD card, I also tried insert_example and the insert was successful! The sketch I'm doing is that when an RFID card is swiped, the employee's information will appear on the screen, then about 2 seconds later, the information will be recorded in the database, including the date and time of the card swipe.
I would start by Serial printing this string out just after you build it..
and then refresh my memory on MYSQL, when shooting in values, do only strings need quotes, doubles quotes or single??
i'm use to firebird sql server, single quotes on strings and no quotes on numerical data..
when you print out the string, paste it into your SQL admin software and try to execute it..
Sorry my English is not very good, I don't quite understand what you mean! I've only been coding arduino for 2 weeks, so can you give me a more specific example? Thanks for your help
It is not right, because the ricedata table should not contain the employee name or section, only the employee id. The other data can be retrieved from the employee table when required. That would be good database design. What if the employee's name changes, or they move to another section? You will have more than one table to update in order to keep data consistent between tables.
But this is not the reason why your code is not working at the moment.
Thank you everyone for helping me find the cause! Because I use Uno, the memroy is not enough, so I can't write data to the DB. After I changed to MEGA2560, it was able to write.
