3 Replies Latest reply on Oct 7, 2014 7:22 AM by Steven Hawkins

    Return json content using webservice connection

    Lunen De Lange Newbie

      Hi Everyone,

       

      I'm new to your product and I'm loving it. However there is one little issue I'm facing which I'm struggling to solve.(It's been a week now) I'm connecting to a webservice with input as JSON and returns JSON content:
      Input Parameters query : string(4000) is:
      {"queryType":"groupBy","dataSource":"wikipedia","granularity":"minute","dimensions":[ "page" ],"aggregations":[ {"type":"count", "name":"rows"}, {"type":"longSum", "fieldName":"count", "name":"edit_count"}], "filter":{ "type":"selector", "dimension":"namespace", "value":"article" }, "intervals":[ "2014-10-02T10:00/2014-10-03T00" ]}

       

      My SQL Result is:

      select * from ( exec "DataProviderView1"."runQueryJson"({"queryType":"groupBy","dataSource":"wikipedia","granularity":"minute","dimensions":[ "page" ],"aggregations":[   {"type":"count", "name":"rows"},   {"type":"longSum", "fieldName":"count", "name":"edit_count"}], "filter":{ "type":"selector", "dimension":"namespace", "value":"article" }, "intervals":[ "2014-10-02T10:00/2014-10-03T00" ]}) ) AS X_X

       

      org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "exec \"DataProviderView1\".\"runQueryJson\"([*]{[*]\"queryType\":" at line 1, column 57.

      Was expecting: "char" | "cast" | "convert" | "any" | "array_agg" | "case" | "exists" | "false" | "hour" | "insert" ...

       

      I have tested my steps by using an existing webservice called "CurrencyConvertor" from http://webservicex.net/CurrencyConvertor.asmx?WSDL and it works without any issues.

       

      My Setup steps are(I have attached the WSDL file):
      Teiid Import - "Web Service Source >> Source and View Model (SOAP)"

      Connection Profile - File From: http://demo.company.uk.com/company_webservice/DataProvider.asmx?WSDL

                                    - End Point: DataProviderSoap
                                    - Binding Type: SOAP11

       

      Service Mode: PAYLOAD

      WSDL Selection Details:

      Operation: runQueryJson

        binding: DataProviderSoap

        port: DataProviderSoap

        service: DataProvider

        id: {http://demo.intenda.uk.com/fraxses_webservice/}DataProviderSoap.runQueryJson

        input message: runQueryJsonSoapIn

        output message: runQueryJsonSoapOut

        fault names: none


      Procedure Definition:
      - Request
          Body - Schema Contents:
                        -runQueryJson
                           -sequence
                              -query

                                  -string
                     Element Info:
                        query

       

      - Response

      Body - Schema Contents:
                        -runQueryJsonResponse
                           -sequence
                              -runQueryJsonResult

                                  -string
                     Column Info:
                        Root Path - /tns:runQueryJsonResponse
                        Grid:             
                              Name: runQueryJsonResult, Data Type: string, Path: /tns:runQueryJsonResult

       

       

      If anyone can help in any way, I would be sooooo grateful.

        • 1. Re: Return json content using webservice connection
          Steven Hawkins Master

          > I'm new to your product and I'm loving it.

           

          Excellent.  Great to hear things are going well for a new user.

           

          As for the parser exception, Teiid does not accept a literal JSON, rather it must be specified as just a string literal in single quotes:

           

          exec "DataProviderView1"."runQueryJson"('{"...'

          1 of 1 people found this helpful
          • 2. Re: Return json content using webservice connection
            Lunen De Lange Newbie

            Thank you Steve! I got it working by using double quotes for some reason:"{"queryType":"groupBy","dataSource":"wikipedia","granularity":"minute","dimensions":[ "page" ],"aggregations":[   {"type":"count", "name":"rows"},   {"type":"longSum", "fieldName":"count", "name":"edit_count"}], "filter":{ "type":"selector", "dimension":"namespace", "value":"article" }, "intervals":[ "2014-10-06T10:59/2014-10-06T11" ]}"

             

            I am now trying to convert the JSON response to XML.

            My original response view query is:

            BEGIN

              SELECT t.* FROM XMLTABLE(XMLNAMESPACES('http://tempuri.org/' AS tns), '/tns:runQuery1Response' PASSING Teiid_FRX_View.runQuery1_response.xml_in COLUMNS runQuery1Result string PATH '/tns:runQuery1Result') AS t;

            END


            I've tried the following(JSONTOXML) but it does not like the format:

            BEGIN

              SELECT t.* FROM XMLTABLE(XMLNAMESPACES('http://tempuri.org/' AS tns), '/tns:runQuery1Response' PASSING JSONTOXML('tns:runQuery1Response', Teiid_FRX_View.runQuery1_response.xml_in) COLUMNS runQuery1Result string PATH '/tns:runQuery1Result') AS t;

            END

             

             

            ERROR: TEIID30070 The function 'JSONTOXML('tns:runQuery1Response', Teiid_FRX_View.runQuery1_response.xml_in)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.

             

             

            Thanks again!