Dragino Yun and fetch data

Hello guys,

My project is a small control access device, I would like to exchange some data with my Dragino yun shield and postgresql server. I succeeded to get variable from shell script psql, following this tutorial :
shell-script-and-postgresql

my problem is that I can not get data from select query i.e, and browse them in my sketch.

my problem is that I can not get data from select query i.e, and browse them in my sketch.

Our problem is that we can not see your sketch, to see how you are doing the select query, nor can we see how your Yun is running a relational database that can be queried.

Hi PaulS,

the database is postgresql.

The sketch:

#include <SoftwareSerial.h>
#include <Console.h>
#include <Process.h>

SoftwareSerial RFID(10, 11); // RX and TX
 
int  val;
int bytesread;
String num;
unsigned char code[11]; //Tableau servant à stocker la trame de 11 octets
 
void setup()
{
  pinMode(5, OUTPUT); //LED
  RFID.begin(9600);    // start serial to RFID reader
  
  // Initialize Console and wait for port to open:
  Bridge.begin();
  Console.begin(); // start serial to PC 
  while (!Console); // Wait for Console port to connect
  
}

 
void loop()
{
  int duree = millis();
  digitalWrite(5, LOW);
 if (RFID.available())
 //Si des données sont présentes sur le port série
 {   
    bytesread = 0;
    while (bytesread<11) 
    {
      if (RFID.available() > 0) 
      {
        val = RFID.read();
        code[bytesread] = val;
        bytesread++;
      }
    }
    num ="";
    for (int i=1;i<11;i++){
      num += String(code[i], HEX);
      }    
 
   num.toUpperCase();
   RFID.flush(); //Vide le buffer série
 Console.println("READER: "+num); //Affiche le numéro lu par le lecteur
 Process p;
 int valproc = 0; 
 if (!p.running())  {
 p.runShellCommand("/root/test.sh "+num);
 while (p.available() && p.peek()!=10 ) {
  valproc=int(p.parseInt());
        }
  
  Console.println(valproc);
      if (valproc == 1) {
        digitalWrite(5, HIGH);
        }else{
        digitalWrite(5, LOW);  
         }
 }
      
 //p.flush();
 duree = millis()-duree;
 Console.println(duree);
 }
delay(20);
}

test.sh :

#!/bin/sh
VAR=$(psql -t -h 192.168.1.42 -d TEST -U openpg -c "SELECT COUNT(*) FROM cards where num_card = '$1';")
echo $VAR;
   num.toUpperCase();

I don't believe that upper case numbers are any different from lower case numbers. Therefore, either this a useless operation or num is a stupid name.

  valproc=int(p.parseInt());

Looks pretty silly casting an int to an int. Either parseInt() is a stupid name or the cast is useless.

The code(s) you posted do something. You need to explain what they actually do. You need to explain how that differs from what you want.

num.toUpperCase();

I used this function before I had the UID of the card in HEX format in the database, and the reader reads the UID in lowercase, I forget to remove it.

My question is clear;
How to retrieve a set of data from a SELECT query, and use it in Arduino sketch ?. that's why I did not put the sketch in my first post

How to retrieve a set of data from a SELECT query, and use it in Arduino sketch ?

It lloks like that is what the sketch and script do. You need to tell us what they ACTUALLY do. Is that do hard to understand?

No it's not so difficult, I understood !

ACTUALLY my sql query "script" is :

#!/bin/sh
VAR=$(psql -t -h 192.168.1.42 -d TEST -U openpg -c "SELECT COUNT(*) FROM cards where num_card = '$1';")
echo $VAR;

it returns an Integer and I can retrieve it with this sketch "function":

.
.
p.runShellCommand("/root/test.sh "+num);
while (p.available() && p.peek()!=10 ) {
int val=p.parseInt();
}
.
.

And now I NEED, and I am a BEGINNER I don't know how I can do to retrieve the result of this query i.e:

SELECT lastname, firstname FROM cards WHERE num_card=$1;

I hope that it's clearer

I don't know how I can do to retrieve the result of this query

If p.available() tells you how many bytes are available, and p.read() returns a char/byte, then you COULD pretend that p was an instance of Stream and treat it JUST LIKE Serial, as far as collecting the data from the process.

Serial input basics - updated

What you do with the resulting string (NULL terminated array of chars) is up to you. I'd be thinking about using strtok() to get the first and last name from the string. YMMV.

Yes absolutely, I'll test all this and come back
I hope there will not be too iteration :frowning:
Thx PaulS.

Sorry for the late :),
indeed it works but I thought there was a ready-made function to extract the columns one by one
Thanks Pauls.