Using the Manager with Databases

Much in the same manner as with TM1, Enterprise Services Manager allows you to define custom SQL statements against relational databases to be published as web methods for use in your applications. These definitions are stored in the SQL Library section and contain all of the information necessary to execute the query and return the information in a JSON or XML format.   Any valid SQL statement can be used in an SQL Library entry, including inserts, updates, deletes, and stored procedure executions.

 

When you select an SQL Libarary entry from the navigation pane, you will be presented with a screen to enter or update the SQL expression as shown below:

 

SQL Library Screen

 

Creating SQL Library Entries

Creating SQL Library Entries simply amounts to typing or pasting an SQL statement into the text area and saving it with a name that can be used later.   In many cases, the SQL statements require context to be useful.  This context can be supplied with the use of tokens.   Tokens are keywords enclosed in percent brackets, preceded by a dollar sign ($) symbol (I.E. ${Year}).   When Enterprise Services encounters a token, Enterprise Services will require the parameter to be specified when the library entry is used.

 

Specifying tokens in SQL statements

Tokens can be used anywhere within the SQL statement, including insert, update and delete statements, however, a token cannot reference another library entry.

 

Consider the following SQL Library entry called CategoryList:

 

SELECT ProductName, UnitPrice

  FROM Products

 WHERE CategoryID In

    (SELECT CategoryID

       FROM Categories

      WHERE CategoryName = "${Category}");

 

When using this SQL library entry in a web method, Enterprise Services will replace the ${Category} token with the parameter specified on the url, such as:

 

 http://localhost/es3/esweb.svc/json/ProductList?Category=Vegetables

 

This library entry can now be used by any application to restrict the list of products to only those that are within the specified category.  

 

 

Deleting SQL Library Entries

To delete a library entry, click on the entry to be deleted from the navigation pane and press the Delete button.