It is a common practice to have a Web Service that accepts input data in JSON format, makes the necessary transformations/verifications and returns output in JSON format.
Here is a short demonstration of a process that checks if the client with given id is in a database storing client information.
If the client is not in the database, a new record will be inserted.
If the client is already in the database, the data specified in JSON input will be used to update the existing record (the information not provided in JSON will be left as it is).
The Web Service will return a JSON structure with the data in the database.
Important Note: Usually when you expose a webservice you will want multiple users to be able to call it at the same moment.
To ensure a process can be executed in parallel in Stambia, you should make sure that all the temporary files and tables are unique per each session.
The easiest way is to use a session variable ${/CORE_SESSION_ID}$ in the masks and file paths of the metadata objects that you will be using in your process.
You can read more about adjusting the names of the temporary tables here.
1. First, create your input and output JSON metadata. To reverse the JSON structure you can simply copy it into your clipboard and then click “Reverse”:
Source:
Reverse:
Reversed metadata:
Session variable CORE_SESSION_ID is used to allow parallel calls to this webservice:
In our example in response we want to get the same structure as in source, so we simply copy the node, rename it and change the file path:
Important note: You cannot use the same metadata for your input and output.
Stambia uses metadata to create temporary files that are later deleted, and it is necessary that those are two different files.
2. Create a mapping that uses the input metadata and the output metadata.
First, we want to treat the entry json data. We only want to use the data that is provided by user to update existing record. For the fields that were in the database before we want to keep the existing values.
So we will load the data from JSON into stage and left join it with the existing data from the target table (T_CUSTOMER)
Now we will load the data from the joined tables. If a field is NULL in the stage we will take a corresponding field from existing record:
CASE WHEN (stage.Title is NULL) THEN T_CUSTOMER.TIT_CODE ELSE stage.Title END
This will load the data into our target table. Now we want to get the response.
To get the response, we have to use the target table as source, however we have to pick a record to load into response JSON structure (otherwise Stambia will load all the records to the table).
To do that we will create a new stage that will just contain id of the record from the source JSON and we will join it with the target table (thus making a new “source set” for our output):
Important Note: Remember to map the id also to the root node of the JSON structure, otherwise your file will not be generated, and you will get no response.
3. Use your mapping in a process, declare the JSON metadata nodes as input and output parameters.
4. Publish your process as webservice.
Note: Each invocation of a Web Service published by Stambia, creates a session that can be consulted from Designer/Analytics as any other session which might be helpful in case you get any errors while calling your WebService.
Here are few examples of calls made to this WS:
Creating new client:
Updating existing client company:
You can publish it in your runtime and test it yourself. All you need is to modify the file paths and launch the designer’s databases and you’re good to go!