How to insert Load cell data to MySQL database using arduino IDE sketch program

Projects Programming
1

Hi,

We need to take 40 impact weight readings in 20 seconds using weighing load sensor of capacity 10. connections among load cell sensor ,hx711 arduino uno and esp8266 are fine...

Problem we are facing is we can insert weight data into mysql database by it is taking much time....for each insertion it is taking at least 5 seconds...

code which we are following is as below,

#include <HX711.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <MySQL_Encrypt_Sha1.h>
#include <MySQL_Packet.h>
#include <WiFiEsp.h>
#include <WiFiEspClient.h>
#include <WiFiEspServer.h>
#include <WiFiEspUdp.h>

#ifndef HAVE_HWSERIAL1
#include "SoftwareSerial.h"
SoftwareSerial Serial1(10, 11); // RX, TX
#endif

#define DOUT A1
#define CLK A0

HX711 scale(DOUT, CLK);
float calibration_factor = 227652; //-106600 worked for my 40Kg max scale setup
WiFiEspClient client; //Use this for ESP8266
MySQL_Connection conn((Client *)&client);
MySQL_Cursor cur = MySQL_Cursor(&conn);

IPAddress server_addr(148,66,135,229); // IP of the MySQL server here go dadday
char user[] = "makarand";
char password[] = "makarand";

char ssid[] = "MAKARAND"; // your network SSID (name)
char pass[] = "345678910"; // your network password

void setup(){
Serial.begin(115200);
scale.set_scale(227652); //Calibration Factor obtained from first sketch
Serial.read();
while (!Serial);

Serial.println("Starting....");
Serial1.begin(9600);
WiFi.init(&Serial1);

int status = WiFi.begin(ssid, pass);
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a wifi connection");
while(true);
}

else {
Serial.println("Connected to network");
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}
for(int i=0;i<40;i++)
{
if(i==0)
{
Serial.println("Connecting...");
if(conn.connect(server_addr,3306,user,password)) {
delay(50000);
Serial1.println("Connected to mysql");
}}
if(i!=0)
{
Serial.println("Connecting...");
if(conn.connect(server_addr,3306,user,password)) {
Serial1.println("Connected to mysql");
}
}
Serial.println("Recording data.");
double xyz=scale.get_units()-0.790-0.014;
Serial.print("Reading: ");
Serial.print(xyz, 3);
Serial.print(" kg");
Serial.println();
dtostrf(xyz,6,3,weight);
String INSERT_SQL1 = "INSERT INTO lalithamakarand.test1 VALUES(";
String strbracket =")";
String INSERT_SQL=INSERT_SQL1+weight+strbracket;
Serial1.print("data is :");
Serial1.println(INSERT_SQL);
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
char copy[50];
INSERT_SQL.toCharArray(copy, 50);
Serial1.print(copy);
cur_mem->execute(copy);
delete cur_mem;
scale.tare();
}}

void loop()
{}

Please help us inserting the 40 readings in 20 seconds...

code.ino (2.33 KB)

2

First thing to try is only open the connection to MySQL once, not for every reading.

3

We did the same earlier but only first reading is getting inserted ....please find the attached image for reference

output.png
output.png857×365 14.3 KB

4

VeeraSekhar:
We did the same earlier but only first reading is getting inserted ....please find the attached image for reference

Post the code you used to do this. Preferably with all the commented out stuff removed.

5

wildbill:
Post the code you used to do this. Preferably with all the commented out stuff removed.

And, format it correctly. The indenting in your first post was atrocious. And, use CODE TAGS.

6

VeeraSekhar:
We did the same earlier but only first reading is getting inserted ....please find the attached image for reference

VeeraSekhar:
Please help us in resolving the issue

Really? Bumping it after just over an hour? Please realize you're not paying for help here.

7

Hi,

Edited the program and attached the ino file

8

I assume you attached the sketch to the first post - don't do that - it makes it hard to follow what changed. I still see the same code though:

    if (i == 0)
    {
      Serial.println("Connecting...");
      if (conn.connect(server_addr, 3306, user, password))
      {
        delay(50000);
        Serial1.println("Connected to mysql");
      }
    }
    if (i != 0)
    {
      Serial.println("Connecting...");
      if (conn.connect(server_addr, 3306, user, password))
      {
        Serial1.println("Connected to mysql");
      }
    }

So if you're on the first iteration, you connect and delay for fifty seconds. Otherwise, just connect. I don't believe you have to connect afresh for every iteration - try just connecting once before the for loop.

9

Thank you for reply ,i will try accordingly and will update the status