This is part 3 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
Part 3 : Writing data using the Sql Script Web Service API (this one)
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 write data to 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.
Writing data using the Sql Script Web Service API
When the user clicks the submit button, we want to submit a new incident to the database. If you look in the source code, there’s a submitNewIncident method that gets called when the user clicks the Submit button. The submitNewIncident method calls the Sql Script Web Service, passing along the incident type and cause selected by the user.
/**
* Submits a new incident by calling a SQL Script through the SQL Script Web Service API.
* When a new script has been successfully submitted, an event is raised through the Event Dispatcher so that
* it can be handled by any other Workbook component.
*/
public submitNewIncident() {
this.applicationContext.getService('httpClients')
.getSqlScriptHttpClient()
.execute(this.SUBMIT_INCIDENT_SCRIPTID, this.createSubmitNewIncidentRequestMessage())
.then(success => {
this.raiseNewIncidentSubmitted();
}, error => {
this.reportError(error);
}).finally(() => {
});
}
/**
* Creates the Web Service request message, passing along the incident type and cause.
*/
private createSubmitNewIncidentRequestMessage(): Invision.Scripting.SqlScriptExecutionRequestMessage {
return {
langId: this.applicationContext.getService<Invision.UserData>('userData').languageId,
params: {
'@typeID': this.selectedIncidentType.ID,
'@causeID': this.selectedIncidentCause.ID
}
};
}
Since we want to send some data to the server and store it in the database, we need to somehow pass that data along through the Web Service call. As we talked about in Part 2, the execute method of the Sql Script Web Service takes two arguments. The first being the id of the Sql Script, and the second is the arguments passed to the script.
To pass data to the Sql Script on the server, you need to construct the params object for the request message. For each parameter your Sql Script expects, you need to add a property to the params object and assign the value. The property name must match the name of the parameter in the Sql Script exactly. This is what’s done in reateSubmitNewIncidentRequestMessage shown above.
The Sql Script on the server looks like this. Notice that the sql paramters matches the properties we specified for the params object of the request message.
INSERT INTO @Object[All Incidents, Setting].DbObjectName (IncidentTypeID,IncidentCauseID) VALUES(@typeID, @causeID)
Next Step
Part 4 : Publishing events to the Workbook