In ModScript, we can make calls to external REST APIs. Being able to pull in data or send data to a REST API really grows the ability to build integrations with ModScript. In my example, I use the experimental SBM feature Data Service. Data Service allows us to create a connection to a database, which can be the current SBM database or any other database via an ODBC DSN, and pre-configure an SQL query that can be requested via REST. The SQL query can have runtime parameters bound to them from URL parameters.

SBM Data Service

The SBM Data Service is an experimental feature. As such, it must be enabled in the TS_SYSTEMSETTINGSNAMESPACED table:

update TS_SYSTEMSETTINGSNAMESPACED set TS_LONGVALUE=1 where TS_NAME='EnableDataServices'

Next, edit DataServiceConf.xml in SBM\Application Engine\bin. Read the big comment in the xml file to get more information about the different types of connections you can use in Data Service.  In my example, I create a DSN-based Connection, even though I could just use a local-SBM connection. The reason I do this is because I think it is likely that my reader will really want to pull data from a different database rather than pull data from the SBM database. ModScript has powerful SQL querying features for querying the SBM database, and probably will not need to invoke a REST service to do it. However, ModScript cannot peek into a non-SBM database using its SQL query features; as such, it is a more likely use case to invoke Data Services via REST to query a separate database. However, to keep the example simple, I use the DSN-based Connection to peek back into the SBM database, as I know it is a database that my readers have on-site.

<DataServices>
  <Connection name="ModScriptService" >
    <DSN_String>DSN=SBM_AE;UID=sa;PWD=serena123!</DSN_String>
  </Connection>
  <DataService name="ModScriptQuery1" connection="ModScriptService">
    <columns>
      <column display="ID" column="TS_ID" type="number-integer" />
      <column display="Name" column="TS_TITLE" type="text" len="512" />
      <column display="Submitter" column="TS_SUBMITTER" type="number-integer" />
      <column display="SubmitDate" column="TS_SUBMITDATE" type="datetime" />
    </columns>
    <query>
      <queryText>select TS_ID, TS_TITLE, TS_SUBMITTER, TS_SUBMITDATE from {namespacePrefix}USR_MODSCRIPTRESTCALL {nolockhint} where TS_ACTIVEINACTIVE={0} and TS_SUBMITTER in ({1})</queryText>
      <params>
        <param index="0" type="int" name="active"/>
        <param index="1" type="intArray" name="submitter"/>
      </params>
    </query>
  </DataService>
</DataServices>


In this example, we search the primary table USR_MODSCRIPTRESTCALL for items where TS_ACTIVEINACTIVE is equal to the value passed on the URL, and TS_SUBMITTER is equal to the value passed on the URL. We return the TS_ID, TS_TITLE, TS_SUBMITTER, and TS_SUBMITDATE. This query could be much more complex. It could create a temp table, insert data into it, then join to that data to collate it all into the desired output. However, this example is not about fancy SQL queries, so we keep it simple.

The DataServiceConf.xml file is only processed on AE startup to keep the service fast. Unfortunately, this means IIS has to be reset any time you change the file. So, after an IIS reset, a call to the DataService URL will set up the query, bind the URL parameters to the query, and execute it.

http://yourserver/workcenter/tmtrack.dll?jsonpage&command=dataservice&service=ModScriptQuery1&active=0&submitter=8

The example output:

{ "data": [{ "ID": 1, "Name": "abc", "Submitter": 8, "SubmitDate": "\/DATE(1525900070000)\/" }, { "ID": 14, "Name": "def", "Submitter": 8, "SubmitDate": "\/DATE(1525914739000)\/" }], "result": { "type": "OK", "msg": "" } }

ModScript

In this example, the ModScript will be fairly simple. It requests to data from Data Service and injects it into the HTML form as a JavaScript variable. I added this script as a pre-transition action on the submit transition of my workflow. What this will do is show the submitter all other items they submitted into this table that are still active. Attached is my example application.

var rest = Ext.CreateAppRecord(Ext.TableId("TS_RESTDATASOURCE")); 
rest.Read("DataServiceModScriptQuery1RESTDataSource"); 
var result = ""; 
if ( !rest.Get( result, [Pair("submitter", Shell.User().GetId()), Pair("active",0)] ) ) { // write an error to Event Viewer 
        Ext.LogErrorMsg("Rest call failed in script " __FILE__ ":\n" Shell.GetLastErrorMessage() ); 
        Shell.RedoMessage() = "Rest call failed"; 
        } 
else { 
    var resultObj = result.from_json(); Ext.WriteStream( "" ); 
    }

The Form

On the form, I added an HTML/JavaScript Widget, with the following contents. It simply iterates the JavaScript variable injected by the ModScript and writes the values as an unordered list.

<script type="text/javascript">
document.write("<ul>");
 
for ( var i in itemsISubmittedThatAreStillOpen ) \{
    document.write("<li>");  
    document.write(itemsISubmittedThatAreStillOpen.Name   ": Submitted on "   new Date( parseInt( itemsISubmittedThatAreStillOpen.SubmitDate.match(/\d /)[0] )));
    document.write("</li>");
}
 
document.write("</ul>");
</script>

Is my example a little contrived? Sure, the form could have invoked Data Service directly, but we are pretending that the ModScript did something important with the JSON before writing it to the form. Also, in many use cases, there is no form involved. Instead, ModScript will invoke a REST API to write data to some integration, or use ModScript to pull data from an external source into a field on the item. Also, ModScript could have written a more complex JavaScript into the page to do all the stuff we do on the form. However, writing JavaScript from inside ModScript is not very fun, as you have to be sure to encode everything correctly. Quoted text can be really annoying. Instead, use ModScript to write the data onto the form can be pretty simple, and then the JavaScript can take it from there. In a more complex example, I would move the JavaScript to its own file, simply exposing a simple function for the HTML/JavaScript widget to invoke (this makes the JavaScript reusable, easier to write using Composer's syntax highlighter, and easier to get to).

Contrived or not, we can see ModScript in action, directly invoking a REST call and doing something with the data.

What's new in SBM 11.4

ModScript in 11.3.1 has the ability to make REST calls. In 11.4, we greatly increased the flexibility of the REST call functionality. Features added in SBM 11.4 for REST callouts:

• Custom URL path parameters
  • 11.3.1 allowed the scripter to add and change URL parameters (values after the ? in the URL). 11.4 extends this to the URL path (values after the protocol, server, and port but before the ?). This allows the scripter to add a single REST Data Source that points to the protocol/server/port to whatever REST API you are interacting with, then add URL path values to invoke the REST functions required.
• 11.3.1 allowed the scripter to invoke REST calls via POST and GET, but 11.4 extends this to PUT, DELETE, and any custom HTTP REST verb desired.
• 11.4 allows the scripter to add any custom header to the HTTP call.
• 11.4 allows the scripter to get the headers from the HTTP result.
• 11.4 allows the scripter to bypass the SBM Proxy if desired.
  • SBM Proxy adds functionality such as the ability to support OATH2, but sometimes it might be desired to bypass the SBM Proxy to avoid any complexity added.
  • Bypassing can help with debugging a REST call that is having a problem (see if the problem is due to SBM Proxy or not).
• As a side note, not related to REST but tangential to this example: In 11.3.1, ModScript has functions like Db.ReadIntegersWithSQL(), but the column types are rigid (you cannot read an int, 2 strings, and a double), which might make using Data Service look good for custom queries on the SBM database. 11.4 has Db.ReadDynaSQL(), allowing any number of columns to be returned in a query, making for extremely flexible database querying of the SBM AE schema. As such, you would only need Data Service for querying external databases. 

SBM ModScript - Table of Contents