11 Replies Latest reply on Apr 19, 2018 8:13 AM by Ramesh Reddy

    How to pass sql query conditions to REST API

    N F Newbie

      Hello,

      Use Teiid Embedded, REST service as data sourec. Using Teiid "web service" resource-adapter and "ws" translator. We would like Teiid to pass the client sql query conditions to the REST API. For example,

      From Squirrel client, If I run the sql query:

        select * from users where region='US'

      We would like Teiid to pass the condition "region='US'" to the REST call:

        http://host:port/TestProd/users?_filter=(region eq 'US')

      Is it possible to configure the dynamic VDB to achieve this? How?

       

      Thank you very much for your help!

        • 1. Re: How to pass sql query conditions to REST API
          Ramesh Reddy Master

          N F,

           

          Currently there is no out of the box way to generate REST service (OData or Custom REST) based on a VDB in Embedded. To provide the REST service, one needs a JEE/Servlet environment, since those can not be guaranteed it is not currently provided. I am sure with some amount of work, we can generate utilities to generate .WAR files for OData or Custom REST given the VDB. 

           

          Then question becomes how you are designing your REST service? If you are developing on your own, then passing a parameter to the query is up to YOUR design.

          If it is GET call,

           

          If this POST, then you pass it in the BODY content.

           

          HTH

           

          Ramesh..

          • 2. Re: How to pass sql query conditions to REST API
            N F Newbie

            Hi Ramesh,

            Thank you very much for the prompt reply.

            We already have a REST service running on Tomcat.

            Our REST API looks like this:

              GET http://localhost:8080/TestProd/Users

              GET http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

            We would like to use sql query to access the data source ( REST service ), and use Teiid embedded as the middleware.

            We would like to push the sql query parameters to the REST API.

             

            My current webservice-vdb.xml looks like this:

             

            <?xml version="1.0" encoding="UTF-8" standalone="yes"?>

            <vdb name="webservice" version="4">

                <property name="UseConnectorMetadata" value="true" />

                <model name="UserSource">

                    <source name="webservice" translator-name="ws" connection-jndi-name="java:/UserRESTWebSvcSource"/>

                </model>

                  <model name="Users" type="VIRTUAL">

                    <metadata type="DDL"><![CDATA[       

                        CREATE  VIEW UsersView (

                        @id varchar(100) , region varchar(100) )

                       AS

                      SELECT

                        A.@id, A.region

                      FROM

                    (EXEC UserSource.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users', 'TRUE')) AS f, XMLTABLE('/response/results/documents'     PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;           

                    ]]>

                    </metadata>

                </model>

            </vdb>

             

            When I run the sql query from Squirrel client:

              select * from usersview where region='US'

            Teiid is calling the REST API:

              http://localhost:8080/TestProd/Users

            Rather than

              http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

             

            What can I do to push the filter parameter to the REST API:?

              http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

            Note: 'US' is a variable value.

             

            Thank you very much for your help

            • 3. Re: How to pass sql query conditions to REST API
              Ramesh Reddy Master

              N F,

               

              OK, I thought you are trying expose the VDB as REST web services before, thus my comment as such above. From this comment it is evident that, you are trying to consume from the REST service, which is fine.

               

              Teiid can do what you are saying in your example at the end, but you would have to write a custom translator for it, where it has smarts to convert in coming SQL query into a YOUR own REST API. Obviously the REST service you are consuming is not based on any standard specification like OData or Swagger for which we can automatically do such conversions of criteria into query parameter. In Teiid's OData translator that is exactly what we do, for example see teiid/connectors/translator-odata4 at master · teiid/teiid · GitHub

               

              Actually depending upon who wrote the your "TestProd" service, it looks to be something very similar to OData (at least the  _filter clause, OData is pretty extensive), if that service is just fronting a Database, you can remove that put Teiid in that place and expose that database as OData service without any coding, then come back and consume it using Teiid Embedded.

               

              HTH


              Ramesh..

              1 of 1 people found this helpful
              • 4. Re: How to pass sql query conditions to REST API
                N F Newbie

                Hi Ramesh,

                Your answer helped a lot. I understand the Odata translator's capability. Our existing REST API is not strictly following the Odata syntax.

                I have another question:

                If I change the REST endpoint in the vdb from:

                  http://localhost:8080/TestProd/Users

                to

                  http://localhost:8080/TestProd/Users?_filter=(region eq [variable])

                Eg:

                  (EXEC UserSource.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users?_filter=(region eq [variable])', 'TRUE')) AS f, XMLTABLE('/response/results/documents'     PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;  

                 

                Without writing a custom translator, Is there a way to pass the client sql query parameter ( for example 'US' ) to the REST API? Eg. substitute the [variable] with value 'US' automatically when I run the sql query from Squirrel client:?

                  select * from usersview where region='US'

                So the called REST API will be:

                http://localhost:8080/TestProd/Users?_filter=(region eq 'US')

                 

                Thank you very much for your help

                • 5. Re: How to pass sql query conditions to REST API
                  Ramesh Reddy Master

                  N F,

                   

                  It is not possible to define a VIEW and do the automatic propagation of the condition, but you can define the stored procedure and then you pass the parameter. You can see a example using Designer here [1]. You can even do the same using XML based vdb as

                   

                  <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
                  <vdb name="webservice" version="1">
                      <model name="OnWeb">
                          <source name="webservice" translator-name="rest" connection-jndi-name="java:/web-ds"/>
                      </model>
                      <model name="test" type="VIRTUAL">
                          <metadata type="DDL"><![CDATA[
                                  CREATE VIRTUAL PROCEDURE getUsers(region varchar(50)) RETURNS (name varchar(25), region varchar(50)) AS
                                  BEGIN
                                     SELECT A.@id as name, A.region as region
                                     FROM
                                       (EXEC UserSource.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users?_filter=(region eq '||test.getUsers.region||')', 'TRUE')) AS f,
                                      XMLTABLE('/response/results/documents' PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;     
                                  END
                            ]]>       
                          </metadata>
                      </model>
                      <translator name="rest" type="ws">
                          <property name="DefaultBinding" value="HTTP"/>
                      </translator>
                  </vdb>
                  

                  (BTW, I did not test the above, but it will be very close it )

                   

                  Then you can execute like

                   

                  select * from (exec test.getUsers('US'))
                  

                   

                   

                  HTH


                  Ramesh..

                   

                  [1] How to Consume a JSON REST Web Service in Teiid Designer

                  1 of 1 people found this helpful
                  • 6. Re: How to pass sql query conditions to REST API
                    N F Newbie

                    Hi Ramesh,

                    When I try to follow your example to use the stored procedure, I get the following exception when execute the query:

                         select * from (exec test.getUsers('USA'))

                     

                    How can I fix this?

                     

                    log4j:WARN No appenders could be found for logger (org.jboss.logging).

                    log4j:WARN Please initialize the log4j system properly.

                    org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "'USA'))[*]" at line 1, column 41.

                    Was expecting: "as" | id

                      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

                      at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

                      at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:721)

                      at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)

                      at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:560)

                      at org.teiid.client.util.ResultsFuture.addCompletionListener(ResultsFuture.java:148)

                      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:556)

                      at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:1063)

                      at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:338)

                      at org.teiid.example.JDBCUtils.execute(JDBCUtils.java:85)

                      at org.teiid.example.TeiidEmbeddedRestWebServiceDataSource.main(TeiidEmbeddedRestWebServiceDataSource.java:75)

                    Caused by: org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "'USA'))[*]" at line 1, column 41.

                    Was expecting: "as" | id

                      at org.teiid.query.parser.QueryParser.convertParserException(QueryParser.java:214)

                      at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:164)

                      at org.teiid.query.parser.QueryParser.parseCommand(QueryParser.java:140)

                      at org.teiid.dqp.internal.process.Request.parseCommand(Request.java:305)

                      at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:399)

                      at org.teiid.dqp.internal.process.Request.processRequest(Request.java:473)

                      at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:642)

                      at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:337)

                      at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

                      at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

                      at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:313)

                      at org.teiid.dqp.internal.process.DQPCore.executeRequest(DQPCore.java:245)

                      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

                      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

                      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

                      at java.lang.reflect.Method.invoke(Method.java:497)

                      at org.teiid.transport.LocalServerConnection$1$1.call(LocalServerConnection.java:177)

                      at java.util.concurrent.FutureTask.run(FutureTask.java:266)

                      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

                      at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:260)

                      at org.teiid.transport.LocalServerConnection$1.invoke(LocalServerConnection.java:175)

                      at com.sun.proxy.$Proxy13.executeRequest(Unknown Source)

                      at org.teiid.jdbc.StatementImpl.execute(StatementImpl.java:688)

                      at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:554)

                      ... 4 more

                    SQL: select * from (exec test.getUsers('USA'))

                     

                     

                    Thank you very much again for your help

                    • 7. Re: How to pass sql query conditions to REST API
                      Ramesh Reddy Master

                      The VDB is corrected as below

                      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>  
                      <vdb name="webservice" version="1">  
                          <model name="OnWeb">  
                              <property name="importer.importWSDL" value="false"/>  
                              <source name="webservice" translator-name="rest" connection-jndi-name="java:/web-ds"/>  
                          </model>  
                          <model name="test" type="VIRTUAL">  
                              <metadata type="DDL"><![CDATA[ 
                                      CREATE VIRTUAL PROCEDURE getUsers(region varchar(50)) RETURNS (name varchar(25), region varchar(50)) AS 
                                      BEGIN 
                                         SELECT A.@id as name, A.region as region 
                                         FROM 
                                           (EXEC OnWeb.invokeHttp('GET', null, 'http://localhost:8080/TestProd/Users?_filter=(region eq '||test.getUsers.region||')', 'TRUE')) AS f, 
                                          XMLTABLE('/response/results/documents' PASSING JSONTOXML('response', f.result) COLUMNS @id string, region string ) AS A;      
                                      END 
                                ]]>         
                              </metadata>  
                          </model>  
                          <translator name="rest" type="ws">  
                              <property name="DefaultBinding" value="HTTP"/>  
                          </translator>  
                      </vdb> 
                      

                       

                      The execute "select * from (exec test.getUsers('USA')) as A"

                      • 8. Re: How to pass sql query conditions to REST API
                        Murty Kirlampalli Newbie

                        Hi Ramesh,

                         

                        I am trying to understand this response from you. What is the JSON(/response/results/documents) format for this and how it looks like? Please provide sample JSON data here. I really appreciate that.

                         

                        Thanks

                        Murty

                        • 9. Re: How to pass sql query conditions to REST API
                          Ramesh Reddy Master

                          Parsing of the JSON document is currently done after conversion from XML as shown in above example, there is no direct parsing of JSON document currently in Teiid. XML gives XPATH/XQUERY help to extract individual element content from the document, there is no good facility for JSON. So, there is no direct parsing JSON example. The above is a JSON example.

                          • 10. Re: How to pass sql query conditions to REST API
                            Murty Kirlampalli Newbie

                            I need to understand and visualize for what format of JSON data the above works fine. I just need JSON sample data.

                             

                             

                            I appreciate your help.

                            • 11. Re: How to pass sql query conditions to REST API
                              Ramesh Reddy Master

                              Something like close to

                               

                              { results: {

                              documents:

                              [

                              {id: "1",

                              region : "foo"

                              },

                              {id:"2",

                              region :"bar"

                              }

                              ]

                              }

                              }