Qlik Community

Qlik Sense Data Connectivity

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Qlik REST Connector: POST method: How to UPLOAD multi-line literals, i.e. CSV file?

Dear all,

the situation is as follows:

In our company, the finance department is using a financial consolidation software that provides as ReST-like interface.

In most cases, the "Qlik REST Connector" works perfectly when it comes to pulling data with the "GET" method.

But for some special data, the ReST-like interface expects the user to upload a tabulator-separated MULIT-LINE CSV file, i.e.

- one header line that specifies the tables of the internal (proprietary) database of the financial consolidation software and

- one or more data lines that specifiy the field values for which a financial amount is about to be looked up and delivered back as a result.

As a result, the string that represents the CSV data must have at least one LINE BREAK, otherwise the ReST-like interface of the financial consolidation software will not accept the request.

The problem here is that the "Qlik REST Connector" does not seem to support multi-line literals, i.e. a string with one or more line breaks!

LET strRequestBody =     'AccountID' & Chr(9) &    // Chr(9) is an escaped 'tabulator' button press

                                        'ConsolidationElementID' & Chr(9) &

                                        'DataLevelID' & Chr(9) &

                                        'AdjustmentLevelID' & Chr(9) &

                                        'PeriodFrom' & Chr(9) &

                                        'PeriodTo' & Chr(13) &    // Chr(13) is an escaped 'carriage return' button press

                                        Chr(10) &                 // Chr(10) is an escaped 'new line' button press

                                        // Now the value for each column follows (it is only one column in the 'CSV' here to keep it simple)

                                        '2407821' & Chr(9) &

                                        '2475462' & Chr(9) &

                                        '1201' & Chr(9) &

                                        '1120' & Chr(9) &

                                        '2017-01' & Chr(9) &

                                        '2017-01' & Chr(13) &

                                        Chr(10);

trace Content of variable 'strRequestBody':

$(strRequestBody);

SQL SELECT

     ...

WITH CONNECTION (

                    URL "$(strReSTAPICallReadCellValues)")

                    HTTPHEADER "Content-Type" "text/csv",

                    BODY "$(strRequestBody)"

                );

I am getting the following error message:

I have already tried to either only use Char(10) or Char(13), but without success.

1. Am I doing something wrong?

2. If not:

    Do you know any workarounds to overcome this (in my opinion ridiculous) limitation of the "Qlik REST Connector"?

Notes:

- The POST works perfectly with "Postman"

- The POST works perfectly in a standalone C# application I developed for general testing (only difference there is that tabulator, carriage return and new line are escaped by using '\t', '\r' and '\n')

- I am using Qlik Sense version "Desktop September 2017".

Best regards,

the Fitness Captain

5 Replies
Highlighted
Specialist III
Specialist III

Hi Captian,

Did you get any solution of this concern ?

Highlighted
Contributor
Contributor

Hello Cap,

Can you try having your request body like this,

LET strRequestBody =    '{"AccountID":2407821,                        

                                        "ConsolidationElementID" :  2475462,

                                                  ........

                                        "PeriodFrom" : "2017-01",

                                         "PeriodTo":"2017-01",

                                                  .........

                                      }'

And also replace chr(10) & Chr(13) in any parameter value to avoid "Multi-line Literal issue"..

Highlighted
Contributor
Contributor

Hello Sudharanbabu Sampath,

no that did not work because the ReST interface of the application I am trying to access does not expect anything else than the word "AccountID" as the first parameter and nothing else - I even asked the vendor of the application I am trying to access and they confirmed that their ReST-like interface only acceps CSV strings as a POST body and nothing else.

But I found out something else that is very interesting:

The Qlik ReST connector actually CAN handle linebreaks in the POST body, but not when one tries to implement them dynamically in the script via the "WITH CONNECTION ( BODY "..."); parameter - so it is cleary a bug in the PARSER that handles the "WITH CONNECTION ( BODY "..."); parameter!!!!

Example:

The following example of performing a POST with CSV content in the body works, but only when the data is previously entered in a text editor as tabulator button press in the GUI of the Qlik ReST Connector only results in selecting the next GUI field ... (and escaping parameters also did not work for me):

Manually_working_v2.png

Now, I build the exact same CSV string in the script:

Dynamically_not_working.png

When entering the SAME information in the string

WITH CONNECTION ( ..., BODY "$(strCSVBody)");     , then I get the known reply:

"Multi-line string literals are not supported."

From my point of view, the problem is that the Qlik developers use the same PARSER for the BODY parameter as for the whole SQL statement. While a surpressing of linebreaks makes sense for the rest of the SQL statement, it does NOT for the BODY parameter as proved above when entering the string in the GUI!

In other words: Pasting a string into the GUI works, but the dynamic parameter in the script does not (nobody would care if it was vice-versa) ...

Best regards,

the Fitness Captain

Highlighted
Contributor
Contributor

For all of you out there that want to see the bug happen (just tested it myself):

- Google for an API post test and find the following address:

https://httpbin.org/post

- In Qlik Sense, create a ReST data source to that addres with METHOD set to POST

- As request body, enter something like this:

test

11111

(IMPORTANT: Use a LINE BREAK!)

- Select Authentication Schema "Anonymous"

- Save the connection (this will WORK as set line breaks in the GUI are supported) under the name "POST_TEST_QLIK_SUPPORT"

- The following Qlik Script will WORK as the variable "strRequestBody" does NOT contain line breaks:

Let strUriPostTest = 'https://httpbin.org/post';

//Let strRequestBody = 'Test' & Chr(13) & Chr(10) & '111';

Let strRequestBody = 'Test' & '111';

LIB CONNECT TO 'POST_TEST_QLIK_SUPPORT';

RestConnectorMasterTable:

SQL SELECT

    "data",

    "json",

    "origin",

    "url",

    "__KEY_root",

    (SELECT

        "__FK_args"

    FROM "args" FK "__FK_args"),

    (SELECT

        "__FK_files"

    FROM "files" FK "__FK_files"),

    (SELECT

        "__FK_form"

    FROM "form" FK "__FK_form"),

    (SELECT

        "Accept-Encoding",

        "Connection",

        "Content-Length",

        "Host",

        "__FK_headers"

    FROM "headers" FK "__FK_headers")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION

    (

        Url "$(strUriPostTest)"

    ,    HTTPHEADER "Content-Type" "text/csv"

    ,    BODY "$(strRequestBody)"

    )

;

Exit Script;

When you now change variable 'strRequestBody' to the following code:

Let strRequestBody = 'Test' & Chr(13) & Chr(10) & '111';

, then the reported multi-line literal error appears.

By this it is plain to see that the error is not caused by the API, but by the Qlik ReST connector.

Best regards,

The Fitness Captain

Highlighted
Partner
Partner

Has anyone found a solution to this?