Open arti86pl opened 5 years ago
Please post your sketch and stored procedure or PM it to me and I’ll look at it.
On Mon, Jul 29, 2019 at 07:05 arti86pl notifications@github.com wrote:
When I try to call the procedure: call testbd.test_procedure() it doesn't work. I have to run a full query to get the data I need. It's painful because of procedures have less text and also can be managed on the server. I'm running MySQL Server 5.6.33
— 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/106?email_source=notifications&email_token=AB6SHYGAPZGXZIKMO3CV6DLQB3FJJA5CNFSM4IHRIO32YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HCAP74Q, or mute the thread https://github.com/notifications/unsubscribe-auth/AB6SHYB4GWWDMGONSEAT47TQB3FJJANCNFSM4IHRIO3Q .
Part of loop: ` char id[] = "5"; char data[] = "TEST"; static const char PROGMEM QUERY_POP[] = "call testdb.test_procedure('%s', '%s');"; char query[256];
sprintf(query, QUERY_POP, id, data);
// Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Execute the query cur_mem->execute(query);
// Fetch the columns and print them cur_mem->get_columns();
// Read the rows and print them row_values *row = NULL; byte open = 0;
do { row = cur_mem->get_next_row();
if (row != NULL)
{
strcpy(uid, row->values[0]);
if(strcmp(uid, "0") == 0)
{
open = 0;
}
else
{
open = 1;
}
}
} while (row != NULL);
delete cur_mem;
Procedure in database:
CREATE DEFINER=test
@xxx.xxx.xxx.xxx
PROCEDURE test_procedure
(in id VARCHAR(3), in data VARCHAR(10))
BEGIN
select if(count(*) > 0, uid, 0) as accessGranded from list join manager on list.id = manager.allowed where list.id = id and status = 1 and uid = (select id from users where id = data;
END
`
The procedure works perfectly when I test it with the same inputs as the above code. Just hangs when I want to call it from NodeMCU
Ok, I’ll look at an example. In the meantime, what do you see in the serial monitor - anything?
On Jul 29, 2019, at 8:24 AM, arti86pl notifications@github.com wrote:
Part of loop: ` char id[] = "5"; char data[] = "TEST"; static const char PROGMEM QUERY_POP[] = "call testdb.test_procedure('%s', '%s');"; char query[256];
sprintf(query, QUERY_POP, id, data);
// Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Execute the query cur_mem->execute(query);
// Fetch the columns and print them cur_mem->get_columns();
// Read the rows and print them row_values *row = NULL; byte open = 0;
do { row = cur_mem->get_next_row();
if (row != NULL) { strcpy(uid, row->values[0]);
if(strcmp(uid, "0") == 0) { open = 0; } else { open = 1; } } } while (row != NULL);
delete cur_mem; Procedure in database: CREATE DEFINER=test@xxx.xxx.xxx.xxx PROCEDURE test_procedure(in id VARCHAR(3), in data VARCHAR(10)) BEGIN select if(count(*) > 0, uid, 0) as accessGranded from list join manager on list.id = manager.allowed where list.id = id and status = 1 and uid = (select id from users where id = data; END `
The procedure works perfectly when I test it with the same inputs as the above code. Just hangs when I want to call it from NodeMCU
— You are receiving this because you commented. Reply to this email directly, view it on GitHub, or mute the thread.
Ok, works for me. Try using the show_results() method as a test. Like this:
char query[] = "call test_rfid.test_proc('a', '123')"; ... MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); cur_mem->execute(query); cur_mem->show_results();
If you're still not getting results, place # define DEBUG 1 in the mysql_packet.cpp file and recompile then post your results from the serial monitor. This will show more information about the errors if there are any.
When I try to call the procedure:
call testbd.test_procedure()
it doesn't work. I have to run a full query to get the data I need. It's painful because of procedures have less text and also can be managed on the server. I'm running MySQL Server 5.6.33