Wednesday, July 29, 2015

New! MySQL Connector/Arduino release-1.0.4 GA and Documentation

After several iterations and a long period of community evaluation, I am happy to report I've released the newest, stable release of the MySQL Connector/Arduino. This library is designed to allow you to connect your Arduino via an Ethernet or WiFi shield to a MySQL database server running on your network (or the Internet!).

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!

46 comments:

  1. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. I've fixed the missing sha1 folder. Please retry your download.

      Delete
  2. Why 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...

    ReplyDelete
  3. Can CC3000 WiFi shield uses this MySQL Connector library?

    ReplyDelete
    Replies
    1. The connector does not work with the CC3000 modules, only the Arduino compatible Ethernet modules.

      Delete
  4. Hello Dr.Bell,

    I have this error ;
    http://i.imgur.com/h4MnHQX.png

    Thanks..

    ReplyDelete
    Replies
    1. You must enable SELECT by uncommenting WITH_SELECT in mysql.h. Take a look at the reference manual. ;)

      https://launchpad.net/mysql-arduino/release-1.0.4-ga/release-1.0.4-ga/+download/MySQL_Connector_Arduino_Reference_Manual-1.0.4ga.pdf

      Delete
    2. I fixed like this :
      #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

      Delete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Sir,
    I 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.

    ReplyDelete
    Replies
    1. What version of MySQL are you using? It is MySQL or something else? ;)

      Are 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.

      Delete
    2. 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.
      If 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.

      Delete
    3. 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.

      Try 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.

      Delete
  7. This comment has been removed by the author.

    ReplyDelete
  8. Hi Chuck,
    1) 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() {
    }

    ReplyDelete
  9. 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.

    There are a number of common mistakes/errors that are explained in the reference manual available on the launchpad site. Read that first. ;)

    ReplyDelete
  10. Dear Chuck
    Can the library be used with Arduino Yun.
    I tried but failed ...
    Thanks,
    Jodi

    ReplyDelete
    Replies
    1. 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.

      However, you can use Python and Connector/Python (from Oracle) to write applications on the bootable Linux side.

      Delete
  11. Hi Chuck,
    I 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
    )

    ReplyDelete
    Replies
    1. 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.

      You 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

      Delete
    2. Thank you Chuck

      I 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).

      Delete
    3. As long as the sha1 functions return the correct scrambled password, you can use any sha1 library with MySQL.

      I 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.

      Delete
  12. Hi Chuck,
    I 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.

    ReplyDelete
    Replies
    1. Hi.

      First, 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.

      Delete
    2. 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.

      Delete
    3. I'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.
      but 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.

      Delete
    4. 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.

      Is 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

      Delete
    5. Sorry, I haven't received your email.

      Delete
    6. i send you an email to d r c h a r l e s b e l l
      i´s ok?

      Delete
    7. Hello again Dr.
      finally take the decision to upgrade the wifi shield and the problem was solved, thanks for your help and attention.
      Ibai.

      Delete
  13. This comment has been removed by the author.

    ReplyDelete
  14. Hi Chuck. Thanks for your great job!

    I´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!

    ReplyDelete
    Replies
    1. Hello. I would have to see your entire sketch. Can you send it to me?

      d r c h a r l e s at g m a i l dot c o m

      Delete
    2. I sent you a mail! Thanks

      Delete
    3. Sorry. Wrong email. try this:

      d r c h a r l e s b e l l at g m a i l dot c o m

      Delete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Hi Chuck! Can I pass a char parameter to my SQL command? Like this: SELECT mytable.id FROM mydb WHERE myCol = %s;
    In this case, I'll use char myChar[] for parameter to my SQL command.

    ReplyDelete
    Replies
    1. Sorry for the delay. I had some trouble with my Google account. Fixed now.

      Yes, 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.

      Delete
  17. Yes, of course you can. Look at "Complex select" example..

    ReplyDelete
  18. Posts 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.

    You can download the connector via the Arduino IDE Library Manager! Just search for "MySQL".

    ReplyDelete
  19. Hello Chuck,
    is 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..

    ReplyDelete
    Replies
    1. You must use dtostrf(). See the example sketches. For an array, you will need to loop through the values.

      Please use the latest version of the connector as described above.

      Delete