-
1. Re: How to pass sql query conditions to REST API
rareddy Oct 12, 2016 9:18 AM (in response to ninglif)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,
- you can pass as query parameter like http://host:port/users?_filter=value
- or a PATH parameter http://host:port/users/value
- or HEADER
If this POST, then you pass it in the BODY content.
HTH
Ramesh..
-
2. Re: How to pass sql query conditions to REST API
ninglif Oct 12, 2016 4:19 PM (in response to rareddy)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
rareddy Oct 12, 2016 5:32 PM (in response to ninglif)1 of 1 people found this helpfulN 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.. -
4. Re: How to pass sql query conditions to REST API
ninglif Oct 12, 2016 6:27 PM (in response to rareddy)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
rareddy Oct 13, 2016 12:11 PM (in response to ninglif)1 of 1 people found this helpfulN 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
-
6. Re: How to pass sql query conditions to REST API
ninglif Oct 18, 2016 11:54 AM (in response to rareddy)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
rareddy Oct 18, 2016 6:16 PM (in response to ninglif)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
klmurty Apr 2, 2018 4:53 AM (in response to rareddy)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
rareddy Apr 2, 2018 9:42 AM (in response to klmurty)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
klmurty Apr 18, 2018 11:32 PM (in response to ninglif)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
rareddy Apr 19, 2018 8:13 AM (in response to klmurty)Something like close to
{ results: {
documents:
[
{id: "1",
region : "foo"
},
{id:"2",
region :"bar"
}
]
}
}