store incoming data from serial monitor to string

hello all!

At the moment im working on a project. With a downloaded mySQLconnector library i can now send query code from my Arduino to my database and the database will answer on my serial monitor. as example it prints the name (stored in the database): Casper
I dont want to just see the result on my serial monitor, i also want to use the received data (in this case, i wanna store the word "Casper" in a string). how do i do this? i have tried it with serial.Read(), but it will only print the data i entered in the Serial monitor and not the data i receive from the database.

i hope this is enough information,
thanks!
casper

i have tried it with serial.Read(), but it will only print the data i entered in the Serial monitor and not the data i receive from the database.

How are you receiving the data from the database? Via an Ethernet connection? WHERE IS YOUR CODE?

yes i am receiving data with an ethernet shield attached to arduino.

here is my current code:

#include <mysql.h>
#include <Wire.h>
#include <Ethernet.h>
#include <EthernetUdp.h>
#include <EthernetServer.h>
#include <Dhcp.h>
#include <util.h>
#include <Dns.h>
#include <EthernetClient.h>
#include <SPI.h>
#include <avr/pgmspace.h>
#include <sha1.h>
#include <Wire.h>

byte mac_addr[] = { 
  0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
byte ip[] = { 
  192, 168, 1, 20 };
//IPAddress ip_addr(192, 168, 1, 2);
IPAddress server_addr(159, 253, 5, 5); 
char user[] = "secret";
char password[] = "secret";
Connector my_conn;  
int rows;

void setup()
{
  Ethernet.begin(mac_addr, ip);
  Serial.begin(9600);
  delay(1000);
  Serial.println("Connecting...");
  if (my_conn.mysql_connect(server_addr, 3306, user, password))
  {
    delay(1000);
    
    //Database select
    my_conn.cmd_query("USE secret");
    
    //SELECT Query command
    my_conn.cmd_query("SELECT gNaam FROM gebruiker");
    
    //Results shown in serial monitor
    my_conn.show_results();
    
    }
  else
  {
    Serial.println("Connection failed.");
  }
}

void loop()
{
}

so i kinda want the printed results from the my_conn.show_results() in a variable so i can finally display it on a LCD-display.

Have you looked at the library documentation for any way to get the results and store them in a buffer, or access them directly?

With a downloaded mySQLconnector library

Downloaded from Mars? Or someplace closer?

yes i have. allthough the library is written in code that i cant figure out and understand so thats kinda why i was wondering if there was an easier way around.

library download: ~chuck-bell/mysql-arduino/trunk : files for revision 2

the only thing i really understand is that the show_results() uses the functions: get_row() and get_field().
and my guess is that these functions handle receiving the database data.

caspervk:
yes i have. allthough the library is written in code that i cant figure out and understand so thats kinda why i was wondering if there was an easier way around.

Sounds like this is a great chance to go through the show_results() method to learn about how the code retrieves the data.

and my guess is that these functions handle receiving the database data.

Did you look at them?

The comment for cmd_query says:

/**
 * cmd_query - Execute a SQL statement
 *
 * This method executes the query specified as a character array that is
 * located in data memory. It copies the query to the local buffer then
 * calls the run_query() method to execute the query.
 * 
 * If a result set is available after the query executes, the field
 * packets and rows can be read separately using the get_field() and
 * get_row() methods.
 *
 * query[in]       SQL statement (using normal memory access)
 *
 * Returns boolean - True = a result set is available for reading
*/

The get_row() method only returns an error code, but the get_field() method takes a pointer to a structure (that you allocate/define), and populates it with the results of one row of the query.

yes i've read thru the definition of the functions and i understand there meaning.'like i already said its sadly enough my knowledge isn't that big and i just can't figure out on my own what the code in each function really does. i've spend hours trying several things, but it won't get me far.

What does the output of the show_results() method look like?

I'd make a copy of that function in my code, and change any private methods it uses to public, so that it compiles. Then, I'd replace the Serial.print() and Serial.println() calls with code to store the data.

Even better, though, would be to offload the mysql stuff to a real server, and just have your code do a GET on a PHP script that interacts with the database. That's how I do it. The GET then spools all the data back via the client instance, easy to read, parse, store, etc.

ok, so in the library i made a copy of the show_results() and called it show_results2() and instead of a void i made it a char.
in the .h file i made variables public and i made an char test variable.
now instead of Serial.print(field->name), i wrote: test = field->name;

char Connector::show_results2() {
  char *str;
  int offset; 
  int num_fields = buffer[4]; // From result header packet
  int res = 0;
  int rows = 0;
  
  for (int f = 0; f < num_fields; f++) {
    field_struct *field = (field_struct *)malloc(sizeof(field_struct));
    res = get_field(field);
    if (res == EOF_PACKET) {
      Serial.println("Bad mojo. EOF found.");
      return;
    }
   // Serial.print(field->name);
	test = field->name;
    if (f < num_fields-1)
      Serial.print(',');
    free(field->db);
    free(field->table);
    free(field->name);
    free(field);
  }
  Serial.println();
  return test;
  read_packet(); // EOF packet
  
  // Read the rows
  do {
    res = get_row();
    if (res != EOF_PACKET) {
      rows++;
      // print column data
      offset = 4;
      for (int f = 0; f < num_fields; f++) {
        str = read_string(&offset);
        Serial.print(str);
        if (f < num_fields-1)
          Serial.print(',');
        free(str);
      }
      Serial.println();
    }
  } while (res != EOF_PACKET);

  // Report how many rows were read
  Serial.print(rows);
  Serial.println(" rows in result.");
}

in the arduino code i finally say: lcd.print(my_conn.show_results2());
allthough, i get the following error:
C:\Users\Casper van Kampen\Desktop\arduino-1.0\libraries\MySQLconnector\mysql.cpp:348: error: return-statement with no value, in function returning 'char'
C:\Users\Casper van Kampen\Desktop\arduino-1.0\libraries\MySQLconnector\mysql.cpp:351: error: invalid conversion from 'char*' to 'char'

what am i doing wrong?

so the first error is because of this return:

if (res == EOF_PACKET) 
	{
      Serial.println("Bad mojo. EOF found.");
      return;
    }

i have no idea what this even does. can i delete this return so it doesnt conflict or is there another way to solve?

the second error: no idea how to solve
i would rather use a string, but somehow i cannot use string even after #include . even saying using std::string test = ""; or just string test = ""; won't work.

and instead of a void i made it a char.

So, you want the function to return 1 character. Doesn't seem very useful to me.

// Serial.print(field->name);
test = field->name;

field->name is an array of chars that you are trying to assign to a char. That won't work the way you seem to think it will.

  Serial.println();
  return test;

So, we're done? I don't think so. We haven't gotten anything but the field name, so far, and you already knew what that was. It was what you did the SELECT on.

C:\Users\Casper van Kampen\Desktop\arduino-1.0\libraries\MySQLconnector\mysql.cpp:348: error: return-statement with no value, in function returning 'char'

Every return statement needs to return 1 char. What one char do you want to return when there is an error?

C:\Users\Casper van Kampen\Desktop\arduino-1.0\libraries\MySQLconnector\mysql.cpp:351: error: invalid conversion from 'char*' to 'char'

You can't store an array of characters in one character. This doesn't seem like it should be too hard to understand.

If you are going to add a method to the class, you need to make it a void method that takes a pointer to where to store the data.

void Connector::show_results2(char *putTheDataHere, int bufSiz)
{
}

Then, you need to call the function with that pointer.

char queryResults[128]; // Make some place to store the data
queryResults[0] = '\0';
my_conn.show_results2(queryResults, sizeof(queryResults));
Serial.print("Query results: ");
Serial.println(queryResults);

In the method, then use stcat() to append data to the array, making sure not to exceed the size of the array (in the second argument).