Calling stored procedures not working

[arti86pl]

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

[ChuckBell]

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 [email protected] 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 .

[arti86pl]

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

[ChuckBell]

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 [email protected] 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 [email protected] 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.

[ChuckBell]

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.