Difficulty entering data in MySQL

[mineiro491]

Hello,

I'm having trouble entering data that is being generated through variables. I'm using the complex_insert example as the basis for my tests, but I'm having trouble inserting data that is inserted through variables.

// Sample query char INSERT_DATA[] = "INSERT INTO iot.nivel (id_equipamento, local, nivel, status) VALUES ('%f',%f,%f,%f)";

I believe that my difficulty is related to VALUE.

VALUES ('%f',%f,%f,%f)";

I haven't found a reference for the correct type of value to use according to each variable.

The variables I need to use are as follows:

//Variaveis int NumEquipamento = 10; String LocalEquipamento = "Reservatorio 01"; float NivelEquipamento = 50.4; int StatusEquipamento = 1;

If the problem is with the VALUES is there a reference document or example so that I can know what is the right parameter for each variable?

Below is my complete code.

include

include

include

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress ip(10,200,1,47); IPAddress server_addr(10,200,1,200); // IP of the MySQL server here char user[] = "root"; // MySQL user login username char password[] = ""; // MySQL user login password // Sample query char INSERT_DATA[] = "INSERT INTO iot.nivel (id_equipamento, local, nivel, status) VALUES ('%s',%f,%f,%f)"; char query[128]; EthernetClient client; MySQL_Connection conn((Client )&client); void setup() { Serial.begin(115200); while (!Serial); // wait for serial port to connect Ethernet.begin(mac_addr,ip); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println(Ethernet.localIP()); //Variaveis int NumEquipamento = 10; String LocalEquipamento = "Reservatorio 01"; float NivelEquipamento = 50.4; int StatusEquipamento = 1; // Initiate the query class instance MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); // Save sprintf(query, INSERT_DATA, NumEquipamento, LocalEquipamento, NivelEquipamento, StatusEquipamento); // Execute the query cur_mem->execute(query); // Note: since there are no results, we do not need to read any data // Deleting the cursor also frees up memory used delete cur_mem; Serial.println("Data recorded."); } else Serial.println("Connection failed."); conn.close(); } void loop() { }

Thanks for listening

[ChuckBell]

You must use %d or %I for integers and %s for strings. For floating point, you will need to use dtostrf() first then add the string value. See the other example sketches for how to deal with floating point numbers in sprintf().

On May 23, 2020, at 6:02 PM, mineiro491 notifications@github.com wrote:

Hello,

I'm having trouble entering data that is being generated through variables. I'm using the complex_insert example as the basis for my tests, but I'm having trouble inserting data that is inserted through variables.

// Sample query char INSERT_DATA[] = "INSERT INTO iot.nivel (id_equipamento, local, nivel, status) VALUES ('%f',%f,%f,%f)";

I believe that my difficulty is related to VALUE.

VALUES ('%f',%f,%f,%f)";

I haven't found a reference for the correct type of value to use according to each variable.

The variables I need to use are as follows:

//Variaveis int NumEquipamento = 10; String LocalEquipamento = "Reservatorio 01"; float NivelEquipamento = 50.4; int StatusEquipamento = 1;

If the problem is with the VALUES is there a reference document or example so that I can know what is the right parameter for each variable?

Below is my complete code.

include

include

include

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress ip(10,200,1,47); IPAddress server_addr(10,200,1,200); // IP of the MySQL server here char user[] = "root"; // MySQL user login username char password[] = ""; // MySQL user login password // Sample query char INSERT_DATA[] = "INSERT INTO iot.nivel (id_equipamento, local, nivel, status) VALUES ('%s',%f,%f,%f)"; char query[128]; EthernetClient client; MySQL_Connection conn((Client )&client); void setup() { Serial.begin(115200); while (!Serial); // wait for serial port to connect Ethernet.begin(mac_addr,ip); Serial.println("Connecting..."); if (conn.connect(server_addr, 3306, user, password)) { delay(1000); Serial.println(Ethernet.localIP()); //Variaveis int NumEquipamento = 10; String LocalEquipamento = "Reservatorio 01"; float NivelEquipamento = 50.4; int StatusEquipamento = 1; // Initiate the query class instance MySQL_Cursor cur_mem = new MySQL_Cursor(&conn); // Save sprintf(query, INSERT_DATA, NumEquipamento, LocalEquipamento, NivelEquipamento, StatusEquipamento); // Execute the query cur_mem->execute(query); // Note: since there are no results, we do not need to read any data // Deleting the cursor also frees up memory used delete cur_mem; Serial.println("Data recorded."); } else Serial.println("Connection failed."); conn.close(); } void loop() { }

