Connect to SQL Server from Microcontroller (Arduino or Fez with .Net Micro Framework)
Asked Answered
K

6

6

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!

Kerr answered 16/9, 2010 at 21:55 Comment(2)
So? How did you finally manage this? :)Antipathetic
I have yet to actually purchase a Fez. They are frequently out of stock. I think I will have to write a service/app to sit in between. From what I've determined, SQL Express does not allow web services. If I am wrong about this, feel free to say so (I'd be excited to learn that!). Otherwise, that would be the best approach.Kerr
L
5

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.

Languet answered 16/9, 2010 at 21:59 Comment(2)
A web service interface might be even easier. SQL Server's built in webservices might take care of the server side for you.Tambour
@David, implementing a full webservice client in arduino/avr seems would be a bit bloated though?Alba
A
2

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!

Antipathetic answered 24/9, 2010 at 14:45 Comment(5)
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?Kerr
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)Kerr
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?Kerr
@Kerr - 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: 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]Antipathetic
@ TIm - As for the existence of classes: Arduino uses C++ so you should be able to use a class like this: cql.com/cppint.html if you wish. I also read on this thread: tinyurl.com/32qh6wb that SQL supports a XML/SOAP interface, so you could also look into that one.Antipathetic
M
0

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.

Mandamandaean answered 16/9, 2010 at 22:2 Comment(0)
A
0

Depending on a couple parameters:

1.0 You are connected via serial port to a host PC.

Build a client app on the host pc which received commands over the serial port and translates these commands into sql, using ADO or whatever you prefer.

2.0 You are using an ethernet shield.

In this instance you now have a few options:

2a. Connect to a webservice and use Soap or a similiar http protocol to send commands, which the service will translate into database commands.

2b. Attempt to implement at least a subset of the TDS protocol and directly connect to the sql server and issue commands directly to it.

Alba answered 15/11, 2010 at 3:8 Comment(0)
P
0

You can set a HTTP end point in SQL Server and then send HTTP put commands to it.

Parnassus answered 31/7, 2012 at 22:44 Comment(0)
L
-1

Look at http://www.logicdata.it/sqleng, they have a nice SQLServer for micros.

Leshalesher answered 12/7, 2011 at 10:22 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.