Knowledge

Rest Connector shows "Has no value, or misplaced semicolon." after upgrading to QlikView November 2017

Article Number: 000048248 | Last Modified: 2018/06/12

Description

After upgrading our Test QlikView environment from 11.2 SR14 to 12.2 Nov 2017 the REST connector stopped working as expected. We have the following connection string that used to work with QlikView 11.2 but does not with 12.2. "has no value, or misplaced semicolon. CUSTOM CONNECT TO ***" is shown as the error.

Environments:

QlikView 12.20 IR through 12.20 SR3 (QlikView November 2017)

Cause

This is the result of curly braces being stripped from values in a connection string when passed to a connector (REST connector in this case). This affects the requestBody when using the POST method as the JSON requires the use of Curly Braces.

Resolution

This issue is expected to be fixed in the November 2017 SR4 build.

Immediate actions if a solution is needed in place.

There is an available workaround that requires having an older version of QlikView available:
• Create/Recreate your REST connection in the older version of QlikView (I used QlikView Desktop 11.20 SR16)
• Select the Data you wish to pull from this source
• Copy/Paste the generated script, including the Connection String, into the new version of QlikView November
• Here are the three most important steps to get the connection working in QlikView November:

• Remove the 'requestBody=' portion of the connection string e.g

requestBody={%3Name%3:%3loegismose-qis%3,%3Key%3:%3o3c+TphXpZkEgG12NZuW60eD3RJe6qiFA6VYdOqed5o%2%3};

• Create a BODY override by adding 'WITH CONNECTION' at the end of the 'RestConnectorMasterTable' Table. The last line should resemble
FROM JSON (wrap on) "root" WITH CONNECTION(
• Paste the unmodified BODY used to make the connection into the newly created open and closed parenthesis, making sure to add BODY on the new line. It also is necessary to replace any double quotes (") with two sets of double quotes ("")
e.g

FROM JSON (wrap on) "root" WITH CONNECTION(

BODY "{""Name"":""loegismose-qis"",""Key"":""o3c+TphXpZkEgG12NZuW60eD3RJe6qiFA6VYdOqed5o=""}" );

This will let us move the BODY out of the Connection String in order to avoid this issue with curly braces

tips:
• When the REST Connector creates the BODY, it will replace certain escape characters found with their equivalent value. So in my example " is replaced with %3 and the = is replaced with %2. This is why I recommend just using the original body used to create the connection string at the beginning as this only requires replacing " with ""
• Your new body must be fully contained in one line so if your JSON was

{
    "user": {
        "name": "abc",
        "fx": {
            "message": {
                "color": "red"
            },
            "user": {
                "color": "blue"
            }
        }
    },
    "timestamp": "2013-10-04T08: 10: 41+0100",
    "message": "I"mABC..",
    "nanotime": "19993363098581330"
}
it will end up looking like
FROM JSON (wrap on) "root" WITH CONNECTION(
BODY "{""user"": {""name"": ""abc"",""fx"": {""message"": {""color"": ""red""},""user"": {""color"": ""blue""}}},""timestamp"": ""2013-10-04T08: 10: 41+0100"",""message"": ""I'mABC.."",""nanotime"": ""19993363098581330""}" );

Get Answers

Find Answers

Qlik Community

Collaborate with over 60,000 Qlik technologists and members around the world to get answers to your questions, and maximize success.

Join Us

Get Support

Have a Question?

Search Qlik's Support Knowledge database or request assisted support for highly complex issues.

Submit a case

Talk with a Representative

Critical Issues

Experiencing a serious issue, please contact us by phone. For Data Integration related issues please refer to your onboarding documentation for current phone number.

Call Us