Search code examples
restqliksenseservicenow-rest-api

Dynamic REST connection to Service Now in Qlik Sense

I'm trying to unify all the REST connections created in QlikSense to ServiceNow since I have to create a new connection every time I pull out data from a new table. This will make difficult to manage all my connections in the future and because of that I'm trying to have only one connection that will override specific parameters depending on the required table by using the "WITH CONNECTION" function.

I tried passing the parameters like what's on the code below but after loading the data there's nothing in the table:

LET vURL = "http://example.org/.../.../.../nameofthetable?";

RestConnectorMasterTable:
SQL SELECT 
    ...my fields to query from Service NOW...
FROM JSON (wrap off) "result" PK "__KEY_result"
WITH CONNECTION (
URL "$(vURL)",
QUERY "timeout" "900",
QUERY "method" "GET",
QUERY "httpProtocol" "1.1",
QUERY "isKeepAlive" "true",
QUERY "bodyEncoding" "UTF-8",
QUERY "sendExpect100Continue" "true",
QUERY "autoDetectResponseType" "true",
QUERY "queryParameters" "sysparm_query%2u_stateINin_progress,Closed,New%1sysparm_fields%2sys_created_by,%1sysparm_display_value%2true",
QUERY "addMissingQueryParametersToFinalRequest" "false",
QUERY "PaginationType" "Offset",
QUERY "OffsetStartField" "sysparm_offset",
QUERY "IsOffsetStartFieldHeader" "false",
QUERY "OffsetStartFieldValue" "0",
QUERY "OffsetCountFieldName" "sysparm_limit",
QUERY "IsOffsetCountFieldHeader" "false",
QUERY "OffsetCountValue" "10000",
QUERY "OffsetTotalPath" "X-Total-Count",
QUERY "IsOffsetTotalPathHeader" "true",
QUERY "allowResponseHeaders" "false",
QUERY "allowHttpsOnly" "true",
QUERY "useProxy" "false",
QUERY "proxyBypassOnLocal" "false",
QUERY "proxyUseDefaultCredentials" "true"
);

I got no error but I don't know if that's the correct syntax to load data or even if this is the right approach I should be using.

I have seen some other people posted similar questions to this but none of them explained in detail how did they solved it.

Can anyone help me on this, as I already asked in the QlikSense community with no luck so maybe there's someone in here.

References:

1.- Dynamically-load-and-fill-variables-from-a-table-through-a-loop

2.- REST Connector - WITH CONNECTION Syntax

3.- WITH CONNECTION keyword

4.- Passing a parameter to REST connector URL

5.- REST API Connector - Multi-table

Solution

  • The WITH CONNECTION part can alter some parameters of the connection but not all of them. The http parameters can be changed there like: the url, headers, query parameters and the body. You cant change the rest like: the method (get or post), timeout, http protocol etc. These properties are connection specific and they have to be updated on connection level (when manually editing an connection or updating it via Repository API)

    Have a look at the documentation about the WITH CONNECTION keyword

    btw the QUERY command will just add a query parameter in the url before sending the request. And thats why you are not getting any data. For example

    let vURL = 'http://example.com'

RestConnectorMasterTable:
SQL SELECT 
    ...my fields to query from Service NOW...
FROM JSON (wrap off) "result" PK "__KEY_result"
WITH CONNECTION (
URL "$(vURL)",
QUERY "type" "XML",
QUERY "subtype" "text"
);

    will alter the url before sending it and the actual url will be http://example.com?type=XML&subtype=text