Component tutorial - Web Service Call

Introduction

The Web Service Call component is used to make one or more web service calls to other servers. It has support for both SOAP and REST services. However, if all you need is an HTTP call, you're probably better off with using the simpler "HTTP Request" component instead. SOAP is a well defined communication protocol running on top of HTTP that you do not want to implement yourself. However, the line between an HTTP API and a REST API is somewhat fuzzy, with REST being a spiritual set of guidelines and best practices that is more or less followed.

Contents of this tutorial

In this tutorial, we will implement one GET service call using "raw response" and JSON using REST.
We will build a web service that calls Skatteverket's open API in order to get a Swedish personal number approved for testing. We will let the user give a desired birth date for the test person.

This tutorial will cover a situation where we do not know the shape of the result beforehand and we will use the feature of triggering more calls based on the response of the first call.

We then continue in two branches. The first branch is how to make multiple webservice calls, the other how we can use the webservice in a "new edit" menu item.

Not included in this tutorial

We do not cover "legacy mode" nor how to not use "raw response". Please note that "legacy mode" really is legacy and should not be used, but it is perfectly fine to not use "raw response". The difference is that with raw requests and response, the whole message sent and received is passed to the SQL code. Otherwise, Softadmin parses the data and provides it in tabular form. That is not covered in this tutorial.

Requirements

You need Softadmin 7.16 as minimum in order to run this tutorial. This tutorial was written for Softadmin 7.30 in case anything differs from the version you are using. From Softadmin 7.32 you'll have access to a logging framework within Softadmin. Some tips for trouble shooting earlier versions will also be provided.

From Softadmin 7.36, two more features were added.

• The new component mode "single mode" allows for an easier implementation when only one call is needed.
• The feature "BeginExtraParams" allows for named parameters to be sent back to the procedure for all calls related to the same @RequestId. This means that the solution in this tutorial to add extra parameters in the @RequestId parameter as JSON is no longer needed.

This tutorial uses an external webservice from Skatteverket that must be up and running if the examples to work.

How the component works

The Webservice call component will call your Stored Procedure several times using the parameter @Action to indicate what it wants to do. Some calls are only made if certain settings are configured, but here is a general sequence diagram. Dotted lines indicate optional calls and results.

If something goes wrong

It's common to run into problems when working with webservice calls. The web service call component itself can be unusually silent when an error occurs. If you have Softadmin 7.32 or later, you can set up logging under "Admin" > "Web service calls" > "Web service call log". After a search, you can configure what to log globally under "Log settings" and set up local overrides on top of this. This log will log the messages leading into and out from Softadmin to the external webservice.

For earlier versions, besides the features Print debug output mentioned later, your best friend is the Profiler. There you can see all calls to the menu item and compare expected inputs and outputs with the documentation.

Pelase note that connection errors etc. are not logged to SoftadminAPI.Log automatically. Instead it is sent as @ErrorMessage in the @Action='StoreError' call. In raw mode, you get response in the @Action='StoreRawResponse' regardless if it is a successful call with HTTPStatusCode with value 200, or any other status code. You have to deal with this yourself.

The standard convention in REST is that status codes between 500-599 indicates server errors thar must be solved by the service owner. Status codes 400-499 indicates client error that can be corrected by the caller by altering the request. However, there are many ways to return errors to the client so make sure you check the documentation for the service you are calling. They usually have different responses per status code. Status code 200 indicates a success.

Another tips is to install your own Webservice client, like "Postman", and run the call yourself with handcrafted requests and headers just to make sure that the API works as intended.

Here is a sequence diagram of how it can look like if one of the webservice calls fails. All following requests in the queue is canceled and the error is reported in the StoreError call instead of StoreRawResponse. Then the Finished call is made, and this is the first time you may return an ADMIN_Message to the user. If you want to notify the user, you'll have to be able detect that an error has occurred yourself. Softadmin will not do this for you.

Part 1.) Create a Web service

Familiarize yourself with the service

It's unfortunately common that API providers do not follow any standard, because there are no standard, or too many standards.
Skatteverket publishes their services under "Digitala samarbeten". You can see a list of all services on the page "Utvecklarportalen".

The service we're interested in in this tutorial is the one called "Testpersonnummer". The service has a documentation in a Swagger which is a common standard for documenting REST APIs. A similar standard is the OpenAPI specification.

