Wifi Shield and the MySQL library

Projects Networking, Protocols, and Devices
1

I realize this is probably a bit of a longshot but I am working on getting this MySQL Arduino Connector library, see here: ~chuck-bell/mysql-arduino/trunk : changes

So far I have gotten pretty much 90% of the way there (after fighting with it for the last couple days), it connects to the MySQL server, auths to the DB and writes records! The problem comes in after it writes (exactly) 4 records and then throws a socket error ("No Socket available"). This would appear to be just me running out of open sockets, seems like a fairly straightforward fix, close the connection when I am done writing right?

Since it is using the built in (it was written for the ethernet shield, not the wifi shield) libraries to establish a connection (client.connect()) I would think that client.stop(); would work. It doesn't...

I can however connect to the server using just the MySQL port (client.connect(server, 3306)) and then do a client.stop(); and that disconnects properly and free's up the socket properly without issue; So the problem would seem to have something to do with the way that the MySQL library handles the connection, more specifically it seems like it just doesn't have a proper "cleanup" routine.

This post doesn't seem to end in a question, I suppose I am just trying to get this to work so I am just laying out what I have done thus far and seeing if anyone out there has any input that could lead to a solution to the problem. I have attached the updated MySQL libraries (the ones I have updated), and the Sha Library (MySQL lib needs it), and a working Arduino example file. I just recently updated the firmware on the Wifi shield also, using the firmware from the latest IDE (1.0.5).

Thanks for any help!

J

mysql.rar (24.7 KB)

2

I have a similar issue. I'm trying to create insert statements, looping through values taken from a temperature component. It successfully connects and executes the first statement but fails for everyone after that. I am wondering if the connection is closing after each query.

3

Not sure why it would only execute 1 statement successfully, might have something to do with your loop, but actually found the answer to my problem, apparently the mysql connect needs to be done in setup rather than in the loop.

Once I moved the DB connection over to setup I can continue writing to the DB, the only concern I guess is how to close the DB connection, I haven't worked with it in a couple weeks but I am sure I just missed something, there is probably a function to close the connection in there somewhere.

4

I think I found the issue, it solved it for me anyway. The my_conn.mysql_connect(server_addr, 3306, user, password) line has to be in the setup method. You can then put the my_conn.cmd_query(sql) in the loop method (it doesn't have to be in the 'if' clause directly after my_conn.mysql_connect). Below is an excerpt, large amounts ommited.

void setup() {
Ethernet.begin(mac_addr);
if (my_conn.mysql_connect(server_addr, 3306, user, password))
{
Serial.println("Query Success!");
}
else
Serial.println("Connection failed.");
}

void loop() {
my_conn.cmd_query(sql);
}