Help!! R/W in SQLite and Yún

Hello I am trying to get some data from SQLite for my project. This is my status now:

I installed SQLite in the micro SD, I created a table with and added some info about user like name, age....

From the sketch in Arduino, I can get the data like this:

ID,Name,Age,Address,Salary
1,Carlos,27,Zamora,10000.00

The code running that process is:

Process p;
p.runShellCommand("sqlite3 -csv -header /mnt/sda1/sensor.db \"select * from accesos where ID=1;\"");
while(p.running());
   while(p.available()>0)
      {

       char c = p.read();
       serial.print(c);
      }
Serial.flush();
Serial.println("Done");

Then I tried to get data from a specific field and wrote:

int x=1;
p.runShellCommand("sqlite3 -csv -header /mnt/sda1/sensor.db "select * from accesos where ID=x;"");

But it didnt work, I think is looking for an ID x and not taking value as 1.

I have no idea how to make it. I have been searching for examples but not being lucky or I find info about SQLite but not about ArduinoYÚN+SQLite

What i want to do is, using an RFID card, having an ID in the card, read that number and then search in the database for that ID, if it exists, send a message through LCD showing also the name of that person. So that's why I need to extract also specific data from only some fields.

I hope you can help me. I don't mind if I have to change to PHP, Python or whatever with my SQLite database, but i need this working. thank you in advance.

carloshm:
int x=1;
p.runShellCommand("sqlite3 -csv -header /mnt/sda1/sensor.db "select * from accesos where ID=x;"");

But it didnt work, I think is looking for an ID x and not taking value as 1.

Yes, you are literally passing a string to sqlite3 that ends with the character sequence "ID=x". Of course, that won't work unless "x" is a valid ID value, which in this case it is not.

The issue is that the "x" that is in your string is just another character in the string - it has no association with your variable x in the sketch. You will have to do some string manipulation to insert the value of variable x into your command string.

Note that this actually has nothing to do with Arduino, the Yun, Linux, sqlite3, etc. This is a general string formatting issue. Do some research on C++ string formatting for some ideas.

There are a few different ways to do it. Here's one way (not necessarily pretty or efficient, but relatively easy to explain):

String command = "sqlite3 -csv -header /mnt/sda1/sensor.db \"select * from accesos where ID=";
command += x;
command += ";\"";
p.runShellCommand(command);

Here's what's happening:

  • The first line builds most of the command string, up until the variable portion.
  • The second line converts the value of the integer variable x into a text string, and appends it to the command string.
  • The third line appends the semicolon and closing quote character to the command string
  • The last line performs the complete command string.

It works perfect!! Thank you very much

I am using this code:

Process p;
  int x=4;
String command = "sqlite3 -csv -header /mnt/sda1/sensor.db \"select * from accesos where ID=";
command += x;
command += ";\"";
p.runShellCommand(command);

int decision=0;
while(p.running());
   while(p.available()>0)
      {
      decision=1;
       char c = p.read();
       Serial.print(c);
      }
      if (decision>0)
      {
        Serial.println("Access Granted");
      }
      else
      {
        Serial.println("Access Denied");
      }

decision=0;    
Serial.flush();
Serial.println("Done");

And I am getting this in the Serial Monitor:

ID,Name,Age,Address,Salary
4,Chilo,30,Zamora,25000.0
Access Granted
Done

Which is correct, but now I have the question, How can i extract the name?? in this case the word Chilo. I need to request the Name field or to cut the string that I already have?? I mean, it would be better to get it from database because I cannot now how long it will be the ID for each person, or how long it will be their name.

I want to print that name in an LCD screen.

If the only thing you want is the name, then you should change the SQL request to return only the name. Instead of using "select * from" where the "*" means all fields, change it to the field you want: "select Name from" where Name is the name of the desired field.

Or, if you are interested in several fields, do some research on "C++ string parsing" or "C++ string token parsing"

In this case, what you may want to do is search for the positions of the first and second commas in the string, and extract everything between them.

Again, this is a general string manipulation question, and is not at all specific to the Yun or SQLite. You may get better answers from a wider audience asking this in the general programming questions forum.

Thank you for your answer, that's exactly what i wanted to do.

And I am sorry if I am asking this questions out of place, sometimes I think the way Yun works is different regardless the programming language and I ask these question.

Actually before starting this project I had only programmed in C, and never used strings. I can't recognize if it's something about general programming or just about Yún. I will try to figure it out first.

Maybe my knowledge is not enough for this project yet. It's the first time I use Arduino, SQLite or any database, PHP.... Less than 1 month programming hehehe. Anyway I am trying to learn as much as possible, I will take your advice and post my questions in the specific forum.

carloshm:
And I am sorry if I am asking this questions out of place, sometimes I think the way Yun works is different regardless the programming language and I ask these question.

The Yun IS rather different than other Arduino boards. That's why there is a special forum section for it, because many of the issues you may run into are unique to this system. For example, very few projects built with other Arduino boards will make use of SQLite, and if they do, it will be on a remote computer.

Actually before starting this project I had only programmed in C, and never used strings.

Yes, the String class and it's operators is built on the concepts of C++ and is unique to Arduino (or at least this particular variant of a String class.) As such, it is indeed a foreign concept to a C programmer. But remember that just about anything you can do in C can still be done in Arduino and C++: if you are familiar with using character array buffers in C to string manipulation, those same techniques will work here.

I can't recognize if it's something about general programming or just about Yún. I will try to figure it out first.

Don't get me wrong - I'm not trying to tell you to go away. I understand that with a lack of experience, it's difficult to figure out just what kind of issue you may have. I'm only trying to suggest the best place to get an answer, and not telling you to leave this section alone.

Maybe my knowledge is not enough for this project yet. It's the first time I use Arduino, SQLite or any database, PHP.... Less than 1 month programming hehehe. Anyway I am trying to learn as much as possible

Wow, looks like you will have your hand full, and will learn a lot by the time you have this project working. Good for you, and good luck with your project!