Arduino Update in MySQL

Dear, I'm new to the forum and Arduino.
The code only the set code into mysql.
Now I need an UPDATE on mysql using data extracted from the Arduino.
I started with the code UPDATE but do not know if this correct.
I include:

query_4 char [] = "update minhacas_a.usuario1 SET position = 'value' where id_rele = 1;"
int = 8 port_relay4
int value = 0;
pinMode (port_relay4, INPUT);
   if (iLoop == 4) {
{
   value = digitalRead (port_relay4); // Read the input pin
  
}

This is the code that is partially working:

char query_1[] = "select comando from minhacas_a.usuario1 where id_rele =1;";
char query_2[] = "select comando from minhacas_a.usuario1 where id_rele =2;";
char query_3[] = "select comando from minhacas_a.usuario1 where id_rele =3;";
char query_4[] = "update minhacas_a.usuario1 set posicao=’valor’  where id_rele =1;";

EthernetClient client;
MySQL_Connection conn((Client *)&client);
MySQL_Cursor cur = MySQL_Cursor(&conn);

int port_relay1 = 5;
int port_relay2 = 6;
int port_relay3 = 7;
int port_relay4 = 8; //VARIABLE WITH INFORMATION WITH INPUT PIN

int valor=0; //VARIABLE WITH INFORMATION WITH INPUT PIN


long iloop = 1;
IPAddress server_ip;



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

  pinMode(port_relay1, OUTPUT); 
  pinMode(port_relay2, OUTPUT);
  pinMode(port_relay3, OUTPUT);
  pinMode(port_relay4, INPUT);
  
while (!Serial); 
  Ethernet.begin(mac_addr);
  Serial.println("Conectando Servidor WEB");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
  }
  else
    Serial.println("Falha na conexão Servidor WEB");
}

void loop() {
  row_values *row = NULL;
  long head_count = 0;
  int irelay1;
  int irelay2;
  int irelay3;
  
  delay(100);

  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

  if (iloop ==1){
    cur_mem->execute(query_1);  
  }
  if (iloop ==2){
    cur_mem->execute(query_2);  
  }  
  if (iloop ==3){
    cur_mem->execute(query_3);  
  }  

// beginning of an attempt to get value to update the database data

  if (iloop ==4){ 
{
  valor = digitalRead(port_relay4);   // read the input pin
  
}

  column_names *columns = cur_mem->get_columns();

  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      head_count = atol(row->values[0]);
    }
  } while (row != NULL);
  delete cur_mem;
  irelay1 = head_count;
  irelay2 = head_count;
  irelay3 = head_count;
  
  if (iloop == 1){
      if (irelay1 == 1){
      digitalWrite(port_relay1, LOW);
    }
    else
    {
      digitalWrite(port_relay1, HIGH);
    } 
  }

  if (iloop == 2){
       if (irelay2 == 1){
      digitalWrite(port_relay2, LOW);
    }
    else
    {
      digitalWrite(port_relay2, HIGH);
    } 
  }
  
  if (iloop == 3){
       if (irelay1 == 1){
      digitalWrite(port_relay3, LOW);
    }
    else
    {
      digitalWrite(port_relay3, HIGH);
    } 
  }

VARIABLE INCLUDED WITH INFORMATION WITH INPUT PIN

iloop = iloop +1;
if (iloop == 4){
  iloop = 1;
}

}

thank you, good week.

any help?

I started with the code UPDATE but do not know if this correct.

Why don’t you? You ran the code. Either it does update the database or it doesn’t. Which is it?

this part of the code is to update the database was tested did not work:

( query_4 char = "update minhacas_a.usuario1 SET position = 'value' where id_rele = 1;" )

this code I would like that when is HIGH update the database to 1 and when it is LOW update to 0.

got it?

thank you so much.

Good Morning

this code I would like that when is HIGH update the database to 1 and when it is LOW update to 0.

When what is HIGH? Or LOW?

char query_4[80];

   sprintf(query_4, "update minhacas_a.usuario1 SET position = '%d' where id_rele = 1;", somethingThatIsHIGHOrLOW);

PaulS:
When what is HIGH? Or LOW?

input of arduino.

char query_4[80];

   sprintf(query_4, "update minhacas_a.usuario1 SET position = '%d' where id_rele = 1;", somethingThatIsHIGHOrLOW);

I will try, thanks.

char query_4[80];

sprintf(query_4, "update minhacas_a.usuario1 SET position = '%d' where id_rele = 1;", somethingThatIsHIGHOrLOW);




I will try, thanks.

Failed !!!

I tried the code below but failed too:

char query_4[80];
int port_relay4 = 9;

void setup

  pinMode(port_relay4, INPUT);


