How can I connect to database from Arduino?

Question

I want to transfer the data from the database to Arduino. For this, I am using MYSQL_Connector library in Arduino. But I'm getting error like this: "Connection Failed". I cannot connect to database. Also, I'm using XAMPP server.

I checked MYSQL server's IP address, port and username-password.

#include <SPI.h>
#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(x,x,x,x);  // the IP address to that of the system on which my MySQL server is set up
char user[] = "user";              // MySQL user login username
char password[] = "1234";        // MySQL user login password


// Sample query
char query[] = "SELECT id FROM world.city";

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

void setup() {
  Serial.begin(9600);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);
  Serial.println("Connecting...");
  if (conn.connect(server_addr, 8080, user, password)) {
    delay(1000);
  }
  else
    Serial.println("Connection failed.");
}

You've put the ip address of the MySQL server as localhost. This tells the Arduino that the MySQL server is set up on the Arduino itself . Change the IP address to that of the system on which your MySQL server is set up and make sure the system and Arduino are on the same network — RishiC, May 31 '19 at 08:16
I have changed the IP address to that of the system on which my MySQL server is set up. Arduino and system are on the same network. I paid attention to this issues. But I still cannot connect. — engineer, May 31 '19 at 08:27
Another source of error might be the port through which you're attempting the connection to the MySQL server. The default port is 3306 whereas you've entered 8080 — RishiC, May 31 '19 at 08:31
Yes, I also entered 3306 port. But still cannot connect:( Also I have changed priviliges on mysql. But I didn't get a result. — engineer, May 31 '19 at 08:35
The only other problem I can think of that might be causing the connection to fail is that you're not giving it enough time . Instead of putting only `Ethernet.begin()` in the while loop, put the entire block upto and including `conn.connect()` — RishiC, May 31 '19 at 08:40
But Ethernet.begin() is not in while loop. Can you explain in more detail what I should do? — engineer, May 31 '19 at 08:51
Right, sorry my mistake. I didn't see the semi colon there. But try putting conn.connect in a while loop till the connection is successful . Like `while(!conn.connect(...)) {}` . An empty while loop will work too, as it will go beyond the loop only once the connection to the server is established — RishiC, May 31 '19 at 09:18
A major problem with your code is that you **are not** checking ethernet interface at all. check the return value of `Ethernet.begin(mac_addr)` Then if the value is not zero then check `Ethernet.localIP()` and check the IP address. After all that try to connect to your DB. — Masoud Rahimi, May 31 '19 at 09:38
Thank you so much to your solutions. But the problem is different. It is about priviliges. — engineer, May 31 '19 at 12:18

score 3 · Accepted Answer · answered May 31 '19 at 12:16

I have solved the problem. Problem is that you will also need to give the user access to whatever database that you want to access. Firstly, you will need to create user. You can use the link below.

https://st2.ning.com/topology/rest/1.0/file/get/2053761171?profile=original

#include <Ethernet.h>
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };

IPAddress server_addr(x,x,x,x);  //the IP address to that of the system on which my MySQL server is set up
char user[] = "bob";              // MySQL user login username
char password[] = "secret";        // MySQL user login password


// Sample query
char query[] = "SELECT id FROM world.city";

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

void setup() {
  Serial.begin(9600);
  while (!Serial); // wait for serial port to connect
  Ethernet.begin(mac_addr);

  //while(!conn.connect(server_addr, 3306, user, password));

  Serial.println("Connecting...");
  if (conn.connect(server_addr, 3306, user, password)) {
    delay(1000);
  }
  else
    Serial.println("Connection failed.");
}

How can I connect to database from Arduino?

1 Answers1