I have been searching for a way to allow my arduino to read from a mysql database. I have found the example that is given in the examples of the arduino. However, since I m using Ethernet Sheild 2 I need to change my #include <Ethernet.h> to #include <Ethernet2.h>. However when I do that I get a lot of errors.
Here is my code:
/*
MySQL Connector/Arduino Example : basic select
This example demonstrates how to issue a SELECT query with no parameters
and use the data returned. For this, we use the Cursor class to execute
the query and get the results.
It demonstrates who methods for running queries. The first allows you to
allocate memory for the cursor and later reclaim it, the second shows how
to use a single instance of the cursor use throughout a sketch.
NOTICE: You must download and install the World sample database to run
this sketch unaltered. See http://dev.mysql.com/doc/index-other.html.
CAUTION: Don't mix and match the examples. Use one or the other in your
own sketch -- you'll get compilation errors at the least.
INSTRUCTIONS FOR USE
1) Change the address of the server to the IP address of the MySQL server
2) Change the user and password to a valid MySQL user and password
3) Connect a USB cable to your Arduino
4) Select the correct board and port
5) Compile and upload the sketch to your Arduino
6) Once uploaded, open Serial Monitor (use 115200 speed) and observe
Note: The MAC address can be anything so long as it is unique on your network.
Created by: Dr. Charles A. Bell
*/
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
byte mac_addr[] = { 0x90, 0xA2, 0xDA, 0x10, 0xE6, 0xD7 };
IPAddress server_addr(192,168,1,153); // IP of the MySQL *server* here
char user[] = "root"; // MySQL user login username
char password[] = "secret"; // MySQL user login password
// Sample query
char query[] = "SELECT * FROM arduino_session";
EthernetClient client;
MySQL_Connection conn((Client *)&client);
// Create an instance of the cursor passing in the connection
MySQL_Cursor cur = MySQL_Cursor(&conn);
void setup() {
Serial.begin(115200);
while (!Serial); // wait for serial port to connect
Ethernet.begin(mac_addr);
Serial.println("Connecting...");
if (conn.connect(server_addr, 3306, user, password)) {
delay(1000);
}
else
Serial.println("Connection failed.");
}
void loop() {
row_values *row = NULL;
long head_count = 0;
delay(1000);
Serial.println("1) Demonstrating using a cursor dynamically allocated.");
// Initiate the query class instance
MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
// Execute the query
cur_mem->execute(query);
// Fetch the columns (required) but we don't use them.
column_names *columns = cur_mem->get_columns();
// Read the row (we are only expecting the one)
do {
row = cur_mem->get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
// Deleting the cursor also frees up memory used
delete cur_mem;
// Show the result
Serial.print(" NYC pop = ");
Serial.println(head_count);
delay(500);
Serial.println("2) Demonstrating using a local, global cursor.");
// Execute the query
cur.execute(query);
// Fetch the columns (required) but we don't use them.
cur.get_columns();
// Read the row (we are only expecting the one)
do {
row = cur.get_next_row();
if (row != NULL) {
head_count = atol(row->values[0]);
}
} while (row != NULL);
// Now we close the cursor to free any memory
cur.close();
// Show the result but this time do some math on it
Serial.print(" NYC pop = ");
Serial.println(head_count);
Serial.print(" NYC pop increased by 12 = ");
Serial.println(head_count+12);
}
If there is a different example i should be following let me know. Or if I am doing something wrong.
If it helps. What I am trying to do is create a automated lightbulb where if I input a 1 into my database the light turns on if I input a 0 the light turns off. I would like to do this by using mysql database and simply connect straight to it over ethernet. Is this possible?
Where are you running MySQL? Why are you not running PHP there, too? If you are, you should create a PHP script that accesses the database and returns a value. Make a simple GET request to get the value.
However when I do that I get a lot of errors.
I searched through your post several times. Not a hint as to what they were.
I was running the MySQL using the mysql connector. I am very new to this. However, if you have an example of something similar I would appreciate it. I was following the example used in the arduino app. I read on the php way I just figured this way would be easier.
I have an Arduino Uno and an Ethernet Shield 2. I would like my Arduino to make pin 13 high when I put in 1 into my database which would be connected over the LAN network. I have been looking around but I cant seem to find a proper tutorial. I have seen ones that allow you to connect via serial. But no luck with Ethernet Shield 2. I have read that I should use PHP. But I am still not 100% familiar with it. If someone could show me an example of this or point me in the right direction it would be great. Thank you in advance.
"Arduino communicates with both the W5500 and SD card using the SPI bus (through the ICSP header). This is on digital pins 10, 11, 12, and 13 on the Uno "
I think you need to use a different pin, 13 is already committed.
Can't help you on the rest.
Why are you inserting a 1 into the database? Why not just send a message to the Arduino to make the pin high? Unless you have a reason that you can't get around for using a database, that just makes the problem unnecessarily difficult.
The reason being is I would like to control multiple lights in my house. And being able to control it using any computer connected to the same network. I just figured MySQL would be the best way of doing this. But if there is a better way I am all for it. Thank you also CrossRoads for the clarification.
Would this work if I had more than one Arduino running?
Since you have code that won't compile, but won't tell us what the errors are, I strongly recommend that you buy a couple dozen Arduinos, and see if that makes the errors go away.
It would be far cheaper, though, to learn how to do your project using no Arduinos. When you can make a client, on the PC, accept a value and store it in the database on the server, because you invoked the correct (php) script on the server, then doing the exact same thing from the Arduino will take about 10 minutes to code, upload, test, and report back here.
Without some sort of protocol implemented (ie: MQTT or something)..
I believe you wold just be stuck consistently polling the DB to see if a '1' is there.
Otherwise what other type of interaction/intervention is going to tel the 'Arduino connected light' to turn on.
It would need to constantly POLL the database to see if there is a change from 'previous state' to 'new state'
What you need is 2 fold IMHO..
you need an interface/GUI (or just open up the database/phpmyadmin or whatever and make the entry) to use to SET this variable to either ON (1) or OFF (0)..
This can be done countless ways.. direct DB access, some sort of HTML/CSS/PHP web page... phone app..etc..etc
** How is your DB/webserver set-up? Are you using WAMP? (bonus it has both MySQL and PHP installed by default)
You'll need some sort of 3rd party script (PHP, again, for example) that your lamp/light connected Arduino calls/requests and returns this "LAMP VARIABLE" that your Arduino will parse and act accordingly.
It may be of some help... this uses a front end Flash app/gui to send data to a PHP script.. this script in turns sends (RGB) values to my Arduino to parse and behave as coded to do so with those values.
This was done over serial... so not exactly what you need to do, but the concepts of all the moving parts is what I think you need to grasp first.
brandonm222:
Would this work if I had more than one Arduino running?
Yes, because each Arduino will have a different IP address. You send the http request to the address of the Arduino controlling each light. You are probably best to assign the IP addresses yourself, using addresses in a range not given out by your router. Otherwise, your router might give out different addresses each time it gets restarted or the Arduinos get restarted.
@PaulRB thank you for your help I found exactly what I was looking for through this post. For anyone else interested in it. I recommend you start here:
Although the tutorial is based on a webpost as you mentioned. My vision is to have multiple Arduinos as you mentioned PaulRB and give them each a static IP. From there I will create one html page that points to every IP organized by location. Thus allowing me to turn on and off lights.
However, one other question I have. I have bought this relay:
I was just wondering if any of you have used it before. Now I know how the wiring basically works. My question is if I just want to use it for the little LEDs on the front will it still work? I just want to test it all out before I start plugging in higher voltage and current to it. If not I will just have to start plugging in real power to it. Thanks again for everyones help on here.
@xl97 thanks for the information as well! I want to eventually go down this route as I feel it is a much more organised approach to keeping everything organised and more visual representation as to what is going on with all the IOs of the Arduino. I will look more into this using the link you provided! Although if you have an example specifically using Ethernet that would be a lot better as my final goal is to tuck this away in my wall and just pass a Ethernet cable with POE. Thanks again for your help.
Yes, those leds on the relay board will indicate the state of each relay even if you have nothing connected to the relay contacts. You will hear the relays click also. Will you use them for 110/240VAC lights?
A smaller, cheaper and easier solution to achieve your project may be to use esp8266 boards and WiFi rather than larger and more expensive combinations of Arduino and Ethernet shields. You can use Arduino IDE and almost the same sketch with esp boards. My favorite boards are Wemos mini, which cost $4+postage from Wemos' shop on AliExpress. But buy genuine Wemos, the clones can be trouble. If you decide on esp, make sure you buy a relay board that will run on 3.3V.
Thank you so much for all the information PaulRB. As for your question I will be using it on 110 lights. I did a quick test today using LEDs away from the relay just to see if it would actually work and it does. For some reason my relay doesn't light up. Ill have to make sure im plugging everything in correctly even though its only 3 cables to it lol. However, I have two follow up questions.
Between the normal Arduino Uno and the other ones you suggested which one would you say is more reliable if say I were to leave it plugged in all the time behind my wall. If so are there any major risks.
I get my LED to light up and turn off with a click of a button on one computer. But if I try from another computer after I tried on the first it seems to mess up the page and it becomes unresponsive any pointers on how to fix this. Here is my now code:
/*--------------------------------------------------------------
Program: eth_websrv_LED
Description: Arduino web server that serves up a web page
allowing the user to control an LED
Hardware: - Arduino Uno and official Arduino Ethernet
shield. Should work with other Arduinos and
compatible Ethernet shields.
- LED and resistor in series connected between
Arduino pin 2 and GND
Software: Developed using Arduino 1.0.3 software
Should be compatible with Arduino 1.0 +
References: - WebServer example by David A. Mellis and
modified by Tom Igoe
- Ethernet library documentation:
http://arduino.cc/en/Reference/Ethernet
Date: 11 January 2013
Author: W.A. Smith, http://startingelectronics.org
--------------------------------------------------------------*/
#include <SPI.h>
#include <Ethernet2.h>
// MAC address from Ethernet shield sticker under board
byte mac[] = { 0x90, 0xA2, 0xDA, 0x10, 0xE6, 0xD7 };
IPAddress ip(192, 168, 1, 170); // IP address, may need to change depending on network
EthernetServer server(80); // create a server at port 80
String HTTP_req; // stores the HTTP request
boolean LED_status = 0; // state of LED, off by default
void setup()
{
Ethernet.begin(mac, ip); // initialize Ethernet device
server.begin(); // start to listen for clients
Serial.begin(9600); // for diagnostics
pinMode(2, OUTPUT); // LED on pin 2
}
void loop()
{
EthernetClient client = server.available(); // try to get client
if (client) { // got client?
boolean currentLineIsBlank = true;
while (client.connected()) {
if (client.available()) { // client data available to read
char c = client.read(); // read 1 byte (character) from client
HTTP_req += c; // save the HTTP request 1 char at a time
// last line of client request is blank and ends with \n
// respond to client only after last line received
if (c == '\n' && currentLineIsBlank) {
// send a standard http response header
client.println("HTTP/1.1 200 OK");
client.println("Content-Type: text/html");
client.println("Connection: close");
client.println();
// send web page
client.println("<!DOCTYPE html>");
client.println("<html>");
client.println("<head>");
client.println("<title>Arduino LED Control</title>");
client.println("</head>");
client.println("<body>");
client.println("<h1>LED</h1>");
client.println("<p>Click to switch LED on and off.</p>");
client.println("<form method=\"get\">");
ProcessCheckbox(client);
client.println("</form>");
client.println("</body>");
client.println("</html>");
Serial.print(HTTP_req);
HTTP_req = ""; // finished with request, empty string
break;
}
// every line of text received from the client ends with \r\n
if (c == '\n') {
// last character on line of received text
// starting new line with next character read
currentLineIsBlank = true;
}
else if (c != '\r') {
// a text character was received from client
currentLineIsBlank = false;
}
} // end if (client.available())
} // end while (client.connected())
delay(1); // give the web browser time to receive the data
client.stop(); // close the connection
} // end if (client)
}
// switch LED and send back HTML for LED checkbox
void ProcessCheckbox(EthernetClient cl)
{
if (HTTP_req.indexOf("LED2=2") > -1) { // see if checkbox was clicked
// the checkbox was clicked, toggle the LED
if (LED_status) {
LED_status = 0;
}
else {
LED_status = 1;
}
}
if (LED_status) { // switch LED on
digitalWrite(2, HIGH);
// checkbox is checked
cl.println("<input type=\"checkbox\" name=\"LED2\" value=\"2\" \
onclick=\"submit();\" checked>LED2");
}
else { // switch LED off
digitalWrite(2, LOW);
// checkbox is unchecked
cl.println("<input type=\"checkbox\" name=\"LED2\" value=\"2\" \
onclick=\"submit();\">LED2");
}
}
brandonm222:
For some reason my relay doesn't light up.
You mean the leds do not light but the relays do switch?
brandonm222:
Between the normal Arduino Uno and the other ones you suggested which one would you say is more reliable if say I were to leave it plugged in all the time behind my wall. If so are there any major risks.
Do you mean house wall or the firewall of your router?
I don't know which would be more reliable. I suggest you get a Wemos Mini or similar and try it to see how reliable it is compared to your Arduino/Ethernet shield.
brandonm222:
2. I get my LED to light up and turn off with a click of a button on one computer. But if I try from another computer after I tried on the first it seems to mess up the page and it becomes unresponsive any pointers on how to fix this.
I can't see any obvious errors with that code, sorry.
Did you make sure that no other devices on your network share the same ip address?
So I wanted to give an update on my progress. To answer your questions PaulRB. I meant to say behind my house wall.
I have tested this using one of my existing light switch connections to my room. I noticed the lights weren't turning on and the relay wasn't clicking turns out the jumper cables used were to blame after using new ones everything was clicking and lighting up correctly. I used a Ethernet cable to connect directly to my laptop and forced an IP on my laptop and accessing the page created by my arduino and Ethernet shield. After doing all tests I could switch on and off my lights directly from my laptop.
My next phase is to allow me to change the state using two different computers. As right now it looks like once I access the server from one computer and than try to change the state from another computer the server seems to crash and gets stuck on either HIGH or LOW until I reboot both the Ethernet shield and the webpage. I may have to go back to the drawing board in regards to my PHP coding as I have also checked to make sure no other device has the same IP address. If anyone has any suggestions please let me know. I will post my update once I get something working.
I would still like to go through the MYSQL and PHP route but I have yet to find a proper tutorial to allow me to do this over Ethernet as I have found it using Serial only.