A web service can have one or more web methods. It's not always clear where the service ends and the method begins, but looking at the endpoint and the swagger definition of methods us usually enough to figure it out. Following those ideas we can see that the "service"-part of the endpoint is https://skatteverket.entryscape.net/rowstore/ and the method part is dataset/b4de7df7-63c0-4e7e-bb59-1f156a591763. Together, those forms the complete endpoint (https://skatteverket.entryscape.net/rowstore/dataset/b4de7df7-63c0-4e7e-bb59-1f156a591763). For this particular service, it's possible to open the endpoint in your web browser and get a html page to use the data directly. This is not standard, but nice in this case. Feel free to explore the html page and the swagger file before we begin.

This Swagger file does not specify the response at all! Unfortunately, this is not uncommon. We will have to test the service in order to see what it returns and then try to act accordingly.

Register the service

To use the webservice call component, we must first register the services and methods that we want to call. This is done by using the menu item "Web Service Calls" in the "Admin" menu group.

Step by step in Softadmin

1. Go to "Webservice calls" in the "Admin" menu group.
2. Click "New web service". Fill in the following fields
   • Module: Customer Project
   • Webservice name: "Skatteverket OpenAPI"
   • TLS version: Let the OS choose
   • Protocol: REST
   • Data format: JSON
   • Dynamic URL: false
   • URL system setting:
     • Create a setting called "Skatteverket OpenAPI Base Url" with value "https://skatteverket.entryscape.net/rowstore/"
     • Softadmin forces you to create a system setting for the base url in order to avoid hard coded urls. In most implementation the base url is different between Stage and Prod instances, so this is a good practice.
   • Proxy override: (leave blank)
   • Authorization
     • Leave everything blank. This is an Open API that does not require authorization.
   • Documentation
     • Documentation link: https://www7.skatteverket.se/portal/apier-och-oppna-data/utvecklarportalen?dataresurs=oppna-data-api
   • Rate limit: (Leave blank)
3. Click "save"

Register the method

A service can have one or more methods. The methods share the same base url, but usually have different path at the end. However, it's not uncommon that a GET and a POST method share the same endpoint.

Step by step in Softadmin

1. Click on the [+]-icon ("New method") to add a method to the service. Fill in the following fields. Usually you get this information from the Swagger file.
   • Method name: Get Test Persons
   • URL suffix: /dataset/b4de7df7-63c0-4e7e-bb59-1f156a591763?testpersonnummer={testpersonnummer}&_limit={limit}&_offset={offset}
     • Note! Softadmin defaults to the method name, but as we said before, not every one follows the same standard.
     • Note! We have parameterized testpersonnummer, limit and offset as part of the url suffix by using { }. That means that we can use dynamic data in the url.
     • Note! We ignore the query parameter _callback as JSONP is not something we will be using.
   • HTTP method: GET
   • External description: Gets a list of test personal numbers from Skatteverket.
   • Request: (leave blank)
     • The method we're calling does not use a request body. It only uses query parameters that we specify in the URL suffix field.
   • Use raw response: true
   • Response type: Text
2. Click "save"

Now we have prepared all the data we need to use this method. If we want to call the method from several places with different data, then we can build many different menu items that all uses the same web service and method. Unfortunately, there is no way to test the method just yet.

Prepare database objects

In order to run this tutorial we'll create a schema and table called WebserviceCallComponentTutorial.TestPerson. Run this script to set it up in your database. If you want to use your own schema, then just change all references before you run any scripts or create any stored procedures.

We will now have a table with roughly this definition.

