Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

At work, one of our primary databases is running on MS SQL Server. I think it's 2008.

I'm currently hitting it via UnixODBC and FreeTDS from a Linux host. Unfortunately, the UnixODBC/FreeTDS are not supported by our admins, so it falls on me. For a long-term strategy I want to move off ODBC and switch to HTTP, using either SOAP or REST calls. The installation is a lot easier with HTTP and most languages understand it, at least Perl, Python and Ruby, which I develop in.

We've got a decent amount of SOAP calls set up on the database for some Java processes, so we can probably switch to that pretty easily. We were discussing REST also, which I like because it's lighter-weight. Our DBAs don't have any REST interfaces set up, so I was wondering if there is a plugin or tool for SQL Server that can do simple REST mappings of the tables to get us jump-started. I've considered writing something in Ruby's Sinatra but that'd be one more thing on my plate.

I searched for "SQL Server + rest" but it's pretty obvious that the Googles will do the wrong thing with the word "REST" and, as a consequence, didn't find anything worth while. So, if you have experience with something providing REST and felt it was a good tool then chime in.

share|improve this question
    
I don't know what REST is, but are you sure you're searched enough? blogs.msdn.com/b/sqllive/archive/2008/06/18/… stackoverflow.com/questions/1162780/… stackoverflow.com/questions/4263433/… This is what I found :-). Maybe this can help. –  Marian Feb 5 '11 at 14:39
1  
REST is a way of getting information using webservices and fixed paths. So a URL might look like http://somesite/salesorder/3342ab23 and would return an XML or JSON element of the salesorder. That's just a guide, not a hard and fast rule. But the idea is to have an easy way to get information that's programmably predictable. –  jcolebrand Feb 5 '11 at 20:02
    
@jcolebrand, exactly. Or, a POST request http://somesite/salesorder/update/3342ab23 with parameters passed in the body. –  the Tin Man Feb 5 '11 at 20:09
1  
Oh too true, I forgot to mention that it relates the HTML verbs (GET POST PUT DELETE iirc) to the CRUD actions... –  jcolebrand Feb 5 '11 at 20:14
    
@Marian, Thank you for trying, but I've already seen those. I can write all the REST calls, but I am plenty busy as is, and expecting another team to do it means it's not likely to happen anytime soon. That is why I asked specifically about a plugin or tool that could get provide rudimentary REST calls. –  the Tin Man Feb 5 '11 at 20:18
show 1 more comment

2 Answers 2

up vote 4 down vote accepted

If you're on SQL Server 2008 R2, I'd recommend taking a look at the Open Data Protocol (also known as OData). Microsoft has some FAQs on it as well, and there is a Java client library available.

Also take a look at sqlREST, which exposes relational databases using web services and works with most databases that have a JDBC driver.

share|improve this answer
    
OOooo, you might be on to something there. Thanks. –  the Tin Man Feb 5 '11 at 20:20
add comment

Background
From wikipedia RESTful API

REST was initially described in the context of HTTP, but is not limited to that protocol. RESTful architectures can be based on other Application Layer protocols if they already provide a rich and uniform vocabulary for applications based on the transfer of meaningful representational state. RESTful applications maximize the use of the pre-existing, well-defined interface and other built-in capabilities provided by the chosen network protocol, and minimize the addition of new application-specific features on top of it.

From MSDN article Anti-Patterns To Avoid In N-Tier Applications

REST, or Representational State Transfer, is a type of Web service that is rapidly gaining in popularity. So you might ask yourself what the difference is between RESTful services and custom Web services, and why you might choose one type over the other. The key difference between the two types is that REST services are resource-centric while custom services are operation-centric. With REST, you divide your data into resources, give each resource a URL, and implement standard operations on those resources that allow creation, retrieval, update, and deletion (CRUD). With custom services, you can implement any arbitrary method, which means that the focus is on the operations rather than the resources, and those operations can be tailored to the specific needs of your application.
...
If you can fit your service into the constraints of REST, doing so will buy you a lot of advantages. ADO.NET Data Services in combination with the Entity Framework makes it easy to create both RESTful services and clients to work with them. The framework can provide more functionality to RESTful services automatically because the services are constrained to follow a specific pattern.

Implementation

  • WCF Data Services "ADO.NET Data Services exposes data, represented as Entity Data Model (EDM) objects, via web services accessed over HTTP. The data can be addressed using a REST-like URI"

    This document describes how to create and use Microsoft® ADO.NET Data Services, and discusses various details around the URI and payload formats. This document is meant as an introduction to ADO.NET Data Services and thus covers the core aspects of the technology, defering discussion of more advanced topics to companion documents.

    The goal of Microsoft® ADO.NET Data Services is to enable applications to expose data as a data service that can be consumed by web clients within corporate networks and across the internet. A data service is reachable via regular HTTP requests, using standard HTTP verbs such as GET, POST, PUT and DELETE to perform CRUD operations against the service. The payload format used by the service is controllable by the application, but all options are simple, open formats such as JSON and Atom/APP.

    The use of web-friendly technologies make ADO.NET Data Services ideal as a data back-end for AJAX-style applications, Rich Interactive Applications and other applications that need to operate against data that is stored across the web.

  • or for fully customised version look into: Canonical REST Entity Service "Demonstrates a REST Service built with WCF 4 that fully complies with HTTP specs for use of GET, PUT, POST, DELETE and includes unit tests to test compliance"

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.