Thanks for listening

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/140, or unsubscribe https://github.com/notifications/unsubscribe-auth/AB6SHYECP3NSU7VBJ7H4TSLRTBBWPANCNFSM4NITWGTA.

[mineiro491]

Good night ChuckBell,

Thank you for your attention. I did some tests and developed a new test code. In this test I have the following variables.

String EquipmentId = "COLETOR01"; String LocalInstalacao = "RESERVATORIO DE AGUA PORTARIA"; String ip_device = "10.200.1.47"; int CurrentLevel; int Status;

Variables with integer are writing perfectly in the database, variables that are inside String are being written with strange characters. Below is the print.

Checking the execution on the Serial Monitor, the variables have the correct values.

My variable that receives the data to execute the query was this way.

char INSERT_DATA[] = "INSERT INTO iot.nivel (id_equipamento, local, nivel, status, ip_dispositivo) VALUES ('%s', '%s', %d, %d, '%s')";

Below is the complete source code.

//BIBLIOTECAS

include

include

include

//DEFINIÇÃO DE PINOS DE CONEXÃO const int Sensor1 = 2; //CAIXA EM 25% const int Sensor2 = 3; //CAIXA EM 50% const int Sensor3 = 4; //CAIXA EM 75% const int Sensor4 = 5; //CAIXA EM 100%

define LedSQL 6 //LED INDICATIVO DE CONEXÃO AO SERVIDOR

define LedVermelho 7 //INDICADOR VISUAL DE NIVEL BAIXO

define LedAmarelo 8 //INDICADOR VISUAL DE NIVEL MEDIO

define LedVerde 9 //INDICADOR VISUAL DE NIVEL ALTO

