Pages: [1]   Go Down
Author Topic: Can I put items in a MySQL database directly from my Arduino?  (Read 2246 times)
0 Members and 1 Guest are viewing this topic.
Scotland
Offline Offline
Newbie
*
Karma: 0
Posts: 5
Student - Kayaker
View Profile
WWW
 Bigger Bigger  Smaller Smaller  Reset Reset

Is it at all possible to send data to a MySQL database from an Arduino with an Ethernet shield?

Currently I have data outputting to a webpage, but I would like to record a history of that data - can I send the data directly to a databse?

Alternatively, can I use PHP to capture the data and send it to a database without having a page constantly open, as I would like to record data over a series of days.

Or, is there another way that I can record the data 'in the cloud' automatically from the Arduino +Ethernet.

Thanks in advance.

Chris
Logged

UK
Offline Offline
Shannon Member
****
Karma: 223
Posts: 12577
-
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

If you have a database accessible over the net, you could execute an SQL query via ODBC if you can find an ODBC client that will run on the Arduino. (I think the chances of that are slim, but you never know.)

If you have a web server running, you could execute an HTTP post to push the data into the web server and have your web server write it to the database.
Logged

I only provide help via the forum - please do not contact me for private consultancy.

Offline Offline
Newbie
*
Karma: 0
Posts: 20
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Nothing thats impossible. You would have to implement the mysql login api using the ethernet library and then write a light weight mysql client.

imo, Too much work, will use too much ram and in the end not really worth it. The best way to do it is ,as pointed out by @PeterH, to post the data to a php file and let it do the heavy lifting.

( p.s I you still wish to make an avr mysql client then try using the avr32 platform. You can port much of the existing client code efficiently. )
« Last Edit: February 26, 2012, 07:51:25 pm by rNmScale » Logged

Phoenix, Arizona USA
Offline Offline
Faraday Member
**
Karma: 39
Posts: 5557
Where's the beer?
View Profile
WWW
 Bigger Bigger  Smaller Smaller  Reset Reset

What you want to learn is called the "MySQL Client Server Protocol" - basically, it's the low-level interface with the MySQL server via its defined IP and port number; you connect to it, then use the protocol to communicate with the server. Do a google on "MySQL client server protocol" for some more info; here are a few links to get started:

http://www.codewalkers.com/c/a/Database-Articles/Client-Server-Communication-in-MySQL/
http://schlueters.de/blog/archives/86-Direct-MySQL-Stream-Access.html
http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol

Briefly looking at it, though, I am not sure you would be able to do this on a standard Arduino UNO (maybe with a Mega - perhaps with a quadram expander); I should also note that since you are asking this, it might be outside of your skill level. If that is the case, then the simplest method would be to use an intermediate server (PHP or otherwise) that you can send HTTP GET/POST requests and have it interface with the DB.

This isn't a bad solution, just a bit more abstraction, and you're going to likely want some kind of web browser interface to the DB anyhow (likely), so running PHP (or Perl, or whatever) to communicate with the DB isn't a great issue.
Logged

I will not respond to Arduino help PM's from random forum users; if you have such a question, start a new topic thread.

0
Offline Offline
Edison Member
*
Karma: 7
Posts: 1235
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

sub'ing to thread.  smiley

I havent bought an ethernet shield yet...(but plan too soon)

could you use some like WAMP server to test locally?  (WAMP is a local web server installing Apache, MySQL, PHP..etc)

coming from a bit a web dev. backgound, going the PHP route seems easier (for me)

if yo are already outputting the data to display on a web page could you just add a few lines of PHP to dump to the DB?

(or is the web page being server by the arduino somehow?)

what kind of project are you doing with all this?


thanks

Logged


Global Moderator
Netherlands
Offline Offline
Shannon Member
*****
Karma: 212
Posts: 13531
In theory there is no difference between theory and practice, however in practice there are many...
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
could you use some like WAMP server to test locally?