CREATE TABLE [WebserviceCallComponentTutorial].[TestPerson](
    [TestPersonId] [int] IDENTITY(1,1) NOT NULL,
    [SearchString] [varchar](12) NULL,
    [PersonalNumber] [varchar](12) NOT NULL,
    [DatetimeInsert] [datetime2](2) NOT NULL,
    [UserIdInsert] [int] NOT NULL

Create a menu item

Menu item

Click "New menu item" in a menu group of your liking. I will call mine "Get test personal numbers" and place it in function block "Henrik", but the important fields for this tutorial is:

• Component: Web Service Call
• Component mode: Meta data
• Web service: Skatteverket OpenAPI
• Object name: WebserviceCallComponentTutorial.Testperson_WebserviceCall
• Parameters
  • Parameter name: BirthDate
    • Argument type: Parameter (ask user)
    • Argument: BirthDate_{MenuItemId}
  • Parameter name: Limit
    • Argument type: Parameter (ask user)
    • Argument: Limit_{MenuItemId}
  • Parameter name: Offset
    • Argument type: Parameter (ask user)
    • Argument: Offset_{MenuItemId}
• Under "Advanced settings"
  • Print debug output: true

Note that we activate Print debug output in order to first test and verify that the service works at all, before we add more intricate workflows in Softadmin. We must uncheck this setting before the menu item is ready to be used.

If you are using Softadmin 7.32 or later, it may be quicker to use Print debug output than to use the logging framework. But if you want to check the log, then open a new tab to your Softadmin system and navigate to "Admin" > "Web service calls" > "Web service call log" and search for your calls. The default setting for a development environment should be to log everything for a few days. If you do not find anything, update the settings. They can be finetuned to a very high degree as requirements can differ alot between different services.

Click "save".

Stored procedure

Create the SQL procedure WebserviceCallComponentTutorial.Testperson_WebserviceCall

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE WebserviceCallComponentTutorial.Testperson_WebserviceCall
    @Action             varchar(30) = NULL,
    @ErrorMessage       varchar(MAX) = NULL,
    @BatchId            varchar(MAX) = NULL,
    @RequestId          varchar(300) = NULL,
    @HttpStatusCode     varchar(MAX) = NULL,

    @BirthDate          varchar(12) = NULL,
    @Limit              int = NULL,
    @Offset             int = NULL 
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    --------------------------------------------------------------------------
    -- This is a stub for the tutorial for Web Service Call. 
    -- 
    -- Softadmin will make up to three different calls per method 
    -- with different values for the parameter "@Action"
    -- 1. "GetRequests" to know what methods to call. It it possible 
    --    to queue several calls here by sending more rows.
    -- 2. "GetUrlData" to know more about each call. In this case, it's the search prompt.
    -- 3a. "StoreResponse" if everything went well. A HttpCode of 
    --     500 is considered "well" in this case as you did get a response.
    -- 3b. "StoreError" if something went really bad. Timeouts and connection 
    --     errors are common here when we do not get a response at all.
    -- 4. "Finished" is for forwarding definitions etc.
    -----------------------------------------------------------------------------

    IF @Action = 'GetRequests'
    BEGIN
        SELECT
            NEWID() [BatchId];

        SELECT
            NEWID() [RequestId],
            'Get Test Persons' [MethodName]
        RETURN;
    END;

    IF @Action = 'GetUrlData'
    BEGIN
        SELECT
            ISNULL(@BirthDate, '') + '*' [testpersonnummer],
            ISNULL(@Limit, 100) [Limit],
            ISNULL(@Offset, 0) [Offset];
    END

    IF @Action = 'StoreRawResponse'
    BEGIN
        DECLARE
            @Json VARCHAR(MAX) = (
            SELECT 
                C.TextContent
            FROM
                #Content C
            );

        IF @HttpStatusCode <> 200
        BEGIN
            DECLARE
                @LogMessage varchar(MAX) =
                    CONCAT('Error when calling web service. Http status ', 
                        @HttpStatusCode,
                        ' Response body: ',
                        @Json);

            EXEC SoftadminApi.Log_LogError 
                @LogMessage = @LogMessage;
            RETURN;
        END

        -- Parse @Json here.

        RETURN;
    END

    IF @Action = 'StoreError'
    BEGIN

        SELECT
            @LogMessage =
                CONCAT('Error when calling web service. Http status ', 
                    @HttpStatusCode,
                    ' Error message: ',
                    @ErrorMessage);

        EXEC SoftadminApi.Log_LogError 
            @LogMessage = @LogMessage;

        RETURN;
    END;

END;

Test the menu item

Run your menu item. At the parameter prompt, type BirthDate: "19950501" and limit: "5".
Click "OK".

Because we checked the box "Print debug output": "true" for this menu item, we will see the call and response printed on the screen.

You may get a response like this.

{
    "next": "https://skatteverket.entryscape.net/rowstore/dataset/b4de7df7-63c0-4e7e-bb59-1f156a591763/json?_offset=5&_limit=5&testpersonnummer=19950501*",
    "resultCount": 18,
    "offset": 0,
    "limit": 5,
    "queryTime": 39647283,
    "results": [
        {
            "testpersonnummer": "199505012394"
        },
        {
            "testpersonnummer": "199505012386"
        },
        {
            "testpersonnummer": "199505022385"
        },
        {
            "testpersonnummer": "199505022393"
        },
        {
            "testpersonnummer": "199505032392"
        }
    ]
}

In most cases, you will have a Swagger file describing structure of the expected result. In this case we're not so lucky. We'll have to investigate and build our parser manually based on the actual result for our call and best guesses. It may feel uncomfortably, but in most cases it will work rather well. As it is JSON, we can use OpenJson in SQL Server to transform it to tabular data. You can run this in your SSMS directly. Similar tools exists for XML but that is not covered in this tutorial. You can also define your expected result in the method definition and let Softadmin parse the response into tabular data, but that is also not in the scope for this tutorial.

DECLARE @Json NVARCHAR(MAX) = '{"next":"https://skatteverket.entryscape.net/rowstore/dataset/b4de7df7-63c0-4e7e-bb59-1f156a591763/json?_offset=5&_limit=5&testpersonnummer=19950501*","resultCount":18,"offset":0,"limit":5,"queryTime":39647283,"results":[{"testpersonnummer":"199505012394"},{"testpersonnummer":"199505012386"},{"testpersonnummer":"199505022385"},{"testpersonnummer":"199505022393"},{"testpersonnummer":"199505032392"}]}';

SELECT
    *
FROM
    OPENJSON(@Json);

Result:

We can see that the most interesting information is stored as a JSON array in the field results. Let's get that array and turn it into a table.

SELECT
    JSON_VALUE(X.Value, '$.testpersonnummer') [TestPersonalNumber]
FROM
    OPENJSON(@Json, '$.results') X

We now know how to get what we want. For more information about OPENJSON, feel free to use Google to learn more. Let's update our stored procedure's IF @Action = 'StoreRawResponse' clause to extract and save this information. We'll also add an IF @Action = 'Finished' in preparation for future use of the menu item.

Here is the full new Stored Procedure:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE WebserviceCallComponentTutorial.Testperson_WebserviceCall
	@Action				varchar(30) = NULL,
	@ErrorMessage		varchar(MAX) = NULL,
	@BatchId			varchar(MAX) = NULL,
	@RequestId			varchar(300) = NULL,
	@HttpStatusCode		varchar(MAX) = NULL,

	@BirthDate			varchar(12) = NULL,
	@Limit				int = NULL,
	@Offset				int = NULL 
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;
	--------------------------------------------------------------------------
	-- This is a stub for the tutorial for Web Service Call. 
	-- 
	-- Softadmin will make up to three different calls per method 
	-- with different values for the parameter "@Action"
	-- 1. "GetRequets" to know what methods to call. It it possible 
	--	  to queue several calls here by sending more rows.
	-- 2. "GetUrlData" to know more about each call. In this case, it's the search prompt.
	-- 3a. "StoreResponse" if everything went well. A HttpCode of 
	--	   500 is considered "well" in this case as you did get a response.
	-- 3b. "StoreError" if something went really bad. Timeouts and connection 
	--	   errors are common here when we do not get a response at all.
	-- 4. "Finished" is for forwarding definitions etc.
	-----------------------------------------------------------------------------

	IF @Action = 'GetRequests'
	BEGIN
		SELECT
			NEWID() [BatchId];
		
		SELECT
			NEWID() [RequestId],
			'Get Test Persons' [MethodName]
		RETURN;
	END;
	
	IF @Action = 'GetUrlData'
	BEGIN
		SELECT
			ISNULL(@BirthDate, '') + '*' [testpersonnummer],
			ISNULL(@Limit, 100) [Limit],
			ISNULL(@Offset, 0) [Offset];
	END

	IF @Action = 'StoreRawResponse'
	BEGIN
		DECLARE
			@Json VARCHAR(MAX) = (
			SELECT 
				C.TextContent
			FROM
				#Content C
			);

		IF @HttpStatusCode <> 200
		BEGIN
			DECLARE
				@LogMessage varchar(MAX) =
					CONCAT('Error when calling web service. Http status ', 
						@HttpStatusCode,
						' Response body: ',
						@Json);

			EXEC SoftadminApi.Log_LogError 
				@LogMessage = @LogMessage;
			RETURN;
		END


		SELECT
			JSON_VALUE(X.Value, '$.testpersonnummer') [TestPersonalNumber]
		INTO #PersonalNumber
		FROM
			OPENJSON(@Json, '$.results') X;

		INSERT INTO WebserviceCallComponentTutorial.TestPerson
		(
		    BatchId,
		    PersonalNumber,
		    DatetimeInsert
		)
		SELECT
		    @BatchId,
		    TestPersonalNumber,
		    SYSDATETIME()
		FROM
			#PersonalNumber

		RETURN;
	END

	IF @Action = 'StoreError'
	BEGIN

		DECLARE
			@LogErrorMessage varchar(MAX) =
				CONCAT('Error when calling web service. Http status ', 
					@HttpStatusCode,
					' Error message: ',
					@ErrorMessage);
		
		EXEC SoftadminApi.Log_LogError 
			@LogMessage = @LogErrorMessage;
		 	
		RETURN;
	END;
	
	---------------------------------------------------
	-- Send a test personal number as passing field. --
	-- Also send the batchId.						 --
	---------------------------------------------------
	IF @Action = 'Finished'
	BEGIN
		DECLARE 
			@PersonalNumber varchar(300);
		SELECT TOP (1)
			@PersonalNumber = TP.PersonalNumber
		FROM
			WebserviceCallComponentTutorial.TestPerson TP
		WHERE
			TP.BatchId = @BatchId;

		IF @PersonalNumber IS NOT NULL
		BEGIN
			SELECT 
				@PersonalNumber [PersonalNumber],
				@BatchId [BatchId]
		END
		ELSE
		BEGIN
			SELECT
				CONCAT('No test personal number could be found for birth date "', @BirthDate, '"') [ADMIN_Message];
			RETURN;
		END

		RETURN;
	END;
END;

Conclusion

In short, when we get results, store them in the database and when the components finishes, forward one of the personal numbers at random and the generated BatchId if we want to query for all found personal numbers.

It's also possible to act on the information directly in the Finished call to the component, but that will of course make the stored procedure less generally usable. It's probably better to raise an event or forward to another menu item for further processing.

Note that we must uncheck Show debug info in the menu item before we can use it. Otherwise it will also not forward to other menu items.

This concludes the first part of this tutorial. You now have a working "webservice call" component. However, do you remember the offset and limit parameters? This API (and many more) has support for paginated data. Instead of getting all results at once (which could overload the system), we only get a maximum of limit number of personal numbers back. This is part 2a in this tutorial. However, maybe we're satisfied if we only get one personal number back and is okay with that, then we can start to use our service right away in part 2b. 2a is not a prerequisite for 2b.

Part 2a.) Handling multiple Webservice calls

