Change the source based on Procedure parameters

[tanmoypalit]

Hi,

I am using a SOAP service which has four completely different WSDL for four regions. As the response is identical, the XQuery will be exactly identical so I don't want to make four view models instead write one virtual procedure and pass the region information as a parameter to the procedure and change the source depending on the parameter.

Following is my current XQUery:

CREATE VIRTUAL PROCEDURE

BEGIN

          DECLARE string VARIABLES.INPUT_ENDPOINT;

          VARIABLES.INPUT_ENDPOINT = ServiceManagerServiceView.getDataReport.soapstring;

          SELECT DataHeader.* FROM (EXEC ServiceManagerService.invoke(binding => 'SOAP11', action => '', request => XMLPARSE(DOCUMENT VARIABLES.INPUT_ENDPOINT))) AS ws, XMLTABLE('/*:getHeaderResponse/return/response/report/record' PASSING ws.result COLUMNS ATTRIBUTES XML PATH 'attribute') AS DataHeader;

END

I want to change the ServiceManagerService depneding on a parametere passed to the procedure. Is there any way to do this?

regards

Tanmoy Palit

[rareddy]

Take look at the procedure language here https://docs.jboss.org/author/display/teiid81final/Procedure+Language

I am guessing, based on the input parameter you can use the IF Statement construct to change the select statement you execute with different ServiceManager.

Ramesh..

[shawkins]

In addition to if/else there are a couple of other options.  You could use multisource models for the soap services.  In multisource mode if you have a parameter name "SOURCE_NAME", then the procedure will be invoked against the source with that name.

You can also use dynamic sql, with the drawback of some additional overhead for creating the dyanmic plan.

Steve

[tanmoypalit]

Thanks Ramesh!!

I am able to condition the Query which works. I used EXECUTE IMMEDIATE <String query> USING <variables>.

Steven,

I would like your idea as well which may be helpful in later stages of development. Is multisource mode available in Teiid 7.7 and does 7.8 designer supports it? If you have any document reference please send them.

Thanks

Tanmoy

[shawkins]

Designer should support it, but I'm not entirely sure what the mechanics are in the GUI.  From a Teiid side all we're looking for is additional metadata in the vdb.xml, see https://docs.jboss.org/author/display/teiid/Multi-Source+Models or the analogous section in the 7.7 docs.

Steve