New Documentation!
Best of all, I've written a reference manual that includes examples of how to use the connector in a variety of ways. Included in the document are advice on how to write your sketches, troubleshooting tips, and a long FAQ compiled from the many questions from my blogs.Changes
The only code change in this release is to fix a defect when using the connector with the latest versions of MySQL.Downloading the Connector
To download the connector library and the new reference manual, visit lp:mysql-arduino.For More Information
As a reminder, there is a forum for Q&A which can be found here. I try to check and respond to forum entries weekly.Happy Arduino!
This comment has been removed by the author.
ReplyDeleteI've fixed the missing sha1 folder. Please retry your download.
DeleteWhy don't add your library to official Arduino's Lbrary Manager? This would give more visibility to it. I think you should move the code to some kind of Git server, though...
ReplyDeleteThanks for the suggestion!
DeleteCan CC3000 WiFi shield uses this MySQL Connector library?
ReplyDeleteThe connector does not work with the CC3000 modules, only the Arduino compatible Ethernet modules.
DeleteHello Dr.Bell,
ReplyDeleteI have this error ;
http://i.imgur.com/h4MnHQX.png
Thanks..
You must enable SELECT by uncommenting WITH_SELECT in mysql.h. Take a look at the reference manual. ;)
Deletehttps://launchpad.net/mysql-arduino/release-1.0.4-ga/release-1.0.4-ga/+download/MySQL_Connector_Arduino_Reference_Manual-1.0.4ga.pdf
I fixed like this :
Delete#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"
byte mac_addr[] = { 0x90, 0xA2, 0xDA, 0x0F, 0x69, 0xAC };
byte ip_addr[] = { 192, 168, 10, 127 };
byte dns_addr[] = { 192, 168, 10, 1 };
byte gateway_addr[] = { 192, 168, 10, 1 };
byte netmask[] = { 255, 255, 255, 0 };
IPAddress server_addr(192, 168, 2, 100);
Connector my_conn; // The Connector/Arduino reference
char user[] = "";
char password[] = ""; //the credentials are correct in my code
char INSERT_SQL[] = "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!');";
void setup() {
Serial.begin(115200);
Ethernet.begin(mac_addr, ip_addr, dns_addr, gateway_addr, netmask); //Yes, I know this is way more than necessary, but just to play it safe
delay(1000);
Serial.print("IP: ");
Serial.println(Ethernet.localIP()); // debugging
Serial.println("Connecting...");
if (my_conn.mysql_connect(server_addr, 3306, user, password)) //connect to database
{
delay(500);
my_conn.cmd_query(INSERT_SQL);
Serial.println("Query Success!");
}
else
Serial.println("Connection failed.");
}
void loop() {
}
thanks for help
This comment has been removed by the author.
ReplyDeleteSir,
ReplyDeleteI have an issue when running a SELECT query. When I run the example code, I receive the results, but after the last row is returned, it returns an empty row where there shouldn't be one and stops cold.
For instance. If I run the following function:
void do_query(const char *q) {
column_names *c; // pointer to column values
row_values *r; // pointer to row values
// First, execute query. If it returns a value pointer,
// we have a result set to process. If not, we exit.
if (!my_conn.cmd_query(q)) {
return;
}
// Next, we read the column names and display them.
//
// NOTICE: You must *always* read the column names even if
// you do not use them. This is so the connector can
// read the data out of the buffer. Row data follows the
// column data and thus must be read first.
c = my_conn.get_columns();
for (int i = 0; i < c->num_fields; i++) {
Serial.println(c->fields[i]->name);
if (i < c->num_fields - 1) {
Serial.print(",");
}
}
// Next, we use the get_next_row() iterator and read rows printing
// the values returned until the get_next_row() returns NULL.
int num_cols = c->num_fields;
int rows = 0;
do {
r = my_conn.get_next_row();
bool value = r;
Serial.print("get_next_row() value = ");
Serial.println(value);
if (r) {
rows++;
for (int i = 0; i < num_cols; i++) {
//Serial.print(r->values[i]);
char *result = r->values[i];
Serial.print(result);
if (i < num_cols - 1) {
Serial.print(", ");
}
}
// Note: we free the row read to free
// the memory allocated for it.
// You should do this after you've processed the row.
my_conn.free_row_buffer();
Serial.println();
}
} while (r);
Serial.print(rows);
Serial.println(" rows in result.");
// Finally, we are done so we free the column buffers
my_conn.free_columns_buffer();
my_conn.clear_ok_packet();
}
The function will run through the do...while loop until it pulls and displays all rows, but "get_next_row()" in the do...while loop returns true again after the last row is read instead of returning NULL. Then the program stops and doesn't go any farther. I'm assuming that the "stop" is due to "get_next_row()"returning true and running the query on a non existent row, resulting in an invalid query.
The exact same thing happens if I use
my_conn.cmd_query_P("select ??? from ??? where ??? = '???'");
my_conn.show_results();
I have no trouble if I directly pull one row alone.
What version of MySQL are you using? It is MySQL or something else? ;)
DeleteAre you using the 1.0.4 code? If so, what happens when you replace your do_query() method with the show_results() method in the connector? Like this:
my_conn.cmd_query(TEST_SELECT_QUERY);
my_conn.show_results();
Also, you state the code works for queries that return one row but have you tried running several queries that return one row? If there is data not being read from the server, you may get packet errors in this case.
I am using the 1.0.4 code and I am using MySQL 5.7.9. I have also tried it with MariaDB from the XAMPP 5.5.30 and 5.6.14 stacks. I get the same result.
DeleteIf I run the following code
#include
#include
#include
#include
//#include // Add this for the Due
#include
#include
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,0,1,54);
char user[] = "root";
char password[] = "root";
Connector my_conn; // The Connector/Arduino reference
const char TEST_SELECT_QUERY[] = "SELECT * FROM world.city LIMIT 10";
void setup() {
Ethernet.begin(mac_addr);
Serial.begin(115200);
delay(1000);
Serial.println("Connecting...");
if (my_conn.mysql_connect(server_addr, 3306, user, password)) {
delay(1000);
}
else {
Serial.println("Connection failed.");
}
my_conn.cmd_query(TEST_SELECT_QUERY);
my_conn.show_results();
Serial.println("Finished with the Query!");
}
void loop() {
}
I get this as my serial monitor output.
Connecting...
ID,Name,CountryCode,District,Population
1,Kabul,AFG,Kabol,1780000
2,Qandahar,AFG,Qandahar,237500
3,Herat,AFG,Herat,186800
4,Mazar-e-Sharif,AFG,Balkh,127800
5,Amsterdam,NLD,Noord-Holland,731200
6,Rotterdam,NLD,Zuid-Holland,593321
7,Haag,NLD,Zuid-Holland,440900
8,Utrecht,NLD,Utrecht,234323
9,Eindhoven,NLD,Noord-Brabant,201843
10,Tilburg,NLD,Noord-Brabant,193238
,,,,
The program just stops here. It should not be returning a row where the commas are at the end, and it should continue on and print the line "Finished with the Query!".
This is where I'm stuck. I have narrowed it down to the do...while loop in the show.results() function, but can't figure out for the life of me how to fix it.
I have tested your sketch above with MySQL 5.7.8 and 5.7.9. The code works correctly. So, there must be something odd about your server installation.
DeleteTry this. Turn on WITH_DEBUG in mysql.h and add print_packet() inside the while loop in show_results() as follows.
...
// Read the rows
while (get_next_row()) {
print_packet();
rows++;
...
This will print out the contents of each packet after the get. What I would need to see is the contents of that last packet.
You can post it here or send it to me at: d r c h a r l e s b e l l at g m a i l dot c o m.
This comment has been removed by the author.
ReplyDeleteHi Chuck,
ReplyDelete1) Can Intel Galileo board gen 2 use this library?
2) Im using Wamp server localhost, is that mean my IPAddress server_addr() is my IPv4 address?
3) I try to connect but serial monitor show connection failed.
Can u help me? please.
here is my code.
#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "mysql.h"
/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10,207,134,61);
/* Setup for the Connector/Arduino */
Connector my_conn; // The Connector/Arduino reference
char user[] = "bob";
char password[] = "secret";
char INSERT_SQL[] =
"INSERT INTO arduino.hello VALUES (NULL, 'Hello, MySQL!')";
void setup() {
Ethernet.begin(mac_addr);
Serial.begin(9600);
delay(1000);
Serial.println("Connecting...");
if (my_conn.mysql_connect(server_addr, 3306, user, password)){
delay(500);
my_conn.cmd_query(INSERT_SQL);
Serial.println("Query Success!");
}
else {
Serial.println("Connection failed!");
}
}
void loop() {
}
Yes, you can use this on the Arduino side of the Galileo. Yes, the IP address needs to be the IP address as shown on the machine that hosts the MySQL server.
ReplyDeleteThere are a number of common mistakes/errors that are explained in the reference manual available on the launchpad site. Read that first. ;)
Dear Chuck
ReplyDeleteCan the library be used with Arduino Yun.
I tried but failed ...
Thanks,
Jodi
Hi. Yes and no. You can use an Ethernet or WiFi shield and the connector (just like any other Arduino), but if you want access to the onboard Ethernet from the Arduino processor, the connector won't work.
DeleteHowever, you can use Python and Connector/Python (from Oracle) to write applications on the bootable Linux side.
Hi Chuck,
ReplyDeleteI am new to MySQL and Arduino
Currently I am doing college project using your code.
In function "send_authentication_packet"
// client flags
buffer[size_send] = byte(0x85);
buffer[size_send+1] = byte(0xa6);
buffer[size_send+2] = byte(0x03);
buffer[size_send+3] = byte(0x00);
here what does 'byte(0xa6)' mean? (does it converts hex to unsigned character byte? if yes is there any function in C to do same hex to byte conversion if no can I write directly
// client flags
buffer[size_send] = 0x85;
buffer[size_send+1] = 0xa6;
buffer[size_send+2] = 0x03;
buffer[size_send+3] = 0x00;
into code
)
Yes, byte() stores an 8-bit unsigned number, from 0 to 255. In this case, I use hexadecimal because it is standard notation in the MySQL protocol.
DeleteYou can, but byte() is safer in case the value you're trying to save is larger than 255 (decimal). In other words, it prevents overflow.
https://www.arduino.cc/en/Reference/Byte
Thank you Chuck
DeleteI got one more question
In scramble_password, I am using functions from sha1.c (from http://oauth.googlecode.com/svn/code/c/liboauth/src/sha1.c) is it ok to use it?
since I am using similar logic provided by you for arduino board to my evalution-kit(uses rtos,sdk all in c). I am creating socket on eval-kit and connecting to remote MySQL server. I am using same algorithmic flow as provided by you in C++.
If this flow work on my eval-kit(will be written in c) will this code going to work for any board(platform independent).
As long as the sha1 functions return the correct scrambled password, you can use any sha1 library with MySQL.
DeleteI am not sure what you're trying to achieve, however I wish you success.
If you need specific help with the Connector, please feel free to contact me.
I might suggest you read the online MySQL internals document. It will answer all of your questions about how the connector works.
Hi Chuck,
ReplyDeleteI have many problems when connecting with the shield wifi. I connect to wireless router but not connect to the mysql server.
I unncoment 2 lines as you indicate in the manual, but I do not connect.
the code I use is that you include in the manual in the section baby steps Listing 2.
/**
* Example: Hello, MySQL!
*
* This code module demonstrates how to create a simple
* database-enabled sketch. With WiFi!
*/
#include "SPI.h"
#include "Ethernet.h"
#include "sha1.h"
#include "WiFi.h"
#include "mysql.h"
/* Setup for Ethernet Library */
byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
IPAddress server_addr(10, 0, 1, 10);
/* Setup for the Connector/Arduino */
Connector my_conn; // The Connector/Arduino reference
char user[] = "bob";
char password[] = "secret";
// WiFi card example
char ssid[] = "my_lonely_ssid";
char pass[] = "horse_no_name";
void setup() {
Serial.begin(115200);
while (!Serial);
delay(1000);
Serial.println("Looking for WiFi");
// WiFi section
int status = WiFi.begin(ssid, pass);
// if you're not connected, stop here:
if ( status != WL_CONNECTED) {
Serial.println("Couldn't get a wifi connection");
while(true);
}
// if you are connected, print out info about the connection:
else {
Serial.println("Connected to network");
IPAddress ip = WiFi.localIP();
Serial.print("My IP address is: ");
Serial.println(ip);
}
delay(1000);
Serial.println("Connecting...");
if (my_conn.mysql_connect(server_addr, 3306, user, password)){
Serial.println("Success!");
} else {
Serial.println("Connection failed.");
}
}
void loop() {
}
Thanks.
Hi.
DeleteFirst, there is a newer version of the connector that is much easier to use WRT WiFi. See https://github.com/ChuckBell/MySQL_Connector_Arduino.
Please read the manual that's on the site for how to install the new connector. Hint: it's available for download directly from the Arduino IDE!
Second, your sample sketch still has the junk SSID and password - perhaps that was intentional. Otherwise, unless there are permissions problems it should work.
But again, try the new connector and the new wifi example sketch.
If you still cannot connect, check the troubleshooting section in the manual as there are a number of things that can go wrong that are not related to the connector.
Finally, if you still have trouble, it is helpful to mention the Arduino board you are using, the WiFi shield (and version), and what version of MySQL you are using.
DeleteI'm testing with the new connector, but the problem is the same. I opened all ports on my firewall, on the router, but can not connect with the server.
Deletebut i try whith the ethernet shield and the new connector and i get connected successfully with the mysql server, so I think it's no permissions problem.
i tried it whith arduino uno R2 and mega 2560, the wifi shield is the original and the firmware version is 1.0.0.
I'm using MySQL WorkBench 6.2 CE
Thanks.
Ok, are you sure? I must point out that the sketch you posted will not work with the newer connector (version 1.1.1a). In fact, it is a sample sketch from version 1.0.4.
DeleteIs it possible you have both installed and your sketch for the Ethernet test uses 1.1.1a but the WiFi sketch uses 1.0.4? I suspect that may be the case.
Also, in the new connector (1.1.1a), you do not have to modify any files or uncomment out anything to use WiFi.
https://github.com/ChuckBell/MySQL_Connector_Arduino/blob/master/extras/MySQL_Connector_Arduino_Reference_Manual.pdf
Finally - assuming you do have 1.1.1a installed - if Ethernet is working but WiFi not, it is likely a problem with the WiFi network. The output from the Serial Monitor will show whether the WiFi shield connected to your WiFi network. Keep in mind if the WiFi network is a different subnet, you could have networking (and possibly permissions) issues that can cause problems. You can also check your WiFi shield by running one of the WiFi example sketches (the one that connects to google for example). If that sketch fails, it's your WiFi shield - which are prone to failing (sadly - guess how I know).
If you are still having problems, let's continue this conversation via email. You can send me your sketch and the output of the Serial Monitor to:
d r c h a r l e s at g m a i l dot c o m
I've sent you an email
DeleteSorry, I haven't received your email.
Deletedrcharles? gmail.com?
Deletei send you an email to d r c h a r l e s b e l l
Deletei´s ok?
Hello again Dr.
Deletefinally take the decision to upgrade the wifi shield and the problem was solved, thanks for your help and attention.
Ibai.
This comment has been removed by the author.
ReplyDeleteHi Chuck. Thanks for your great job!
ReplyDeleteI´m using:
- Arduino Uno with standard Ethernet shield.
- Mariadb 5.5.5-10.1.13
- Running example Complex select
- My select is a “CALL procedure();”
It hangs on the second loop. If i run the same sketch with a big big SELECT query runs ok.
Using the same Complex select sketch with CALL procedure and using show_results() works fine.
Seems It can’t get into the for of colum_names after the first loop.
Any idea? Will continue doing tests.
Thanks.
———
Serial output:
Connecting...
Connected to server version 5.5.5-10.1.13-MariaDB
> Running SELECT with dynamically supplied parameter
RESULTADO
1
> Running SELECT with dynamically supplied parameter
> Running SELECT with dynamically supplied parameter
Bad mojo. EOF found reading column header.
ERROR: You must read the columns first!
Hello. I would have to see your entire sketch. Can you send it to me?
Deleted r c h a r l e s at g m a i l dot c o m
I sent you a mail! Thanks
DeleteSorry. Wrong email. try this:
Deleted r c h a r l e s b e l l at g m a i l dot c o m
Ok. Done!
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi Chuck! Can I pass a char parameter to my SQL command? Like this: SELECT mytable.id FROM mydb WHERE myCol = %s;
ReplyDeleteIn this case, I'll use char myChar[] for parameter to my SQL command.
Sorry for the delay. I had some trouble with my Google account. Fixed now.
DeleteYes, you can. See the example sketches. Also, please start using the latest version of the connector, 1.1.1a available for download via the Library Manager in Arduino IDE. Just search for MySQL.
Yes, of course you can. Look at "Complex select" example..
ReplyDeletePosts to this blog are now closed. Please see my blog of the latest release of the connector at http://drcharlesbell.blogspot.com/2016/01/new-release-mysql-connectorarduino-110a.html.
ReplyDeleteYou can download the connector via the Arduino IDE Library Manager! Just search for "MySQL".
Hello Chuck,
ReplyDeleteis it possible to sent an array of float to mysql?
In former time i do this with a php script, now i wanna test it with direct acces to mysq.
I've got an array of 16 temperatures code like this:
float TempSensoren[16];
...
char INSERT_DATA[] = "INSERT INTO mysql.temperaturen (temp1,temp2) VALUES (TempSensoren[0],TempSensoren[1])";
....
if (my_conn.mysql_connect(server, 3306, user, passwort)) // Verbindung zum mysql-Server aufbauen
{
Serial.println("Verbunden, Sende Daten...");
my_conn.cmd_query(INSERT_DATA);
Serial.println("Daten an MYSQL.temperaturen uebertragen.");
...
but this doesn't work.
I think the problem is the dynamic variable(array) in the insert_DATA[] perhaps ther will be an other way to push an array at once to the mysql..
You must use dtostrf(). See the example sketches. For an array, you will need to loop through the values.
DeletePlease use the latest version of the connector as described above.