We can trigger additional webservice calls based on the response from the first call, in SQL Call: Store raw response, by returning "Resultset: Requests (optional)"

Go back to the menu item from part 1. Set Print debug output = 1 again under advanced settings and run it with the following parameters.

We get the following response.

{
	"next": "https://skatteverket.entryscape.net/rowstore/dataset/b4de7df7-63c0-4e7e-bb59-1f156a591763/json?_offset=3&_limit=3&testpersonnummer=19950501*",
	"resultCount": 18,
	"offset": 0,
	"limit": 3,
	"queryTime": 53476103,
	"results": [
		{
			"testpersonnummer": "199505012394"
		},
		{
			"testpersonnummer": "199505012386"
		},
		{
			"testpersonnummer": "199505022385"
		}
	]
}

The actual response can vary, but in this case we can see that we got the first 3 records of a total of 18 possible, according to parameters resultCount, limit and offset. To get all records, we could either just increase the limit to a higher value, but to get a solution that always works, we need to make additional calls until no more records are returned, or until we reach resultCount. The fields in this tutorial is based on what "Skatteverket" provided and are not part of a standard. However. it is very common for REST API's to have some kind of support for pagination.

If you call an API that returns too much data at once you'll run into problems like "Out of momory"-exceptions from Softadmin. The best fix for this is simply to paginate the calls. A few Megabytes of data is no problem, but respones even below 100 MB have been seen to cause problems in some cases.

