SELECT function with variable

Hello,

I was able to use the SELECT command to bring a value from the database. However in the SELECT function I need to use a variable that will be informed during the execution of the program. How do I name this variable within the SELECT function?

Actual

const char SELECT_POP[] = "SELECT altura FROM projrfid.Dados WHERE tag = '709'";
char query[128];

Future

float variable = 709;

const char SELECT_POP[] = "SELECT altura FROM projrfid.Dados WHERE tag = variable";
char query[128];

My code:

#include <ESP8266WiFi.h>
#include <SPI.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#define SS_PIN D4
#define RST_PIN D2
#include <stdlib.h>
#include <stdio.h>

#include "arduino_secrets.h"

// DEFINIÇÕES
int f;

// DECLARAÇÃO DE VARIÁVEIS PARA WIFI
char ssid[] = SECRET_SSID;        // your network SSID (name)
char pass[] = SECRET_PASS;        // your network password
int status = WL_IDLE_STATUS;      // status

// DECLARAÇÃO DE VARIÁVEIS PARA MySQL
IPAddress server_addr(85, 10, 205, 173);  // IP of the MySQL *server* here
char user[] = SECRET_USERDB;              // MySQL user login username
char password[] = SECRET_PASSDB;          // MySQL user login password

const char SELECT_POP[] = "SELECT altura FROM projrfid.Dados WHERE tag = '709'";
char query[128];

float variable;

// INSTANCIANDO OBJETOS
WiFiClient client;
MySQL_Connection conn((Client *)&client);
// Create an instance of the cursor passing in the connection
MySQL_Cursor cur = MySQL_Cursor(&conn);

// DECLARAÇÃO DE FUNÇÕES
void conectaWifi();

// ***************** INÍCIO DO SETUP *************************
void setup() {
  Serial.begin(115200);

  pinMode(LED_BUILTIN, OUTPUT);
  digitalWrite(LED_BUILTIN, LOW);
 
  while (!Serial) {
    ; // ESPERA O SERIAL ESTAR PRONTO 
  }

  conectaWifi();

  // CONECTA NO MySQL
  while (!conn.connect(server_addr, 3306, user, password)) {
    Serial.println("Conexão SQL falhou.");
    conn.close();
    delay(1000);
    Serial.println("Conectando SQL novamente.");
  }
  Serial.println("Conectado ao servidor SQL.");
  digitalWrite(LED_BUILTIN, HIGH);
   
}
// ***************** FIM DO SETUP ***************************

// ***************** INÍCIO DO LOOP *************************
void loop() {
   delay(1000);
  
  // Initiate the query class instance
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  // Supply the parameter for the query
  // Here we use the QUERY_POP as the format string and query as the
  // destination. This uses twice the memory so another option would be
  // to allocate one buffer for all formatted queries or allocate the
  // memory as needed (just make sure you allocate enough memory and
  // free it when you're done!).
  sprintf(query, SELECT_POP, 9000000);
  // Execute the query
  cur_mem->execute(query);
  // Fetch the columns and print them
  column_names *cols = cur_mem->get_columns();
  
  // Read the rows and print them
  row_values *row = NULL;
  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      for (f = 0; f < cols->num_fields; f++) {
        Serial.print(row->values[f]);
        
        float ftemp = atof(row->values[f]); //converte char* em float
        Serial.printf("conversao: %.2f",ftemp);
       
        if (f < cols->num_fields-1) {
          Serial.print(',');
        }
      }    
      Serial.println();
    }
  } while (row != NULL);
  // Deleting the cursor also frees up memory used
  delete cur_mem;
}
// ***************** FIM DO LOOP ***************************

void conectaWifi() {
  // check for the WiFi module:
  Serial.begin(115200);
  
  WiFi.begin(SECRET_SSID, SECRET_PASS);

  Serial.println();
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(SECRET_SSID);

  WiFi.begin(SECRET_SSID, SECRET_PASS);

  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
}

If your tag is in an integer of some sort, you can use sprintf. If it has to be a float, you will need to use dtostrf and then sprintf.

wildbill:
If your tag is in an integer of some sort, you can use sprintf. If it has to be a float, you will need to use dtostrf and then sprintf.

Thanks for your atention!

I need to put in the number 709 a variable that will be incremented during the program. How do I do that ?

SELECT_POP = “SELECT altura FROM projrfid.Dados WHERE tag = ‘709’”;
^^^^

this variable will be updated by reading an RFID.

Use your SELECT_POP as the format for a sprintf.

const char SELECT_POP[] = "SELECT altura FROM projrfid.Dados WHERE tag = '%d'";

It depends on the type of variable (int, long, unsigned) what %d needs to be; e.g. %u for an unsigned int, %lu for an unsigned 32 bit.

Next you can use sprintf to create the actual select statement

void setup()
{
  Serial.begin(57600);

  // variable to put in select statement
  int id = 709;

  // create a select statement with id included; caters for 10-digit number (or 9-digit with sign)
  char select[sizeof(SELECT_POP) + 10];
  sprintf(select, SELECT_POP, id);

  // show the select statement
  Serial.println(select);
}

Question is if you really need it. The below will send the same to the serial port; I understand that you probably don't use the serial port but with a network it should be the same.

const char SELECT_POP2[] = "SELECT altura FROM projrfid.Dados WHERE tag = ";

void setup()
{
  Serial.begin(57600);

  // variable to put in select statement
  int id = 709;

  // send select statement
  Serial.print(SELECT_POP2);
  Serial.print("'");
  Serial.print(id);
  Serial.print("'");
}

Thanks my friend!

You helped me!