Arduino Ethernet Shield - Writing to database

Hello,

I'm atm trying to get my collected data from the Arduino device and getting it in to a external mysql database.

I've tryed googeling every posible thing i could think of regarding this simple task. And now i'm here :)

So what are my options?

Can I write directly to a mysql database with c++ without having someking of connetor library like mysql++?

Or

Can I write my data an external file like .txt .csv via ftp? And then my a php script that makes the import rutine.

I've read somewhere that if the device is connected via serial I could use php.serial class to call and collect the output with php.-

But what if php.class do i use when the device is connected via lan?

I really hope someone can help make my life a bit easier.

Thx :)

You can write the data directly to the database. Therefore you need a php script on a webserver that saves the values to the database.

With the arduino and an ethernet shield you can call the address from your php script

value=analogRead(AnalogPinNr);  
...
client.print("GET /savedb.php?value=");
client.print(value);
client.println(" HTTP/1.1");
client.println("Host: www.yourwebsite.com");
client.println("User-Agent: MyArduino");
...

You can divide your GET-Request into more than one code line. This way you can send more than one value.

To use this solution your arduino board with ethernet shield has to be connected to your local intranet and it needs access to the Website that is mentioned in the code.

I'm trying to find how to connect directly an arduino with a sql server or mysql or whatever, directly from arduino to the database throw the ethernet shield, but i didn't find nothing, except put a library in the arduino like bd-library for example, but i don't know if that works, anyone knows another solution for that problem, without using a web server?

Thank you and sorry for my bad english.

It isn't possible.

why?

What is the purpose of the ethernet shield. It is to allow the Arduino to connect to a network. As such, it is only designed to send network-supported commands, like HTTP PUT and HTTP GET.

Networks don't know nothing about sql-server.

but you have 4 sockets in the arduino ethernet shield, you can conect one socket to a sql server and interchange informatión, with the apropiate library, no?

Sql server is software, not hardware. The sql server software runs on hardware. The ethernet shield allows you to execute HTTP PUT and HTTP GET commands. Those PUT and GET commands are routed to the software that is listening on the specified port, which is usually port 80. If you don't explicitly set a port, it is assumed to be port 80.

The software on the machine that you are connecting to that monitors port 80 is usually httpd.

httpd does not know how to interface with sql server.

There are plenty of scripting languages that DO know how to talk to sql server, and those scripting languages can be invoked by httpd.

But, you can't send a command like "select * from myTable" to httpd and expect it to know what to do with it.

Obiously i'm saying without http protocol, using throw a library the protocol of the sql server, a library writen in c that use a socket to interchange information with the sql server.

you only can connect by http with the shield of arduino?, it has a complete ip stack I supose you can interact with ftp, smtp or whatever you want with te apropiate library, it's the same with the protocol of the sql server

http, ftp, and smtp are all network protocols. There is no sql protocol for directly communicating with a database. You have to send data from the Arduino (and attached shields) that the specified network server can deal with. sql statements are not something that any network server knows how to deal with.

That's why there are scripting languages that the network server can invoke, and that can communicate with the database, returning network-server-understandable data that the network server can return to the Arduino.

Obiously, you can’t send a sql statement raw by ethernet, but “Microsoft SQL Server” or “MySQL” uses a protocol to comunicate by ethernet, i’m talking about that protocol, not sql raw

If you can figure out how to make the arduino talk the right protocol, you should be able to talk directly to a database. A native arduino mysql library would be a rather entertaining, if, i fear, complicated, creation.

However, unless you REALLY want to do so, i suggest you take the simpler approach of using a go-between, be it a CGI as suggested above, or a simple TCP based daemon written in some scripting language like perl. You'll likely have a working setup much quicker than implementing the native DB protocol.

But, that said, don't let me stop you if thats what you want to do!

I quickly read through this and if I get it correctly, there is no way to connect to a database directly. Would the closest solution be, to read/write to a text file and just use that as a database? If not, what is the method would the most sensible?Thanks

Get or build a custom network client that listens on port 8080. Use the mysql host computers' power to check and sanitize the data BEFORE putting into the db.

The last thing you need is raw network noise being dumped into your db.

Also add some security, so no one else can poison your db. And record the transactions, so you can follow the activity.

SQL server can talk XML over HTTP. It has a SOAP interface.

You'll need a SQL server (2005 will do) that is configured appropriately. Then you'll need to code some stuff on the arduino to create the xml to send and parse the results.

It shouldnt be a problem

Can you take PHP and MySql and put it on a server and then have the arduino send a php script comand to transfer the data from the arduino to and into the mysql data file??

There are several easy solutions to the problem. The shield will say anything you want once you make a TCP or UDP connection. It will use about any port that you want. TCP makes the connection. Http can be the wrapper protocol to get across the net. If you can get the timing and coded responses right you can talk to just about anything out there. The easiest way to use an sql database is probably to set up a php script that you can interact with to get whatever you want in or out. SOAP is probably the easiest way to go. You can find lots of PHP info on how to make a page that will implement this protocol and use just about any database out there.

PaulS is not totally correct, you are not limited to PUT, GET, etc. However it is a good method to get the job done.

How do you reduce the max number of sockets to 2 instead of 4 so I can get 4kb rx buffer instead of 2kb?