Note that we also get the field next with a prepared url to call for the next 3 records. This can be very convenient but it does not work with our currently registered webservice. We can only return the parameters we defined in the method declaration and not set a whole url. However, it is possible to set Dynamic URL in the webservice definition in order to let the SQL code set the complete url. This is not covered in this tutorial. If this feature is used, then also query parameters must be handled and concatenated to the url as well. However, it gives much freedom when multiple methods have to be triggered in a row, even though it can also be solved using forwarding or raising events.

But we will instead calculate and register all required calls based on the first call. It will take a bit of work and introduce a few extra concepts like "recursive CTE"s and "FOR JSON PATH", but it works. It can be done in other ways as well. This service did not turn out to be ideal to demonstrate paginating, but it shows how it can be done.

Steps

1. Detect more data

   Unfortunately, we do not know how many pages of data we will have to retrieve until after the first call. Otherwise we could have added all calls to the queue directly in the first GetRequests call. Instead, we have to read more data from the response that we previously discarded. We want to know if offset is 0, meaning we're on the first run, and if resultCount > limit, meaning there is more data to get at all. We'll add this as an IF-statement in the Store raw response call.

2. Calculate number of calls

   In Store raw response we can add all the calls at once. However, it may be tricker that expected. We'll be using a recursive CTE in order to solve this. Please note that a simple WHILE-loop will probably always be enough as webservice calls generally takes a lot more time anyway that it doesn't matter.

