Go Down

Topic: SELECT function with variable (Read 163 times) previous topic - next topic

leonepedro

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
Code: [Select]

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


Future
Code: [Select]


float variable = 709;

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


My code:
Code: [Select]

#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");
}

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.

leonepedro

#2
Nov 11, 2019, 02:05 am Last Edit: Nov 11, 2019, 02:06 am by leonepedro
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.

sterretje

#3
Nov 11, 2019, 04:33 am Last Edit: Nov 11, 2019, 04:34 am by sterretje
Use your SELECT_POP as the format for a sprintf.

Code: [Select]
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

Code: [Select]
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.
Code: [Select]
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("'");
}
If you understand an example, use it.
If you don't understand an example, don't use it.

Electronics engineer by trade, software engineer by profession. Trying to get back into electronics after 15 years absence.

leonepedro


Go Up