search

ChuckBell / MySQL_Connector_Arduino

Database connector library for using MySQL with your Arduino projects.
331 stars 132 forks source link

Can't read return from DB function #88

Closed hserras closed 2 years ago

hserras commented 5 years ago

Hello all, I'm doing a project which I connect a ESP8266 to a mySQL database. If I use some select statements, I can then iterate every row and column, with no problem. But if I call a function, I can't read the rows. It just blocks. Can you tell me if this library doesn't work with functions in DB?

Thanks in advance

ChuckBell commented 5 years ago

It may depend. What version of MySQL are you using? What is the SQL you’re trying to run?

On Wed, Mar 6, 2019 at 14:02 Hugo Serras notifications@github.com wrote:

Hello all, I'm doing a project which I connect a ESP8266 to a mySQL database. If I use some select statements, I can then iterate every row and column, with no problem. But if I call a function, I can't read the rows. It just blocks. Can you tell me if this library doesn't work with functions in DB?

Thanks in advance

— 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/88, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4L_VxgX0WZ63JOhrYxXxL5KfvPTPks5vUBC9gaJpZM4bhmAy .

hserras commented 5 years ago

ChuckBell, thank you very much for your quick reply.

I'm using mySQL version 15.1 Distrib 5.5.60-MariaDB in an Azure machine.

The statement that i'm running is: SET @p0='DD EC B4 89'; SELECT CheckUserAccess(@p0) AS CheckUserAccess; This statement only returns the column name and one row, with a 0 or a 1.

For now i'm trying with a single column and a single row, but the idea is to return more data.

If I do the same with a simple select, I can achieve what I want. But the idea is to make some functions and return data to keep the knowledge in the DB and not in the ESP8266

I would really appreciate your help, since this is for a work in progress to a university project

ChuckBell commented 5 years ago

Sadly, the connector does not work with non-Oracle MySQL variants. Try again with a MySQL version distributed by Oracle.

Plus, you should execute one and only one query at a time. Try again with only one query per call.

On Wed, Mar 6, 2019 at 2:16 PM Hugo Serras notifications@github.com wrote:

ChuckBell, thank you very much for your quick reply.

I'm using mySQL version 15.1 Distrib 5.5.60-MariaDB in an Azure machine.

The statement that i'm running is: SET @p0 https://github.com/p0='DD EC B4 89'; SELECT CheckUserAccess(@p0 https://github.com/p0) AS CheckUserAccess; This statement only returns the column name and one row, with a 0 or a 1.

For now i'm trying with a single column and a single row, but the idea is to return more 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/88#issuecomment-470238282, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4FEU-NeOndNBb_6yIwlfJbyZ4h_3ks5vUBP3gaJpZM4bhmAy .

hserras commented 5 years ago

Thanks, ChuckBell.

I will try first doing one query at a time and if not possible, i'll try with a oracle mysql

Thanks a lot.

hserras commented 5 years ago

Hello ChuckBell, Sorry to be bothering you again.

I have installed a new mySQL database, and now I have this version: mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64)

If I call both statements at the same time, the behavior is precisely the same, it blocks. But then, I thought of what you said, instead of running one sentence with two selects, run them separated.

But, if I ran the first part of the statement: SET @p0='DD EC B4 89';

And then the second: SELECT CheckUserAccess(@p0) AS CheckUserAccess;

At the time that I run the second, I do not have the value anymore and it returns me a NULL. Do you have any idea? How can I keep the value passed as @p0 ????

Thanks for your pacience

ChuckBell commented 5 years ago

That version is not an Oracle MySQL, sorry. I can tell from the version number. If you’re using a Debian Linux system, you can download the apt repository from Oracles’ MySQL website or you can download the installers for your specific OS from the same site.

However, you should be able to get your query to work. What you will need to do is make the query dynamically load the variable. So, you won’t be using a variable in the local variable MySQL sense, rather, you would pass the information to the query using sprintf(). Take a look at the select example sketches for the connector and the documentation for how to do this.

Or, if you want to test it statically, just hard code the value. ;)

On Mar 7, 2019, at 11:32 AM, Hugo Serras notifications@github.com wrote:

Hello ChuckBell, Sorry to be bothering you again.

I have installed a new mySQL database, and now I have this version: mysql Ver 14.14 Distrib 5.7.25, for Linux (x86_64)

If I call both statements at the same time, the behavior is precisely the same, it blocks. But then, I thought of what you said, instead of running one sentence with two selects, run them separated.

