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

    Return json content using webservice connection

    lunendl
    lunendl Oct 6, 2014 6:30 AM

      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
          shawkins
          shawkins Oct 6, 2014 9:48 AM (in response to lunendl)

          > 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
            • Actions
          • 2. Re: Return json content using webservice connection
            lunendl
            lunendl Oct 7, 2014 5:22 AM (in response to shawkins)

            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!

              • Actions
            • 3. Re: Return json content using webservice connection
              shawkins
              shawkins Oct 7, 2014 7:22 AM (in response to lunendl)

              Continued on XML data (type as VARCHAR) to XML Table(true XML)

                • Actions