REST CONNECTOR, Override request body

[pablolabbe]

I'm have a connection to an external webservice using the new Qlik Rest Connector for Qliksense.

The service requires parameters in the request body to authenticate and to set some query parameters like the initial and end date.

Now I have to automate the extraction to extract data from last day, so how can I override the request body in the script to set a new date interval ?

[Bjorn_Wedbratt]

Hi Pablo,

Have you tried using a variable in the script to dynamically update requestBody in the Connect string?

[pablolabbe]

I made some tests with different parameters inside WITH CONNECTION CLAUSE.

adding a BODY clause + variable with my body definition appear to be accepted by the sintax but server returns error.

RestConnectorMasterTable << RestConnectorMasterTable Erro de script: QVX_UNEXPECTED_END_OF_DATA: HTTP protocol error 400 (Bad Request):

The clause

WITH CONNECTION (

URL "http://wsgslog.globalsearch.com.br/V2050/posicoes.asmx",

HTTPHEADER "Host" "wsgslog.globalsearch.com.br",

HTTPHEADER "Content-Type" "text/xml; charset=utf-8",

HTTPHEADER "SOAPAction"  "http://systemsat.gslog.webservice/Lista_UltimasPosicoes",

BODY "$(vRequestBody)"

)

;

The request body variable

LET vRequestBody = '';

let vRequestBody = vRequestBody & '<?xml version="1.0" encoding="utf-8"?>';

let vRequestBody = vRequestBody & '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">';

let vRequestBody = vRequestBody & '<soap:Body>';

let vRequestBody = vRequestBody & '<Lista_UltimasPosicoes xmlns="http://systemsat.gslog.webservice/">';

let vRequestBody = vRequestBody & '<EmpCliente>xxxx</EmpCliente>';

let vRequestBody = vRequestBody & '</Lista_UltimasPosicoes>';

let vRequestBody = vRequestBody & '</soap:Body>';

let vRequestBody = vRequestBody & '</soap:Envelope>';

let vRequestBody = replace(vRequestBody,'"','&quot;');

If I remove the BODY parameter from WITH CONNECTION CLAUSE then the connection works fine.

Maybe I have made a mistake generating the request body variable but I can´t figure out what is causing the error.

Regards,

Pablo

[Bjorn_Wedbratt]

Hmm, but that's a SOAP request, not REST, or am I missing something here? Do you have the full script you could share?

Did you check with Fiddler or similar tool to see the actual request being sent? Could it be that you need to set BODY as:

BODY "'$(vRequestBody)'" i.e. single-quote the variable

Note sure, but maybe '$(vRequestBody)' would be enough...

[pablolabbe]

Checked the request with fiddler. The format appear to be correct but the webservices reject the body with escaped character.

I tried to send the body without escaping and withou quoting the variable but the connector rejects with an unknown substring error . Checked the following variations and errors:

BODY $(vRequestBody)

Unknown substring, string, or value at (68,5): '<?xml version="1.0" encoding="utf-8'

BODY '$(vRequestBody)'

Unknown substring, string, or value at (68,5): ''<?xml version="1.0" encoding="utf-'

BODY "'$(vRequestBody)'"

Unknown substring, string, or value at (68,23): '.0" encoding="utf-8"?><soap:Envelop'

[pablolabbe]

Hi Bjorn,

  I've tried with  BODY "'$(vRequestBody)'" , but have no luck. 

  Do I need to escape the quote character in my request body variable ?

[Bjorn_Wedbratt]

Hi Pablo,

Sorry about the delay on a response. Reality knocked on the door

Anyway I raised this question internally as I haven't found a way to even include BODY in the WITH CONNECTION statement and I cannot find any documentation supporting if this a valid setting.

If I try adding BODY "" I always end up with an error "Unexpected lexem".

If I remove BODY from WITH CONNECTION it works OK with HTTPHEADER as described in the help section.

When trying it out I'm using GlobalWeather Web Service as I'm not sure I have access to the service you provided above (guess you need to supply a "EmpCliente" and password according to the scheme).

I will get back as soon as I got a response for you

[pablolabbe]

Hi Bjorn,

   I made a new test using Fiddler and it shows that BODY clause works. It put the xml into the request.

  The BODY parameter is accepted in the GA release not in the beta release. When running the Beta version, I got the same issue with invalid Lexem. Are you running the last version ?

   The web service that I'm connecting requires some parameters for authentication. As these parameters are the user name and password, I can´t share it. So we can try with a public web service.

Regards,

Pablo

[Bjorn_Wedbratt · Accepted solution]

Hi Pablo,

you were right! For unknown reasons I was still running the beta version. Uninstalled and reinstalled the GA and after some playing around with escaping the quotes I got it to work as well. Here's my working code snippet against a public webservice:

Let vRequestBody ='';
let vRequestBody = vRequestBody &'<?xml version="1.0" encoding="utf-8"?>';
let vRequestBody = vRequestBody &'<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope"> ';
let vRequestBody = vRequestBody &'<soap12:Body>';
let vRequestBody = vRequestBody &'<GetWeather xmlns="http://www.webserviceX.NET">';
let vRequestBody = vRequestBody &'<CityName>Lund</CityName>';
let vRequestBody = vRequestBody &'<CountryName></CountryName>';
let vRequestBody = vRequestBody &'</GetWeather>';
let vRequestBody = vRequestBody &'</soap12:Body>';
let vRequestBody = vRequestBody &'</soap12:Envelope>';
let vRequestBody = replace(vRequestBody,'"', chr(34)&chr(34));


RestConnectorMasterTable:
SQL SELECT 
  "__KEY_Envelope",
  (SELECT 
  (SELECT 
  "GetWeatherResult",
  "__FK_GetWeatherResponse"
  FROM "GetWeatherResponse" FK "__FK_GetWeatherResponse")
  FROM "Body" PK "__KEY_Body" FK "__FK_Body")
FROM XML "Envelope" PK "__KEY_Envelope"
WITH CONNECTION (
BODY "$(vRequestBody)"
);

Glad you solved your issues as well

[pablolabbe]

Hi Bjorn,

  Using chr(34)&chr(34) in the replace function do the trick.

  I got it to work to me too.

Thank you !