Reading Values from MYSQL

Projects Programming
1

Hey Guys,
Im new to reading from MYSQL on an Arduino (Mega), I have been using it to write to a DB for ages, but cannot get my head around this.

I have a DB called relaycontrol1, it has several tables but the one im intrested in is called relaycontroldefaults, that has the floowing fields : relaynumber and default status.

The plan is on boot to read the table, and set the status on each relay ..

the Relaynumber is type int (1 to 16) and defaultstatus is boolean 1 being on 0 being off

Relaynumber, defaultstatus
1 , 0
2 , 1

ETC..

Im using the latest library by chuck, but struggling with this... My code (that does not work) is as follows :

cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 1 ");
cur.get_columns();
relaystate1 = atol(row->values[0]);
Serial.println(row->values[0]);

cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 2 ");
relaystate2 = atol(row->values[0]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 3 ");
relaystate3 = atol(row->values[2]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 4 ");
relaystate4 = atol(row->values[3]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 5 ");
relaystate5 = atol(row->values[4]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 6 ");
relaystate6 = atol(row->values[5]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 7 ");
relaystate7 = atol(row->values[6]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 8 ");
relaystate8 = atol(row->values[7]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 9 ");
relaystate9 = atol(row->values[8]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 10 ");
relaystate10 = atol(row->values[9]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 11 ");
relaystate11 = atol(row->values[10]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 12 ");
relaystate12 = atol(row->values[11]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 13 ");
relaystate13 = atol(row->values[12]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 14 ");
relaystate14 = atol(row->values[13]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 15 ");
relaystate15 = atol(row->values[14]);
cur.get_columns();
cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 16 ");
relaystate16 = atol(row->values[15]);
// Now we close the cursor to free any memory

  cur.close();

  // Show the result
 Serial.print("Relay one = "); Serial.println(relaystate1);
 Serial.print("Relay two = "); Serial.println(relaystate2);
 Serial.print("Relay 3 = "); Serial.println(relaystate3);
Serial.print("Relay 4 = "); Serial.println(relaystate4);
Serial.print("Relay 5 = "); Serial.println(relaystate5);
Serial.print("Relay 6 = "); Serial.println(relaystate6);
Serial.print("Relay 7 = "); Serial.println(relaystate7);
Serial.print("Relay 8 = "); Serial.println(relaystate8);
Serial.print("Relay 9 = "); Serial.println(relaystate9);
Serial.print("Relay 10 = "); Serial.println(relaystate10);
Serial.print("Relay 11 = "); Serial.println(relaystate11);
Serial.print("Relay 12 = "); Serial.println(relaystate12);
Serial.print("Relay 13 = "); Serial.println(relaystate13);
Serial.print("Relay 14 = "); Serial.println(relaystate14);
Serial.print("Relay 15 = "); Serial.println(relaystate15);
Serial.print("Relay 16 = "); Serial.println(relaystate16);

Im up for any help as I just have never done this before - and I have other tables etc I will want to read in the future (Timers etc)

I can access the DB etc fine so the username password etc are all good..
Thanks in advance..

2

Im new to reading from MYSQL on an Arduino (Mega),

You can't run MySQL on a Mega.

You can't read from a MySQL database on a server from the Mega.

You CAN have the Mega make a GET request, and have some application on the server handle that request and return the data it gets when it reads from the MySQL database.

I have a DB called relaycontrol1

Strange name. The table might reasonably be called relayControl. There is no reason to have a numeric suffix in the ONE table's name.

The plan is on boot to read the table, and set the status on each relay ..

So, you have to make multiple GET requests, to get the status for each relay.

My code (that does not work) is as follows :

Your code won't even compile, so talk of it working, or not, is useless babble.

relaystate1 = atol(row->values[0]);

Just how many states can your relays take on? Why do you need a long to store 0 or 1?

Are you sure you don't need a long long?

cur.execute("SELECT * FROM `relaycontroldefaults` WHERE `Relaynumber` = 13 ");
relaystate13 = atol(row->values[12]);
cur.get_columns();

Make a request. Use some data. Get the response data. Your cart is on the wrong end of THAT horse.

3

Hi,

Thanks for replying..

I got exactly what I need working before your reply..

The Arduino mega, connects to the MYSQL DB on the windows server fine, reads the values and actions it !..

Below is the code I came up with !

void loadrelaydefaults()
{char query[] = "SELECT defaultstatus FROM `relaycontroldefaults` ";
  
int i =1;  

//row_values *row = NULL;
boolean head_count = 0;

//  delay(1000);
cur.execute("Use relaycontrol1"); //Select the DB
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn); // Execute the query 
cur_mem->execute(query); 
// Fetch the columns and print them   
column_names *cols = cur_mem->get_columns(); 
  for (int f = 0; f < cols->num_fields; f++) {     Serial.print(cols->fields[f]->name);     if (f < cols->num_fields-1) {       Serial.print(',');     }   }

 Serial.println();   // Read the rows and print them   
 row_values *row = NULL;   
 do {     
    row = cur_mem->get_next_row();     
 if (row != NULL) {       
 for (int f = 0; f < cols->num_fields; f++) { 
   Serial.print(i);Serial.print(" ");
   Serial.print(row->values[f]);Serial.print(" , ");
   //relaystatus[f] = atol(row->values[f]);//  !( (char)atoi(row->values[f]) )
  relaystatus[i] = !( (char)atoi(row->values[f]));
  Serial.print("Relay status = ");
  Serial.print(relaystatus[i]); Serial.print(" ");
  i = i+1;
 if (f < cols->num_fields-1) {           
 Serial.print(',');         }    
 }       
 Serial.println();     }   
 } while (row != NULL);   // Deleting the cursor also frees up memory used   
 delete cur_mem; 

  // Show the result Note ON  = 1 anything else is off
 Serial.print("Relay one = "); Serial.println(relaystatus[1]);
 Serial.print("Relay two = "); Serial.println(relaystatus[2]);
 Serial.print("Relay 3 = "); Serial.println(relaystatus[3]);
Serial.print("Relay 4 = "); Serial.println(relaystatus[4]);
Serial.print("Relay 5 = "); Serial.println(relaystatus[5]);
Serial.print("Relay 6 = "); Serial.println(relaystatus[6]);
Serial.print("Relay 7 = "); Serial.println(relaystatus[7]);
Serial.print("Relay 8 = "); Serial.println(relaystatus[8]);
Serial.print("Relay 9 = "); Serial.println(relaystatus[9]);
Serial.print("Relay 10 = "); Serial.println(relaystatus[10]);
Serial.print("Relay 11 = "); Serial.println(relaystatus[11]);
Serial.print("Relay 12 = "); Serial.println(relaystatus[12]);
Serial.print("Relay 13 = "); Serial.println(relaystatus[13]);
Serial.print("Relay 14 = "); Serial.println(relaystatus[14]);
Serial.print("Relay 15 = "); Serial.println(relaystatus[15]);
Serial.print("Relay 16 = "); Serial.println(relaystatus[16]);


for (int i = 0; i < 17; i=i+1)
{relaycontrol(i,relaystatus[i]);
}
}
4

I guess that you how found this ? : MySQL Connector/Arduino in Launchpad

5

Pretty much Yes, Expanded on it now, and reading loads !!!!!