//VARIAVEIS GLOBAIS int NivelAtual; int Status; int Nivel1; int Nivel2; int Nivel3; int Nivel4; int CalculoNivel; //PARAMETROS DE IDENTIFICAÇÃO DO EQUIPAMENTO String IdEquipamento = "COLETOR01"; String LocalInstalacao = "RESERVATORIO DE AGUA PORTARIA"; String ip_dispositivo = "10.200.1.47"; //CONFIGURAÇÕES DE REDE byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED }; IPAddress ip(10,200,1,47); EthernetClient client; MySQL_Connection conn((Client *)&client); //CONFIGURAÇÕES DO SERVIDOR IPAddress server_addr(10,200,1,200); // IP DO SERVIDOR MYSQL char user[] = "root"; // USUARIO DO MYSQL char password[] = ""; // SENHA MYSQL //INSTRUÇÕES DO BANCO MYSQL char INSERT_DATA[] = "INSERT INTO iot.nivel (id_equipamento, local, nivel, status, ip_dispositivo) VALUES ('%s', '%s', %d, %d, '%s')"; char query[128]; //**** INICIANDO O SETUP **** void setup() { //HABILITANDO MONITOR SERIAL Serial.begin(115200); //DEFINIÇÕES DE PORTAS pinMode(Sensor1, INPUT_PULLUP); pinMode(Sensor2, INPUT_PULLUP); pinMode(Sensor3, INPUT_PULLUP); pinMode(Sensor4, INPUT_PULLUP); pinMode(LedSQL, OUTPUT); pinMode(LedVermelho, OUTPUT); pinMode(LedAmarelo, OUTPUT); pinMode(LedVerde, OUTPUT); //LIBERANDO CONEXÃO SERIAL while (!Serial); //CONECTANDO COM A REDE LOCAL Serial.begin(115200); while (!Serial); // wait for serial port to connect Ethernet.begin(mac_addr,ip); Serial.println("Connecting..."); delay(1000); } void loop() { //MEMORIA DE CALCULO Serial.println("Estamos Dentro do LOOP"); Nivel1 = digitalRead(Sensor1); Serial.print("Sensor 1 = "); Serial.println(Nivel1); delay(2000); Nivel2 = digitalRead(Sensor2); Serial.print("Sensor 2 = "); Serial.println(Nivel2); delay(2000); Nivel3 = digitalRead(Sensor3); Serial.print("Sensor 3 = "); Serial.println(Nivel3); delay(2000); Nivel4 = digitalRead(Sensor4); Serial.print("Sensor 4 = "); Serial.println(Nivel4); delay(2000); CalculoNivel = Nivel1+Nivel2+Nivel3+Nivel4; Serial.print("Calculo do Nivel = "); Serial.println(CalculoNivel); delay(2000); //ALTERAÇÃO DE VARIÁVEIS DE ACORDO COM O NIVEL if(CalculoNivel == 1) { NivelAtual = 25; Status = 1; } if(CalculoNivel == 2) { NivelAtual = 50; } if(CalculoNivel == 3) { NivelAtual = 75 ; }
if(CalculoNivel == 4) { NivelAtual = 100; Status = 0; } //CONDIÇÕES PARA CADA NIVEL APRESENTADO if(NivelAtual == 25) { digitalWrite(LedVermelho, HIGH); digitalWrite(LedAmarelo, LOW); digitalWrite(LedVerde, LOW); Serial.println(); Serial.print("Nivel Atual = "); Serial.print(NivelAtual); Serial.println(" %"); Serial.println(); Serial.print("Status = "); Serial.println(Status); Serial.println("========================================"); } if(NivelAtual == 50) { digitalWrite(LedVermelho, HIGH); digitalWrite(LedAmarelo, HIGH); digitalWrite(LedVerde, LOW); Serial.println(); Serial.print("Nivel Atual = "); Serial.print(NivelAtual); Serial.println(" %"); Serial.println(); Serial.print("Status = "); Serial.println(Status); Serial.println("========================================"); } if(NivelAtual == 100) { digitalWrite(LedVermelho, HIGH); digitalWrite(LedAmarelo, HIGH); digitalWrite(LedVerde, HIGH); Serial.println(); Serial.print("Nivel Atual = "); Serial.print(NivelAtual); Serial.println(" %"); Serial.println(); Serial.print("Status = "); Serial.println(Status); Serial.println("========================================"); } //DEPURAÇÃO DE DADOS DENTRO DAS VARIAVEIS Serial.print("ID do Equipamento: "); Serial.println(IdEquipamento); Serial.print("Local de Instalação: "); Serial.println(LocalInstalacao); Serial.print("Nivel do Reservatorio: "); Serial.println(NivelAtual); Serial.print("Status: "); Serial.println(Status); Serial.print("IP do Dispositivo: "); Serial.println(ip_dispositivo); delay(5000); //INSERINDO INFORMAÇÕES NO BANCO DE DADOS if (conn.connect(server_addr, 3306, user, password)) { delay(1000); // Initiate the query class instance MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Save sprintf(query, INSERT_DATA, IdEquipamento, LocalInstalacao, NivelAtual, Status, ip_dispositivo); // Execute the query cur_mem->execute(query); // Note: since there are no results, we do not need to read any data // Deleting the cursor also frees up memory used delete cur_mem; Serial.println("Data recorded.");} Serial.println("========================================"); }

Once again I thank you for your help.

[ChuckBell]

Closed due to inactivity. Please open a new ticket if this is still relevant.