yes, definitely !
I used xampp - cousin of wamp - last year in a project (can't disclose code, sorry), worked very well, recommended!.
Logged

Rob Tillaart

Nederlandse sectie - http://arduino.cc/forum/index.php/board,77.0.html -
(Please do not PM for private consultancy)

0
Offline Offline
Edison Member
*
Karma: 7
Posts: 1235
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

nice!.

that makes it a bit easier to test locally.. LOL

although I havent begun to even look into this yet.. so Im not 'up' on how polling/parsing the page to dump or collect data works yet.  (it IS on the list of things to play with)   smiley
Logged


Offline Offline
God Member
*****
Karma: 4
Posts: 813
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Or, is there another way that I can record the data 'in the cloud' automatically from the Arduino +Ethernet.

You can create outgoing network connections just fine. Or UDP packets. You can put whatever data you want into those. The question then becomes: what receives these messages?

It's probably easier to write a little process that runs on a "regular" computer that listens for connections or packets from the Arduino, and then turns around and stores the data in a database. The database could even run on the same machine. The point is, the Arduino should just use a simple network protocol to "dump" its data, and the complex operation of properly talking to a MySQL database, handling login, error cases, etc, should probably be delegated to a more complex CPU.
Logged

0
Offline Offline
Edison Member
*
Karma: 7
Posts: 1235
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

makes sense to me..

my disconnect is the 'dump and or polling' for data part..

Im guessing this is handled in whatever pub library that people are using?

With some built in methods/functions to dump/send/transmit data to a target url/server side script...  and another function for pulling/polling for data from a target url/server side script?

Does the lib/code have a place for you to set up IP/taregt urls...etc..etc to define?



another level of confusion for me, (which I believe is a different animal all together), is using the Arduino 'as' a web server...

I understand you can set up the ethernet shield..to 'host' its own web page (with-in reason).. and you can open up a port on your router, forwarded o the Arduino 'IP'.....

I dont quite understand a 'why' though...  couldnt you use the true/real web server to do communcation as well?  either way?

I load up my web page (not hosted on Arduino..but my own domain)..

I write some code/script that does whatever.. eventually the page would either change or set a var...so that the polling Arduino can grab it...yes?

Could also be say way for reverse....

I use my Arduino to 'sense' something.. I then dump this 'value' to a php script.. which in turnes does whatever (dumps to DB or creates XML file..etc)..

I load up my web page.. that either hits the DB or reads the .xml file and parse data on my web page (again none of this hosted on the Arduino)..

is this assumption on the process.. correct?  and if so....  Im a bit unclear as to WHY you would use the Arduino as a 'host'..?

I think Im just stuck thinking INSIDE the box..as I dont have much exposure to the ethernet shield or projects using it.


Thanks.
Logged


New Jersey
Offline Offline
Faraday Member
**
Karma: 65
Posts: 3639
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
I dont quite understand a 'why' though... 
If you have a simple web page that displays data gathered by the arduino or has controls on it to instruct the Arduino to do something, it might be preferable for simplicity and power consumption reasons to avoid using a separate web server. You certainly don't have to do it this way of course.
Logged

0
Offline Offline
Edison Member
*
Karma: 7
Posts: 1235
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

ahh...  ok.   smiley-grin

maybe also faster?  since you not pinging a server constantly to 'watch' (poll) for changes in data?

Its a bit of a different 'mind set' than Im used to in programming..  (not C or anything)..

where as we can set 'listeners' to our objects that 'watch' for interaction, button events, mouse events..etc.. (instead of being in a constant loop and polling/checking)

and arrays are handled different too.. (than Im used to)..  just a bit more learning is all!  LOL..

once I get more 'hands on' on a project like this.. hopefully things will make more sense.



Logged


Turku
Offline Offline
Full Member
***
Karma: 0
Posts: 217
Arduino rocks
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Where is your database? If it is in PC, then it is easy. But it feels like all the answers are for running database in Arduino? If your database is running in a computer PC or Mac, then you connect Arduino to that machine with serial bus or ethernet or anyway you can.

The next step is putting that data into your database. I know MS databases and with them it is easy, because even I can do it. Look for samples, example programs from net.
Logged

New Jersey
Offline Offline
Faraday Member
**
Karma: 65
Posts: 3639
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
I dont quite understand a 'why' though
Another thing occurred to me on the way home - if you're opening up ports on the router to give you access to the site from the internet, you now have something of a security risk if you're exposing a LAMP or WAMP server. Many people don't have the time or the expertise to defend their web server adequately against attack. No-one's going to be hacking a web server running on an Arduino though!
Logged

Offline Offline
God Member
*****
Karma: 4
Posts: 813
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

Quote
Does the lib/code have a place for you to set up IP/taregt urls...etc..etc to define?

Yes, the Ethernet shield library allows you to create connections to remote IPs, or accept incoming connections, or send/receive UDP packets to/from IPs.
No, the Ethernet library does not contain a full HTTP client library that understands URLs and the HTTP protocol. That would probably be too large to fit in the Arduino anyway. That's why I suggest using a custom, simple, TCP protocol, that works similar to a serial wire really. The computer on the other end needs to understand this same protocol -- listen on the appropriate port, when connections come in, read the data from the Arduino, and do whatever is necessary with it.
Logged

Offline Offline
Newbie
*
Karma: 0
Posts: 6
View Profile
 Bigger Bigger  Smaller Smaller  Reset Reset

hi friends. i am new to arduino. using UNO board. i am having a great doubt. i am doing a project on nfc shield. so i want to know when the nfc tags are read by the sheild it should compare the ID of tag with sql server and if matched it should go for next loop... what should i do for that... pleasee help me...
Logged

Pages: [1]   Go Up
Jump to: