Connecting Arduino to MySQl Database W/ USB Using MysqlIO

by am7

81K5557

Intro: Connecting Arduino to MySQl Database W/ USB Using MysqlIO

I always thought about controlling arduino from another device over Wi-Fi or internet, but I didn’t want sacrifice any pin for communication. I already made a project to control my PC with arduino using IR remote (You can find the link here).

I upgraded the project to next level to communicate to MySQL database. Arduino sends data via Serial port to mysqlio in specific format i.e. “query=SELECT * FROM arduino”. Mysqlio detects these specific keywords and extracts the mysql command from the received string. Then it sends the same extracted mysql command to perform query.

I already found an instructable to communicate to mysql database for Linux and Mac systems in python. Windows doesn’t allow control serial ports from that application, moreover the mysql library that I designed is completely similar to real MySQL syntax, it seems like you doing MySQL in arduino itself.

I submitted this project for contest, so please vote if you like it.

STEP 1: Materials Required

  1. Any Arduino
  2. USB cable A to B

STEP 2: Development Tools Required

  • Arduino IDE (Link to download IDE)
  • Wamp Server or another MySQL service (Note: You can also use any free MySQL server from internet, it will give your project wireless capabilities)
  • And of course, MysqlIO

STEP 3: Setting Up the Database for Arduino

Run Wamp Server and open your internet browser, type ‘localhost’ in the address bar. You’ll see something as this image.

Click on phpmyadmin and create a new database ‘arduino’.

Now, create a table, name it as ‘sensors’ with 3 fields.

Use this as reference:

‘id’ as INT with auto increment.

‘name’ as VARCHAR of size 100.

‘value’ as INT.

Save the table, and now we are ready to code arduino.

STEP 4: Coding the Arduino

Extract the downloaded mysql.zip files in arduino’s libraries folder.

Restart the arduino to load the mysql library. Then go to: File>Example>mysql>ConnectToMysql

Change the user and password according to your mysql server configuration.

Upload the code to arduino.

STEP 5: Talking to MySQL Database With Arduino

Download and install the MysqlIO app. Make sure Wamp Server is running else it will give an error.

Select the com port to which arduino is connected and the baud rate.

Mysqlio will receive data from the arduino and decode the received data into useable mysql commands.

As you can see, Connected to localhost and Disconnected which indicates that arduino has successfully connected to mysql database arduino.

Try other examples like, InsertRecord, DeleteRecord or any other code of your own.

You can also alter the parameters with variables to store sensor’s data over mysql database.

STEP 6: Conclusion

Creating a website or another app to read these values in mysql database and performs events according to the values will give unimaginable powers to your project.

If you use an online mysql server instead of ‘localhost’(i.e., host=”mysql.yourdomain.com”) your project gains the power of internet via USB cable using no Wi-Fi or Ethernet shields, all you need is to connect your computer to internet.

I’m not aware of any other app or library which can communicate to MySQL database in much simpler syntax. I already created a website with complete documentation and examples of Mysqlio application (here), it is completely open-source. You can download the Mysqlio source code and improve the app, it is written in VB .NET, you are welcome to any suggestions.

You can also download the Source code of MysqlIO, MysqlIO app or Arduino's library from my website too, there you will find references of other functions.

I already created another project to control the arduino from a php website on Wamp Server. Using MysqlIO app, I will post that instructable soon.

34 Comments

Ir Gloire 1 year ago

how to setup a complex insert with lysqlIO

paribesh_c 1 year ago

I tried the code but it is showing an error

Arduino: 1.8.19 (Windows Store 1.8.57.0) (Windows 10), Board: "Arduino Uno"
C:\Users\parib\AppData\Local\Temp\arduino_modified_sketch_127409\ConnectToMysql.ino: In function 'void setup()':
C:\Users\parib\AppData\Local\Temp\arduino_modified_sketch_127409\ConnectToMysql.ino:30:9: warning: ISO C++ forbids converting a string constant to 'char*' [-Wwrite-strings]
host = "127.0.0.1";
^~~~~~~~~~~
C:\Users\parib\AppData\Local\Temp\arduino_modified_sketch_127409\ConnectToMysql.ino:31:9: warning: ISO C++ forbids converting a string constant to 'char*' [-Wwrite-strings]
user = "root";
^~~~~~
C:\Users\parib\AppData\Local\Temp\arduino_modified_sketch_127409\ConnectToMysql.ino:32:9: warning: ISO C++ forbids converting a string constant to 'char*' [-Wwrite-strings]
pass = "Admin@456";
^~~~~~~~~~~
C:\Users\parib\AppData\Local\Temp\arduino_modified_sketch_127409\ConnectToMysql.ino:33:7: warning: ISO C++ forbids converting a string constant to 'char*' [-Wwrite-strings]
db = "medsupport";
^~~~~~~~~~~~
C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp: In function 'int mysql_connect(char*, char*, char*, char*)':
C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp:31:5: error: redefinition of 'int mysql_connect(char*, char*, char*, char*)'
int mysql_connect(char *host, char *user, char *pass, char *db){
^~~~~~~~~~~~~
C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp:10:5: note: 'int mysql_connect(char*, char*, char*, char*)' previously defined here
int mysql_connect(char *host, char *user, char *pass, char *db){
^~~~~~~~~~~~~
C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp: In function 'String mysql_result_query(String, String)':
C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp:67:10: error: conversion from 'int' to 'String' is ambiguous
return 0;
^
In file included from C:\Program Files\WindowsApps\ArduinoLLC.ArduinoIDE_1.8.57.0_x86__mdqgnx93n4wtt\hardware\arduino\avr\cores\arduino/Arduino.h:232:0,
from C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp:7:
C:\Program Files\WindowsApps\ArduinoLLC.ArduinoIDE_1.8.57.0_x86__mdqgnx93n4wtt\hardware\arduino\avr\cores\arduino/WString.h:61:2: note: candidate: String::String(const __FlashStringHelper*)
String(const __FlashStringHelper *str);
^~~~~~
C:\Program Files\WindowsApps\ArduinoLLC.ArduinoIDE_1.8.57.0_x86__mdqgnx93n4wtt\hardware\arduino\avr\cores\arduino/WString.h:59:2: note: candidate: String::String(const char*)
String(const char *cstr = "");
^~~~~~
C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp:71:10: error: conversion from 'int' to 'String' is ambiguous
return 0;
^
In file included from C:\Program Files\WindowsApps\ArduinoLLC.ArduinoIDE_1.8.57.0_x86__mdqgnx93n4wtt\hardware\arduino\avr\cores\arduino/Arduino.h:232:0,
from C:\Users\parib\OneDrive\Documents\Arduino\libraries\mysql\mysql.cpp:7:
C:\Program Files\WindowsApps\ArduinoLLC.ArduinoIDE_1.8.57.0_x86__mdqgnx93n4wtt\hardware\arduino\avr\cores\arduino/WString.h:61:2: note: candidate: String::String(const __FlashStringHelper*)
String(const __FlashStringHelper *str);
^~~~~~
C:\Program Files\WindowsApps\ArduinoLLC.ArduinoIDE_1.8.57.0_x86__mdqgnx93n4wtt\hardware\arduino\avr\cores\arduino/WString.h:59:2: note: candidate: String::String(const char*)
String(const char *cstr = "");
^~~~~~
exit status 1
Error compiling for board Arduino Uno.
This report would have more information with
"Show verbose output during compilation"
option enabled in File -> Preferences.

please help.

NathM1 5 years ago

D:\Installed\Arduino\libraries\mysql\mysql.cpp: In function 'int mysql_connect(char*, char*, char*, char*)':

D:\Installed\Arduino\libraries\mysql\mysql.cpp:31:5: error: redefinition of 'int mysql_connect(char*, char*, char*, char*)'

int mysql_connect(char *host, char *user, char *pass, char *db){

^

D:\Installed\Arduino\libraries\mysql\mysql.cpp:10:5: note: 'int mysql_connect(char*, char*, char*, char*)' previously defined here

int mysql_connect(char *host, char *user, char *pass, char *db){

^

exit status 1

Error compiling for board Arduino/Genuino Uno.

please help me i can't connect to wamp server

sonalmane517 2 years ago

i m facing same problem ...can anyone help me out

muskanp1 5 years ago

Did you find a solution to it ?

sharisankar76 4 years ago

Sir,this library doesn't supporting.Please correct it &update it.

hajouraa 4 years ago

why your library is not working for me

Alegzandriuno 4 years ago

Really bad project overall. In order to properly compile you have to edit the mysql CPP file in C:\Users\user\Documents\Arduino\libraries\mysql because it contains 2 functions named the same way "mysql_connect'. The Return Record example not only that it doesn't work, but it gives out an error, because the "mysql_result_query" that is used (and created in the cpp file) is faulty. You can replace "mysql_result_query" with "mysql_query" in the arduino code and it will compile and the mysqlio app will connect succesfully, however the querry will not show any correct information. For me atleast it either returns the value "43" or the character "%". 5/10

muskanp1 5 years ago

While uploading the code in arduino I came across a compilation error , how can I resolve that ?

ronak_jadav 5 years ago

pagal hogaye he shab chhoti chhoti problem ke liye yaaha comment karte he ...

JahnaviR2 6 years ago

I have a doubt regarding the RetrieveRecord in that library.What's the need of using digital pin 12 there.

ANy one please explain this to me.

if(result == "False")
{
digitalWrite(12, LOW);
}
else
{
digitalWrite(12, HIGH);
}

ThankYou.

ronak_jadav 5 years ago

chal ab bhav mat kha tera number de.....

RishithaS 5 years ago

I have this problem. Its a problem with the library.

C:\Users\user\Documents\Arduino\libraries\mysql\mysql.cpp: In function 'int mysql_connect(char*, char*, char*, char*)':

C:\Users\user\Documents\Arduino\libraries\mysql\mysql.cpp:31:5: error: redefinition of 'int mysql_connect(char*, char*, char*, char*)'

int mysql_connect(char *host, char *user, char *pass, char *db){

C:\Users\user\Documents\Arduino\libraries\mysql\mysql.cpp:10:5: note: 'int mysql_connect(char*, char*, char*, char*)' previously defined here

int mysql_connect(char *host, char *user, char *pass, char *db){

C:\Users\user\Documents\Arduino\libraries\mysql\mysql.cpp: In function 'String mysql_result_query(String, String)':

C:\Users\user\Documents\Arduino\libraries\mysql\mysql.cpp:67:10: error: converting to 'String' from initializer list would use explicit constructor 'String::String(int, unsigned char)'

C:\Users\user\Documents\Arduino\libraries\mysql\mysql.cpp:71:10: error: converting to 'String' from initializer list would use explicit constructor 'String::String(int, unsigned char)'

keerti_vardhan 6 years ago

After completing all the steps when I am trying compile the code in Arduino is always throwing this error:

mysql.cpp:74:10: error: converting to 'String' from intiailizer list would use explicit constructor 'String::String(int, unsigned char)'

return 0;

^

We tried various ways to resolve this but still it is not working.

Please help me with this.

SajiM5 6 years ago

Unable to install due to error when installing Mysqlio application.

"The application requires that assembly MySql.Data version 6.9.4.0 in the GAC first

What to do>

JahnaviR2 6 years ago

Just search in net for MySql.Data 6.9.4.0 version and download that.Then
again run the setup file.The error will not persist.Don;t download any
higher versions as they won't work.

And i just wanted to know whether all these code(connecting to database,retrieving records) worked for you.If so please let me know in the comment.Thank You.

JahnaviR2 6 years ago

Just search in net for MySql.Data 6.9.4.0 version and download that.Then again run the setup file.The error will not persist.Don;t download any higher versions as they won't work.

AhmedJ17 8 years ago

Hi am7,

I trying to use you your project in real life,

but I have this errors just like djsrini's ...

pleas update If you figure how to fix this.

thank you.

C:\Program Files (x86)\Arduino\libraries\mysql\mysql.cpp: In function 'String mysql_result_query(String, String)':

C:\Program Files (x86)\Arduino\libraries\mysql\mysql.cpp:67:10: error: converting to 'String' from initializer list would use explicit constructor 'String::String(int, unsigned char)'

return 0;

^

C:\Program Files (x86)\Arduino\libraries\mysql\mysql.cpp:71:10: error: converting to 'String' from initializer list would use explicit constructor 'String::String(int, unsigned char)'

return 0;

^

exit status 1
Error compiling.

More Comments