Arduino Leonardo ETH v3 and MySQL Direct Insert

1/*
2  Arduino leonardo eth v3 and mysql 
3  This example demonstrates  how to store data in a
4  table in mysql from 4 button connected to pins 2, 3,  4, 5 
5  For this, we will create a special database and table for testing.
6  The following are the SQL commands you will need to run in order to setup
7  your database for running this sketch.
8
9  CREATE DATABASE machine;
10  CREATE  TABLE machine.state (
11    num integer primary key auto_increment,
12    message  char(40),
13    recorded timestamp
14  );
15
16  Here we see one database and  a table with three fields; a primary key that
17  is an auto_increment, a string,  and a timestamp. This will demonstrate how
18  to save a date and time of when  the row was inserted, which can help you
19  determine when data was recorded or  updated.
20
21  INSTRUCTIONS FOR USE
22
23  1) Create the database and table  as shown above.
24  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
25  3) Change the user and password  to a valid MySQL user and password in my case it is sa and 156444
26  4) install  mysql library in your ide go to sketch > include library > manage library
27  and  in the filter your search type mysql then install it
28  5) arduino leonardo use  etehrnet2 library so you need to install it in your ide go to sketch > include library  > manage library
29  and in the filter your search type ethernet2 then install  it
30  6) also you need to modify this file MySQL_Packet.h the original created  by Dr.Charles is - include <Ethernet.h> 
31  the new is - include <Ethernet2.h>  otherwise it will give your error message and not compiled  
32  7) Connect a USB  cable to your Arduino
33  8) Select the arduino leonardo eth board and port 
34  9) Compile and upload the sketch to your Arduino
35  10) connect 4 push button  between ground and pins 2, 3, 4, 5 through 10k Ohm resistor 
36  11) After the  sketch has run for some time , press some push buttons and open a mysql client and  issue
37     the command: SELECT * FROM machine.state; to see the data
38     recorded.  Note the field values and how the database handles both the
39     auto_increment  and timestamp fields for us. You can clear the data with.
40      "DELETE FROM  machine.state".
41
42  Note: The MAC on your board in back if not you can put  any thing but make sure it is unique in your network .
43  Note your mysql default  port address is 3306 but this will conflict with Skype so you can change it to 3307  in both your 
44  sketch and your Mysql and make sore this port is open in your  server firewall .
45  many thanks for Dr.Charles the man how make this possible  
46  Created by: Samir Mohamad tawfik 28-1-2018
47  samirtwf@gmail.com
48*/
49
50#include  <Ethernet2.h>
51#include <MySQL_Connection.h>
52#include <MySQL_Cursor.h>
53int  pushButton1 = 2;
54int pushButton2 = 3;
55int pushButton3 = 4;
56int pushButton4  = 5;
57
58byte mac_addr[] = {  0x90, 0xA2, 0xDA, 0x10, 0xBD, 0x2C };
59
60IPAddress  server_addr(192,186,157,77);  // IP of the MySQL *server* here
61char user[] =  "sa";              // MySQL user login username
62char password[] = "156444";        // MySQL user login password
63
64EthernetClient client;
65MySQL_Connection  conn((Client *)&client);
66
67void setup() {
68  pinMode(pushButton1, INPUT_PULLUP);
69  pinMode(pushButton2, INPUT_PULLUP);
70  pinMode(pushButton3, INPUT_PULLUP);
71  pinMode(pushButton4, INPUT_PULLUP);
72  Ethernet.begin(mac_addr);
73   if (conn.connect(server_addr,  3307, user, password)) {
74    delay(1000);
75  }
76 }
77void loop() {
78  delay(2000);
79  int buttonState1 = digitalRead(pushButton1);
80  if (buttonState1 == LOW) {   
81  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine one  on')";  // Sample query
82  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
83  cur_mem->execute(INSERT_SQL);
84  delete cur_mem;
85  delay(1000); 
86   }
87  int buttonState2 = digitalRead(pushButton2);
88  if (buttonState2 == LOW) { 
89  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine two  on')";  // Sample query
90  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
91  cur_mem->execute(INSERT_SQL);
92  delete cur_mem;
93  delay(1000); 
94   }
95  int buttonState3 = digitalRead(pushButton3);
96  if (buttonState3 == LOW) {     
97  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine three  on')";  // Sample query
98  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
99  cur_mem->execute(INSERT_SQL);
100  delete cur_mem;
101  delay(1000); 
102   }  
103  int buttonState4 = digitalRead(pushButton4);
104  if (buttonState4 == LOW)  {    
105  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine  four on')";  // Sample query
106  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
107  cur_mem->execute(INSERT_SQL);
108  delete cur_mem;
109  delay(1000); 
110  }      
111  }
112

