Generic web request API (REST API) is a standard de facto method used by contemporary components and applications to communicate with each other. Generic web request may be implemented using virtually any programming tool (.NET, PHP, Java, etc.); its language and platform neutrality ensures efficient interoperability between components created by different programmers and allows to integrate applications running in different server environments. This blog article shows how it is possible to create a fully functional data-bound REST API web service or component staying at the data-tier level programming in SQL.
Arnica Software web platform has a feature called multi-language scripting, which allows using various supported programming languages to create server executable scripts. Such scripts may be infused into forms, reports, hyperlink actions, web portal components (container pages and portlets), etc., to implement dynamic behavior. One of supported languages is SQL. Arnica web platform wraps SQL execution against selected data source (which may target MS SQLServer, Oracle, MySQL, IBM DB2 and other databases), and provides all necessary connectors to edit and store re-usable SQL statements, to read web request input parameters and use them within SQL; it automatically processes and formats returned data, generates a web response and sends it via HTTP stream to the calling component.
To demonstrate how it works, we created a simple script using SQL with Arnica WebScript. The same example may be reproduced with Arnica WebReport, Arnica LinkServer, Arnica WebPortal, or Arnica UnifiedLogon products, which are included into the Arnica Software web platform. Examples below are available from Arnica demo site, and may be executed using URL specified for each example below.
To run an SQL script within the WebScript scripting environment there should be three metadata items specified at the beginning of the script: scripting language type, data source, and output options. Script metadata section is included at the beginning of script content and enclosed within delimiters <! and !>. SQL code itself follows metadata section. In the script below, the metadata item Language=SQL tells the scripting engine that the script is written in SQL; the item DataSource=NorthWind7 specifies the data source which is used to run the SQL script; and the item OutputOption=@XML@ResponseWrite tells the script engine to convert data returned by SQL query to XML and send data to HTTP stream as a web service response:
<! [Metadata] Language=SQL DataSource=Northwind7 OutputOption=@XML@ResponseWrite [/Metadata] !> SELECT TOP 5 CustomerID, CompanyName, Country FROM Customers WHERE Country='USA' ORDER BY CustomerID
This script may be executed using the following URL: http://demo.arnicasoftware.com/WebScript/Output/Proxy.aspx?Method=ExecuteScriptSet&ScriptSetCode=DEMO7_SQL_Based_Web_Services&ScriptCode=CreateXMLResponse
This request returns XML which looks like this (different browsers might not show XML properly formatted; to go around this issue use page view source when the browser returns service response):
<?xml version = "1.0" encoding="UTF-8" standalone="yes"?> <root> <row> <customerid>GREAL</customerid> <companyname>Great Lakes Food Market</companyname> <country>USA</country> </row> <row> <customerid>HUNGC</customerid> <companyname>Hungry Coyote Import Store</companyname> <country>USA</country> </row> <row> <customerid>LAZYK</customerid> <companyname>Lazy K Kountry Store</companyname> <country>USA</country> </row> <row> <customerid>LETSS</customerid> <companyname>Let's Stop N Shop</companyname> <country>USA</country> </row> <row> <customerid>LONEP</customerid> <companyname>Lonesome Pine Restaurant</companyname> <country>USA</country> </row> </root>
As this example shows, with minimal programming, we were be able to create a fully functional web service, which returns a customer list in XML format and is accessible via REST API.
The next step is to add more dynamics to the web service and specify, for example, country name as a parameter, and then use its value in the WHERE clause of the SQL statement. To achieve this, the SQL script below was modified to include evaluatable expressions, which may reference URL query string parameters, posted form properties, cookies values, etc. In the following example, the script was modified to read the query string parameter CountryName which provides a value for SQL statement:
<!
[Metadata]
Language=SQL
DataSource=Northwind7
OutputOption=@XML@ResponseWrite
[/Metadata]
!>
SELECT TOP 5
CustomerID,
CompanyName,
Country
FROM
Customers
WHERE
Country='<%oServer.oWeb.GetQueryStringParameter("CountryName")%>'
ORDER BY
CustomerIDThe URL calling the service would look like this: http://demo.arnicasoftware.com/WebScript/Output/Proxy.aspx?Method=ExecuteScriptSet&ScriptSetCode=DEMO7_SQL_Based_Web_Services&ScriptCode=CreateXMLResponseWithParameter&CountryName=UK
Note that this URL contains parameter CountryName=UK, and the query returns results, filtered by specified country:
<?xml version = "1.0" encoding="UTF-8" standalone="yes"?> <root> <row> <customerid>AROUT</customerid> <companyname>Around the Horn</companyname> <country>UK</country> </row> <row> <customerid>BSBEV</customerid> <companyname>B's Beverages</companyname> <country>UK</country> </row> <row> <customerid>CONSH</customerid> <companyname>Consolidated Holdings</companyname> <country>UK</country> </row> <row> <customerid>EASTC</customerid> <companyname>Eastern Connection</companyname> <country>UK</country> </row> <row> <customerid>ISLAT</customerid> <companyname>Island Trading</companyname> <country>UK</country> </row> </root>
The are more than 20 options which may be specified with the metadata item OutputOption. These options change how the script engine handles data returned by SQL query and how it formats the output. For example, option @Attribute changes the XML format from element-based to attribute-based:
<!
[Metadata]
Language=SQL
DataSource=Northwind7
OutputOption=@XML@ResponseWrite@Attribute
[/Metadata]
!>
SELECT TOP 5
CustomerID,
CompanyName,
Country
FROM
Customers
WHERE
Country='USA'
ORDER BY
CustomerID
This script may be executed using the following URL from Arnica Software demo site (view source of the page generated by this request, as browsers do not handle attribute-based XML as well as element-based XML, and thus the page may appear blank): http://demo.arnicasoftware.com/WebScript/Output/Proxy.aspx?Method=ExecuteScriptSet&ScriptSetCode=DEMO7_SQL_Based_Web_Services&ScriptCode=CreateXMLResponseWithParameter2
The web service returns XML with attribute-based values:
<?xml version = "1.0" encoding="UTF-8" standalone="yes"?> <root> <row customerid="GREAL" companyname="Great Lakes Food Market" country="USA"></row> <row customerid="HUNGC" companyname="Hungry Coyote Import Store" country="USA"></row> <row customerid="LAZYK" companyname="Lazy K Kountry Store" country="USA"></row> <row customerid="LETSS" companyname="Let's Stop N Shop" country="USA"></row> <row customerid="LONEP" companyname="Lonesome Pine Restaurant" country="USA"></row> </root>
Or, as another output formatting option, the script may be configured to return an easy to read HTML content with header and body elements and table captions. This is is achieved by adding options @HTML, @CompleteHTML, @TableElement, and @ColumnCaptions:
<!
[Metadata]
Language=SQL
DataSource=Northwind7
OutputOption=@ResponseWrite@HTML@CompleteHTML@TableElement@ColumnCaption
[/Metadata]
!>
SELECT TOP 5
CustomerID,
CompanyName,
Country
FROM
Customers
WHERE
Country='USA'
ORDER BY
CustomerID
This script could be executed using the following URL: http://demo.arnicasoftware.com/WebScript/Output/Proxy.aspx?Method=ExecuteScriptSet&ScriptSetCode=DEMO7_SQL_Based_Web_Services&ScriptCode=CreateXMLResponseWithParameter3
The result appears as in the screenshot below:
Another example of using data-bound SQL script is to implement a web service returning a simple value based on data analysis algorithm performed by the sequence of SQL commands. This is similar to how a function returns a simple value based on the combination of input parameters, with the difference that the "function" is written with SQL, implemented as a web service, and exposed via REST API, and thus may be consumed by any component written in any language, capable of making generic web requests.
In the example below, the SQL batch finds the customer with most orders and sends its name and country as a simple response without extra XML or HTML wrapper:
The returned value is text:
This article demonstrates how to create a web service accessible via REST API without writing any web-service-specific code. The programming was only engaged at the higher data tier level using SQL. Arnica WebScript was used to edit, save and execute the SQL script as a web service. Arnica WebScript provides a web interface for working with the scripts; programmers don't have to be connected to the data server and may use only a web browser to implement a complete web service solution. SQL scripting is available in other Arnica Software products, such as WebReport, UnifiedLogon, WebPortal. Examples and products discussed in this article are available from the Arnica Software demo site at http://www.arnicasoftware.com/resources/demo.aspx.
<!
[Metadata]
Language=SQL
DataSource=Northwind7
OutputOption=@ResponseWrite@Text
[/Metadata]
!>
DECLARE
@nMaxOrderCount numeric(15),
@cCustomerID varchar(20),
@cCustomerName varchar(200)
CREATE TABLE #Temp
(
OrderCount numeric(15),
CustomerID varchar(20)
)
INSERT INTO #Temp
(
OrderCount,
CustomerID
)
SELECT
COUNT(*), CustomerID
FROM
Orders
GROUP BY
CustomerID
SELECT
@nMaxOrderCount =
(SELECT TOP 1 MAX(OrderCount) FROM #Temp)
SELECT
@cCustomerID = (SELECT TOP 1 CustomerID FROM #Temp
WHERE OrderCount = @nMaxOrderCount)
SELECT
@cCustomerName = (SELECT CompanyName + ', ' + Country
FROM Customers WHERE CustomerID = @cCustomerID)
SELECT CustomerName = @cCustomerName
DROP TABLE #Temp
This SQL script may be executed using the following URL: http://demo.arnicasoftware.com/WebScript/Output/Proxy.aspx?Method=ExecuteScriptSet&ScriptSetCode=DEMO7_SQL_Based_Web_Services&ScriptCode=GetBestCustomerNameThe returned value is text:
Save-a-lot Markets, USA