void loop

  int val = digitalRead (port_relay4);
 
if (val == LOW) 
{

sprintf(query_4, "update minhacas_a.usuario1 SET posicao = 0 where id_rele = 1;");
}
else  
{
sprintf(query_4, "update minhacas_a.usuario1 SET posicao = 1 where id_rele = 1;");
}

It will be possible to change the database directly with Arduino, not using php?

darcicidade:
It will be possible to change the database directly with Arduino, not using php?

No.

darcicidade:
Failed !!!

I tried the code below but failed too:

char query_4[80];

int port_relay4 = 9;

void setup

pinMode(port_relay4, INPUT);

void loop

int val = digitalRead (port_relay4);

if (val == LOW)
{

sprintf(query_4, "update minhacas_a.usuario1 SET posicao = 0 where id_rele = 1;");
}
else 
{
sprintf(query_4, "update minhacas_a.usuario1 SET posicao = 1 where id_rele = 1;");
}





It will be possible to change the database directly with Arduino, not using php?

On a big enough Arduino and if you are prepared to write a new MySQL client from scratch perhaps...

Failed !!!

What failed was you. The code did something. You failed to say what it actually did. You expected it to do something. You failed to say what you expected.

You failed to have expectations that match reality.

You could try again, and perhaps succeed this time.

PaulS:
What failed was you. The code did something. You failed to say what it actually did. You expected it to do something. You failed to say what you expected.

You failed to have expectations that match reality.

You could try again, and perhaps succeed this time.

I would like the code sends to the database:

pinMode = LOW send 0
pinMode = HIGH send 1

Direct connection between Arduino and mysql.

PaulS:
What failed was you. The code did something. You failed to say what it actually did. You expected it to do something. You failed to say what you expected.

You failed to have expectations that match reality.

You could try again, and perhaps succeed this time.

thank you for your help with the code.
maybe my interpretation was not enough.
I'll keep trying.

Have you access to MySQL logs, haven't you? What they say when fails?

Cheers, Ale.

Direct connection between Arduino and mysql.

When you modify your instance of MySQL to read from the serial port, let us know.

The code you posted does SOMETHING. What does it do? Forget about what YOU want for the moment!

PaulS:
When you modify your instance of MySQL to read from the serial port, let us know.

The code you posted does SOMETHING. What does it do? Forget about what YOU want for the moment!

the code at the moment is:

When Column = 1 in database → the output arduino = HIGH
When Column = 0 in database → the output arduino = LOW

this connection between this direct arduino and mysql and this ok.

basically the Arduino is reading database information and processing the digital outputs

thanks.

Do you have the Arduino sending Serial data to a PC running MySQL?
Sounds like that is what you need:

if (digitalRead (inputPin) == 1){
Serial.write (1); // or however you want that formatted
}
else {
Serial.write(0);
}

Or receive data from the PC:

if (Serial.available() >0){  // sent a byte?
incomingByte = Serial.read(); // read it
if (incomingByte == '1'){  // set output based on input received
digitalWrite (outputPin, HIGH);
}
if (incomingByte == '0'){
digitaWrite (outputPin, LOW);
}
}

CrossRoads:
Do you have the Arduino sending Serial data to a PC running MySQL?

not.
the Arduino is weberserver in home and database is webserver.
this is conection:

byte mac_addr = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(46,105,95,24);

char user = "xxxx";
char password = "xxxx";

thank you so much

darcicidade:
When Column = 1 in database -> the output arduino = HIGH
When Column = 0 in database -> the output arduino = LOW

Ah - you didn't mention that. This:

   sprintf(query_4, "update minhacas_a.usuario1 SET position = '%d' where id_rele = 1;", somethingThatIsHIGHOrLOW);

of course will not work becase it puts quotes around the value. your position column is a numeric type. Or is it? Are you using a numeric 0/1 or a boolean true/false?

   char theQuery;
   if(theValue)
     query = "update minhacas_a.usuario1 SET position = 1 where id_rele = 1;"
   else 
     query = "update minhacas_a.usuario1 SET position = 0 where id_rele = 1;"

In any case, if this is going through PHP then I would expect that you would send an HTTP request to the PHP and that PHP would construct the query. very surprised to see sql in the arduino code.

Also surprised to see a semicolon on the end of that SQL. Semicolons aren't part of the sql, usually: they indicate breaks between SQL blocks in a scripting client of some kind.

