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;
}
}
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.
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.
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);
}
}
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.