How to create a model created from a WS

[carlossc]

I have created a Web Service as a Relational Source Model and bind it to a XML-Relational SOAP connector but I don't know how to query the source model.

I've followed these steps to create the model:

- Import a Web Service as a Relational Source Model (using the "Import")

This WS has only one operation: "average_monthly_revenue" and it returns pairs of ("TAXID", "REVENUE")

Teiid creates three "Base Tables": getAvgMonthlyRevenueRequest, getAvgMonthlyRevenueResponse and item_AVERAGE_MONTHLY_REVENUEReturnRow.

It also creates an XML-Relational SOAP connector.

- Add the model to a Virtual Database and bind the source model to the connector.

At this point, I don't know how to query this WS: When click "Execute" in the Virtual Database dialog I've tried to select data from the three tables but I always get an error.

[jdoyle]

I haven't published any doc on this importer connector yet, but I think I can guild you through conceptually.

getAvgMonthlyRevenueRequest contains fields for parameters to the service, so you want to supply values in your SQL to the fields of this table in the WHERE clause.  This table outputs a ResponseOut value which is the key to the cached response document.  This value has to be used in the WHERE clause to bind any tables modeling response data to the response document.

getAvgMonthlyRevenueResponse and item_AVERAGE_MONTHLY_REVENUEReturnRow model the response from the service.  You can select data from these tables after binding them to the response document.

Without knowing what your columns are called, a sample query might look like this:

SELECT i.colOne, i.colTwo

FROM item_AVERAGE_MONTHLY_REVENUEReturnRow i, getAvgMonthlyRevenueRequest req

WHERE req.ResponseOut = i.ResponseIn // bind the response doc

AND req.paramOne = 2002

AND req.paramTwo = 'Jan';

In this sample I've skipped over getAvgMonthlyRevenueResponse because these are often just empty nodes to contain the multiplicity of the response data.  That may or may not be true in your case.

Hope this helps.

~jd

[carlossc]

Thank you very much for your quick response.

"item_AVERAGE_MONTHLY_REVENUEReturnRow" contains two columns: TAXID and REVENUE.

So I've executed this query following your valuable advice:

SELECT i.TAXID, i.REVENUE
FROM item_AVERAGE_MONTHLY_REVENUEReturnRow i, getAvgMonthlyRevenueRequest req 
WHERE req.ResponseOut = i.ResponseIn and req.parameters= "";

I've put req.parameters= "" because this WS doesn't have input parameters (it's for testing).

But the query hasn't returned anything.

So, I've tried with this:

SELECT i.TAXID, i.REVENUE, req.ResponseOut
FROM item_AVERAGE_MONTHLY_REVENUEReturnRow i, getAvgMonthlyRevenueRequest req 
WHERE req.ResponseOut = i.ResponseIn and req.parameters= "";

(note the req.ResponseOut in the selection part of the query)

and the result is odd. The content of the cells of the column "ResponseOut" is

"|username|2.25|<http://localhost:9090/server/admin/revenue/services/revenue>0"

and the result has ten rows which is the number of rows that the web service returns. But the TAXID and REVENUE cells are still empty.

[jdoyle]

So you might want to capture the response from the wire (wireshark is a great tool for this), having the response doc can be helpful.

Getting no data from those columns can be caused by incorrect XPaths in the table and columns, or possibly incorrect namespaces. 

The table and columns each have a property in the model called 'Name in Source'.  In the SOAP-Relational model these properties are used to hold XPaths.  The table Name in Source defines a node that forms the base for a relational row, and the column name in source values define the columns in that row.  If these XPAths do not match up with the response document, no data is selected out.  Namespace prefixes are defined in the XPaths, and can be resolved with the 'Namespace Prefixes' property to ensure they are correct as well.

Don't worry about the value of the ResponseOut, it's just an internal key that is used to lookup the cached response document internally in order to satisfy the sub-queries that are generated from your user query.

~jd