Bad mojo. EOF found reading column header. ERROR: You must read the columns first!

[xIceFox]

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 <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

#include <SPI.h>
#include <MFRC522.h>


#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 :)

[ChuckBell]

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 <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h> #include <SPI.h> #include <MFRC522.h> #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 <#86>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp>.

[xIceFox]

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

[ChuckBell]

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 ***@***.***> 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 ***@***.******@***.***); update namensdatenbank set Intervall = Intervall + 1 Where ***@***.***; Else select "ERROR LETZES MAL ABMELDUNG"; insert into errorliste(Name,Error,Errorprozedur,TerminalID,Abgearbeitet) values ***@***.***,"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 ***@***.***,"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 := ***@***.*** ***@***.***); If @NeueZeit > "00:00:00" Then Select @AlteZeit := Gesamtstunden FROM namensdatenbank where Name=Nam; Select @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 <#86 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AH0j4MiLgh7hdkOM7cYRa82qQyIpqIPaks5vUXmJgaJpZM4bQ6Kp> .

[aguslopezz]

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 <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h> #include <SPI.h> #include <MFRC522.h> #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 <#86>, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp.

Hey im having the issue that you put as option B). How do I make sure that I also read column data?

[ChuckBell]

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 ***@***.***> 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 <Ethernet.h> #include <MySQL_Connection.h> #include <MySQL_Cursor.h> #include <SPI.h> #include <MFRC522.h> #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 <#86 <#86>>, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp <https://github.com/notifications/unsubscribe-auth/AH0j4MO7ZjxNIanP69gxqR62CY9039tiks5vRGDigaJpZM4bQ6Kp>. 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 <#86 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AB6SHYD2DIRYVBBBCL3NOOTSPLG2ZANCNFSM4G2DUKUQ>.

[xIceFox]

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.

[KarcinMarkowski]

hey xlceFox, can you share your custom PHP API? Sorry that I'm writing here, but I see no other option.

[xIceFox]

I will send it this evening in here. If I forgot it, write me again tomorrow 😉

[KarcinMarkowski]

Ok, thank you!

[KarcinMarkowski]

So, can you share? Tank You!

[xIceFox]

$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>";
}
?>

[KarcinMarkowski]

Ok, thank you!