Chuck's Blog: Introducing MySQL Connector/Arduino [PDF]

Apr 6, 2013 - Nope, not working so far, I even assinged dns and gateway to the arduino, all the web works fine, like set

9 downloads 40 Views 477KB Size

Recommend Stories


Introducing the IBMDB2i Storage Engine for MySQL
I cannot do all the good that the world needs, but the world needs all the good that I can do. Jana

Download PHP4 & MySQL PDF
Life isn't about getting and having, it's about giving and being. Kevin Kruse

[PDF] Download Blog, Inc
Silence is the language of God, all else is poor translation. Rumi

@BamarSpeedShop | Karismahideung's Blog [PDF]
Tail tidy adalah spakbor belakang pendek, dimana fungsinya untuk menempatkan plat nomer dan lampu sein. Penggunaannya bisa menambah kesan racy, simple dan clean pada motor beraliran sport seperti Yamaha R15. Produk ini dibuat khusus untuk Yamaha R15

Penulis Blog | www.edipsw.com [PDF]
Penulis Blog | www.edipsw.comwww.edipsw.com/penulis-blog/ - Translate this page

Blog Platform PDF
Goodbyes are only for those who love with their eyes. Because for those who love with heart and soul

[PDF] Blog, Inc
If your life's work can be accomplished in your lifetime, you're not thinking big enough. Wes Jacks

Blog - The Mesh [PDF]
Oct 18, 2014 - Jika anda membutuhkan jasa bersih rumah atau yang biasa disebut sebagai cleaning service di bandung anda bisa menghubungi kami. Kami akan ...... Meja kursi pada umumnya dibuat menggunakan bahan kayu jati, mahoni, meh dan trembesi. Memb

Mysql pdf türkçe indir
How wonderful it is that nobody need wait a single moment before starting to improve the world. Anne

SK Collet Chucks
What you seek is seeking you. Rumi

Idea Transcript


More Next Blog»

Create Blog Sign In

Chuck's Blog

Saturday, April 6, 2013

Followers

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. Blog Archive

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.

2016 (4) 2015 (5) 2014 (7) t 2013 (8)

Example Code

December (1)

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.

Introducing MySQL Utilities release-1.3.0 Introducing MySQL Utilities release-1.2.1 2012 (4) 2011 (4)

About Me

Chuck View my complete profile

/* 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/mysqlarduino). 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. http://www.apress.com/ 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.

Posted by Chuck at 3:50 PM Labels: arduino, connector, mysql

396 comments: Jonathan April 7, 2013 at 8:29 AM Fantastic! - I can't wait to try it. Any idea when the code will be posted? It isn't up on the link yet. Reply Replies Steve Spence April 18, 2013 at 5:01 PM http://bazaar.launchpad.net/~chuck-bell/mysql-arduino/trunk/files Reply

Ira Laefsky April 23, 2013 at 3:50 PM 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 Reply

Zhihang Shen April 23, 2013 at 7:44 PM 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? Reply Replies May 23, 2013 at 5:39 AM

Apply the diff included with the source code. Reply

Paulo Leao May 6, 2013 at 8:40 AM 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)' Reply Replies May 23, 2013 at 5:40 AM

Apply the diff included with the source code.

Empty June 9, 2013 at 12:04 PM what this is mean sir ? Reply

Snottlebocket May 12, 2013 at 11:56 AM 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? Reply Replies Chuck

May 23, 2013 at 5:42 AM

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

Gianluca Valenza May 23, 2013 at 4:54 AM 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. Reply Replies Chuck

May 23, 2013 at 5:38 AM

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.

Gianluca Valenza May 24, 2013 at 2:51 AM 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. Reply

EE Student May 24, 2013 at 8:12 PM 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... Reply

Ícaro May 27, 2013 at 8:38 AM 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, Reply Replies Ícaro May 28, 2013 at 7:40 AM 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, Reply

Kirk Holmes June 10, 2013 at 11:39 AM 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 `Connector::mysql_connect(IPAddress, int, char*, char*)'

reference

to

What did I do wrong? Reply

Keith Hawe June 13, 2013 at 1:39 PM

Reply

Vineet Anand June 16, 2013 at 11:39 AM Same is the problem with me, the compiler says "Connector" does not name a type.. please reply soon. Reply Replies Chuck

June 17, 2013 at 8:41 AM

You must place the connector Arduino folder in your Arduino libraries folder. Reply

Kristher June 19, 2013 at 12:37 PM how do I apply the .diff file? Reply

Chuck

June 19, 2013 at 12:50 PM

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! Reply Replies Kristher June 19, 2013 at 4:31 PM 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 Reply

Chuck

June 25, 2013 at 6:07 AM

Yes, it can. Take a read through http://stackoverflow.com/questions/517257/how-do-i-apply-a-diff-patch-onwindows

this:

Reply Replies Robinson Wadu June 28, 2013 at 6:34 AM 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 : [email protected]

Chuck

June 28, 2013 at 7:10 AM

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

Robinson Wadu June 28, 2013 at 10:06 AM 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... Reply

sujan June 25, 2013 at 10:39 PM Nice tutorial keep on writing jobs in burdwan Reply

Moataz Ossama Aydarous June 28, 2013 at 4:39 AM i cant find the library where is the library please for download ? Reply

Chuck

June 28, 2013 at 7:10 AM

Go to the LP site and click on "code". Or follow this link: http://bazaar.launchpad.net/~chuck-bell/mysql-arduino/trunk/files Reply

CDCC8 June 29, 2013 at 1:18 PM 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 '

Smile Life

When life gives you a hundred reasons to cry, show life that you have a thousand reasons to smile

Get in touch

© Copyright 2015 - 2024 PDFFOX.COM - All rights reserved.