views:

174

answers:

3

I'm looking for examples, tutorials, or just "this+this+this should work" for reading from and writing to a SQL server (2008) from a microcontroller such as the Arduino board. I've also looked at (and will probably go with) devices with the .Net Micro Framework such as the Fez Cobra. The Micro Framework does not include ADO. I'm sure this is going to involve some XML, but I can't figure out which technology to further investigate. I do not want to have an PC application to serve as a go-between.

Thanks!

+3  A: 

Honestly, I would make a thin service that would sit in-front of your database and use something lightweight like protobuf to get the data into your micro.

I doubt you'll be able to implement TDS in the limited power and memory of an AVR.

joshperry
A web service interface might be even easier. SQL Server's built in webservices might take care of the server side for you.
David
A: 

I don't know how you got the XML idea, but I advise you to have a look at the Microsoft SQL Server protocol documentation, which gives you a pretty good idea of what you actually would have to implement to natively talk to your SQL server.

Jim Brissom
A: 

Still not sure what your question is about... The tag "arduino" let me think you are asking about Arduino code, but the comment below make me doubt you are asking for .NET stuff. Here's a tentative answer should the question be on how to manage things on the Arduino side.

Method 1: using serial communication and a proxy

On the Arduino, I would simply output SQL queries on the Serial port, like this:

Serial.begin(115200);
Serial.println("INSERT INTO table_name VALUES (value1, value2, value3,...);");

On the server side (or on the side of the computer you are using to access the Internet), I would write a simple script that opens the connection with the DB and forward the query to it. This could be achieved either by CLI commands or opening a network connection on a given port. I do not use Windows/.NET, etc... but on a GNU/Linux box, using python and mysql, the proof-of-concept code that I would write would look something like (BEWARE: UNTESTED!):

import os, serial

self.ser = serial.Serial("/dev/ttyUSB0", 115200)
query = self.ser.readline().strip()
return os.popen("mysql -u<my_usr> -hlocalhost -p<my_pass> -e" + query)

Method 2: using a lan shield or directly an Arduino LAN board

The latter will be shipped sometimes soon (see this presentation if you are curious about it). The first is already available. Writing the code for it should be dead easy (see docs here). The result should be something down the line of (UNTESTED):

Serial.begin(<speed>);
Ethernet.begin(<mac_number>, <ip>);
Client client(<server>, <port>);
client.println("GET <path> HTTP/1.0");
while (client.available()) {
  char c = client.read();
  Serial.print(c);
}

HTH!

mac
There's no direct serial connection with the sql server. If you are referring to a board/shield which communicates with the mcu by serial, then yes this would be part of it. But my question is what will the code look like that initiates the connection with the sql server and closes it? What you have posted is the sql commands in between, but how do you open/close this connection?
Tim
Update in response to your edit: I mention both Arduino and .Net Micro platforms, but the problem is the same. Neither support a built-in way of connecting to SQL, and I am having trouble figuring out how to connect to SQL on a low-level. Everyone connects to SQL using existing classes: for .NET, there's ADO, etc.; for PHP, there's classes. For Arduino/.Net Micro, there are no classes. (cont'd)
Tim
Your post implies that you can connect through a GET request. That's great if it will work, but can you point to an example of what a complete GET request would look like for a particular SELECT/INSERT/UPDATE SQL command?
Tim
@Tim - As stated beforehand, I do not work on MS platforms, so I can't give you a tested answer, yet googling "accessing microsoft SQL server via HTTP" you get: http://tinyurl.com/2uh8rev which (at least for a GNU/Linux guy) seems to fit the bill. Beware that my example code was just meant as proof-of-concept: should you go for that solution, I would rather use POST or GET over https for security reasons. [another comment follows]
mac
@ TIm - As for the existence of classes: Arduino uses C++ so you should be able to use a class like this: http://www.cql.com/cppint.html if you wish. I also read on this thread: http://tinyurl.com/32qh6wb that SQL supports a XML/SOAP interface, so you could also look into that one.
mac