3. Pass the parameters

   Note: Since Softadmin 7.36, you can use the feature "BeginExtraParams" instead, allowing you to pass your own parameters between calls. The section below shows how the same can be achieved without this feature.

   We may only create new requests in Store raw response, not pass parameters to Softadmin. That is done in the following Get URL data. We will get one for each new request. We could have created a database table that stores the RequestId generated here together with the offset required to get the right data. However, we can use a trick to avoid this. We'll be using the fact that the parameter @RequestId is of datatype varchar(max). We have been using a GUID so far, but it is not limited to this.

   We will create a small JSON with our real RequestId but also the parameters we want to use. As long as it is unique per @BatchId we'll be fine. This of course means that we also need to redo our current Get requests and Get URL data to embed and extract those parameters. The full code follows here:

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE WebserviceCallComponentTutorial.Testperson_WebserviceCall
	@Action				varchar(30) = NULL,
	@ErrorMessage		varchar(MAX) = NULL,
	@BatchId			varchar(MAX) = NULL,
	@RequestId			varchar(MAX) = NULL,
	@HttpStatusCode		varchar(MAX) = NULL,

	@BirthDate			varchar(12) = NULL,
	@Limit				int = NULL,
	@Offset				int = 0 
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;
	--------------------------------------------------------------------------
	-- This is a stub for the tutorial for Web Service Call. 
	-- 
	-- Softadmin will make up to three different calls per method 
	-- with different values for the parameter "@Action"
	-- 1. "GetRequests" to know what methods to call. It it possible 
	--	  to queue several calls here by sending more rows.
	-- 2. "GetUrlData" to know more about each call. In this case, it's the search prompt.
	-- 3a. "StoreResponse" if everything went well. A HttpCode of 
	--	   500 is considered "well" in this case as you did get a response.
	-- 3b. "StoreError" if something went really bad. Timeouts and connection 
	--	   errors are common here when we do not get a response at all.
	-- 4. "Finished" is for forwarding definitions etc.
	-----------------------------------------------------------------------------

	IF @Action = 'GetRequests'
	BEGIN
		SELECT
			NEWID() [BatchId];
		
		SELECT
			'Get Test Persons' [MethodName],
			(SELECT 
				NEWID() [requestId],
				ISNULL(@BirthDate, '') + '*' [birthDate],
				ISNULL(@Offset, 0) [offset],
				ISNULL(@Limit, 100) [limit]
			FOR JSON PATH) [RequestId]
		RETURN;
	END;
	
	IF @Action = 'GetUrlData'
	BEGIN
		SELECT
			BirthDate [testpersonnummer],
			ISNULL(Offset, 0) [offset],
			ISNULL(Limit, 100) [limit]
		FROM
			OPENJSON(@RequestId)
		WITH 
		(
			BirthDate	varchar(20) '$.birthDate',
			Offset		int	'$.offset',
			Limit		int	'$.limit'
		)
	END

	IF @Action = 'StoreRawResponse'
	BEGIN
		DECLARE
			@Json VARCHAR(MAX) = (
			SELECT 
				C.TextContent
			FROM
				#Content C
			);

		IF @HttpStatusCode <> 200
		BEGIN
			DECLARE
				@LogMessage varchar(MAX) =
					CONCAT('Error when calling web service. Http status ', 
						@HttpStatusCode,
						' Response body: ',
						@Json);

			EXEC SoftadminApi.Log_LogError 
				@LogMessage = @LogMessage;
			RETURN;
		END


		SELECT
			JSON_VALUE(X.Value, '$.testpersonnummer') [TestPersonalNumber]
		INTO #PersonalNumber
		FROM
			OPENJSON(@Json, '$.results') X;

		INSERT INTO WebserviceCallComponentTutorial.TestPerson
		(
		    BatchId,
		    PersonalNumber,
		    DatetimeInsert
		)
		SELECT
		    @BatchId,
		    TestPersonalNumber,
		    SYSDATETIME()
		FROM
			#PersonalNumber

		DECLARE
			@ResponseResultCount	int,
			@ResponseOffset			int,
			@ResponseLimit			int

		SELECT
			@ResponseResultCount	= ResultCount,
			@ResponseOffset			= Offset,
			@ResponseLimit			= Limit
		FROM
			OPENJSON(@Json)
		WITH 
		(
			ResultCount	int '$.resultCount',
			Offset		int	'$.offset',
			Limit		int	'$.limit'
		)	

		----------------------------------------------
		-- In case of emergency!					--
		-- Uncomment this and re-save the procedure	--
		-- if we get stuck in an infinite loop.		--
		----------------------------------------------

		-- SELECT 1 [ClearQueue];
		-- RETURN;

		----------------------------------------------
		-- If we're on the first call and we have	--
		-- more results than our limit,				--
		-- queue all the remaining calls			--
		----------------------------------------------
		IF @ResponseOffset = 0 AND @ResponseResultCount > @ResponseLimit 
		BEGIN
			;WITH GenerateRows AS (
			  SELECT @ResponseLimit AS offset
			  UNION ALL
			  SELECT offset + @ResponseLimit
			  FROM GenerateRows
			  WHERE offset + @ResponseLimit < @ResponseResultCount
			)
			SELECT
				'Get Test Persons' [MethodName],
				(SELECT 
					NEWID() [requestId],
					ISNULL(@BirthDate, '') + '*' [birthDate],
					offset [offset],
					@ResponseLimit [limit]
				FOR JSON PATH) [RequestId],

				'Retrieving data'			[ProgressTitle],
				CONCAT(offset + @ResponseLimit, '/', @ResponseResultCount) [ProgressText],
				offset + @ResponseLimit		[ProgressStepsCompleted],
				@ResponseResultCount		[ProgressStepsTotal]
			FROM GenerateRows
		END;

		RETURN;
	END

	IF @Action = 'StoreError'
	BEGIN

		DECLARE
			@LogErrorMessage varchar(MAX) =
				CONCAT('Error when calling web service. Http status ', 
					@HttpStatusCode,
					' Error message: ',
					@ErrorMessage);
		
		EXEC SoftadminApi.Log_LogError 
			@LogMessage = @LogErrorMessage;
		 	
		RETURN;
	END;
	
	---------------------------------------------------
	-- Send a test personal number as passing field. --
	-- Also send the batchId.						 --
	---------------------------------------------------
	IF @Action = 'Finished'
	BEGIN
		DECLARE 
			@PersonalNumber varchar(300);
		SELECT TOP (1)
			@PersonalNumber = TP.PersonalNumber
		FROM
			WebserviceCallComponentTutorial.TestPerson TP
		WHERE
			TP.BatchId = @BatchId;

		IF @PersonalNumber IS NOT NULL
		BEGIN
			SELECT 
				@PersonalNumber [PersonalNumber],
				@BatchId [BatchId]
		END
		ELSE
		BEGIN
			SELECT
				CONCAT('No test personal number could be found for birth date "', @BirthDate, '"') [ADMIN_Message];
			RETURN;
		END

		RETURN;
	END;
