Closed xIceFox closed 2 years ago
Catching up on some posts. Forgive me if I’ve asked this already, but what version of MySQL are you using?
This error manifests in two cases; a) when using a non-Oracle version of MySQL, or b) when not reading the data from a previous query before running another. In (b), make sure you also read the column data since that is sent over the wire first. Check out the sample sketches for how to do this properly.
That said, your sketch is using stored routines and if they are returning values or a result set, you must read that before executing the next query. Now, a challenge for you: write your SQL statements without the stored routines. IMHO, stored routines are short cuts and optimizations which can cause issues like this during development. I make it a point to avoid them until my code is working correctly - then I add the optimizations. ;)
On Feb 25, 2019, at 5:17 PM, xIceFox notifications@github.com wrote:
Hello, I do a project where I read RFID Card System to check how long people are on a event. My Arduino is the MySQL Client, which reads the RFID Cards and call procedures on the SQL Server. Im using the MySQL Connector to run stored Procedures in MySQL. At the End of the Procedure in MySQL I do:
Select "Successful" or when the Procedure crashes: Select "Error"
Now I want to read this with Basic Select.
The Problem is: The First time I do the Basic Select, it works fine. I get the Information of the Procedure instantly.
The Second Time I do the Basic Select, I get an empty Line back.
The Third Time I do the Basic Select, I get an Bad mojo. EOF found reading column header. ERROR: You must read the columns first!.
I can post my Serial Monitor here:
22:55:15.378 -> Connecting... 22:55:17.623 -> Connected to server version 8.0.14 22:59:30.872 -> Modus: Anmelden 22:59:32.067 -> Gelesene UID: 7A 2E 8C BB 22:59:32.615 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:32.719 -> Successful 22:59:32.719 -> Anmeldung abgeschlossen :) 22:59:34.122 -> Gelesene UID: 7A 2E 8C BB 22:59:34.666 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:35.555 -> 22:59:35.555 -> Anmeldung abgeschlossen :) 22:59:36.810 -> Gelesene UID: 7A 2E 8C BB 22:59:37.355 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:37.390 -> Bad mojo. EOF found reading column header. 22:59:37.424 -> ERROR: You must read the columns first! 22:59:37.459 -> 22:59:37.459 -> Anmeldung abgeschlossen :)
I don't know why, but when I reset the Arduino, or I reconnect to the Server with the SoftReset, the first time it will work. The Problem is, that I haven't the time to soft reset my Arduino every time, because there are many people trying to get into this event.
My code is here:
`#include
include
include
include
include
define RST_PIN 5
define SS_PIN 53
IPAddress server_addr(I will hide this for security reasons :D); byte mac_addr[] = {I will hide this for security reasons :D }; char user[] = "I will hide this for security reasons :D"; char password[] = "I will hide this for security reasons :D";
EthernetClient client; MySQL_Connection conn((Client *)&client);
MFRC522 mfrc522(SS_PIN, RST_PIN);
int GID = 1; char query[128]; char SQLQueryAnmeldung[] = "call sas.Anmeldung('%s',%d);"; char SQLQueryAbmeldung[] = "call sas.Abmeldung('%s',%d);"; String Modus;
void setup() { pinMode(30, INPUT); pinMode(31, INPUT); pinMode(22, OUTPUT); pinMode(24, OUTPUT); pinMode(26, OUTPUT); Serial.begin(9600);
SPI.begin(); mfrc522.PCD_Init();
Ethernet.begin(mac_addr); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(500); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } else { Serial.println("Connection failed."); digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); } }
void loop() {
if (mfrc522.PICC_IsNewCardPresent() && mfrc522.PICC_ReadCardSerial() ) { if (Modus == "Anmeldung") { SQLAnmeldung(ReadTag()); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); }
if (Modus == "Abmeldung") { SQLAbmeldung(ReadTag()); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } }
if (digitalRead(30)) { Modus = "Anmeldung"; delay(1000); Serial.println("Modus: Anmelden"); } if (digitalRead(31)) { Modus = "Abmeldung"; delay(1000); Serial.println("Modus: Abmelden"); } }
void SQLAnmeldung(String RFID) { row_values row = NULL; String Res = ""; digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); delay(500); sprintf(query, SQLQueryAnmeldung, RFID.c_str(), GID); Serial.println(query); MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); column_names *columns = cur_mem->get_columns();
do { row = cur_mem->get_next_row(); if (row != NULL) { Res = row->values[0]; } } while (row != NULL); delete cur_mem; Serial.println(Res); Serial.println("Anmeldung abgeschlossen :)"); delay(500);
}
void SQLAbmeldung(String RFID) { digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); delay(500); sprintf(query, SQLQueryAbmeldung, RFID.c_str(), GID); MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); delete cur_mem; Serial.println("Abmeldung abgeschlossen :)"); }
String ReadTag() { String UID; Serial.print("Gelesene UID: ");
for (byte i = 0; i < mfrc522.uid.size; i++) { if (mfrc522.uid.uidByte[i] < 0x10) { UID = UID + "0"; } UID = UID + String(mfrc522.uid.uidByte[i], HEX); if (i != mfrc522.uid.size - 1) { UID = UID + " "; } } UID.toUpperCase(); Serial.println(UID); mfrc522.PICC_HaltA(); return UID; }
`
Im thankful for every help i can get :)
— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/86, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp.
Hey, thanks for your reply.
The problem is that I must use the Stored Procedures, because they are processing very much things, like the timemanagement and they look that the user doesn't LogIn or LogOut twice. The Arduino cant process so much thinks this fast as the procedure does. I can post them here if you want to see it:
LogIn:
CREATE DEFINER=`Arduino`@`%` PROCEDURE `Abmeldung`(IN RFIDTAG text, IN GID integer)
BEGIN
declare AI integer;
declare Nam text;
declare LastStat text default "";
Select @Nam:=Name From namensdatenbank Where RFID = RFIDTAG;
Select @AI:=Intervall From namensdatenbank Where Name = @Nam;
Set @LastStat := "";
SET @sqlquery = concat('select @LastStat := Status from ' , @Nam , ' Where Status = "Anmeldung" AND AI = @AI');
Select @sqlquery;
PREPARE stmt FROM @sqlquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
If @LastStat = "Anmeldung" Then
Select @AI:=Intervall From namensdatenbank Where Name = @Nam;
SET @sqlquery = concat("INSERT INTO " , @Nam , "(AI,Name,Status,Zeit,GID) VALUES(", @AI , ",'", @Nam ,"','Abmeldung',current_timestamp," , GID ,")");
PREPARE stmt FROM @sqlquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
call Gesamtstunden(@Nam,@AI);
update namensdatenbank set Intervall = Intervall + 1 Where Name=@Nam;
Else
select "ERROR LETZES MAL ABMELDUNG";
insert into errorliste(Name,Error,Errorprozedur,TerminalID,Abgearbeitet) values (@Nam,"Doppelte Abmeldung", "Abmeldeprozedur", GID, "NEIN");
END IF;
END
LogOut:
CREATE DEFINER=`Arduino`@`%` PROCEDURE `Anmeldung`(IN RFIDTAG text, IN GID integer)
BEGIN
declare AI integer;
declare Nam text;
declare LastStat text default "";
Select Name From namensdatenbank Where RFID = RFIDTAG INTO @Nam;
SET @sqlquery = concat("CREATE TABLE IF NOT EXISTS " , @Nam , "(AI integer,Name text,Status text,Zeit timestamp,GID integer);");
PREPARE stmt FROM @sqlquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Select Intervall From namensdatenbank Where Name = @Nam INTO @AI;
Set @LastStat := "";
SET @sqlquery = concat('select Status from ' , @Nam , ' Where Status = "Anmeldung" AND AI = @AI INTO @LastStat');
PREPARE stmt FROM @sqlquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
If @LastStat = "Anmeldung" Then
select "ERROR LETZES MAL ANMELDUNG";
insert into errorliste(Name,Error,Errorprozedur,TerminalID,Abgearbeitet) values (@Nam,"Doppelte Anmeldung", "Anmeldeprozedur", GID,"NEIN");
Else
SET @sqlquery = concat("INSERT INTO " , @Nam , "(AI,Name,Status,Zeit,GID) VALUES(", @AI , ",'", @Nam ,"','Anmeldung',current_timestamp," , GID ,")");
PREPARE stmt FROM @sqlquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
select "Erfolgreich";
END IF;
END
Timemanagement:
CREATE DEFINER=`Arduino`@`%` PROCEDURE `Gesamtstunden`(IN Nam Text, IN IV integer)
BEGIN
declare times1 timestamp default 0;
declare times2 timestamp default 0;
declare Ges time default 0;
declare AlteZeit time default 0;
declare NeueZeit time default 0;
Set @times1 := 0;
Set @times2 := 0;
Set @NeueZeit := 0;
SET @sqlquery = concat('Select @times1 :=Zeit FROM ',Nam, ' WHERE Status = "Anmeldung" AND AI = ', IV);
Select @sqlquery;
PREPARE stmt FROM @sqlquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET @sqlquery = concat('Select @times2 :=Zeit FROM ',Nam, ' WHERE Status = "Abmeldung" AND AI = ', IV);
Select @sqlquery;
PREPARE stmt FROM @sqlquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Select @NeueZeit := timediff(@times2,@times1);
If @NeueZeit > "00:00:00" Then
Select @AlteZeit := Gesamtstunden FROM namensdatenbank where Name=Nam;
Select @NeueZeit := addtime(@AlteZeit,@NeueZeit);
Update namensdatenbank Set Gesamtstunden = @NeueZeit Where Name=Nam;
End If;
END
Like you see that is not possible to do in Arduino, because I need to hold the LogIn/LogOut time under one second.
I'm using MySQL Version: 8.0.14
Thanks for your help :) IceFox
I think there are two possibilities; one a bit far-fetched and another that may explain things.
First, is it possible the size of the RFID forces an overflow in the sprintf() call? The query variable is only 128 bytes.
Second, stored procedures are not expected to return results. Is it possible sometimes your procedures are returning results? If so, we will need a way to determine if there are results and then and only then read them. That could explain it.
On Thu, Mar 7, 2019 at 3:41 PM xIceFox notifications@github.com wrote:
Hey, thanks for your reply.
The problem is that I must use the Stored Procedures, because they are processing very much things, like the timemanagement and they look that the user doesn't LogIn or LogOut twice. The Arduino cant process so much thinks this fast as the procedure does. I can post them here if you want to see it:
LogIn: CREATE DEFINER=Arduino@%PROCEDUREAbmeldung`(IN RFIDTAG text, IN GID integer) BEGIN declare AI integer; declare Nam text; declare LastStat text default "";
Select @Nam:=Name From namensdatenbank Where RFID = RFIDTAG;
Select @AI:=Intervall From namensdatenbank Where Name = @Nam; Set @LastStat := ""; SET @sqlquery = concat('select @LastStat := Status from ' , @Nam , ' Where Status = "Anmeldung" AND AI = @AI'); Select @sqlquery; PREPARE stmt FROM @sqlquery; EXECUTE stmt; DEALLOCATE PREPARE stmt;
If @LastStat = "Anmeldung" Then Select @AI:=Intervall From namensdatenbank Where Name = @Nam; SET @sqlquery = concat("INSERT INTO " , @Nam , "(AI,Name,Status,Zeit,GID) VALUES(", @AI , ",'", @Nam ,"','Abmeldung',current_timestamp," , GID ,")"); PREPARE stmt FROM @sqlquery; EXECUTE stmt; DEALLOCATE PREPARE stmt; call Gesamtstunden(@Nam,@AI); update namensdatenbank set Intervall = Intervall + 1 Where Name=@Nam; Else select "ERROR LETZES MAL ABMELDUNG"; insert into errorliste(Name,Error,Errorprozedur,TerminalID,Abgearbeitet) values (@Nam,"Doppelte Abmeldung", "Abmeldeprozedur", GID, "NEIN"); END IF;
END '
LogOut:
CREATE DEFINER=Arduino@%PROCEDUREAnmeldung`(IN RFIDTAG text, IN GID integer) BEGIN declare AI integer; declare Nam text; declare LastStat text default "";
Select Name From namensdatenbank Where RFID = RFIDTAG INTO @Nam;
SET @sqlquery = concat("CREATE TABLE IF NOT EXISTS " , @Nam , "(AI integer,Name text,Status text,Zeit timestamp,GID integer);"); PREPARE stmt FROM @sqlquery; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Select Intervall From namensdatenbank Where Name = @Nam INTO @AI; Set @LastStat := ""; SET @sqlquery = concat('select Status from ' , @Nam , ' Where Status = "Anmeldung" AND AI = @AI INTO @LastStat'); PREPARE stmt FROM @sqlquery; EXECUTE stmt; DEALLOCATE PREPARE stmt;
If @LastStat = "Anmeldung" Then select "ERROR LETZES MAL ANMELDUNG"; insert into errorliste(Name,Error,Errorprozedur,TerminalID,Abgearbeitet) values (@Nam,"Doppelte Anmeldung", "Anmeldeprozedur", GID,"NEIN");
Else SET @sqlquery = concat("INSERT INTO " , @Nam , "(AI,Name,Status,Zeit,GID) VALUES(", @AI , ",'", @Nam ,"','Anmeldung',current_timestamp," , GID ,")"); PREPARE stmt FROM @sqlquery; EXECUTE stmt; DEALLOCATE PREPARE stmt; select "Erfolgreich"; END IF;
END`
Timemanagement:
CREATE DEFINER=Arduino@%PROCEDUREGesamtstunden`(IN Nam Text, IN IV integer) BEGIN declare times1 timestamp default 0; declare times2 timestamp default 0; declare Ges time default 0; declare AlteZeit time default 0; declare NeueZeit time default 0;
Set @times1 := 0; Set @Times2 https://github.com/Times2 := 0; Set @NeueZeit := 0;
SET @sqlquery https://github.com/sqlquery = concat('Select @times1 :=Zeit FROM ',Nam, ' WHERE Status = "Anmeldung" AND AI = ', IV); Select @sqlquery https://github.com/sqlquery; PREPARE stmt FROM @sqlquery https://github.com/sqlquery; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sqlquery https://github.com/sqlquery = concat('Select @Times2 https://github.com/Times2 :=Zeit FROM ',Nam, ' WHERE Status = "Abmeldung" AND AI = ', IV); Select @sqlquery https://github.com/sqlquery; PREPARE stmt FROM @sqlquery https://github.com/sqlquery; EXECUTE stmt; DEALLOCATE PREPARE stmt;
Select @NeueZeit := timediff(@Times2 https://github.com/Times2,@times1); If @NeueZeit > "00:00:00" Then Select @AlteZeit := Gesamtstunden FROM namensdatenbank where Name=Nam; Select @NeueZeit := addtime(@AlteZeit,@NeueZeit); Update namensdatenbank Set Gesamtstunden = @NeueZeit Where Name=Nam; End If; END`
Like you see that is not possible to do in Arduino, because I need to hold the LogIn/LogOut time under one second.
I'm using MySQL Version: 8.0.14
Thanks for your help :) IceFox
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/86#issuecomment-470685980, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MiLgh7hdkOM7cYRa82qQyIpqIPaks5vUXmJgaJpZM4bQ6Kp .
Catching up on some posts. Forgive me if I’ve asked this already, but what version of MySQL are you using? This error manifests in two cases; a) when using a non-Oracle version of MySQL, or b) when not reading the data from a previous query before running another. In (b), make sure you also read the column data since that is sent over the wire first. Check out the sample sketches for how to do this properly. That said, your sketch is using stored routines and if they are returning values or a result set, you must read that before executing the next query. Now, a challenge for you: write your SQL statements without the stored routines. IMHO, stored routines are short cuts and optimizations which can cause issues like this during development. I make it a point to avoid them until my code is working correctly - then I add the optimizations. ;) … On Feb 25, 2019, at 5:17 PM, xIceFox @.**> wrote: Hello, I do a project where I read RFID Card System to check how long people are on a event. My Arduino is the MySQL Client, which reads the RFID Cards and call procedures on the SQL Server. Im using the MySQL Connector to run stored Procedures in MySQL. At the End of the Procedure in MySQL I do: Select "Successful" or when the Procedure crashes: Select "Error" Now I want to read this with Basic Select. The Problem is: The First time I do the Basic Select, it works fine. I get the Information of the Procedure instantly. The Second Time I do the Basic Select, I get an empty Line back. The Third Time I do the Basic Select, I get an Bad mojo. EOF found reading column header. ERROR: You must read the columns first!. I can post my Serial Monitor here: 22:55:15.378 -> Connecting... 22:55:17.623 -> Connected to server version 8.0.14 22:59:30.872 -> Modus: Anmelden 22:59:32.067 -> Gelesene UID: 7A 2E 8C BB 22:59:32.615 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:32.719 -> Successful 22:59:32.719 -> Anmeldung abgeschlossen :) 22:59:34.122 -> Gelesene UID: 7A 2E 8C BB 22:59:34.666 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:35.555 -> 22:59:35.555 -> Anmeldung abgeschlossen :) 22:59:36.810 -> Gelesene UID: 7A 2E 8C BB 22:59:37.355 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:37.390 -> Bad mojo. EOF found reading column header. 22:59:37.424 -> ERROR: You must read the columns first! 22:59:37.459 -> 22:59:37.459 -> Anmeldung abgeschlossen :) I don't know why, but when I reset the Arduino, or I reconnect to the Server with the SoftReset, the first time it will work. The Problem is, that I haven't the time to soft reset my Arduino every time, because there are many people trying to get into this event. My code is here: `#include
#include )&client); MFRC522 mfrc522(SS_PIN, RST_PIN); int GID = 1; char query[128]; char SQLQueryAnmeldung[] = "call sas.Anmeldung('%s',%d);"; char SQLQueryAbmeldung[] = "call sas.Abmeldung('%s',%d);"; String Modus; void setup() { pinMode(30, INPUT); pinMode(31, INPUT); pinMode(22, OUTPUT); pinMode(24, OUTPUT); pinMode(26, OUTPUT); Serial.begin(9600); SPI.begin(); mfrc522.PCD_Init(); Ethernet.begin(mac_addr); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(500); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } else { Serial.println("Connection failed."); digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); } } void loop() { if (mfrc522.PICC_IsNewCardPresent() && mfrc522.PICC_ReadCardSerial() ) { if (Modus == "Anmeldung") { SQLAnmeldung(ReadTag()); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } if (Modus == "Abmeldung") { SQLAbmeldung(ReadTag()); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } } if (digitalRead(30)) { Modus = "Anmeldung"; delay(1000); Serial.println("Modus: Anmelden"); } if (digitalRead(31)) { Modus = "Abmeldung"; delay(1000); Serial.println("Modus: Abmelden"); } } void SQLAnmeldung(String RFID) { row_values row = NULL; String Res = ""; digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); delay(500); sprintf(query, SQLQueryAnmeldung, RFID.c_str(), GID); Serial.println(query); MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); column_names columns = cur_mem->get_columns(); do { row = cur_mem->get_next_row(); if (row != NULL) { Res = row->values[0]; } } while (row != NULL); delete cur_mem; Serial.println(Res); Serial.println("Anmeldung abgeschlossen :)"); delay(500); } void SQLAbmeldung(String RFID) { digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); delay(500); sprintf(query, SQLQueryAbmeldung, RFID.c_str(), GID); MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); delete cur_mem; Serial.println("Abmeldung abgeschlossen :)"); } String ReadTag() { String UID; Serial.print("Gelesene UID: "); for (byte i = 0; i < mfrc522.uid.size; i++) { if (mfrc522.uid.uidByte[i] < 0x10) { UID = UID + "0"; } UID = UID + String(mfrc522.uid.uidByte[i], HEX); if (i != mfrc522.uid.size - 1) { UID = UID + " "; } } UID.toUpperCase(); Serial.println(UID); mfrc522.PICC_HaltA(); return UID; } ` Im thankful for every help i can get :) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#86>, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp.#include #include #include #define RST_PIN 5 #define SS_PIN 53 IPAddress server_addr(I will hide this for security reasons :D); byte mac_addr[] = {I will hide this for security reasons :D }; char user[] = "I will hide this for security reasons :D"; char password[] = "I will hide this for security reasons :D"; EthernetClient client; MySQL_Connection conn((Client
Hey im having the issue that you put as option B). How do I make sure that I also read column data?
Include this line of code. See the example ’select' sketches.
column_names *cols = cur_mem->get_columns();
On Nov 11, 2020, at 1:04 PM, aguslopezz notifications@github.com wrote:
Catching up on some posts. Forgive me if I’ve asked this already, but what version of MySQL are you using? This error manifests in two cases; a) when using a non-Oracle version of MySQL, or b) when not reading the data from a previous query before running another. In (b), make sure you also read the column data since that is sent over the wire first. Check out the sample sketches for how to do this properly. That said, your sketch is using stored routines and if they are returning values or a result set, you must read that before executing the next query. Now, a challenge for you: write your SQL statements without the stored routines. IMHO, stored routines are short cuts and optimizations which can cause issues like this during development. I make it a point to avoid them until my code is working correctly - then I add the optimizations. ;) … <x-msg://2/#> On Feb 25, 2019, at 5:17 PM, xIceFox @.**> wrote: Hello, I do a project where I read RFID Card System to check how long people are on a event. My Arduino is the MySQL Client, which reads the RFID Cards and call procedures on the SQL Server. Im using the MySQL Connector to run stored Procedures in MySQL. At the End of the Procedure in MySQL I do: Select "Successful" or when the Procedure crashes: Select "Error" Now I want to read this with Basic Select. The Problem is: The First time I do the Basic Select, it works fine. I get the Information of the Procedure instantly. The Second Time I do the Basic Select, I get an empty Line back. The Third Time I do the Basic Select, I get an Bad mojo. EOF found reading column header. ERROR: You must read the columns first!. I can post my Serial Monitor here: 22:55:15.378 -> Connecting... 22:55:17.623 -> Connected to server version 8.0.14 22:59:30.872 -> Modus: Anmelden 22:59:32.067 -> Gelesene UID: 7A 2E 8C BB 22:59:32.615 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:32.719 -> Successful 22:59:32.719 -> Anmeldung abgeschlossen :) 22:59:34.122 -> Gelesene UID: 7A 2E 8C BB 22:59:34.666 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:35.555 -> 22:59:35.555 -> Anmeldung abgeschlossen :) 22:59:36.810 -> Gelesene UID: 7A 2E 8C BB 22:59:37.355 -> call sas.Anmeldung('7A 2E 8C BB',1); 22:59:37.390 -> Bad mojo. EOF found reading column header. 22:59:37.424 -> ERROR: You must read the columns first! 22:59:37.459 -> 22:59:37.459 -> Anmeldung abgeschlossen :) I don't know why, but when I reset the Arduino, or I reconnect to the Server with the SoftReset, the first time it will work. The Problem is, that I haven't the time to soft reset my Arduino every time, because there are many people trying to get into this event. My code is here: #include
#include )&client); MFRC522 mfrc522(SS_PIN, RST_PIN); int GID = 1; char query[128]; char SQLQueryAnmeldung[] = "call sas.Anmeldung('%s',%d);"; char SQLQueryAbmeldung[] = "call sas.Abmeldung('%s',%d);"; String Modus; void setup() { pinMode(30, INPUT); pinMode(31, INPUT); pinMode(22, OUTPUT); pinMode(24, OUTPUT); pinMode(26, OUTPUT); Serial.begin(9600); SPI.begin(); mfrc522.PCD_Init(); Ethernet.begin(mac_addr); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(500); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } else { Serial.println("Connection failed."); digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); } } void loop() { if (mfrc522.PICC_IsNewCardPresent() && mfrc522.PICC_ReadCardSerial() ) { if (Modus == "Anmeldung") { SQLAnmeldung(ReadTag()); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } if (Modus == "Abmeldung") { SQLAbmeldung(ReadTag()); digitalWrite(26, LOW); digitalWrite(24, HIGH); digitalWrite(22, LOW); } } if (digitalRead(30)) { Modus = "Anmeldung"; delay(1000); Serial.println("Modus: Anmelden"); } if (digitalRead(31)) { Modus = "Abmeldung"; delay(1000); Serial.println("Modus: Abmelden"); } } void SQLAnmeldung(String RFID) { row_values row = NULL; String Res = ""; digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); delay(500); sprintf(query, SQLQueryAnmeldung, RFID.c_str(), GID); Serial.println(query); MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); column_names columns = cur_mem->get_columns(); do { row = cur_mem->get_next_row(); if (row != NULL) { Res = row->values[0]; } } while (row != NULL); delete cur_mem; Serial.println(Res); Serial.println("Anmeldung abgeschlossen :)"); delay(500); } void SQLAbmeldung(String RFID) { digitalWrite(26, HIGH); digitalWrite(24, LOW); digitalWrite(22, LOW); delay(500); sprintf(query, SQLQueryAbmeldung, RFID.c_str(), GID); MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); delete cur_mem; Serial.println("Abmeldung abgeschlossen :)"); } String ReadTag() { String UID; Serial.print("Gelesene UID: "); for (byte i = 0; i < mfrc522.uid.size; i++) { if (mfrc522.uid.uidByte[i] < 0x10) { UID = UID + "0"; } UID = UID + String(mfrc522.uid.uidByte[i], HEX); if (i != mfrc522.uid.size - 1) { UID = UID + " "; } } UID.toUpperCase(); Serial.println(UID); mfrc522.PICC_HaltA(); return UID; } Im thankful for every help i can get :) — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#86 https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/86>, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp.#include #include #include #define RST_PIN 5 #define SS_PIN 53 IPAddress server_addr(I will hide this for security reasons :D); byte mac_addr[] = {I will hide this for security reasons :D }; char user[] = "I will hide this for security reasons :D"; char password[] = "I will hide this for security reasons :D"; EthernetClient client; MySQL_Connection conn((Client Hey im having the issue that you put as option B). How do I make sure that I also read column data?
— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/86#issuecomment-725575186, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYD2DIRYVBBBCL3NOOTSPLG2ZANCNFSM4G2DUKUQ.
Hey, the event was on October last year. I have found a solution. I didnt use the MYSQL Library and wrote a custom php API myself, and passed the data there. If someone experiences the same problems, write me. I can help you with my API.
hey xlceFox, can you share your custom PHP API? Sorry that I'm writing here, but I see no other option.
I will send it this evening in here. If I forgot it, write me again tomorrow 😉
Ok, thank you!
So, can you share? Tank You!
$db_server = 'Database IP';
$db_user = 'Database User';
$db_password = 'Database Password';
$db_name = 'Database Name';
$php_pw = "Password to run this script";
$pw = $_GET['PW'];
if ($pw == $php_pw){
// Create connection
$conn = new mysqli($db_server, $db_user, $db_password, $db_name);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$stmt = $conn->prepare("Put SQL Statement here");
$stmt->bind_param("si", $rfid, $GID); // If you want to put variables in it
$rfid = $_GET['RFID']; Here i get variables from the Arduino (Grab it from the link)
$GID = $_GET['GID'];
$stmt->execute();
$stmt->close();
$sql = "SELECT x FROM table";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
// output data of each row
while($row = $result->fetch_assoc()) {
echo "<p>" . $row["Put Table Row here to Output"] . "</p>";
}
}
$sql = "Another SQL Statement";
$result = $conn->query($sql);
$conn->close();
} else {
echo "<p>What schould go back to the Arduino. You can grab it with the Arduino by reading the html output</p>";
}
?>
Ok, thank you!
Hello, I do a project where I read RFID Card System to check how long people are on a event. My Arduino is the MySQL Client, which reads the RFID Cards and call procedures on the SQL Server. Im using the MySQL Connector to run stored Procedures in MySQL. At the End of the Procedure in MySQL I do:
Select "Successful" or when the Procedure crashes: Select "Error"
Now I want to read this with Basic Select.
The Problem is: The First time I do the Basic Select, it works fine. I get the Information of the Procedure instantly.
The Second Time I do the Basic Select, I get an empty Line back.
The Third Time I do the Basic Select, I get an Bad mojo. EOF found reading column header. ERROR: You must read the columns first!.
I can post my Serial Monitor here:
I don't know why, but when I reset the Arduino, or I reconnect to the Server with the SoftReset, the first time it will work. The Problem is, that I haven't the time to soft reset my Arduino every time, because there are many people trying to get into this event.
My code is here:
Im thankful for every help i can get :)