Iam doing a database search (db: door, table: tag, column: id) for a specific string and i want to know if the string found in database to write in a variable value 1 and if not 0. Iam trying to understand using do_query how can i have my result in a variable??
Iam stuck on that one some days now and i would appreciate a push.
Thnx in advance.
#include <SPI.h>
#include <Ethernet.h>
#include <sha1.h>
#include <stdlib.h>
#include <mysql.h>
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(xxx,xxx,xxx,xxx);
IPAddress ip(xxx,xxx,xxx,xxx);
char user[] = "xxx";
char password[] = "xxx";
Connector my_conn; // The Connector/Arduino reference
void setup()
{
Serial.begin(9600);
Ethernet.begin(mac_addr,ip);
char id[] = "61BF49A49G";
char QUERY_POP[64];
sprintf(QUERY_POP,"SELECT id FROM door.tag WHERE id = '%s'",id);
if (my_conn.mysql_connect(server_addr, 3306, user, password))
{
delay(1000);
do_query(QUERY_POP);
}
else
Serial.println("Connection failed.");
}
void loop()
{
}
void do_query(const char *q)
{
column_names *c; // pointer to column values
row_values *r; // pointer to row values
// First, execute query. If it returns a value pointer,
// we have a result set to process. If not, we exit.
if (!my_conn.cmd_query(q))
{
return;
}
// Next, we read the column names and display them.
//
// NOTICE: You must *always* read the column names even if
// you do not use them. This is so the connector can
// read the data out of the buffer. Row data follows the
// column data and thus must be read first.
c = my_conn.get_columns();
for (int i = 0; i < c->num_fields; i++)
{
Serial.print(c->fields[i]->name);
if (i < c->num_fields - 1)
{
Serial.print(",");
}
}
Serial.println();
// Next, we use the get_next_row() iterator and read rows printing
// the values returned until the get_next_row() returns NULL.
int num_cols = c->num_fields;
int rows = 0;
do
{
r = my_conn.get_next_row();
if (r)
{
rows++;
for (int i = 0; i < num_cols; i++)
{
Serial.print(r->values[i]);
if (i < num_cols - 1)
{
Serial.print(", ");
}
}
Serial.println();
// Note: we free the row read to free the memory allocated for it.
// You should do this after you've processed the row.
my_conn.free_row_buffer();
}
} while (r);
Serial.print(rows);
Serial.println(" rows in result.");
// Finally, we are done so we free the column buffers
my_conn.free_columns_buffer();
}
The serial output after a correct query is: Connected to server version 5.6.21 id 61BF49A49G 1 rows in result.
The number of rows returned tells you your answer - there's no need in this case to actually parse anything out of the data returned from the database. Just test rows at the end of do_query - if it's zero, the tag isn't in the database else it is.
You should probably set your TagExists (or whatever) flag to to false before you try to execute the query too, in case the query fails.
Iam doing baby steps in coding so i could need some guidance.
What iam trying to do is an rfid tag compare with allowed ones i already have on a mysql database.
So from loop() i have a string tagValue given from one RFID Reader les say 6A0049AD9F and after i got that value then i call findTag (tagValue) in order to make string compare with the allowed ones from mysql and if found to return me in loop() a value of 1 else a value of 0.
So in findTag i connect to database and with do_query(QUERY_POP) i check the database. My problem is there because i dont know how to return something from do_query back in findTag().
/* Search for a specific tag in the database */
int findTag( char tagValue[10] )
{
if (my_conn.mysql_connect(server_addr, 3306, user, password))
{
delay(1000);
char QUERY_POP[64];
sprintf(QUERY_POP,"SELECT id FROM door.tag WHERE id = '%s'",tagValue);
do_query(QUERY_POP);
//here i have to add the condition returning from do_query
if (............)
{
return(1);
}
else
return(0);
}
// We're done with the buffers so Ok to clear them (and save precious memory).
my_conn.free_columns_buffer();
my_conn.free_row_buffer();
}
else
Serial.println("Connection failed.");
}
/* Database query for */
void do_query(const char *q)
{
column_names *c; // pointer to column values
row_values *r; // pointer to row values
// First, execute query. If it returns a value pointer,
// we have a result set to process. If not, we exit.
if (!my_conn.cmd_query(q))
{
return;
}
// Next, we read the column names and display them.
//
// NOTICE: You must *always* read the column names even if
// you do not use them. This is so the connector can
// read the data out of the buffer. Row data follows the
// column data and thus must be read first.
c = my_conn.get_columns();
for (int i = 0; i < c->num_fields; i++)
{
Serial.print(c->fields[i]->name);
if (i < c->num_fields - 1)
{
Serial.print(",");
}
}
Serial.println();
// Next, we use the get_next_row() iterator and read rows printing
// the values returned until the get_next_row() returns NULL.
int num_cols = c->num_fields;
int rows = 0;
do
{
r = my_conn.get_next_row();
if (r)
{
rows++;
for (int i = 0; i < num_cols; i++)
{
Serial.print(r->values[i]);
if (i < num_cols - 1)
{
Serial.print(", ");
}
}
Serial.println();
// Note: we free the row read to free the memory allocated for it.
// You should do this after you've processed the row.
my_conn.free_row_buffer();
}
} while (r);
Serial.print(rows);
Serial.println(" rows in result.");
// Finally, we are done so we free the column buffers
my_conn.free_columns_buffer();
}
int do_query(const char *q)
{
column_names *c; // pointer to column values
row_values *r; // pointer to row values
// First, execute query. If it returns a value pointer,
// we have a result set to process. If not, we exit.
if (!my_conn.cmd_query(q))
{
return 0;
}
// Next, we read the column names and display them.
//
// NOTICE: You must *always* read the column names even if
// you do not use them. This is so the connector can
// read the data out of the buffer. Row data follows the
// column data and thus must be read first.
c = my_conn.get_columns();
for (int i = 0; i < c->num_fields; i++)
{
Serial.print(c->fields[i]->name);
if (i < c->num_fields - 1)
{
Serial.print(",");
}
}
Serial.println();
// Next, we use the get_next_row() iterator and read rows printing
// the values returned until the get_next_row() returns NULL.
int num_cols = c->num_fields;
int rows = 0;
do
{
r = my_conn.get_next_row();
if (r)
{
rows++;
for (int i = 0; i < num_cols; i++)
{
Serial.print(r->values[i]);
if (i < num_cols - 1)
{
Serial.print(", ");
}
}
Serial.println();
// Note: we free the row read to free the memory allocated for it.
// You should do this after you've processed the row.
my_conn.free_row_buffer();
}
} while (r);
Serial.print(rows);
Serial.println(" rows in result.");
// Finally, we are done so we free the column buffers
my_conn.free_columns_buffer();
return rows;
}