END;

Note that regardless of all those changes with how @RequestId is used, all changes are still contained in our Stored Procedure. Nothing else have changed. The menu item in part 2a will work regardless.

As a bonus, we've added a progress bar to the menu item by adding the following code.

'Retrieving data'			[ProgressTitle],
CONCAT(offset + @ResponseLimit, '/', @ResponseResultCount) [ProgressText],
offset + @ResponseLimit		[ProgressStepsCompleted],
@ResponseResultCount		[ProgressStepsTotal]

Part 2b.) Using a webservice in a New Edit

We will here use the NewEdit feature setFieldValuesFromWebService in order to call our web service via a button on a new edit field.

Note! This will be a limited demo, so we will only create a GetEditFields-procedure and no InsertUpdate-procedure.

Because the limitation that menu items to be used with "setFieldValuesFromWebService" may not have parameters, we'll have to create a new "Webservice call" menu item without parameters first. As long as we reuse the same SQL Procedure, creating more that one "Webservice Call" menu items is no big deal.

New Webservice call menu item

Click "New menu item" in a menu group of your liking. I will call mine "Get test personal number (js)" and place it in function block "Henrik". The important fields for this tutorial is to only have parameters of type "passing fields" and to assign an alias. You can use those values:

• Component: Web Service Call
• Component mode: Meta data
• Alias: WebserviceCallComponentTutorial.TestPersonalNumber
• Web service: Skatteverket OpenAPI
• Object name: WebserviceCallComponentTutorial.Testperson_WebserviceCall
• Parameters
  • Parameter name: BirthDate
    • Argument type: Passing Field
    • Argument: BirthDate_{MenuItemId}
  • Parameter name: Limit
    • Argument type: Passing Field
    • Argument: Limit_{MenuItemId}

