Arduino Project Hub
Project tutorial

Arduino leonardo eth v3 and mysql direct insert © GPL3+

This example demonstrates how to store data in a table in mysql from 4 button connected to pins 2, 3, 4, 5 direct connection

  • 2 views
  • 0 comments
  • 0 respects

Components and supplies

About this project

https://youtu.be/p2MXCNBC8BU

This example uses Arduino Leonardo ETH and MySQL to demonstrate how to store data in a table in MySQL from 4 button connected to pins 2, 3, 4, 5. I need to monitor 4 machine and time when it on. We will create a special database and table for testing.

The following are the SQL commands you will need to run in order to setup your database for running this sketch.

CREATE DATABASE machine; 
CREATE TABLE machine.state ( 
num integer primary key auto_increment, 
message char(40), 
recorded timestamp 
); 

Here we see one database and a table with three fields; a primary key that is an auto_increment, a string, and a timestamp. This will demonstrate how to save the date and time of when the row was inserted, which can help you determine when data was recorded or updated.

Instructions for use

1) Create the database and table as shown above.

2) Change the address of the server to the IP address of your MySQL server in my case my IP is 192.186.157.77

3) Change the user and password to a valid MySQL user and password in my case it is sa and 156444

4) Install MySQL library in your IDE by going to sketch > include library > manage library, and in the filter use search type MySQL, then install it.

5) Arduino Leonardo uses Ethernet2 library, so you need to install it in your IDE. Go to sketch > include library > manage library, and in the filter use search type ethernet2, then install it.

6) Also, you need to modify file MySQL_Packet.h, the original created by Dr.Charles. Change include <Ethernet.h> to include <Ethernet2.h> , otherwise it will give you an error message and not compile.

7) Connect a USB cable to your Arduino.

8) Select the Arduino Leonardo ETH board and port.

9) Compile and upload the sketch to your Arduino.

10) Connect 4 push button between ground and pins 2, 3, 4, 5 through 10k Ohm resistor.

11) After the sketch has run for some time, press some push buttons and open a MySQL client and issue the command: SELECT * FROM machine.state; to see the data recorded. Note the field values and how the database handles both the auto_increment and timestamp fields for us.

Notes

  • Use the MAC on your board in back; if not, you can put anything but make sure it is unique in your network.
  • Your MySQLl default port address is 3306, but this will conflict with Skype so you can change it to 3307 in both your sketch and your MySQL. And make sure this port is open in your server firewall.

Created by: Samir Mohamad tawfik 28-1-2017

samirtwf@gmail.com

Schematics

Arduino leonardo eth v3 to my sql
inserttomysql_cAa9KmdFjf.ino

Code

Arduino leonardo eth v3 and mysqlArduino
Arduino leonardo eth v3 and mysql direct insert
/*
  Arduino leonardo eth v3 and mysql 
  This example demonstrates how to store data in a
  table in mysql from 4 button connected to pins 2, 3, 4, 5 
  For this, we will create a special database and table for testing.
  The following are the SQL commands you will need to run in order to setup
  your database for running this sketch.

  CREATE DATABASE machine;
  CREATE TABLE machine.state (
    num integer primary key auto_increment,
    message char(40),
    recorded timestamp
  );

  Here we see one database and a table with three fields; a primary key that
  is an auto_increment, a string, and a timestamp. This will demonstrate how
  to save a date and time of when the row was inserted, which can help you
  determine when data was recorded or updated.

  INSTRUCTIONS FOR USE

  1) Create the database and table as shown above.
  2) Change the address of the server to the IP address of your MySQL server in my case my IP is 192.186.157.77
  3) Change the user and password to a valid MySQL user and password in my case it is sa and 156444
  4) install mysql library in your ide go to sketch > include library > manage library
  and in the filter your search type mysql then install it
  5) arduino leonardo use etehrnet2 library so you need to install it in your ide go to sketch > include library > manage library
  and in the filter your search type ethernet2 then install it
  6) also you need to modify this file MySQL_Packet.h the original created by Dr.Charles is - include <Ethernet.h> 
  the new is - include <Ethernet2.h> otherwise it will give your error message and not compiled  
  7) Connect a USB cable to your Arduino
  8) Select the arduino leonardo eth board and port 
  9) Compile and upload the sketch to your Arduino
  10) connect 4 push button between ground and pins 2, 3, 4, 5 through 10k Ohm resistor 
  11) After the sketch has run for some time , press some push buttons and open a mysql client and issue
     the command: SELECT * FROM machine.state; to see the data
     recorded. Note the field values and how the database handles both the
     auto_increment and timestamp fields for us. You can clear the data with.
      "DELETE FROM machine.state".

  Note: The MAC on your board in back if not you can put any thing but make sure it is unique in your network .
  Note your mysql default port address is 3306 but this will conflict with Skype so you can change it to 3307 in both your 
  sketch and your Mysql and make sore this port is open in your server firewall .
  many thanks for Dr.Charles the man how make this possible 
  Created by: Samir Mohamad tawfik 28-1-2018
  samirtwf@gmail.com
*/

