Saturday, April 6, 2013

Introducing MySQL Connector/Arduino

Have you ever wanted to use a local database server to store data from your Arduino projects? Would you like to be able to send queries directly to a MySQL database from your Arduino sketch? Well, now you can!

The MySQL Connector/Arduino is a new technology made for the Arduino permitting you to connect your Arduino project to a MySQL server via an Ethernet shield without using an intermediate computer or a web-based service. 

Having direct access to a database server means you can store data acquired from your project as well as check values stored in tables on the server and keep the network local to your facility including having a network that isn't connected to the internet or any other network.

Example Code


The Connector/Arduino is an Arduino library that encapsulates everything you need to communicate with a MySQL server. It's also very easy to use. The following shows a simple sketch to connect to a MySQL server and insert a row of data at startup.

/**
* Example: Hello, MySQL!
*
* This code module demonstrates how to create a simple 
* database-enabled sketch.
*/
#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, 0, 1, 23);

/* Setup for the Connector/Arduino */
Connector my_conn; // The Connector/Arduino reference

char user[] = "root";
char password[] = "secret";
char INSERT_SQL[] = 
 "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)";

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(500);
     /* Write Hello, World to MySQL table test_arduino.hello */
     my_conn.cmd_query(INSERT_SQL);
     Serial.println("Query Success!");
  }
  else
    Serial.println("Connection failed.");
}

void loop() {
}


As you can see, the library adds very few methods for communicating with a MySQL server.

What Can It Do?


The Connector/Arduino library allows you to issue queries to the database server in much the same manner as you would through the MySQL client application. You can insert, delete, and update data, call functions, create objects, etc. Issuing SELECT queries are also possible but they incur a bit more thought concerning memory management.

