12 Replies Latest reply: Jun 9, 2016 8:10 AM by Don Lee RSS

    REST CONNECTOR, Override request body

    Pablo Labbe

      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 ?

        • Re: REST CONNECTOR, Override request body
          Björn Wedbratt

          Hi Pablo,

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

            • Re: REST CONNECTOR, Override request body
              Pablo Labbe

              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

                • Re: REST CONNECTOR, Override request body
                  Björn 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...

                    • Re: REST CONNECTOR, Override request body
                      Pablo Labbe

                      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 ?

                        • Re: REST CONNECTOR, Override request body
                          Björn 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

                            • Re: REST CONNECTOR, Override request body
                              Pablo Labbe

                              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

                                • Re: REST CONNECTOR, Override request body
                                  Björn Wedbratt

                                  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:

                                   

                                  [code]
                                  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

                      • Re: REST CONNECTOR, Override request body
                        Pablo Labbe

                        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'


                        • Re: REST CONNECTOR, Override request body
                          Don Lee

                          Hi Guys,

                           

                          I hope my question is relevant to this topic, I would like to ask help on how can I get the data from GA using Rest Connector. I already got the connection after I reload it I only gets the headers or metric data.

                           

                          I would like to get also the rows data based from below figure.

                           

                          Thanks in advance

                           

                           

                           

                          ga1.png