#include <Ethernet2.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
int pushButton1 = 2;
int pushButton2 = 3;
int pushButton3 = 4;
int pushButton4 = 5;

byte mac_addr[] = {  0x90, 0xA2, 0xDA, 0x10, 0xBD, 0x2C };

IPAddress server_addr(192,186,157,77);  // IP of the MySQL *server* here
char user[] = "sa";              // MySQL user login username
char password[] = "156444";        // MySQL user login password

EthernetClient client;
MySQL_Connection conn((Client *)&client);

void setup() {
  pinMode(pushButton1, INPUT_PULLUP);
  pinMode(pushButton2, INPUT_PULLUP);
  pinMode(pushButton3, INPUT_PULLUP);
  pinMode(pushButton4, INPUT_PULLUP);
  Ethernet.begin(mac_addr);
   if (conn.connect(server_addr, 3307, user, password)) {
    delay(1000);
  }
 }
void loop() {
  delay(2000);
  int buttonState1 = digitalRead(pushButton1);
  if (buttonState1 == LOW) {   
  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine one on')";  // Sample query
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(INSERT_SQL);
  delete cur_mem;
  delay(1000); 
   }
  int buttonState2 = digitalRead(pushButton2);
  if (buttonState2 == LOW) { 
  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine two on')";  // Sample query
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(INSERT_SQL);
  delete cur_mem;
  delay(1000); 
   }
  int buttonState3 = digitalRead(pushButton3);
  if (buttonState3 == LOW) {     
  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine three on')";  // Sample query
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(INSERT_SQL);
  delete cur_mem;
  delay(1000); 
   } 
  int buttonState4 = digitalRead(pushButton4);
  if (buttonState4 == LOW) {    
  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine four on')";  // Sample query
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
  cur_mem->execute(INSERT_SQL);
  delete cur_mem;
  delay(1000); 
  }      
 }

Comments

Similar projects you might like

Arduino 101 BLE App

Project in progress by Alexis Santiago Allende

  • 1,528 views
  • 10 comments
  • 21 respects

FM radio

Project tutorial by Patrick Müller

  • 7,394 views
  • 1 comment
  • 33 respects

Avoid Injuries with Smart Backpack

Project tutorial by Chathuranga Liyanage

  • 843 views
  • 2 comments
  • 11 respects

Aquariumatic

Project in progress by Craig Hissett

  • 5,842 views
  • 2 comments
  • 16 respects

Arduino Digital Clock Version 2

Project showcase by John Wafik William

  • 287 views
  • 0 comments
  • 4 respects

Arduino Temperature Control

Project tutorial by Team pandhoit

  • 830 views
  • 3 comments
  • 9 respects
Add projectSign up / Login