Since we now have a pretty good idea that our web service call will work, we'll skip the debug option completely.

New NewEdit menu item

Create a newEdit-menu item and give it any name you want, for example "Create test person". Set it's GetEditFields-SQL to WebserviceCallComponentTutorial.Testperson_GetEditFields and set "Field Tables" to WebserviceCallComponentTutorial.TestPerson.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE WebserviceCallComponentTutorial.Testperson_GetEditFields
	@Id int = NULL
AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;
	-----------------------------------------------------------
	-- This is a short demo of how one field can depend on a 
	-- Webservice call made using another field as input.
	-----------------------------------------------------------
	SELECT
		CONVERT(varchar(8), NULL) [BirthDate],
		CONVERT(VARCHAR(12), NULL) [PersonalNumber]
	WHERE
		1 = 0; -- (This will ensure 0 rows returned in new-mode.)
END;

New field information

Right click on the field BirthDate and create a field information with the following settings:

• Field type: Textbox
• Scripts:
  • Buttons/Label: Find Person
  • Button/Javascript: setFieldValuesFromWebService('WebserviceCallComponentTutorial.TestPersonalNumber', 'BirthDate={BirthDate}&Limit=1');

To test the new function, go to the newEdit menu item. Enter a birth date (for example 19950501) and click on the button. This will trigger a web service call with the entered birth date, limited to one result (as we are only interested in one personal number). Any fields matching whats being forwarded from the webservice call menu item are filled automatically. In our case, this will be the field PersonalNumber

Conclusion

We now have a way of triggering a server side web service call with the press of a button. Another common way of interacting with a webservice is to forward the user to the webservice call menu item after a newEdit has been saved.

If you modified the webservice as in part 2a, you may have realized that we could have created a smarter way to call the service in 2b with limit=1 and not trying to get all pages of available data. This is not part of this tutorial, but you could add a new parameter that can be used to actually limit the results to only 1 call regardless. If this service would have been used in a real context, then you would also want to validate the BirthDate parameter to require a full date. We risk getting a very large amount of data as we can ask for all test persons born in the 20th century with no check at all.

Also, in a real implementation, the table WebserviceCallComponentTutorial.TestPerson would have some kind of cleanup job or other mechanism to delete old data. Here it will just grow indefinitely.

Cleanup

You can use the script WebServiceCallTutorialCreateSchemaAndTables.sql to drop the tables and schema created in this tutorial. It will also drop the procedures named WebserviceCallComponentTutorial.Testperson_WebserviceCall and WebserviceCallComponentTutorial.Testperson_GetEditFields if they exists. If you named your procedures differently, please drop them manually. Also don't forget to delete your created menu items, field information and parameters. And last but not least, delete your created webservice and method.