When issuing SELECT queries, the query strings must fit into memory and the largest row of a result set must also fit in memory. This is because result sets are read one row at a time and the class uses an internal buffer for building data packets to send to the server. The connector reads one packet-at-a-time and since the Arduino has a limited data size, the combined length of all fields must be less than available memory. It is suggested long strings be stored in program memory using PROGMEM (see cmd_query_P in the mysql.cpp file http://bazaar.launchpad.net/~chuck-bell/mysql-arduino/trunk/view/head:/mysql.cpp).

Most projects are those that need to store data and in that case the only memory requirements are those for the SQL statements. However, with careful planning, you can preserve memory by using parametrized queries.

Limitations


As you can imagine, a library that communicates with a MySQL server is larger than most libraries. Indeed, it consumes about 16-20k of program space. Fortunately, the latest Arduino boards have enough memory that only the most complex projects need be concerned. And in that case you can move to a larger Arduino board like the Arduino Mega.

Aside from memory, the following are some limitations you may want to consider when planning your sketches.

  • Query strings (the SQL statements) must fit into memory.
  • Result sets are read one row-at-a-time and one field-at-a-time.
  • The combined length of a row in a result set must fit into memory.
  • Server error responses are processed immediately with the error code and text written via Serial.print.

How To Get MySQL Connector/Arduino


You can download Connector/Arduino from LaunchPad (https://launchpad.net/mysql-arduino). The library is open source, licensed as GPLv2, and owned by Oracle Corporation. Thus, any modifications to the library that you intend to share must meet the GPLv2 license.

By popular demand, I have made available a zip file that contains the mysql_connector and patched sha1 code. Go to https://launchpad.net/mysql-arduino and download the zip file, extract it, and copy the mysql_connector and sha1 folders to your Arduino/Libraries folder.

For More Information


If you would like to get started using the library, please feel free to download it and checkout the example and the text files for specifics of installation and use.

However, if you'd like a full tutorial and learn more about using the Connector/Arduino in your project and to learn more about sensor networks, look for my book entitled Beginning Sensor Networks with Arduino and Raspberry Pi (Apress) due June 2013.


Note


The Connector/Arduino library was created to demonstrate the versatility of the MySQL client protocol and to provide a unique capability for the Arduino platform. While offered under the GPLv2 license, the library is not supported by Oracle.

396 comments:

  1. Fantastic! - I can't wait to try it.

    Any idea when the code will be posted? It isn't up on the link yet.

    ReplyDelete
    Replies
    1. http://bazaar.launchpad.net/~chuck-bell/mysql-arduino/trunk/files

      Delete
  2. I am a reviewer for Apress and O'Reilly would love to preview book-- I am also looking at an Open Source Project to collect Health and A Fitness Data from ARduino & Linux Box In The CLoud and on Local DB could you contact me at
    laefsky at comcast dot net?

    Thanks
    --Ira Laefsky

    ReplyDelete
  3. in sha1.h virtual void write(uint8_t);
    error: conflicting return type specified for 'virtual void Sha1Class::write(uint8_t)'
    do you know how to fix it?

    ReplyDelete
    Replies
    1. Apply the diff included with the source code.

      Delete
  4. C:\arduino-1.0.3\libraries\Sha/sha1.h:26: error: conflicting return type specified for 'virtual void Sha1Class::write(uint8_t)'
    C:\arduino-1.0.3\hardware\arduino\cores\arduino/Print.h:48: error: overriding 'virtual size_t Print::write(uint8_t)'

    ReplyDelete
  5. Hi, I spend some time playing around with this to see if I could use it to stick readings from a pir sensor into a database.

    I moved the db insertion bit into it's own function so I can reuse it any time the pir sensor reports an event. But it only works the first time, after that it says connection failed.

    Reading through mysql.ccp it also seems like a client connection is opened but never closed, shouldn't it close after each transaction?

    I pastebinned my code here: http://pastebin.com/D6NeF2JX

    Can you help me understand why the addMotionEvent function works the first time but reports failed connections afterwards? Does it trip up because I already have a connection open?

    ReplyDelete
    Replies
    1. The call to my_conn.mysql_connect() must be placed in the setup() method. This is because we do that only once. Move your call to addMotionEvent() to loop().

      I sent PM with more details.

      Delete
  6. Hi! I can't understand how i can connect to a specific DB, there is no code where i specify the name of the DB.

    Here in the query:

    "INSERT INTO test_arduino.hello VALUES ('Hello, MySQL!', NULL)"

    i specify only the name of the Table, that is "test_arduino", and the field, that is "hello".

    I want to know also what the parameter "3306" in that function stand for:

    my_conn.mysql_connect(server_addr, 3306, user, password)

    Thanks for the help.

    ReplyDelete
    Replies
    1. The syntax is INSERT INTO db.table (columns) VALUES (values). For example, if your database is named 'db1' and your table named 'test_arduino', the syntax is:

      INSERT INTO db1.test_arduino VALUES('Hello, MySQL!', NULL)

      Note that the columns specification is optional if you have listed values for all columns in the table.

      The 3306 is the port that the server is listening on.

      Delete
    2. Thanks!
      I have another question for you:

      I have a byte[] type variable that i want to send to the DB using Arduino (It is a code i obtain using a RFID Reader Shield), so i want to convert Byte to String... I tried to use the StringGetBytes method, but it doesn't work.
      Do you have any idea to resolve it? If you need it i can send you the code. That's a very important project i'm doing at School.

      Delete
  7. Hey Chuck,

    This library had been very helpful for my school project. I've got a few questions about it though:

    -I was looking through the library and I noticed that when you check the OK packet you use a variable called packet_len, but I could not find where that variable was declared. Could you provide any insight to this? This is important to us because we are getting stuck in the memcpy call within the parse_ok_packet function contained in this library.

    -We're wondering if you need to search for a site's unique port number instead of using the default port 3306? If so, how do we do this? (We're using http://www.freesqldatabase.com) Sorry, we're new to databases...

    ReplyDelete
  8. Hi Chuck,
    I'm trying to use your code but I always receive the same error from the Serial.print function ("Error 255=." and then "Connection failed"). Do you know why it could be?
    Thanks,

    ReplyDelete
    Replies
    1. Solved! In case anyone get the same error, the main problem was I didn't access to mysql database. The solution is GRANT ALL PRIVILEGES ON *.* TO 'USER'@''DEVICES's IP' IDENTIFIED BY 'PASSWORD'; on the cmd.
      Good luck,

      Delete
  9. Hi Chuck,

    I think you're a life saver...IF i can get this to work. LOL. I've finally figured out how to apply the diff files and got rid of a bunch of errors but now with the example code when I try to compile I get:

    mysql.cpp.o: In function `setup':
    C:\Program Files (x86)\Arduino/mysql.ino:29: undefined reference to `Connector::mysql_connect(IPAddress, int, char*, char*)'

    What did I do wrong?

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Same is the problem with me, the compiler says "Connector" does not name a type..

    please reply soon.

    ReplyDelete
    Replies
    1. You must place the connector Arduino folder in your Arduino libraries folder.

      Delete
  12. Use patch.

    If you do not have patch or cannot install it, you can apply the diff manually. Open the diff file and find those lines marked with a '-' and replace them with the lines marked '+' for each file listed. Don't forget to remove the '+'s!

    ReplyDelete
    Replies
    1. can this be done in windows? I'm sorry i am new to this. Can you give me some instructions on how to do it? thanks

      Delete
  13. Yes, it can. Take a read through this: http://stackoverflow.com/questions/517257/how-do-i-apply-a-diff-patch-on-windows

    ReplyDelete
    Replies
    1. i have already patch the diff file into the origin file of sha1.h but when i run it again it seems the same problem like before :
      /usr/share/arduino-1.0.5/libraries/Sha/sha1.cpp:78: error: prototype for ‘void Sha1Class::write(uint8_t)’ does not match any in class ‘Sha1Class’
      /usr/share/arduino-1.0.5/hardware/arduino/cores/arduino/Print.h:49: error: candidates are: size_t Print::write(const char*)
      /usr/share/arduino-1.0.5/hardware/arduino/cores/arduino/Print.h:53: error: virtual size_t Print::write(const uint8_t*, size_t)
      /usr/share/arduino-1.0.5/libraries/Sha/sha1.h:27: error: virtual size_t Sha1Class::write(uint8_t*, int)
      /usr/share/arduino-1.0.5/libraries/Sha/sha1.h:26: error: virtual size_t Sha1Class::write(uint8_t)


      i use linux ubuntu...and i have already do all the comment answer from you...please help me..to fix it..maybe you can give the right code to my email : rolins020598@gmail.com

      Delete
    2. I think the diff didn't apply correctly. Files sent.

      Delete
    3. thanks you so much for the files..yes, i think i did a wrong apply for the diff files...thanks again Mr. Chuck.it works now...

      Delete
  14. Nice tutorial keep on writing
    jobs in burdwan

    ReplyDelete
  15. i cant find the library where is the library please for download ?

    ReplyDelete
  16. Go to the LP site and click on "code". Or follow this link:

    http://bazaar.launchpad.net/~chuck-bell/mysql-arduino/trunk/files

    ReplyDelete
  17. I cannot run the code. I just paste your code and try to compile it, but when i do that, it's returning the following errors:

    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:36: error: stray '\342' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:36: error: stray '\206' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:36: error: stray '\220' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:264: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:265: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:267: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:268: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:269: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:271: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:272: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:273: error: stray '#' in program
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:364: error: stray '#' in program
    In file included from sketch_jun29a.ino:18:
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:374:10: error: too many decimal points in number
    In file included from sketch_jun29a.ino:17:
    C:\Program Files (x86)\Arduino\libraries\Connector/sha1.h:26: error: conflicting return type specified for 'virtual void Sha1Class::write(uint8_t)'
    C:\Program Files (x86)\Arduino\hardware\arduino\cores\arduino/Print.h:48: error: overriding 'virtual size_t Print::write(uint8_t)'
    In file included from sketch_jun29a.ino:18:
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:1: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:12: error: 'var' does not name a type
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:13: error: 'var' does not name a type
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:14: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:267: error: expected constructor, destructor, or type conversion before '.' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:267: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:268: error: expected constructor, destructor, or type conversion before '.' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:268: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:269: error: expected constructor, destructor, or type conversion before '.' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:269: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:282: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:282: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:288: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:288: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:295: error: expected unqualified-id before '<' token
    C:\Program Files (x86)\Arduino\libraries\Connector/mysql.h:295: error: expected unqualified-id before '<' token

    I'm newbie in Arduino, and i just want to put some data from 3 sensors into a database.

    Please help me:(

    ReplyDelete
    Replies
    1. You do not CnP from the code page in LP! You can right-click on each file and choose "save link as..." (or equivalent) and save them to a folder in your Arduino/Libraries folder.

      Delete
  18. I did just that but i'm getting the same errors :/

    ReplyDelete
    Replies
    1. Ok, so install bazaar (http://wiki.bazaar.canonical.com/Download) and get the code that way:

      bzr branch lp:~chuck-bell/mysql-arduino/trunk

      If that doesn't work, send me your email address and I'll send you the code. chuck.bell@oracle.com

      Delete
    2. By the way, i'm using Windows. I don't know if it makes a difference.

      Delete
  19. me podrias enviar la libreria completa sin los diff?

    ReplyDelete
  20. Potentially useful Arduino library. If I could make a couple of constructive comments with a view to making the connector more widely available, I would recommend two things. First, downloading the files from Launchpad I believe is more difficult than it needs to be. Packaging the files in a standard Arduino folder/zip would be great and providing a patched version of sha1.cpp would be helpful. I would love to implement the connector in a variety of projects and I know many others in the Arduino community feel the same, we just need to lower the barriers to adoption just a little bit.

    Cheers and thank you for your work on this useful project.

    ReplyDelete
  21. can you send me library full without the diff?

    ReplyDelete
  22. When using the mysql_connect() method, what should our username look like if it is not a server on the LAN?
    example:

    IPAddress server_addr(00,11,222,33); //Example Ip
    char user[] = "MyArduino";
    char password[] = "Pass1234";
    my_conn.mysql_connect(server_addr, 3306, user, password)

    Should my username look like that? or does it need to be "MyArduino@*router ip:port*?

    I've tried it both ways and have had zero success.
    My permissions are fine, because I've been able to connect via php script before.

    Thanks so much, this is such an awesome thing to be able to do. Nice Work. The community thanks you

    I

    ReplyDelete
    Replies
    1. The user and password should be the same as if you used the mysql client:

      mysql -uroot -psecret ...

      i.e. "root" = user name , "secret" = password

      If these are correct and you still cannot connect, be sure the user has permission to connect to your host. Check the online reference manual for how to do this. Hint: user and host (the IP address of your Arduino) must appear in the mysql.user table -or- you can use the '%' wildcard for the host.

      One more hint:

      GRANT ALL ON some_db.* TO 'MyArduino'@'%' IDENTIFIED BY 'Pass1234';

      Delete
    2. I was able to access a database from freesqldatabase.com but still not from my godaddy sql database. Which I find odd. But, I'm probably better off with the free one anyway. Thanks so much for your help

      Delete
  23. Congratulations on your program.
    Can you provide me a patched version of sha1.cpp.

    Best regards,

    JJ Harry

    ReplyDelete
  24. Go to https://launchpad.net/mysql-arduino and download the code there. You will find a zip file with both the mysql_connector and sha1 libraries. The sha1 files have been patched. Copy the mysql_connector and sha1 folder to your Arduino/Libraries folder and restart your Arduino IDE.

    ReplyDelete
  25. Hello,
    All works perfectly.
    I thank you for your quick response.

    Best regards,

    JJ Harry

    ReplyDelete
  26. Works great here too! I am assuming that I can get the Arduino to pull data from mySQL too? Any example code?

    Cheers

    Andrew

    ReplyDelete
    Replies
    1. Look at the method show_results() in mysql.cpp. Use that as a base for building your own method to retrieve data. From a high-level it is the same: do a query then process (read) the results.

      The biggest limitation is making sure a row fits in memory.

      Delete
    2. Thanks Chuck. Had a look but way out of my knowledge base. :( Any pointers would be great!

      Delete
  27. Excellent work direct integration is a wonderful thing. I like many Arduino enthusiasts use the EtherCard.h library as there are various low cost connectors that are readily available. Do you think it is possible to get this system to run using this library instead of the Ethernet.h that it currently uses?

    Cheers,

    Logan

    ReplyDelete
    Replies
    1. Thanks! Sure, take a look at the hooks for the Ethernet and client library. You should be able to simply change these accordingly and off you go! See:

      - headers in mysql.h
      - Ethernet.* calls
      - client.* calls

      Delete
    2. hi,
      nice work, can you upload a version of the mysql.h for the ethercard.h lib?

      greats

      Delete
    3. Sorry, there is no version that supports the Ethercard library. However, as I've mentioned previously, it should not be difficult to modify the connector to use the Ethercard library. Look for the Ethernet class specifics in mysql.h and mysql.cpp and change the code accordingly.

      Delete
  28. I'm getting this :\ Any Ideas?

    mysql_connector\mysql.cpp.o: In function `Connector::get_lcb_len(int)':
    C:\Program Files (x86)\Arduino\libraries\mysql_connector/mysql.cpp:718: multiple definition of `Connector::get_lcb_len(int)'
    mysql.cpp.o:C:\Users\pain\AppData\Local\Temp\build119164676294339302.tmp/mysql.cpp:718: first defined here
    mysql_connector\mysql.cpp.o: In function `Connector::read_int(int, int)':
    C:\Program Files (x86)\Arduino\libraries\mysql_connector/mysql.cpp:753: multiple definition of `Connector::read_int(int, int)'
    mysql.cpp.o:C:\Users\pain\AppData\Local\Temp\build119164676294339302.tmp/mysql.cpp:753: first defined here
    mysql_connector\mysql.cpp.o: In function `Connector::parse_eof_packet(eof_packet*)':
    C:\Program Files (x86)\Arduino\libraries\mysql_connector/mysql.cpp:627: multiple definition of `Connector::parse_eof_packet(eof_packet*)'
    mysql.cpp.o:C:\Users\pain\AppData\Local\Temp\build119164676294339302.tmp/mysql.cpp:627: first defined here
    mysql_connector\mysql.cpp.o: In function `Connector::parse_error_packet()':
    C:\Program Files (x86)\Arduino\libraries\mysql_connector/mysql.cpp:651: multiple definition of `Connector::parse_error_packet()'
    mysql.cpp.o:C:\Users\pain\AppData\Local\Temp\build119164676294339302.tmp/mysql.cpp:651: first defined here

    ReplyDelete
    Replies
    1. You must ensure the mysql_connector and sha folders are in your Arduino/Libraries folder, your Arduino IDE is restarted after the copy (if running), and you create a new sketch to include the connector header as per the example. The example will not compile from the libraries folder.

      Delete
  29. Hi Chuck,

    How can i declare variables into INSERT query? Example:

    temp = analogRead(pinsensor);
    door = "open";

    char INSERT_SQL[] = "INSERT INTO datacenter.tb_teste (data, temp, door) VALUES (now(), temp, door)";

    don't work for me.

    Thank's

    ReplyDelete
    Replies
    1. I think you will need to construct a complete string, concatenating the variable's values in. Something like: WholeString = "Beginning of the string" + stringval(door) + ", '" + door + "The last part of the string" to produce "insert into db.table (data, temp, door) values(now(), 89.2, 'open')"

      google search sprintf, seems to be pretty valuable function for converting numbers into strings.

      Delete
    2. Well done. Thanks for answering! Couldn't have responded better myself. :)

      Delete
    3. Thanks for the tips, is not working very well for me, but I'll keep trying. This is the key to all my project and I'm stuck.

      Delete
    4. error: call of overloaded 'String(float&)' is ambiguous
      C:\Program Files (x86)\Arduino\hardware\arduino\cores\arduino/WString.h:70: note: candidates are: String::String(long unsigned int, unsigned char)
      C:\Program Files (x86)\Arduino\hardware\arduino\cores\arduino/WString.h:69: note: String::String(long int, unsigned char)
      C:\Program Files (x86)\Arduino\hardware\arduino\cores\arduino/WString.h:68: note: String::String(unsigned int, unsigned char)
      C:\Program Files (x86)\Arduino\hardware\arduino\cores\arduino/WString.h:67: note: String::String(int, unsigned char)
      C:\Program Files (x86)\Arduino\hardware\arduino\cores\arduino/WString.h:66: note: String::String(unsigned char, unsigned char)
      C:\Program Files (x86)\Arduino\hardware\arduino\cores\arduino/WString.h:65: note: String::String(char)

      My variable is float so, i can't convert.

      Delete
    5. Sure you can. Hint: see/google dtostrf(). http://forum.arduino.cc/index.php?topic=103935.0

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

      Delete
    7. Creates a string with float variables converted and inserted into the temp char array.

      #include stdlib.h // blog editor won't allow correct syntax here

      ...

      void displayTemperature(DeviceAddress deviceAddress) {
      float tempC = sensors.getTempC(deviceAddress);
      float tempF = sensors.getTempF(deviceAddress);
      char tmp[9];
      if(tempC == -127.00) {
      wscrstr = "Error Getting Temp";
      scrolldisp(wscrstr);}
      else {
      tmp[0] = 'C';
      tmp[1] = ':';
      tmp[2] = ' ';
      dtostrf(tempC,3,1, &tmp[3]); // C: 53.5
      wscrstr = tmp; //C: 53.5
      tmp[0] = 'F';
      tmp[1] = ':';
      tmp[2] = ' ';
      dtostrf(tempF,3,1, &tmp[3]); // F: 128.3
      wscrstr += " ";
      wscrstr += tmp;
      wscrstr += " "; // C: 58.5 F: 128.3
      scrolldisp(wscrstr); } // function to display to LCD
      return; }

      Delete
  30. Sir,

    THANK YOU!!!, THANK YOU!!!, THANK YOU!!!

    It's only a hobby project, but this IS the piece I needed!!!

    ReplyDelete
  31. If I may say so, you can use the following method:

    String tempC ;
    String tempF ;

    String buf;
    char charBuf[128];

    tempC = String(myDHT22.getTemperatureC(),1);
    tempF = String(myDHT22.getHumidity(),1);

    buf = "INSERT INTO datacenter.tb_teste VALUES (NULL, " + tempC + ", " + tempF + ")";

    buf.toCharArray(charBuf, 128);

    my_conn.cmd_query(charBuf);

    Best regards,
    JJ Harry

    ReplyDelete
  32. Can anyone shed some light on how I can use the SELECT function? I have followed Chuck's example and can see the data read from mySQL on the serial monitor but can't see what I need to do to get the Arduino to use the data. Any help would be appreciated.

    ReplyDelete
    Replies
    1. You must use the get_field() method and get_string() to get each column value. You can then convert them to whatever data type you need. See the details of the get_results() example. Instead of sending the values to Serial.print(), you convert them to the data type you need.

      Delete
    2. Hello Chuck,

      I can not retrieve database records in a table!
      If you could please give me an example of code.
      it would be nice.

      Best regards,
      JJ Harry

      Delete
    3. Have you looked at the show_results() method? That is an example of retrieving data.

      You can do this:

      my_conn.cmd_query("SHOW DATABASES");
      my_conn.show_results();

      Now, if you want to retrieve specific values, look at how the show_results() method is implemented. It will show you examples of how to retrieve rows and fields. Note that all fields come to us as strings and you must convert them to the data type you need. See other posts here for more information.

      Delete
    4. Hi Chuck,

      Do you cover how to do this in your book "Beginning Sensor Networks with Arduino and Raspberry Pi"? I am somewhat lost trying to work out how to implement this. :)

      Delete
    5. No, that book is all about collecting data and doing way cool things with Arduino and Raspberry Pi.

      If you can describe what you want to do (or maybe a small facsimile), I will create an example for you. Maybe include the SELECT you want to run and a pseudo example of how you want to use the values returned?

      I want to provide something close to what you want to do. ;)

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

      Delete
    7. Hi Chuck.

      Got the book anyway. It's been a good read so far!

      Okay here is what I am trying to do. Basically I want to read a value from the database and get the Arduino to use that value to set a pin high or low.

      Here is an example of what I am thinking.

      "SELECT STATUS FROM tronix.alist WHERE Arduino='10.1.1.200'"

      The status field would store the value (either 1 or 0).

      Does that make sense?

      Delete
    8. Hi. I am considering adding some easier to use methods to the library for doing SELECTs. I wrote the connector originally thinking 99.999% of use would be inserting data.

      That being said (and until I get some bandwidth to code the changes), you can look at the show_results() method for how to retrieve results.

      In essence, you need to do it in this order:

      1) issue the SELECT query
      2) read all of the column headers returned
      one field and a time
      3) read a row of the result
      one field at a time.

      For your example, this means reading one column header then one row. Look through the show_results() method and you will see these steps.

      For the mods I plan, I want to make it like an iterator when you call something like get_row() which returns an array or EOF when there are no more rows. At least, that's what I am thinking.

      I'll post my changes once I get some time to work on it.

      Delete
  33. Hi Chuck, thanks a lot for this great library. I have an issue where I am not able to use it though. This is the code I am using from ur example in this post.


    #include
    #include
    #include
    #include
    #include "mysql.h"

    /* Setup for Ethernet Library */
    byte mac_addr[] = {0x90,0xA2,0xDA,0x0E,0x99,0x06};
    IPAddress server_addr(127,0,0,1);

    /* Setup for the Connector/Arduino */
    Connector my_conn; // The Connector/Arduino reference

    char user[] = "user";
    char password[] = "secret";
    char INSERT_SQL[] =
    "INSERT INTO arduino_test.tb_arduino_test (text,name) VALUES ('Hello, MySQL!', 'name')";

    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(500);
    /* Write Hello, World to MySQL table test_arduino.hello */
    my_conn.cmd_query(INSERT_SQL);
    Serial.println("Query Success!");
    }
    else
    Serial.println("Connection failed.");
    }

    void loop() {
    }


    I have made sure that the arduino has full permission to the database and that all libraries are in place. But whenever I run the program it tells me that the "Connection Failed". Do you know anything that would be causing this. Also I tried the Ethernet.begin function with the ip of the arduino along with the mac to no avail also. Thanks for your help. Any replies would be appreciated.

    ReplyDelete
    Replies
    1. Use the actual IP of your MySQL server (not the loopback), the correct user name and password.

      You can test it as follows:

      suppose IP = 192.168.1.3
      suppose user = cell
      suppose passwd = gidget

      mysql -ucell -pgidget -h 192.168.1.3 --port=3306

      If you cannot connect via the Arduino but can with the above command, you likely have an issue with your Ethernet shield. In which case you should try other examples to see if all is well there.

      Delete
    2. My MySQL server is on localhost as I am still learning about databases. To be exact, Im using the wampserver bundle. So am I not supposed to use the ip address for the localhost?

      Delete
    3. Yes, you use the IP address for your localhost - just don't use the loopback address. Use the IP address assigned by DHCP (or what you entered if configured for a static address).

      For Windows, you can find it by running ipconfig. For POSIX, you can run ifconfig.

      Delete
    4. YAY!! Thanks a lot Chuck, I used the local ip address i found through ipconfig and sure enough, it worked. Your a lifesaver Chuck. Thanks for the fast replies!! :)

      Delete
  34. If you were to use a query with a variable within the arduino code such as temperature or lightlevel, how would the syntax look?

    This doesn't seem to work.

    "INSERT INTO arduino_test.light_logger (value,time) VALUES (lightlevel,now())";

    where lightlevel is the variable

    Thanks

    ReplyDelete
    Replies
    1. That is correct, it will not. You are attempting to call the now() function and retrieve the value of a variable from within the string. To build the string, you must concatenate the static parts with the dynamic parts. I give a hint below.

      sprintf(buf, "INSERT INTO test.temp VALUES (NULL, %hi.%01hi, %i.%01i)",
      myDHT22.getTemperatureCInt()/10,
      abs(myDHT22.getTemperatureCInt()%10),
      myDHT22.getHumidityInt()/10,
      myDHT22.getHumidityInt()%10);
      my_conn.cmd_query(buf);

      You can find the code for this example in the dht22_sensor_node.cpp file in the example folder under the MySQL Connector/Arduino library.

      You can also see other replies above on this topic.

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

    ReplyDelete
  36. Amazing library and work!

    I was wondering if it would be possible for me to use this library with a GSM shield.

    if i change in the .h file:

    #include "Ethernet.h" to #include "GSM.h".
    EthernetClient client; to GSMClient client;.

    I would test it myself but the GSM shield is pretty expensive and I would rather ask an expert before making such a purchase.

    Thanks!

    ReplyDelete
  37. I am a firm believer in never saying 'never'. So I will say initially, "Yes, you can!" But there is a catch.

    While the basic methods provided in the Connector/Arduino can be modified to be used with a typical GSM shield, there is a lot of work to do to mate the two together. More specifically unless you can find someone who has written a TCP/IP layer for the GSM shield, you may have to write all of the code to "speak" TCP/IP through the modem yourself.

    Take a look at the examples on this page: http://www.cooking-hacks.com/index.php/documentation/tutorials/arduino-3g-gprs-gsm-gps#step14 (scroll down a bit). Notice the code to talk to the GSM modem.

    To change Connector/Arduino to use a GSM shield, you must replace the calls in the Connector/Arduino code that send/receive data to/from the Ethernet shield with your own methods to do the same with the GSM shield.

    So once again, I think it is possible but it will be a lot of work in the end. That being said, if you get it to work please share! :)

    ReplyDelete
    Replies
    1. Hi Chuck!

      I think that I will stick with my ethernet shield for now and perhaps get to work on that for the next stage of my project!

      I did notice one thing however. When the internet goes out temporarily and I try to send a query, the entire arduino just hangs. I have narrowed it down to the parse_ok_packet method specifically the packet_len parameter of the memcpy function. I tried to take out the entire memcpy function which allowed for it to work even after the internet cable was pulled out and plugged in again (given that i did not send more than 4 queries).

      Any quick fixes up your sleeve there? I will try to take a closer look at it tomorrow however it is not likely that my skills will suffice when it comes to accomplishing this.

      Delete
    2. No, sorry. The code was written without much in the way of error handling for network failures.

      You could try to disconnect then reconnect each time you want to run a query and add a retry loop. That might catch most network issues but it won't help if the connection is dropped just before or during the query.

      Delete
    3. Yeah that is actually exactly what i did! It makes the code run much slower but much more reliable when it comes to servers disconnecting or in the case of bad internet connections.

      That is actually important for my project since it will be installed in many different cities and it is not logical to send someone out to restart the system when the internet / server goes down for a few minutes.

      If I reach anything of use I will be sure to post it here!

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

    ReplyDelete
  39. HI Chuck,

    I'm trying to use the library but the database connection does not. MySQL returns the following error:

    "[Warning] IP address '192 .168.1.90 'could not be resolved in such host is known"

    I've used the option GRANT ALL PRIVILEGES ON *. * TO 'USER' @ '%' IDENTIFIED BY 'PASSWORD but could not resolve this error.

    PS.: Excuse my English because I am Brazilian and I'm using Google Translator.

    ReplyDelete
    Replies
    1. Are you saying you get the error about the IP address on the server? If so, check to make sure the IP address you are using is valid for your network. For example, make sure the other computers on your network have IP addresses of the form 192.168.1.NNN where NNN is in range 1-254.

      What message do you see in the Serial Monitor? Does it hang?

      Delete
  40. Hello Sir
    Your library is a great help and is exactly what I need for a school project.

    The problem I'm running is that I,m trying to save RFID Tag reads to database table but despite the fact that I dont have any errors in code I still cant connect or receive data in database.

    I have pastebinned my code here: http://pastebin.com/4nfettcX

    Could you please Identify what I,m doing wrong?

    ReplyDelete
  41. Hi. I see two problems initially.

    First, the IP values are suspect:

    IPAddress ip_addr(192, 168, 32, 136);
    IPAddress server_addr(192, 168, 32, 136);

    These should not be the same values. The ip_addr is the address of your Arduino. The server_addr is the address of your MySQL server. They cannot be the same values.

    Second, you have an incorrect INSERT statement:

    "INSERT INTO dbo.Tag VALUES (result, NULL)";

    This will result in an error from the server. You must tell the code to substitute whatever value for 'result' that you want to save.

    For example, you are probably thinking this:

    INSERT INTO dbo.Tag VALUES (1239102938, NULL);

    ...or some such. You must write the code to do the substitution for you. Like this:

    sprintf(INSERT_SQL, "INSERT INTO dbo.Tag VALUES ('%d' NULL)", result);

    Take care to use the correct formatting operator.

    One other thing to check: make sure your root user can connect to the server from a remote host. This would do it:

    GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY 'root';

    Disclaimer: this is not a recommended practice but is fine for non-public, non-production work. Consider why I say that (rhetorically of course). :P

    ReplyDelete
  42. HI Chuck .... Good Job ! I ask at you because you're expert on your library !! how can i use your library with GSM/GPRS shield ??

    ReplyDelete
    Replies
    1. See comments above. It can be done but will be a lot of work.

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

    ReplyDelete
  44. The following sentence is translated by google
    I am currently trying to ask temperature values ​​written to the database, but the monitor is connected with a display window (Connecting. ..) but then the next line on the display fails ("Connection failed."),
    I refer you to do is to draw up the code that would be which side there is a problem??? (Trouble to help me to answer)
    ---------------------------------------------------------
    byte mac[]={0x00, 0xBA, 0xEB, 0xAC, 0xDE, 0x12};
    IPAddress server_addr(127,0,0,1);
    Connector my_conn;
    char user[] = "root";
    char password[] = "123";
    char INSERT_SQL[] = "INSERT INTO tempdb.table1 (temp_temp1) values ('1234')";

    void setup() {
    Ethernet.begin(mac);
    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
  45. The problem is the server IP address. This should not be localhost. By using 127.0.0.1 you are telling the connector that the MySQL server is running on the Arduino. What you need is the IP address of the machine where the MySQL server is running.

    ReplyDelete
  46. Use the following as google translate please forgive me
    I have apache installed together with my computer, my ip is fixed ip but I put it into this same ip with me, and then press the burn ip This will result in a conflict to do .......

    Because I have to use arduino to an Ethernet network version, so I do not know how to solve

    Saying that if I use cmd find my static ip is 123.143.125.25 ip database that I will not do is 123.143.125.25??

    If you put it into this IPAddress server_addr (123,143,125,25); cause two ip conflict, then how to solve .. I recently would have been to solve this problem, it will often leave a message to ask you a question, how to solve, or do you have email, you can leave your mail, so I can ask you more quickly, or skype

    ReplyDelete
    Replies
    1. The only way you can get a conflict is if you do this:

      Ethernet.begin(mac, server_addr);

      In this case, it causes the Ethernet shield to use the same IP as your server. You want this:

      Ethernet.being(mac);

      In this case, your DHCP server will assign an IP address to your shield.

      If you are using this second form and still getting a conflict, determine what IP address is unique and use a static address like this:

      IPAddress my_addr(10, 0, 1, 54);
      Ethernet.begin(mac, my_addr);

      Delete
  47. The following sentence is the same as google translate please forgive me

    Monitor window has been shown that (Connecting. ..), but did not appear wait a long time
       
    Serial.println ("Query Success!");

    Or

      Serial.println ("Connection failed.");

    What could the problem ....??

    I go to this site [https://launchpad.net/mysql-arduino]
    Download this [mysql_connector_complete.zip] tarballs and there are three files these two files (sha1 and mysql_connector) placed in the path C: \ Documents and Settings \ j \ Desktop \ arduino-1.5.2-windows \ arduino-1.5.2 \ libraries

    Currently measured out the result has been shown (Connecting. ..) but it did not appear that would be such a long time what the problem is ......?

    ReplyDelete
    Replies
    1. If you are seeing a hang, this means your Ethernet shield is not connecting to your server. You should verify the network cable/connection is valid by connecting a known working computer to the cable.

      Once you verify there is no issue with the network, you must then ensure you are using the correct IP address for your MySQL server, the correct login and password, and that the MySQL server permits that user to connect. Hint: GRANT ALL ON *.* TO 'user'@'%' ...

      Delete
  48. The following is a google translation:
    byte mac [] = {0x00, 0xBA, 0xEB, 0xAC, 0xDE, 0x12};
    mac is my host that sets mac do??
    Or piece of ethernet mac it??

    ReplyDelete
  49. You can choose whatever address you want provided it is unique on your network. This is not an IP address however.

    ReplyDelete
  50. The following is a google translation:
    Do you have skype??, Or other contact info, I was impatient to be good grasp of it, and I skype is aaa47022003, mailbox is aaa47022003@gmail.com, very urgent need to connect to the database, so look to how to contact you, or to grid long to each other, how to deal with


    Try to use skype contact, please you

    ReplyDelete
  51. Today, the government appears
    ----------------------------------------------------------
    Connecting...
    Error: 76 = Access denied for user 'root'@'172.20.174.92' (using password: YES).
    Connection failed.
    ----------------------------------------------------------

    ReplyDelete
    Replies
    1. This indicates either your user id and password are incorrect or the user+host does not have permissions to connect to the server.

      Delete
  52. The following sentence is google translate

    Finally succeeded, even go in there


    Then
    Temperature sensing how I go and temperature values ​​written to the database

    If you simply write a 123 directly in the values ​​can be displayed in the database

    char INSERT_SQL [] = "INSERT INTO tempdb.table3 (temp_temp1) values ​​('123 ')";
      my_conn.cmd_query (INSERT_SQL);
    -------------------------------------------------- -----------------------------------------------
    But if I want to temperature values ​​in the values ​​indicated by T how to do it
    I am currently this method does not work

      float T = sensors.getTempCByIndex (0);
    char INSERT_SQL [] = "INSERT INTO tempdb.table3 (temp_temp1) values ​​(T)";
      my_conn.cmd_query (INSERT_SQL);

    How do I go about trouble solution, please you myself!! Thank you

    ReplyDelete
    Replies
    1. Like this:

      float t = do_something();
      char buf[128];
      sprintf(buf, "INSERT INTO test.motion VALUES (NULL, '%f')", t);
      my_conn.cmd_query(buf);

      Delete

  53. Hi Dr. Chuck.

    Thanks you for this great work of yours!

    Here is my related blog featuring out your MySQL connector.A simple explanations and How-Tos

    http://cobecoballes-embedded.blogspot.com/2013/09/mysql-connector-arduino-arduino2560.html


    Cheers!


    E^3
    Philippines

    ReplyDelete
  54. T is I definition Temperature Code

    float T= sensors.getTempCByIndex(0);

    Monitor window appears:

    Error: 43 = Unknown column 'T' in 'field list'.

    Help me

    ReplyDelete
    Replies
    1. It is your INSERT statement. You need to reference the correct column name and provide space (in this case via a format string and sprintf). In your previous examples, you had this:

      float T = sensors.getTempCByIndex (0);
      char INSERT_SQL [] = "INSERT INTO tempdb.table3 (temp_temp1) values ​​(T)";
      my_conn.cmd_query (INSERT_SQL);

      You want this:

      float t = sensors.getTempCByIndex (0);
      char buf[128];
      sprintf(buf, "INSERT INTO tempdb.table3 (temp_temp1) values('%f')", t);
      my_conn.cmd_query(buf);

      Delete

    2. Hi Folks

      float T= sensors.getTempCByIndex(0);
      char buf[128];
      sprintf(buf, "INSERT INTO test.motion VALUES ('%f')", T);
      my_conn.cmd_query(buf);


      Check this out for more infos..
      http://cobecoballes-embedded.blogspot.com/2013/09/mysql-connector-arduino-arduino2560.html

      Hope this helps


      E^3
      Philippines

      Delete
  55. TO E^3
    Compile successfully but the database of the values ​​0

    ReplyDelete
  56. Hi there folks! I am having a great headache with this! I downloaded the libraries, got them into the correcto folder, compiled this code:
    #include
    #include
    #include
    #include
    #include "mysql.h"


    byte mac_addr[] = { 0xDE, 0xAD, 0xBE, 0xEF, 0xFE, 0xED };
    //IPAddress ip_addr(169, 254, 250, 84);
    IPAddress server_addr(169, 254, 250, 84);
    char user[] = "arduino";
    char password[] = "arduino";

    /* progrem memory used for quries */
    //const char query[] PROGMEM = "INSERT INTO test.ttext VALUES (NULL, 'Insert from my Arduino!', NULL)";
    //const char query_s[] PROGMEM = "SELECT c1, c2, c3 FROM test.ttext";

    Connector my_conn; // The Connector/Arduino reference



    void setup() {
    Ethernet.begin(mac_addr);
    Serial.begin(115200);
    delay(1000);
    Serial.println("Connecting...");
    if (my_conn.mysql_connect(server_addr, 3306, user, password))
    {Serial.println("Connection.");
    delay(500);}
    else
    Serial.println("Connection failed.");
    }

    void loop() {

    }

    Opened the Serial monitor, it says Connecting... for quite a while then Connection Failed, I don't know what the problem is, I already checked the cable, Arduino Ethernet shield is working properly, I checked the ip using ipconfig in cmd (windows) then created a new user from mysql command promt and granted all the priviledges on it, I am loosing my mind!!! Can someone help please?

    ReplyDelete
    Replies
    1. Some things to check:

      If it is freezing on the "Connecting...", it means your Ethernet shield is not connecting to your database server.

      You are using the 169.254 network. Is your Ethernet shield connected to your computer via a crossover cable?

      Furthermore, I am not certain the Ethernet class defaults to the 169.254 address/network (I'll have to try that sometime), so if there is a DHCP server on your network, the shield could be getting its IP address which is likely not 169.254.

      If you really want to use 169.254, I would assign an IP to the shield manually like this:

      IPAddress ip_addr(169,254,250,88);
      ...
      Ethernet.begin(mac_addr, ip_addr);

      Note that you must choose a value for the address other than your database server.

      You say the shield is working. Have you tried other Ethernet sketches - say from the IDE examples?

      Similarly, have you tried connecting a computer to the same network cable and accessing your database server with the MySQL client? This will eliminate (or confirm) all of the issues related to connecting to the server.

      Check the account to make sure it is permitted to connect via the shield's IP address.



      Delete
    2. Thanks for the short reply! I was at first using a hub and got another ip 192... (checked with ipconfig) it didn't work, then I connected the shield directly to my laptop and no results. Which ip should I use? Should I assing that to the laptop? And I proved a lot of examples using the shield, even the sd slot and all was fine, then I tried the library and no results so far, I would really appreciate your help! I know it is time consuming, but I will one day repay the favor :)
      I guess the thing is the ips, this is meant for a server right? (I tried a server) Can I use this in a local computer with a mysql database?? Maybe I am getting things wrong since the begining!

      Delete
    3. I guess I will try on a mysql server in linux, wish me luck!

      Delete
  57. Thanks for the short reply! I was at first using a hub and got another ip 192... (checked with ipconfig) it didn't work, then I connected the shield directly to my laptop and no results. Which ip should I use? Should I assing that to the laptop? And I proved a lot of examples using the shield, even the sd slot and all was fine, then I tried the library and no results so far, I would really appreciate your help! I know it is time consuming, but I will one day repay the favor :)

    ReplyDelete
    Replies
    1. The Arduino needs its own IP address. For example, if your laptop has 192.168.1.5, give the Arduino 192.168.1.6. So long as they are not the same (but are on the same network) and the IP isn't in use elsewhere you're fine. This would be ip_addr() in my previous example.

      The server_addr would be the IP address of your database server (presumably your laptop). So the Ethernet.begin() would be (substituting the actual values):

      Ethernet.begin(mac_add, 192.168.1.6);

      ...and the my_conn.mysql_connect() would be:

      my_conn.mysql_connect(192.168.1.5, 3306, user, password)

      Does that help?

      Delete
    2. Nope, not working so far, I even assinged dns and gateway to the arduino, all the web works fine, like setting a web page and other things, I was wondering it was the user or password but nope, I entered from my ubuntu laptop to the mysql server, so far no results, and I am loosing my miiind!!!!! Do you know any alternatives? Again, I am grateful for your help!!!

      Delete
  58. The above problem has not been solved yet

    Below is a small mention (I'm not using arduino
    # include
    # include )

    I would like to say that I use another way to send temperature data to the database are sent to the database, but temperatures are shown integers
    Such as: 26oC so I want to ask, how to let him send out the float, such as: 26.55oC

    The following is to create a database format settings:

    # --------- Name ------------ Type ----- empty ---- Default

    3 ---- temp_temp1 --------- float -------- yes ------ NULL


      sensors.requestTemperatures ();
      float T = sensors.getTempCByIndex (0);
      server.write (T);
      Serial.println (T);

    Control room window to see a floating point value, but to look at the database is an integer

    This:
    server.write (T);
    I had to check his grammar only

    server.write (data)
    data: the value to write (byte or char)

    So I would like to ask how he can display floating point

    ReplyDelete
    Replies
    1. It appears your question is not related to the topic of this blog. More precisely, this is a blog for the Connector/Arduino and I happily entertain questions related to the connector and its use with MySQL. This is not a general forum for help about all things Arduino.

      In the future, please refer to the Arduino and associated forums for help with the Ethernet class and its methods.

      That being said, the write() method you are referring to writes the value in bytes to the client. If you send it a float *and* the length of the variable, it will send the client a string of bytes that represent the binary form of the value. Your client therefore must reassemble those bytes and use or convert them accordingly. However, if you send it only a value and no length, it sends exactly one byte. Which may explains the missing decimal portion.

      Delete
  59. Another thing]
    My arduino temperature sensing + database
    Direct manual transmission value is written to the database in the database can be dangerous
    But let the temperature value is automatically written still not resolved

    I really need your mailbox, I want to send my temperature code into your mailbox, I hope you can help me with me for a long period of time, the temperature values ​​can not be thrown into the database, please!!

    My mailbox is aaa47022003@gmail.com
    If do not want to open the case, please send a letter to indicate your name, I can contact you on

    ReplyDelete
  60. Hi Chuck,

    Great Blog.

    How can I contact with you.?

    I´m involved on a project that, perhaps, it,s interesting for both.

    Thank you,

    Jordi

    ReplyDelete
    Replies
    1. You can contact me directly at: drcharlesbell@gmail.com.

      Delete
  61. Chuck, did you expeimented with an arduino yun ? Does it works with such a board? Thnaks in advance

    ReplyDelete
  62. No, not yet. But the Yun is on my Christmas wish list. :)

    ReplyDelete
    Replies
    1. I tryed to port your lib to Yun but it does'nt work.
      The main change should be to replace Eternetclient calls with the equivalent Yunclient calls. Unfortunately after a (apparently) working connect call my code hang at the first available() call...

      Delete
    2. I have tested the code on Yun. But not through the bridge. However, it does work if you plop an Ethernet shield onto the Yun. Afterall, it is essentially a Leondardo on one side. ;)

      I plan to experiment with it some more in the future. But for now, I am satisfied the code works on the Arduino side of the Yun.

      Delete
    3. OK! so now you have a double nic arduino :-)

      Delete
    4. Technically, 3 - the Yun has ETH and WiFi. :P

      Delete
  63. Hey Chuck, thanks for this code. I am meeting the problem of
    "ArdandEther.cpp.o: In function `_GLOBAL__sub_D_mac_addr':
    /usr/share/arduino/ArdandEther.ino:145: undefined reference to `Connector::mysql_connect(IPAddress, int, char*, char*)'
    collect2: error: ld returned 1 exit status". I see on the previous comments to move the mysql_connector folder into the arduino's libraries. I did it but I don't think it worked. I have restarted the arduino program and even my computer but the bug still exists. Thank you!

    ReplyDelete
  64. It appears you do not have the library installed in the correct folder. You can tell easily without compiling anything by checking the File | Examples menu in the Arduino IDE. If you do not see "mysql_connector", you do not have the library installed in the right place.

    If that is the case, check the value of your "Sketchbook location" in the preferences pane of the IDE. This is the root of the folder that should contain the Libraries folder. Note: watch case - some platforms are case sensitive.

    Now, if you do see the mysql_connect example, but you still get a similar error like this, you may have inadvertently placed the mysql.h and mysql.cpp in the folder structure of your sketch. Remove them if that is the case.

    Lastly, check the #include for the connector. Some platforms evaluate the following differently:

    #include "mysql.h"

    and

    #include

    Use the one with the <>s.

    ReplyDelete
    Replies
    1. Drat. My editor removed the brackets.

      The second #include is:

      #include <mysql.h>

      Delete
    2. Hey Chuck! Thanks for your reply! I tried all that you said and I couldn't find any solution. Then I suddenly thought of what you said about the mysql.h and mysql.cpp. I copied the two files into my sketch folder along with my own .ino files. THEN IT WORKED!!!! Am I supposed to add those two files to my sketch? Thank you so much!!!

      Delete
  65. Hey Chuck, I am running thin on Ubuntu but I cannot connect everytime. I connected the ethernet to my computer since I have the mysql running on my computer. I tried connecting to my database by "mysql -uroot -psecret -h 127.0.0.1 --port=3306" and it worked in Ubuntu. But when I use 127.0.0.1 for the server address, it didn't work and connection failed. I remember that you said to not use loopback but I am not sure what else I should do. Please help me Thank you!

    ReplyDelete
  66. Since you are using Ubuntu, aside from checking to make sure the bind-address and skip-networking options are commented out in your my.cfg (and reboot MySQL), you may also need to check AppArmor to see if it is blocking MySQL.

    https://help.ubuntu.com/lts/serverguide/mysql.html

    ReplyDelete
  67. Hay Chuck.
    How a great code. Actually, I want to connect Arduino to mysql in PC (remote server) without using ethernet shield. The Arduino is connected to PC by serial port. Would you show me some codes ? I have been searched about codes for this but it always using ethernet shield. Please help me.Thank you very much :)

    ReplyDelete
    Replies
    1. Hi. Sorry, I haven't worked on that aspect. I might suggest looking for websites or books on writing processing code.

      Delete
  68. for beginners: http://www.freemysqlhosting.net/

    They dont provide IP for access (host name :sql4.freemysqlhosting.net then go to http://www.hcidata.info/host2ip.htm and find ip for host )

    It works nicely
    Thank you buddy.

    ReplyDelete
  69. Chuck - Apparently you have the patience of a Saint! One ~short question: Can the MySQL library be [easily] slimmed-down for a Decimila (SP?)? I only want/need the upload from Arduino to MySQL functionality. If so, any specific pitfalls as I take this on? Thank you for any insight.

    ReplyDelete
    Replies
    1. Hi! Sorry, the library - even stripped down - is about 20KB so with only about 14.3KB free on the ATmega168, it's way too big. :|

      However! You can upgrade your chip to the ATmega328. I've not tried this myself but it may be worth trying. The newer chip is not too expensive and available from a number of vendors like Adafruit.

      http://www.adafruit.com/products/123

      Hope this helps.

      Delete
    2. Bummer...not ready to do that at this moment. Thanks anyway!

      Mark

      Delete
  70. hi Chuck,

    I get the following error in Arduino Due:

    mysql_connector_basic.ino:4: fatal error: avr/pgmspace.h: No such file or directory
    compilation terminated.

    What is the solution?

    ReplyDelete
    Replies
    1. Comment out the line. Not needed for 1.5.4 beta IDE and Due.

      Delete
    2. thank you,
      I used version 1.5.4 beta ID and Due and solved the problem.


      Despite not getting errors nothing appears in the table test arduino.hello .
      I 've used MySQL and Xampp and it did not work.

      The program I'm using is this :

      # include
      # include
      # include
      / / # include / / Add this for the Due if you need drostrf
      # include
      # include

      / * Setup for Ethernet Library * /
      mac_addr byte [ ] = { 0x90 , 0xA2 , 0xDA , 0x0E , 0xAA , 0xBB } ;
      IPAddress SERVER_ADDR (127 , 0, 0 , 1);

      / * Setup for the Connector / Arduino * /
      Connector my_conn / / The Connector / Arduino reference

      char user [ ] = "root " ;
      char password [ ] = " 1234" ;
      INSERT_SQL char [ ] =
      " INSERT INTO test_arduino.hello VALUES (' Hello , MySQL ! ' , NULL ) " ;

      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 ) ;
      / * Write Hello , World test_arduino.hello to MySQL table * /
      my_conn.cmd_query ( INSERT_SQL );
      Serial.println ( " Query Success ! " ) ;
      }
      else
      Serial.println ( " Connection failed . " ) ;
      }

      void loop ( ) {
      }

      The output is :
      Connecting ..


      Delete
    3. The IP address you need to use is the external IP address of the machine where your MySQL server is installed.

      The loopback address - 127.0.0.1 is the 'home' address. So in this case, you are telling the connector that the MySQL server is executing on the Arduino. Which is clearly incorrect.

      Delete
    4. Chuck, which line am I supposed to comment out? I first get a similar error: libraries\sha1\sha1.cpp:3: fatal error: avr/pgmspace.h: No such file or directory
      compilation terminated.

      If I comment out avr/pgmspace.h from the sha1.cpp file, I get these errors after running:
      C:\Users\ekatzenstein\Documents\Arduino\libraries\sha1\sha1.cpp:11: error: expected initializer before 'PROGMEM'
      C:\Users\ekatzenstein\Documents\Arduino\libraries\sha1\sha1.cpp: In member function 'void Sha1Class::init()':
      C:\Users\ekatzenstein\Documents\Arduino\libraries\sha1\sha1.cpp:20: error: 'sha1InitState' was not declared in this scope
      C:\Users\ekatzenstein\Documents\Arduino\libraries\sha1\sha1.cpp:20: error: 'memcpy_P' was not declared in this scope

      Thoughts?

      Delete
    5. Also, if I comment out #include "sha1.h" in the arduino sketch, i get this error:

      mysql.cpp:28: fatal error: sha1.h: No such file or directory
      compilation terminated.

      Delete
    6. I'm sorry, I lost the train of this topic. Perhaps this was posted out of order? What thread is this related to?

      We can also discuss topics on Connector/Arduino on the MySQL forum: http://forums.mysql.com/list.php?175

      Better still, you can send me email at drcharlesbell@gmail.com with a full description of the problem (with example code) if you want to have a more in-depth conversation (and explanation). ;)

      Delete
  71. once again thanks for the response.


    I've tried several ways but without success...


    I will explain my situation:

    the web server is the Xampp and the operating system is windows 7.

    The ethernet shield is connected to the router.

    I used the ipconfig and got the following IP addresses:

    IPV4: 192.168.1.66
    Mask: 255.255.255.0
    Gateway: 192.168.1.254

    The address 192.168.1.66 is the server address Xampp. Because if I use this on another computer on my home appears the main page of Xampp.

    to test the database. I used following php code and it worked



    Help me..

    ReplyDelete
    Replies
    1. Hehehe...it appears your PHP code got eaten by the big, bad HTML monster. :) This blog thinks things with <> around them are HTML tags. Please repost using > and < for > and <.

      Aside from that, your SERVER_ADDR variable should be 192.168.1.66 in your sketch.

      There are many things that can be the culprit here. But there are some tried and true ways to check to see where the problem lies.

      1) Do this on your MySQL server in a command window:

      mysql -uroot -p1234 -h 192.168.1.66 --port=3306

      If this works, you know the server is able to accept network connections. If it does not, check the MySQL reference manual for how to change your server to accept network connections.

      If you get an error, it is possible that the user was setup incorrectly or does not have permissions. For example, how was the host name specified in the CREATE USER (or GRANT) command?

      2) Connect a second machine to the same ethernet cable as your Arduino. Use the command above in a terminal to connect.

      If (1) works but (2) doesn't, you either have a host name issue for the user or the network connection is suspect.

      If this succeeds, do an ipconfig (or ifconfig) and check the network settings. Write them down. Note the subnet - is it different from the MySQL server?

      3) Once (1) and (2) succeed, go back to your Arduino and attempt to load and execute the WebClient Ethernet example.

      If it fails, either your Arduino or the Ethernet shield isn't working properly!

      Note: tell me which Ethernet shield and Arduino you are using. There are some known (and not so known) incompatibilities - some with work arounds.

      4) If (1), (2), and (3) succed, try your sketch again. It should work. If it does not, you may have DHCP or gateway issues. Go to Arduino.cc and look up the Ethernet.begin() method and implement a manual network setup where you supply an IP address, Gateway, mask, etc. Make sure the values are in the proper order!

      Delete
  72. FYI Bulletin:

    New Forum
    ----------------------------------
    See my new post about the new forum for discussing Connector/Arduino. The forum is here: http://forums.mysql.com/list.php?175

    Also see: http://drcharlesbell.blogspot.com/2013/10/announcing-new-forum-for.html

    New Version
    ----------------------------------
    There is a new version of the connector! Check it out on LP: https://launchpad.net/mysql-arduino.

    Also see: http://drcharlesbell.blogspot.com/2013/10/introducing-mysql-connectorarduino-100.html



    ReplyDelete
  73. thank you! .. thank you! ..

    already works ..

    I will explain what I did for those who have the same problem:

    in the command window:

    > mysql -uroot -p1234 -h192.168.1.66 --port = 3306

    then select the database:

    > use test_arduino

    privilege:

    > grant all on *.* to root@”192.168.1.%” identified by “1234”;

    ReplyDelete
  74. Hi Dr. Chuck

    You can send a array to database?

    example:

    int array [] = {1,2,3,4,5,6,7,8,9,10};

    INSERT_SQL char [] = "INSERT INTO test (num) VALUES ('array')";

    ReplyDelete
    Replies
    1. Hi,

      No, SQL (MySQL) does not work like that. You can do a bulk insert like this:

      insert into test (num) values (1), (2), (3),...(10);

      However, if you want to pass values generated in your sketch, you will need to use sprintf() - or similar - to create the string.

      See previous posts on this topic for an example. Or, checkout my blog post on the new version of Connector/Arduino. There are examples there too.

      Delete
  75. Hi Dr. Chuck

    what is the limit of data I can send at one time?

    The function sprintf limit the amount of data I can send because of its buffer.

    Is there any way to send a large amount of data at once?

    eg send 100 numbers of type float.

    ReplyDelete
    Replies
    1. That depends on the board you use. I suspect you will run out of memory if you attempt to create a string that long on a Leonardo or even the new Yun. If you use a Due it will probably fit.

      However, I would ask why you want to attempt to insert 100 values in one go? Why not loop through the values and insert them one-at-a-time? If performance is an issue, you could do 10 or so at a time - whatever will fit in memory.

      Remember, you will be converting the floats to string before adding them with the sprintf() method. Keep in mind the dtostrf library consumes a lot of program space - check it and see.

      Also, if you are trying to store a lot of precision, you could be using more memory than you need. For example, are 2 decimal points enough or do you need 16 or 32?

      Some people have even used integers using multipliers of 100 or more to "shift" the values.

      It just depends on what you want to do.

      I would lean towards KISS and make a loop to insert one-at-a-time and see if that performs well enough. If it does, don't break it. ;)

      Delete
    2. Thanks for the answer Dr.Chuck,

      I'm using the Arduino Due.

      wanted to send 100 values ​​of type float (2 decimal points) read by the sensor for data base to build a graph.

      I'm doing the following:

      (...)

      char query[1000];
      float datav[] = {1.01, 1.02, (...), 1.40};
      String buf;
      String d[1000];


      for(int i=0; i<40; i++){

      d[i] = String (datav[i]);

      }

      buf = "INSERT INTO database.test VALUES (" + d[0] + ", " + d[1] + ", " + d[2] + ", " (...) "+"+ d[29] + ")";

      buf.toCharArray(query, 1000);

      my_conn.cmd_query(query);

      Output:

      over d [30] the string buf is cut.

      how do I make a loop to enter the values one-at-a-time?

      Delete
    3. You have a number of logic errors in the sample code. I am certain you are not getting what you think. Hint: print the string to the Serial monitor and see what you're getting. Also, try to use that insert statement in MySQL. It will not work.

      Do this instead:

      #include // Add this for the Due if you need drostrf

      ...

      const char INSERT_DATA[] = "INSERT INTO test_arduino.temps VALUES (%s)";

      ...

      float me_floats[1000];
      char query[64];
      char temperature[10];
      for (int i=0; i<1000;i++) {
      dtostrf(me_floats[i], 1, 1, temperature);
      sprintf(query, INSERT_DATA, temperature);
      my_conn.cmd_query(query);
      }

      Hint: see my more recent post on the 1.0.0beta release of C/Arduino.

      Delete
  76. Dear Chuck,
    Im having problem with waiting time when line down or problems in connection.
    it takes 20 seconds to 2-5 mins to return value from cmd_querry, or connect functions.
    do you have any solution for this.

    ReplyDelete
    Replies
    1. Delays were inserted to handle the relatively long time an Arduino+Ethernet takes to respond. If you want to adjust these to meet your needs, look at:

      boolean Connector::mysql_connect(IPAddress server, int port,
      char *user, char *password)

      for connection delays and:

      int Connector::wait_for_client()

      for delays in post-connect timing.

      Hint: look at the loops and delay() values. Adjust as you see fit. You will know when you've gone too far when connections and queries start failing randomly with a stable server.

      Delete
  77. Hi Dr.Chuck,

    I can insert 100 numbers one by one as proposed by the Dr.Chuck:

    (...)

    const char INSERT_DATA[] = "INSERT INTO test_arduino.temps VALUES (%s)"

    float me_floats[100];
    char query[64];
    char temperature[10];

    for (int i=0; i<100;i++) {
    dtostrf(me_floats[i], 1, 1, temperature);
    sprintf(query, INSERT_DATA, temperature);
    my_conn.cmd_query(query);
    }

    What happens is that all the values ​​are entered in the same column of the database.

    table temps:

    | point1 |
    | 1 |
    | 2 |
    | 3 |
    | (...) |
    | 100 |

    It is possible to insert each value of float me_floats [i] in various columns as follows in table temps2 and inserting one by one. Because if I send it all at once will not fit in the buffer sprintf

    table temps2

    | Point1 | ponit2 | point3 | (...) | point100 |
    | 1 | 2 | 3 | (...) | 100 |

    ReplyDelete
    Replies
    1. I don't fully understand why you want a table with one row to have so many repeated columns. Is there are reason for this? Help me understand your data storage needs. For example, are you saying these 100 values define an entity of some sort? Or are these 100 values related in some way?

      Or better.... How do you plan to use this data?

      Delete
    2. the values relate to a period of a voltage signal (100 points). The objective is to build a graph (sinusoid) with points point1, point2, point3, (...), point100 that refers to a period.

      after consulting the database is possible to know point1, point2, .. with the following syntax php ;

      point1:
      $row['point1']

      point2:
      $row['point2']

      Delete
    3. Hmmm...well, I'll address your situation first:

      There is simply no easy way to use the connector and make a string long enough to predictably hold all of the values you want to send as a single row to the database. The size of the string may be too large for some boards (e.g. Uno, Leondardo). You could try a Due but I am not sure that will be enough.

      To figure out how large you need to make the buffer, consider the size (in characters) of your largest value +1 for the decimal point and +1 for a comma then multiply times 100. Next, add the number of characters for the rest of the INSERT command. That is the minimal size you need. Again, if you have enough memory space you should be able to do this.

      However, if that doesn't work, one way you could do it is send 10 or so at a time. First, you use an INSERT command to insert the first N values. Next, loop over the remaining values using an UPDATE command. Note that you will have to have some way to identify the row (a key). It is ugly and the code may not be pretty but it can be done.

      Now, back to the issue of why 100 points in a single row... that is not the normal way one saves graph information to a database (but not completely invalid, just odd). You may want to do some more research on how best to save graph data to a relational database. There may be better ways than saving 100 columns in a single row.

      Delete
  78. Hello.

    I have a problem with mysql. The problem is, when is canceled conection with internet. When the conection with internet is back, the write to mysql does not work anymore. Can you help me, what to do? I suppose, that the script first conect to the sql. Than wrote down datas in sql. And at last canceled conection with sql. This sircle will repeat (loop).
    But i do not know, how to canceled conection with base. Please, send me any sugestions.

    Best regards

    ReplyDelete
    Replies
    1. See the forum entry:

      http://forums.mysql.com/read.php?175,598724,599289#msg-599289

      I have been discussing a solution there.

      Delete
  79. Chuck,

    Will this work with the Arduino Uno & Wifi shield instead of the Ethernet Shield?

    Thanks,

    John

    ReplyDelete
    Replies
    1. Yes! However, you must use the latest 1.0.0 beta release. See my blog post on that topic for notes on how to enable the code that supports the WiFi shield.

      Delete
  80. Hi Folks!

    Just a reminder we have a new forum where we can discuss the connector: http://forums.mysql.com/list.php?175

    Also, there is a new version of the connector available on launchpad.

    ReplyDelete
  81. Dr. Chuck,

    Thanks for your quick response! I've since bought your book and have been using it to help me get my Arduino updating MySQL with sensor data and display that on a webpage.

    I keep getting an error though, here is my serial monitor output:

    Couldn't get a WiFi connection!
    Connected to network!
    My IP address is: 10.0.0.15
    Connecting to MySQL...
    Connection failed.
    No Socket available
    No Socket available
    No Socket available
    No Socket available
    Connection failed.

    Here is my Arduino sketch:

    #include
    #include
    #include
    #include

    /*Wireless Info: */
    char ssid[] = "wireless_ssid";
    char pass[] = "the_wifi_password";
    IPAddress server_addr(10,0,0,4);
    int status = WL_IDLE_STATUS;

    /*MySQL Login Info: */
    char user[] = "root";
    char password[] = "secret";


    /*create connector object*/
    Connector my_conn;


    /*SQL insert string*/
    char INSERT_SQL[] = "INSERT INTO test.hello VALUES ('hello from Arduino!', null)";


    void setup() {
    Serial.begin(115200);
    int status = WiFi.begin(ssid, pass);
    if(status != WL_CONNECTED){
    Serial.println("Couldn't get a WiFi connection!");
    while(true);
    }

    else{
    Serial.println("Connected to network!");
    IPAddress ip = WiFi.localIP();
    Serial.print("My IP address is: ");
    Serial.println(ip);
    }



    /*Connect to MySQL */

    Serial.println("Connecting to MySQL...");
    if (my_conn.mysql_connect(server_addr, 3306, user, password)){
    Serial.println("Inside database ! ");
    delay(500);
    /* Write data to MySQL table */
    my_conn.cmd_query(INSERT_SQL);
    }
    else{
    Serial.println("Connection failed.");
    }
    if (my_conn.mysql_connect(server_addr, 3306, user, password))
    {
    Serial.println("inside database 2nd try !");
    delay(500);
    /* Write data to MySQL table */
    my_conn.cmd_query(INSERT_SQL);
    }
    else
    Serial.println("Connection failed.");
    }


    void loop() {
    }

    Any suggestions on what I might be doing wrong would be greatly appreciated. I was trying to update a SQL database on a webserver but I think my school firewall was a complication so now i'm running a WAMP server with MySQL locally.. also I verified user root has all privileges granted on any (%) device.

    Thanks a lot Chuck!

    John

    ReplyDelete
    Replies
    1. my #include files are SPI.h, WiFi.h, sha1.h, mysql.h btw,

      thanks again!

      John

      Delete
    2. If you are using a Leonardo (or newer), you should add the following after the 'begins'.

      while (!Serial);

      Other than that, I do not see anything wrong with your sketch.

      You could look at this post to see if it gives any hints:

      http://forum.arduino.cc/index.php/topic,170460.0.html

      But first, I would use another computer and attempt to connect to your MySQL server using the mysql client. This will ensure your server is actually reachable from another machine via WiFi.

      If this doesn't work, let me know what Arduino and WiFi shield you have and I'll do some more research.

      Delete
  82. This is a great library, but I'm noticing that it is not compatible with the Wire.h library for arduino (http://arduino.cc/en/reference/wire)

    Seems like a pretty major issue, any reason why this would be? I've had a sketch running well if I comment out the Wire library, but it no longer works when I load it:

    //#include
    #include
    #include
    #include "sha1.h"
    #include "mysql.h"

    /* Setup for Ethernet Library */
    byte mac_addr[] = { 0x90, 0xA2, 0xDA, 0x0D, 0x7C, 0xD9 };
    IPAddress server_addr(10, 0, 0, 177);

    /* Setup for the Connector/Arduino */
    Connector my_conn; // The Connector/Arduino reference
    char user[] = "admin";
    char password[] = "*****";

    char INSERT_SQL2[] =
    "INSERT INTO arduino.podBeta (degreesF,RH) VALUES ('122', '11')";

    void setup() {
    Ethernet.begin(mac_addr);
    Serial.begin(115200);
    delay(1000);
    Serial.println("Connecting...");
    my_conn.mysql_connect(server_addr, 3306, user, password);
    }

    void loop() {

    delay(500);
    my_conn.cmd_query(INSERT_SQL2);
    Serial.println("Query Success!");
    delay(5000);
    }

    Any help would be much appreciated as I'm on a tight deadline!

    ReplyDelete
    Replies
    1. Chuck, to simplify the issue, I'm noticing that when I add just the line #includeWire.h to the sample file you've posted, the connection no longer works. Are you able to replicate the issue?

      Delete
    2. Off the top of my head, it is either a conflict with the I2C bus (some Ethernet cards have SD drives that use I2C on A4 and A5) or you're running out of memory (unlikely). Check to see if you can either reassign the I2C pins for your wire device or disable the I2C on the Ethernet shield.

      Delete
    3. Please post your hardware choices - what shield(s) you are using and the Arduino model. I'll try to reproduce it.

      Delete
    4. Thanks Chuck, I'm using Arduino UNO R3 (https://www.sparkfun.com/products/11224) and POE Ethernet Shield (https://www.sparkfun.com/products/9026). Is there a way to disable the I2C on the Ethernet shield but not the Arduino? I'll need I2C because I have a number of sensors running through it.

      Delete
    5. Oh and another thing of note: Ethernet still works with I2C on this shield. An earlier prototype was uploading values to Xively and working. I'm making an occupancy comfort sensor for our office (https://xively.com/feeds/650821181)

      Delete
    6. What happens when you run the sketch with the wire.h included but no other sensor attached?

      If that works, reconnect your sensors and try this out to see if all of the devices are detected and there isn't a conflict:

      http://playground.arduino.cc/Main/I2cScanner

      Delete
    7. When I detach everything, it still hangs at "Connecting..." when I add the Wire Library.

      Delete
    8. Chuck, are you able to replicate this issue?

      Delete
    9. I was able to reproduce the problem. It is a memory issue. The Wire library uses too much memory. Since we cannot control that, we have to turn to the connector.

      Fortunately, the connector can handle this. We need to use PROGMEM. Substitute the following in your sketch and it will compile and run correctly.

      1) include the progmem library:

      #include <avr/pgmspace.h>

      2) make your local strings in flash:

      PROGMEM prog_char INSERT_SQL2[] = "INSERT INTO arduino.podBeta (degreesF,RH) VALUES ('122', '11')";

      3) use the PROGMEM version of cmd_query:

      my_conn.cmd_query_P(INSERT_SQL2);

      Delete
    10. Chuck, thanks for looking into this but I don't think this will work. It indeed works for the sample sketch, but from what I understand, variables cannot go into program memory. So these sensor values are unable to log through program memory, correct?

      When I try this, I get the error "initializer failed to determine size of 'INSERT_SQL2'....Any suggestions? Would it be possible to reduce the size of the connector library so that it may only write values to a database, rather than select them?

      Delete
    11. If you use only static strings it will work. However, it seems you need dynamic strings, yes? In that case you are correct. PROGMEM won't help much. The connector and Wire libraries are just too large to be used together on a Uno or lower board.

      I think then you will need to either modify the library to reduce its size (not recommended), use a larger board like a Mega, or use a second Arduino to capture your sensors and forward the data to the first Arduino to store it in the database. There are several ways of linking two Arduino - see the serial examples, not the I2C examples. ;)

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

    ReplyDelete
  84. Can we use the Ethernet shield without POE to MySql? and can we use this shield with another NFC shield at the same time?

    ReplyDelete
  85. Yes, you can use the Ethernet shield with or without POE. You can also use other shields. In many of my projects I have an XBee shield as well as a prototype shield for other components.

    However, I've read where some NFC shields are not compatible with the Arduino Ethernet shield. You should check the documentation for your NFC shield to be certain it is compatible with the Ethernet shield (i.e. it doesn't use the same pins). If no docs, Google! ;)

    ReplyDelete
  86. Hi, i´ve testet your code with ubuntu 13.10 and got an compile error:

    /usr/share/arduino/libraries/sha1/sha1.cpp:11:25: error: variable ´sha1InitState´must be const in order to be put into read-only section by means of ´__atribute_((progmem))´

    do you have any suggestions?
    sorry for my bad english :-)
    best regards,
    Martin

    ReplyDelete
  87. Hi. What version of the Arduino IDE are you using? Most results suggest installing the latest version of the IDE. Some have solved similar problems this way.

    Other than that, check the location of the sha files/library. Make sure you copied them to your Arduino Libraries folder.

    ReplyDelete
    Replies
    1. good evening. i used the 1.0.3 Version of the Arduino IDE, that was the problem. with the nightly-build it's no problem to "translate" the code...thank you for your help!

      Delete
  88. Hi. I have 2 sketches, 1 based on your example above which puts sensor data into a database which works fine and another which is a webserver which displays the sensor data on a webpage.
    I cant get them both to work at the same time. It seems that as soon as I have this line
    my_conn.cmd_query(SQL_Buf)
    The webserver fails to accept connections. Can I use the ethernet shield for both functions at the same time?

    ReplyDelete
    Replies
    1. Hello. I am a bit confused. You say 2 sketches but are trying to use a single Ethernet shield. Are you saying your sharing the shield with 2 Arduino? Or is it simply you have 1 sketch with two different features implemented?

      In the case of sharing the Ethernet shield. Don't go there! :P Use 2 different Arduino each with an Ethernet shield. You should not experience problems unless both Ethernet shields are using the same IP address. Make sure they have different IP addresses.

      In the case of 2 features in a single sketch doing 2 different things, I would suggest either, a) using 2 different connections but this may require more memory.

      Regardless, it may be best if you sent me your sketch so I can see what is going on. You can post it on patelink or some such if it is large.

      Delete
    2. Hi Chuck, Sorry for the confusion. I was meaning I want to have connectivity to a mysql DB and also be able to have the webserver running on the arduino at the same time in the same sketch across a single arduino with ethernet (those are the 2 features).
      I have the 2 features above working individually in different sketches but if I try to put them together in a single sketch, as soon as I run this line
      my_conn.cmd_query(SQL_Buf)
      the webserver fails to accept client connections.
      Here is my code (unfinished as it doesn't put anything into the DB yet but didn't keep working on it until I get this issue sorted)
      http://pastebin.com/v2BKeaX4

      Thanks

      Delete
    3. Thanks for putting the code on pastebin! :)

      I see one issue that will most assuredly cause the issue you describe.

      You need to put the mysql_connect() in setup() like my examples. As it is now, if you call the MySQL() method, it will attempt to use a connection that has not been initialized which will indeed hang. Move the entire if-else to setup() and try it again.

      Note: there is no disconnect() method (yet) so you cannot connect then disconnect and reconnect, etc.

      Delete
  89. Hi Chuck, please help. this is what I have:


    * Sensor RFID ID-20LA
    * Etrhernet R3 Arduino Shields
    * Router DHCP
    * Motor MySql 5.1 Red


    Database Name: TAG
    Table Name: TEST


    The table fields are:

    * ID (AutoNumber)
    * Tag (Char)
    * Name (Char)

    To read RFID "Serial Monitor" cards:

    # Include
    SoftwareSerial ID20 (3.2) / / virtual serial port
    i talks;
    void setup ()
    {
    Serial.begin (9600);
    id20.begin (9600);
    }
    void loop ()
    {
    if (id20.available ()) {
    i = id20.read () / / get character ID20
    Serial.print (i) / / send character to serial monitor
    Serial.print ("");
    }
    }


    As I can capture data from the variable (i) and save in mysql?

    This is the MAC Arduino:
    0x90, 0xA2, 0xDA, 0x0E, 0xd8, 0xEF

    Thanks from Chile.

    ReplyDelete
    Replies
    1. I don't see any code for the connector. Take a read of my latest blog post on the connector: http://drcharlesbell.blogspot.com/2013/10/introducing-mysql-connectorarduino-100.html. The post has detailed examples of how to do what you want - read a value and formulate a query to insert it into a database. Remember, the connector connect method goes in setup() and the query in the loop after you've read your sensor.

      Delete