Custom REST Web Services from Queries

Slate supports the ability to create custom REST-based web services using custom SQL queries.  These queries can be defined as SELECT statements that use a "FOR XML PATH" instruction, indicating that they should export data as structured XML.

For example, the following query pulls a list of all events with a future event date into a structured XML document:

select getdate() as [@updated],
  (select f.[category] as [folder], f.[date] as [dtstart], f.[dtend], f.[deadline],
    coalesce(f.[summary], p.[summary]) as [title],
    coalesce(f.[location], p.[location]) as [location],
    coalesce(f.[street], p.[street]) as [street],
    coalesce(f.[city], p.[city]) as [city],
    coalesce(f.[region], p.[region]) as [region],
    coalesce(f.[postal], p.[postal]) as [postal],
    coalesce(f.[country], p.[country]) as [country],
    (select [name] from [world].dbo.[country] where ([id] = coalesce(f.[country], p.[country]))) as [countryName],
    convert(varchar, coalesce(f.[geo], p.[geo]).Lat) as [lat],
    convert(varchar, coalesce(f.[geo], p.[geo]).Long) as [lng],
    (select [value] from [config] where ([key] = 'https')) + '/register/'
      + coalesce(f.[alias] + '/', '?id=' + dbo.toGuidString(f.[id])) as [url],
    isnull(nullif(convert(varchar(max), f.[description].query('declare default element namespace "http://www.w3.org/1999/xhtml"; /html/body/node()')), ''), convert(varchar(max), f.[description]))
  from [form] f
  left outer join [form] p on (p.[id] = f.[parent])
  where (f.[type] = 'event') and (f.[date] > getdate())
  for xml path('event'), type)
for xml path('events'), type

If you wish to pass in parameters and values through the query string, edit the parameters in the Edit Web Service dialog and set them to the following:

<param id="dtstart" type="date" />
<param id="dtend" type="date" />

To determine the service endpoint, select the Share button in the Edit Query option and select the service type:  XML, JSON, or JSONP.  The appropriate service endpoint is then provided. To pass in parameters, such as dtstart and dtend, append a string like "&dtstart=1/1/2000&dtend=1/1/2099" to the service.  The query could make use of these parameters as follows:

where (f.[type] = 'event') and (f.[date] between @dtstart and @dtend)

Acceptable parameter types include date, datetime, varchar, int, real, uniqueidentifier, and bit.  The following parameter IDs are reserved and should never be used: callback, base, run, query, user, identity, id, h, and output.  Note the use of id, h, and output in the service endpoints. These parameters should be static, representing the following:

  • id: The query ID
  • h: A salted hash of the query ID and an additional authentication check
  • output: The service type

This web service, as constructed, is publicly accessible, so pull only publicly-available information.  If you want to expose sensitive data through a web service, create a username/password pair for the service.  This can be defined by adding a username and password through the Share dialog box.  This secures the web service behind basic authentication.  (The web service will be called over HTTPS, so the username and password remain in cipher text even when sent with basic authentication.)

If you prefer to export the data via JSON with a callback (also known as JSONP), pass a callback function name to the callback query string parameter.

<script>function myCallbackFunction(data) { alert(data); }</script>
<script src="https://slate/manage/query/process?id={id}&h={hash}&output=json&callback=myCallbackFunction"></script>

This technique enables you to get around cross-site domain restrictions. You can also use the jQuery $.getJSON function, as shown in the next example.

JSONP can be combined with the use of the @identity variable in your scripts (which corresponds to the "id" of the currently-authenticated "person") to provide safe identity-aware functionality for browser applications.

Assume that we build a web service as follows:

select [first], [last], [email]
from [person]
where ([id] = @identity)
for xml path('person'), type

We can then build an HTML page with the following HTML and Javascript:

<p>Hello, <span id="first">Guest</span>. Your email is <span id="email">unknown</span>.</p>

<script>
// this example assumes that we have the jQuery library loaded
// the callback=? parameter instructs jQuery to use JSONP
$.getJSON('https://slate/manage/query/process?id={id}&h={hash}&output=json&callback=?',
function (data) {
if (data == null) alert('You are not authenticated.');
else {
$('#first').text(data.person.first);
$('#email').text(data.person.email);
}
});
</script>

When we access the page above, displays an alert if we are not currently authenticated within Slate (consider redirecting the student to Slate to log in).  If we are logged in, it replaces the first name and the email address <span> elements with the appropriate values from the database.

Was this article helpful?
4 out of 5 found this helpful