1/*
2  Arduino leonardo eth v3 and mysql 
3  This example demonstrates
4  how to store data in a
5  table in mysql from 4 button connected to pins 2, 3,
6  4, 5 
7  For this, we will create a special database and table for testing.
8
9  The following are the SQL commands you will need to run in order to setup
10
11  your database for running this sketch.
12
13  CREATE DATABASE machine;
14  CREATE
15  TABLE machine.state (
16    num integer primary key auto_increment,
17    message
18  char(40),
19    recorded timestamp
20  );
21
22  Here we see one database and
23  a table with three fields; a primary key that
24  is an auto_increment, a string,
25  and a timestamp. This will demonstrate how
26  to save a date and time of when
27  the row was inserted, which can help you
28  determine when data was recorded or
29  updated.
30
31  INSTRUCTIONS FOR USE
32
33  1) Create the database and table
34  as shown above.
35  2) Change the address of the server to the IP address of your
36  MySQL server in my case my IP is 192.186.157.77
37  3) Change the user and password
38  to a valid MySQL user and password in my case it is sa and 156444
39  4) install
40  mysql library in your ide go to sketch > include library > manage library
41  and
42  in the filter your search type mysql then install it
43  5) arduino leonardo use
44  etehrnet2 library so you need to install it in your ide go to sketch > include library
45  > manage library
46  and in the filter your search type ethernet2 then install
47  it
48  6) also you need to modify this file MySQL_Packet.h the original created
49  by Dr.Charles is - include <Ethernet.h> 
50  the new is - include <Ethernet2.h>
51  otherwise it will give your error message and not compiled  
52  7) Connect a USB
53  cable to your Arduino
54  8) Select the arduino leonardo eth board and port 
55
56  9) Compile and upload the sketch to your Arduino
57  10) connect 4 push button
58  between ground and pins 2, 3, 4, 5 through 10k Ohm resistor 
59  11) After the
60  sketch has run for some time , press some push buttons and open a mysql client and
61  issue
62     the command: SELECT * FROM machine.state; to see the data
63     recorded.
64  Note the field values and how the database handles both the
65     auto_increment
66  and timestamp fields for us. You can clear the data with.
67      "DELETE FROM
68  machine.state".
69
70  Note: The MAC on your board in back if not you can put
71  any thing but make sure it is unique in your network .
72  Note your mysql default
73  port address is 3306 but this will conflict with Skype so you can change it to 3307
74  in both your 
75  sketch and your Mysql and make sore this port is open in your
76  server firewall .
77  many thanks for Dr.Charles the man how make this possible
78  
79  Created by: Samir Mohamad tawfik 28-1-2018
80  samirtwf@gmail.com
81*/
82
83#include
84  <Ethernet2.h>
85#include <MySQL_Connection.h>
86#include <MySQL_Cursor.h>
87int
88  pushButton1 = 2;
89int pushButton2 = 3;
90int pushButton3 = 4;
91int pushButton4
92  = 5;
93
94byte mac_addr[] = {  0x90, 0xA2, 0xDA, 0x10, 0xBD, 0x2C };
95
96IPAddress
97  server_addr(192,186,157,77);  // IP of the MySQL *server* here
98char user[] =
99  "sa";              // MySQL user login username
100char password[] = "156444";
101        // MySQL user login password
102
103EthernetClient client;
104MySQL_Connection
105  conn((Client *)&client);
106
107void setup() {
108  pinMode(pushButton1, INPUT_PULLUP);
109
110  pinMode(pushButton2, INPUT_PULLUP);
111  pinMode(pushButton3, INPUT_PULLUP);
112
113  pinMode(pushButton4, INPUT_PULLUP);
114  Ethernet.begin(mac_addr);
115   if (conn.connect(server_addr,
116  3307, user, password)) {
117    delay(1000);
118  }
119 }
120void loop() {
121  delay(2000);
122
123  int buttonState1 = digitalRead(pushButton1);
124  if (buttonState1 == LOW) {   
125
126  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine one
127  on')";  // Sample query
128  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
129
130  cur_mem->execute(INSERT_SQL);
131  delete cur_mem;
132  delay(1000); 
133   }
134
135  int buttonState2 = digitalRead(pushButton2);
136  if (buttonState2 == LOW) { 
137
138  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine two
139  on')";  // Sample query
140  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
141
142  cur_mem->execute(INSERT_SQL);
143  delete cur_mem;
144  delay(1000); 
145   }
146
147  int buttonState3 = digitalRead(pushButton3);
148  if (buttonState3 == LOW) {     
149
150  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine three
151  on')";  // Sample query
152  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
153
154  cur_mem->execute(INSERT_SQL);
155  delete cur_mem;
156  delay(1000); 
157   }
158  
159  int buttonState4 = digitalRead(pushButton4);
160  if (buttonState4 == LOW)
161  {    
162  char INSERT_SQL[] = "INSERT INTO machine.state (message) VALUES ('machine
163  four on')";  // Sample query
164  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);
165
166  cur_mem->execute(INSERT_SQL);
167  delete cur_mem;
168  delay(1000); 
169  }      
170
171  }
172