Introduction
SData is Sage’s new REST based Web Services API used by many of our applications. I’ve blogged about SData a few times already: SData in Sage ERP Accpac 6, More on SData and Sage ERP Accpac 6 and Stateful SData. Calling something a RESTful Web Services API sounds pretty technical and difficult; however, this blog post will try to show that using SData isn’t all that hard and that perhaps it provides and easier to use access method than using SQL or other APIs.
In fact, you might think that you need a complicated SDK with a giant reference manual to use SData. In fact you don’t need an SDK at all. Since SData is based on web standards, many tools already know how to deal with SData. In previous blog posts I showed how SData queries were just URLs that you can just enter in a browser like Chrome and then see the XML response in the browser window. Not very friendly. In this article we’ll show how you can use standard open source tools to play with SData and then how even commercial tools like Microsoft Excel have the capability to deal with SData since Excel knows how to interpret web standards. All the documentation for SData is also freely availalble at http://sdata.sage.com/.
cURL
cURL is an open source command line utility and library. You can use the command line utility to execute SData commands to retrieve data and to insert/update/delete records. cURL is a handy utility to anything you like with things that use the HTTP protocol including requests to web sites and web services.
For instance you can issue the command:
curl --user "ADMIN:ADMIN" http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers
which will return all the customer records from the Accpac SAMINC database. The --user parameter is used to set the login, note that these must be in upper case for Accpac. Change localhost with your own server name and replace SAMINC with the company id that you want to use.
You can read a specific record by specifying the index as in the following example that reads good old Ronald Black:
curl --user "ADMIN:ADMIN" http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers(‘1200’)
Try playing with the query language specified at http://interop.sage.com/daisy/sdata/Queries/Filtering.html to retrieve just the records you want. For instance:
curl --user "ADMIN:ADMIN" http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers?where=IDCUST gt '1500'
to get customers with codes greater than 1500.
You can also insert records with cURL, for instance issue the command:
curl --user "ADMIN:ADMIN" http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers -X POST -T cust.xml
where cust.xml contains:
<?xml version="1.0" ?>
<entry xmlns:sdata=”http://schemas.sage.com/sdata/2008/1”
xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”
xmlns="http://www.w3.org/2005/Atom">
<id>Customer</id>
<title>Some title</title>
<content>Content</content>
<sdata:payload>
<customer>
<IDCUST>STEVE</IDCUST>
<NAMECUST>Stephen Smith</NAMECUST>
<IDGRP>RTL</IDGRP>
<IDACCTSET>USA</IDACCTSET>
<CODETAXGRP>CALIF</CODETAXGRP>
<IDBILLCYCL>BILLWK</IDBILLCYCL>
<TEXTSTRE1>865 W. 14th Ave.</TEXTSTRE1>
<NAMECITY>Vancouver</NAMECITY>
<CODESTTE>B.C.</CODESTTE>
<CODEPSTL>V5Z 1R2</CODEPSTL>
<CODECTRY>Canada</CODECTRY>
<NAMECTAC>Stephen Smith</NAMECTAC>
<TEXTPHON1>(604) 555-1234</TEXTPHON1>
</customer>
</sdata:payload>
</entry>
With these examples, if you try them and have problems, check the double quotes, Word and blogging software tends to change regular double quotes to the slanted ones which then doesn’t work in XML. Also before user its intended to be two dashes not a long dash.
SData from Excel
Excel has a very good ability to query web sites and then parse the data returned. Excel knows about most web standards including the Atom feeds used by SData. You do this by creating a data connect from the web and specify the SData URL as indicated in the dialog below:
It will prompt you for a user id and passwork, enter your Accpac credentials (all in upper case). There are a lot of extra fields and URLs returned in the worksheet that is produced. But in the middle is all the data that you want from Accpac.
Now you can create a more user friendly worksheet based on this one. You can refresh the data anytime you like and you can automate the collection with a VBA macro. So in a new worksheet you can create charts linked to this data, do calculations on it, or manipulate it any other way you like using Excel.
Finding SData URLs
So how do you know what are the SData feed available from an application? You can run Fiddler to spy on the HTTP traffic to see what requests are being made (similar to other Accpac Spy programs).
But SData is actually fairly self-documenting. For instance if you want to get the list of all SData resources for a given dataset then enter:
curl --user "ADMIN:ADMIN" http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC
We call this a shortened URL and it returns a feed of all the SData resources that you can use at the next level of the URL. In the same way if you enter:
curl --user "ADMIN:ADMIN" http://localhost/SDataServlet/sdata/sageERP/accpac
it will return to you a feed that contains a list of all the Accpac datasets (companies) on that server.
If you want to get information on a feed you can use a $schema call, so if you issue:
curl --user "ADMIN:ADMIN" http://localhost/SDataServlet/sdata/sageERP/accpac/SAMINC/Customers/$schema
you will get back a description of all the fields in the feed.
Summary
Many people view SData as a developer technology for ISVs to integrate with Sage products. But I think for partners and integrators that are comfortable performing customizations and integrations using SQL, that SData provides another rich approach to consider. I think that especially for newer graduates entering careers installing, customizing and maintaining ERP systems, that URLs and REST based Web Services will be the much more natural way to go.