Basically, there are a large number of things that might be wrong here.

  • the sketch logic is wrong and it never gets to the bit that's supposed to send the sql
  • the sketch logic is wrong and is trying to send the wrong value as the payload (wrong pin, wrong variable name)
  • the sketch blows up while trying to send the sql (eg, buffer overruns in the sprintf)
  • the ethernet isn't connected (layer 0: plug fell out of something important,possibly a power adapter)
  • the ethernet is misconfigured (eg, mac collision because you c/p code, ip on the wrong subnet)
  • the php isn't running, or is buggy
  • the php refuses to take a connection from the arduino (eg, ip whtelisting)
  • php acts as an http server and you are not sendng an http message at all
  • http issues: ssl/plain, bad content types, wrong version HTTP/1.0
  • the php script isn't running on that server
  • the php script isn't where you think it is
  • the php script takes different parameters to what you are passing
  • the php script is buggy
  • the php server doesn't have the mysql client set up
  • the php server isn't set up to connected to the right database
  • the database won't accept the php connection (password, ip whitelisting, etc)
  • the connection times out and php doesn't reestablish it
  • the sql is malformed (that semicolon might be your problem)
  • the table isn't where you expect it to be, or has a case-sensitive name, or a different name
  • the column isn't where you expect it to be, or has a case-sensitive name, or a different name
  • the column has the wrong type
  • the table hasn't been initialised and doesn't have a row 1
  • the mysql user doesn't have update permission for that particular table (***very likely)
  • the transaction doesn't commit for some other reason
  • it all works perfectly fine, but the method that you use to check whether it worked or not is wrong
  • it all works perfectly fine, but you are wrong about what value you should be seeing there

And you come here and write "It fail! Help!" You don't post your entire sketch, like the sticky says to, you don't tell us fairly important stuff like data types, and you don't present any logs (mysql or php server logs). Dude - there is no possible way other than by a sheer lucky guess that anyone here can be any help to you.

You know, if you made a little effort to pull togther the information that we would need to be of any help to you, then in the process of pulling that information together you might be able to fix it yourself.

PaulMurrayCbr:
Ah - you didn't mention that. This:

   sprintf(query_4, "update minhacas_a.usuario1 SET position = '%d' where id_rele = 1;", somethingThatIsHIGHOrLOW);

of course will not work becase it puts quotes around the value. your position column is a numeric type. Or is it? Are you using a numeric 0/1 or a boolean true/false?

   char theQuery;

if(theValue)
    query = "update minhacas_a.usuario1 SET position = 1 where id_rele = 1;"
  else
    query = "update minhacas_a.usuario1 SET position = 0 where id_rele = 1;"







In any case, if this is going through PHP then I would expect that you would send an HTTP request to the PHP and that PHP would construct the query. very surprised to see sql in the arduino code.

Also surprised to see a semicolon on the end of that SQL. Semicolons aren't part of the sql, usually: they indicate breaks between SQL blocks in a scripting client of some kind.

Basically, there are a large number of things that might be wrong here. 

- the sketch logic is wrong and it never gets to the bit that's supposed to send the sql
- the sketch logic is wrong and is trying to send the wrong value as the payload (wrong pin, wrong variable name)
- the sketch blows up while trying to send the sql (eg, buffer overruns in the sprintf)
- the ethernet isn't connected (layer 0: plug fell out of something important,possibly a power adapter)
- the ethernet is misconfigured (eg, mac collision because you c/p code, ip on the wrong subnet)
- the php isn't running, or is buggy
- the php refuses to take a connection from the arduino (eg, ip whtelisting)
- php acts as an http server and you are not sendng an http message at all
- http issues: ssl/plain, bad content types, wrong version HTTP/1.0
- the php script isn't running on that server
- the php script isn't where you think it is
- the php script takes different parameters to what you are passing
- the php script is buggy
- the php server doesn't have the mysql client set up
- the php server isn't set up to connected to the right database
- the database won't accept the php connection (password, ip whitelisting, etc)
- the connection times out and php doesn't reestablish it
- the sql is malformed (that semicolon might be your problem)
- the table isn't where you expect it to be, or has a case-sensitive name, or a different name
- the column isn't where you expect it to be, or has a case-sensitive name, or a different name
- the column has the wrong type
- the table hasn't been initialised and doesn't have a row 1
- the mysql user doesn't have update permission for that particular table (__***very likely__)
- the transaction doesn't commit for some other reason
- it all works perfectly fine, but the method that you use to check whether it worked or not is wrong
- it all works perfectly fine, but you are wrong about what value you should be seeing there 

And you come here and write "It fail! Help!" You don't post your entire sketch, like the sticky says to, you don't tell us fairly important stuff like data types, and you don't present any logs (mysql or php server logs). Dude - there is no possible way other than by a sheer lucky guess that anyone here can be any help to you.

You know, if you made a little effort to pull togther the information that we would need to be of any help to you, then in the process of pulling that information together you might be able to fix it yourself.

thanks for the help everyone.

the code is running perfectly with you help.

hugs, good week.