This is part 2 in the series about creating Workbook plugins for Profitbase InVision.
Part 1 : The Basics
Part 2 : Reading data using the Sql Script Web Service API (this one)
Part 3 : Writing data using the Sql Script Web Service API
Part 4 : Publishing events to the Workbook
Part 5 : Conditional execution of GenericAction
Download : Source code and sample database
In this post, we’ll walk through using the Web Service API of InVision to read from the database. As I mentioned in Part 1, InVision is built using AngularJS, so some experience with that framework is required.
You should download the source code from the link above to follow along as I explain the parts of the code that’s relevant to understanding using the Web Service API.
Reading data using the Sql Script Web Service API
The source code has a function called loadIncidentTypes. This function loads incident types from the database through the Web Service API for executing SQL Scripts, so that a list of options can be presented to the user.
/**
* Loads incident types from the database through the Web Service API for executing SQL Scripts.
* The SQL Script is defined in the Solution database on the backend.
*/
private loadIncidentTypes(): ng.IPromise<boolean> {
var deferred = this.applicationContext.$q.defer<boolean>();
this.applicationContext.getService('httpClients')
.getSqlScriptHttpClient()
.execute(this.GET_INCIDENTTYPES_SCRIPTID, {
langId: this.applicationContext.getService<Invision.UserData>('userData').languageId,
params : null
})
.then(result => {
this.incidentTypes = result.data ? Invision.Data.DataPackageReader.createObjectArray(result.data) : null;
}, error => {
this.reportError(error);
}).finally(() => {
deferred.resolve(true);
});
return deferred.promise;
}
Lets walk through the code
There are basically 3 parts of this code that we should look closer at:
- The applicationContext service.
When your plugin component inherits from the PluginComponent base class, the applicationContext service is automatically injected into your component. Through the applicationContext service, you get access to the servics and interaction model of the Workbook.
The service I’m using here, is the ‘httpClients’ service. Through the httpClient service, you get access to the different Web Service APIs that you can use to access resources in InVision. - Through the httpClients service, I’m getting a reference to the SqlScriptHttpClient. The SqlScriptHttpClient is used for calling into the Sql Script Web Service, and has a single method, execute. The execute method takes two arguments; the id of the Sql Script and a message body containing the language id of the user and a params object (they are both optional unless your script is parameterized). If you look at the sample code, GET_INCIDENTTYPES_SCRIPTID is just a string with the id of the Sql Script we want to execute (get it by right clicking the node in the Solution Explorer).
- The execute method returns a promise, and when it’s done we’re converting the result into an array of objects (rows) that we’ll present to the user. Note that the Sql Script is set up to return data, otherwise we would get nothing back even if the call succeeds.
Data returned from most InVision Web Service APIs are wrapped as a package with a header and the acutal data serialized, so that the overall size of the payload is smaller than it would be if the data was returned as, say, standard JSON. The header describes how the compressed data can be deserialized into an array of objects, and the DataPackageReader is the service we can use for this task. So for example, if your Sql Script returns information about people, the result of sending that through DataPackageReader.createObjectArray may look something like this:[ { name : 'Windows', company : 'Microsoft' }, { name : 'Android', company : 'Google' }, { name : 'iOS', company : 'Apple' } ]
Next step