But, if I ran the first part of the statement: SET @p0 https://github.com/p0='DD EC B4 89';

And then the second: SELECT CheckUserAccess(@p0 https://github.com/p0) AS CheckUserAccess;

At the time that I run the second, I do not have the value anymore and it returns me a NULL. Do you have any idea? How can I keep the value passed as @p0 https://github.com/p0 ????

Thanks for your pacience

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/88#issuecomment-470596500, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4NRy_3ZRPmSL4YgGFW-JZV4FYjyTks5vUT8AgaJpZM4bhmAy.

hserras commented 5 years ago

Hello Chuck,

Thanks for the info. I will try to install the "real" mySQL from oracle.

But before that, I already tried with sprintf(), both hardcoded and dynamically and the behavior is the same. I'm starting to believe that I really can't run to statements in the same query.

cur_mem->execute(query2); column_names cols = cur_mem->get_columns(); row_values row = NULL; do { Serial.println("beginning do while"); row = cur_mem->get_next_row(); Serial.println("xpto");

He seems to run the query2, he enters the do while loop, because he prints the Serial.println("beginning do while"); , but the second one he doesn't print.

So, do you think that with the Oracle MySQL i will get past this? What's your opinion?

Thanks, mate

ChuckBell commented 5 years ago

Well, no. The connector is what doesn't support running two queries in the same pass. You will have to do them one at a time. What I mean by passing in the data is this:

char query[] = "call test.makeit(%d)"; char buffer[256]; ... sprintf(buffer, query, my_int); ... cur->execute(buffer);

I can send you a complete example if that will help.

genesiopmn commented 4 years ago

I have same problem, i will apreciate so much an exemple. TKS.

genesiopmn commented 4 years ago

`#include

include

include

include

include

include

include

include

include

include

include

include

include

include

include

/ wiring the MFRC52, to ESP8266 (ESP-12) RST = GPIO0 D3 SDA(SS) = GPIO15 D8 MOSI = GPIO13 D7 MISO = GPIO12 D6 SCK = GPIO14 D5 GND = GND 3.3V = 3.3V /

//const uint16_t mqtt_port = 11113; //Port của CloudMQTT

define RST_PIN 0 // RST-PIN für RC52, - RFID - SPI - Modul GPIO0

define SS_PIN 15 // SDA-PIN für RC52, - RFID - SPI - Modul GPIO15

const char ssid = "xxxx"; // change according to your Network - cannot be longer than 32 characters! const char pass = "xxxxx" ; // change according to your Network char *msg;

// NETWORK: Static IP details... IPAddress ip(192, 168, 88, 200); IPAddress gateway(192, 168, 88, 1); IPAddress subnet(255, 255, 255, 0);

define redled 10

define greenled 9

define boardled 2

int timezone = -3; //int countPerson =0; MFRC522 mfrc522(SS_PIN, RST_PIN); //--> Create MFRC522 instance. LiquidCrystal_I2C lcd(0x27, 16, 2);

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

int GID = 1; char SQLQueryEntrar[] = "SELECT * from dbname.users where RFID = %s;"; char SQLQuerySair[] = "INSERT INTO nodemcu_rfidrc522_mysql.tb_saida (Status,RFID,Time,GID) VALUES('Saida','%s',current_timestamp)"; char query[256]; String Modus = "Entrar";

IPAddress server_addr(192, 168, 88, 11); // MySQL server IP char user[] = "rfid"; // MySQL user char password[] = "xxxxx"; // MySQL password

void setup() { //----------------SETUP-----------------

pinMode(redled, OUTPUT); digitalWrite(redled, LOW); pinMode(greenled, OUTPUT); digitalWrite(greenled, LOW);

Serial.begin(115200); // Initialize serial communications delay(10);

pinMode(boardled, OUTPUT); SPI.begin(); // Init SPI bus mfrc522.PCD_Init(); // Init MFRC52, Serial.println(); Serial.println(ssid);

// Static IP Setup Info Here... // config static IP WiFi.config(ip, gateway, subnet); while ((WiFi.status() != WL_CONNECTED)) { delay(500); Serial.print("."); } Serial.println(""); Serial.println("WiFi conectado");

// Start the server // server.begin(); Serial.println("Servidor iniciado"); Serial.print(WiFi.localIP()); delay(1000);

configTime(timezone * 3600, 0, "0.pool.ntp.org", "time.nist.gov"); Serial.println("\nConectando ao servidor de horas"); while (!time(nullptr)) { Serial.print("."); delay(1000); } Serial.println(""); Serial.println("Conectando ao DB"); while (conn.connect(server_addr, 3306, user, password) != true) { delay(200); Serial.print ( "." ); digitalWrite (boardled, HIGH); }

Serial.println(""); Serial.println("Conectado com MySQL Server!");

Serial.println(F("Pronto!")); Serial.println(F("======================================================")); Serial.println(F("Passe o cartão UID:"));

} //---------- FIM DO SETUP -----

void loop() { //---------- INICIO DO LOOP -----

if (mfrc522.PICC_IsNewCardPresent() && mfrc522.PICC_ReadCardSerial() ) { if (Modus == "Entrar") { SQLEntrar(ReadTag()); digitalWrite(10, LOW); digitalWrite(9, HIGH); digitalWrite(2, LOW); }

if (Modus == "Sair") {
  SQLSair(ReadTag());
  digitalWrite(10, LOW);
  digitalWrite(9, HIGH);
  digitalWrite(2, LOW);
}

}

/ if (digitalRead(30)) { Modus = "Anmeldung"; delay(1000); Serial.println("Modus: Anmelden"); } if (digitalRead(31)) { Modus = "Abmeldung"; delay(1000); Serial.println("Modus: Abmelden"); } / }

void SQLEntrar(String RFID) { row_values row = NULL; String Res = ""; digitalWrite(10, HIGH); digitalWrite(9, LOW); digitalWrite(2, LOW); delay(500); sprintf(query, SQLQueryEntrar, RFID.c_str()); 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("Entrada liberada :)"); delay(500);

}

void SQLSair(String RFID) { digitalWrite(10, HIGH); digitalWrite(9, LOW); digitalWrite(2, LOW); delay(500); sprintf(query, SQLQuerySair, RFID.c_str()); MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); delete cur_mem; Serial.println("Sair concluído :)"); }

String ReadTag() { String UID; Serial.print("Ler 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; }

void tryconnect() { if (!conn.connected()) { delay(500); digitalWrite(10, LOW); digitalWrite(9, HIGH); digitalWrite(2, LOW); } else { Serial.println("Connection failed."); Serial.println("Connecting..."); (conn.connect(server_addr, 3306, user, password)); digitalWrite(10, LOW); digitalWrite(9, LOW); digitalWrite(2, LOW); if (conn.connected()) { digitalWrite(10, LOW); digitalWrite(9, LOW); digitalWrite(2, LOW); Serial.println("SQL Connected succesful");

}

} }

`

ChuckBell commented 4 years ago

Which problem? There are at least two mentioned here. If the problem relates to the topic, which version of MySQL are you using?

On Wed, Feb 12, 2020 at 02:35 genesiopmn notifications@github.com wrote:

`#include

include

include

include

include

include

include

include

include

include

include

include

include

include

include

/ wiring the MFRC52, to ESP8266 (ESP-12) RST = GPIO0 D3 SDA(SS) = GPIO15 D8 MOSI = GPIO13 D7 MISO = GPIO12 D6 SCK = GPIO14 D5 GND = GND 3.3V = 3.3V /

//const uint16_t mqtt_port = 11113; //Port của CloudMQTT

define RST_PIN 0 // RST-PIN für RC52, - RFID - SPI - Modul GPIO0

define SS_PIN 15 // SDA-PIN für RC52, - RFID - SPI - Modul GPIO15

const char ssid = "xxxx"; // change according to your Network - cannot be longer than 32 characters! const char pass = "xxxxx" ; // change according to your Network char *msg;

// NETWORK: Static IP details... IPAddress ip(192, 168, 88, 200); IPAddress gateway(192, 168, 88, 1); IPAddress subnet(255, 255, 255, 0);

define redled 10

define greenled 9

define boardled 2

int timezone = -3; //int countPerson =0; MFRC522 mfrc522(SS_PIN, RST_PIN); //--> Create MFRC522 instance. LiquidCrystal_I2C lcd(0x27, 16, 2);

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

int GID = 1; char SQLQueryEntrar[] = "SELECT * from dbname.users where RFID = (%s);"; char SQLQuerySair[] = "INSERT INTO nodemcu_rfidrc522_mysql.tb_saida (Status,RFID,Time,GID) VALUES('Saida','%s',current_timestamp)"; char query[256]; String Modus = "Entrar";

IPAddress server_addr(192, 168, 88, 11); // MySQL server IP char user[] = "rfid"; // MySQL user char password[] = "xxxxx"; // MySQL password

void setup() { //----------------SETUP-----------------

pinMode(redled, OUTPUT); digitalWrite(redled, LOW); pinMode(greenled, OUTPUT); digitalWrite(greenled, LOW);

Serial.begin(115200); // Initialize serial communications delay(10);

pinMode(boardled, OUTPUT); SPI.begin(); // Init SPI bus mfrc522.PCD_Init(); // Init MFRC52, Serial.println(); Serial.println(ssid);

// Static IP Setup Info Here... // config static IP WiFi.config(ip, gateway, subnet); while ((WiFi.status() != WL_CONNECTED)) { delay(500); Serial.print("."); } Serial.println(""); Serial.println("WiFi conectado");

// Start the server // server.begin(); Serial.println("Servidor iniciado"); Serial.print(WiFi.localIP()); delay(1000);

configTime(timezone * 3600, 0, "0.pool.ntp.org", "time.nist.gov"); Serial.println("\nConectando ao servidor de horas"); while (!time(nullptr)) { Serial.print("."); delay(1000); } Serial.println(""); Serial.println("Conectando ao DB"); while (conn.connect(server_addr, 3306, user, password) != true) { delay(200); Serial.print ( "." ); digitalWrite (boardled, HIGH); }

Serial.println(""); Serial.println("Conectado com MySQL Server!");

Serial.println(F("Pronto!"));

Serial.println(F("======================================================")); Serial.println(F("Passe o cartão UID:"));

} //---------- FIM DO SETUP -----

void loop() { //---------- INICIO DO LOOP -----

if (mfrc522.PICC_IsNewCardPresent() && mfrc522.PICC_ReadCardSerial() ) { if (Modus == "Entrar") { SQLEntrar(ReadTag()); digitalWrite(10, LOW); digitalWrite(9, HIGH); digitalWrite(2, LOW); }

if (Modus == "Sair") {

SQLSair(ReadTag());

digitalWrite(10, LOW);

digitalWrite(9, HIGH);

digitalWrite(2, LOW);

}

}

/ if (digitalRead(30)) { Modus = "Anmeldung"; delay(1000); Serial.println("Modus: Anmelden"); } if (digitalRead(31)) { Modus = "Abmeldung"; delay(1000); Serial.println("Modus: Abmelden"); } / }

void SQLEntrar(String RFID) { row_values row = NULL; String Res = ""; digitalWrite(10, HIGH); digitalWrite(9, LOW); digitalWrite(2, LOW); delay(500); sprintf(query, SQLQueryEntrar, RFID.c_str()); 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("Entrada liberada :)"); delay(500);

}

void SQLSair(String RFID) { digitalWrite(10, HIGH); digitalWrite(9, LOW); digitalWrite(2, LOW); delay(500); sprintf(query, SQLQuerySair, RFID.c_str()); MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); delete cur_mem; Serial.println("Sair concluído :)"); }

String ReadTag() { String UID; Serial.print("Ler 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; }

void tryconnect() { if (!conn.connected()) { delay(500); digitalWrite(10, LOW); digitalWrite(9, HIGH); digitalWrite(2, LOW); } else { Serial.println("Connection failed."); Serial.println("Connecting..."); (conn.connect(server_addr, 3306, user, password)); digitalWrite(10, LOW); digitalWrite(9, LOW); digitalWrite(2, LOW); if (conn.connected()) { digitalWrite(10, LOW); digitalWrite(9, LOW); digitalWrite(2, LOW); Serial.println("SQL Connected succesful");

}

} }

`

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/88?email_source=notifications&email_token=AB6SHYCD6QM4BJVUA5W7LH3RCORDPA5CNFSM4G4GMAZKYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOELPXWGI#issuecomment-585071385, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYAE2HB55N5JWDRJFLTRCORDPANCNFSM4G4GMAZA .

genesiopmn commented 4 years ago

Forgive me Chuck. Thanks to your guidance on the mysql version, I was able, after many sleepless nights, to make a select. I used MariaDB 14 "Realy!! Does not WORK!". With it I was able to insert. I downloaded the Oracle MySQL version.

ChuckBell commented 4 years ago

Ok, let me know how it goes.

ChuckBell commented 2 years ago

Closed due to inactivity. Please open a new ticket if this is still relevant.