[teiid-designer-users] How to import a Web Service as Relational Source Model
John Doyle
jdoyle at redhat.com
Wed May 27 09:06:58 EDT 2009
Hi Davide,
The steps you describe to create the model appear correct. The difficulty is most likely in the creation of the correct query. In the case of models generated from web services, the SQL generated by in the SQL Explorer is not likely to be sufficient.
I'm going to guess that the getAllTasksReturn table contains the data you're looking for, if that's correct, then your query will need to look something like this:
select getAllTasksReturn.ColumnA, getAllTasksReturn.ColumnB
FROM getAllTasksReturn, getAllTasksResponse, getAllTasksRequest
WHERE getAllTasksRequest.ResponseOut = getAllTasksReturn.ResponseIn **this joins the XML request to the XML response**
AND getAllTasksRequest.Param1 = 'something' **this sends a param in the XML request**
The short explanation of what is happening is that the web service connector does not handle SQL join syntax, so the Teiid engine breaks the SQL down into a single SQL statement per table. So the web service connector will service the following queries from the above SQL.
SELECT ResponseOut FROM getAllTasksRequest WHERE Param1 = 'something'
SELECT ColumnA, ColumnB FROM getAllTasksReturn WHERE ResponseIn = <ResponseOut from above query>
Does this help? Once you've figured out the query, it is often preferable to put it in a virtual layer above the physical model in order to encapsulate the additional complexity presented by the web service to relational model.
~john
----- "Davide Cerbo" <d.cerbo at pronetics.it> wrote:
> Hi all, I have an issue when I'm trying to import a Web Service as
> Relational Source Model.
> I'm doing the following steps:
> 1) Right click on Project and select "Import" from menu
> 2) Then I choose "Web Service as Relational Source Model"
> 3) After that I have pressed "Next", insert "Url" of WSDL, validate
> it, choose "Model Name" and "Location"
> 4) Finally I choose one method to import from web service then press
> finish.
>
> This operation create 3 tables in target model:
> - getAllTasksResponse
> - getAllTasksReturn
> - getAllTeasksRequest
>
> Are the above steps formerly correct?
>
> Then I add the model to a VDB and click on Execute button on VDB
> editing screen.
> In SQLExplorer perspective I click on created tables and I generate
> the following 3 SQL queries:
> 1) select ResponseOut from
> "Test"."Soap.FunctionPointsServiceService.getAllTasks.getAllTasksRequest"
> 2) select ResponseIn, mmid from
> "Test"."Soap.FunctionPointsServiceService.getAllTasks.getAllTasksResponse"
> 3) select getAllTasksResponse_mmid, ResponseIn from
> "Test"."Soap.FunctionPointsServiceService.getAllTasks.getAllTasksReturn"
>
> The first query runs without errors, but the resultset is empty.
> Second and third one give me this exception:
>
> com.metamatrix.jdbc.MMSQLException: Group has an access pattern
> which
> has not been met: group(s)
>
> [Test.Soap.FunctionPointsServiceService.getAllTasks.getAllTasksReturn];
> access pattern(s) [Access Pattern: Unsatisfied
>
> [Soap.FunctionPointsServiceService.getAllTasks.getAllTasksReturn.ResponseIn]
> History
> [[Soap.FunctionPointsServiceService.getAllTasks.getAllTasksReturn.ResponseIn]]]
> at
> com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:82)
> at
> com.metamatrix.jdbc.MMSQLException.create(MMSQLException.java:63)
> at
> com.metamatrix.jdbc.MMStatement.executeSql(MMStatement.java:424)
> at
> com.metamatrix.jdbc.MMStatement.execute(MMStatement.java:326)
> at
> net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.processQuery(SqlExecProgress.java:192)
> at
> net.sourceforge.sqlexplorer.sqlpanel.SqlExecProgress.run(SqlExecProgress.java:121)
> at
> org.eclipse.jface.operation.ModalContext$ModalContextThread.run(ModalContext.java:121)
> Caused by: [QueryPlannerException]Group has an access pattern which
> has not been met: group(s)
>
> [Test.Soap.FunctionPointsServiceService.getAllTasks.getAllTasksReturn];
> access pattern(s) [Access Pattern: Unsatisfied
>
> [Soap.FunctionPointsServiceService.getAllTasks.getAllTasksReturn.ResponseIn]
> History
> [[Soap.FunctionPointsServiceService.getAllTasks.getAllTasksReturn.ResponseIn]]]
> at
> com.metamatrix.query.optimizer.relational.rules.RuleAccessPatternValidation.validateAccessPatterns(RuleAccessPatternValidation.java:120)
> at
> com.metamatrix.query.optimizer.relational.rules.RuleAccessPatternValidation.validateAccessPatterns(RuleAccessPatternValidation.java:76)
> at
> com.metamatrix.query.optimizer.relational.rules.RuleAccessPatternValidation.execute(RuleAccessPatternValidation.java:68)
> at
> com.metamatrix.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:400)
> at
> com.metamatrix.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:144)
> at
> com.metamatrix.query.optimizer.QueryOptimizer.optimize(QueryOptimizer.java:241)
> at
> com.metamatrix.query.optimizer.QueryOptimizer.recursiveOptimize(QueryOptimizer.java:215)
> at
> com.metamatrix.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:123)
> at
> org.teiid.dqp.internal.process.Request.generatePlan(Request.java:534)
> at
> org.teiid.dqp.internal.process.Request.processRequest(Request.java:623)
> at
> org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:373)
> at
> org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:206)
> at
> org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
> at java.lang.Thread.run(Thread.java:619)
>
>
> Have I make some mistakes? Have you experienced these kind of
> problems? let me know
> Thanks in advance.
>
> --
> --
> Davide Cerbo
> -----------
> http://davide.cerbo.born-to-co.de
> http://jesty.it
> -----------
> http://www.pronetics.it
> http://www.sourcesense.com
> -----------
> _______________________________________________
> teiid-designer-users mailing list
> teiid-designer-users at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/teiid-designer-users
More information about the teiid-designer-users
mailing list