Currently I have a model structure that has been created based on a database schema (this database is only used to create model structure, no need to use it query/udpate data). And I have a web service which provide insert/update/delete/select functions. How can I operate the model, but the real implementation is through the web service?

What I have done:

1) Import from a database to create the model schema

2) create a view model base on the source model created by #1

3) create a web service model which ref my web service, it contains some errors.

4) I want to combine the view model created by #2 and the web service source model created by #3, but the #3 cannot be dragged to the position "SOURCE" using the Designer.

Regarding the steps, am I wrong. Or any better solution?

Regarding the errors at #3, the error is "Resolver Error: Group does not exist: jalenCISWSResponses.jalenTeiidDrPortType_insertSubject_insertSubjectResponse"

Teiid "Web Service" model is to expose the data in through web services. It generally uses a XML model underneath to expose the data created. Web Service model, gives the user to parse the input document into scalar values that can be passed as criteria to the XML view model.

You *should* be able to create a web service model, and create operations that can web service that can execute the insert/update/delete operations as this same as other virtual procedures. However this seems to be not working currently. See TEIID-625, TEIID-626, may be they will get fixed before 7.2

Use case: Use Web Service to insert/update/delete/select data into/from database.

The database (no SQL provided) only exposes a WS providing the interfaces like that:

insertCustomer();

updateCustomer();

deleteCustomer();

getCustomer();

getAllCustomers();

So Teiid only can see the WS, it doesn't know there is a database.

We have two models here, one is used by WS (call it WS Model below), the other is the one exposed to end user (call it User Model below). They are different.

For example:

a Customer WS Model contains fields ( id, firstname, lastname)

a Customer User Model contains fields ( id, fullname)

NOTE: fullname = firstname + "." + lastname. I use this simple exmple only to show that User Model is diffrent from the WS Model, so there may need a mapping between them (In Teiid, from my understanding, it should be done by writing SQL).

My steps:

1. Create a database based on the schema of User Model (I only use this database's schema to create Source Model, no real data will be stored in it)

2. Import the Relational Source Model from the JDBC database just created

3. Create a Relational View Model In Teiid (Use View Model instead of Source Model created in #2 because we need to mapping User Model with WS Model)

4. I need to import the WS, butI don't know in this case should I import "WSDL into Relational Source Model" or "WSDL into Web Service Model"?(I don't know the difference between them. Acutally I have tried both)

5. Once imported the WS Model, I want to define the mapping between User Model and WS Model. Here I also met problems. I don't know how to use the SQL to mapping it with the View Models I created in #3.

1) You need to first create a web service either using Teiid or not that does the insert/update/delete. If you want use Teiid, this will be one VDB, rest of the models would be in another VDB. Since there is *no* virtualization of data, it would be simple and more performant if you do not use Teiid to create this web service directly. But, if you are looking to create web-service with out any coding Teiid can do this, for slightly more processing.

2) Then create Teiid VDB, and use "WSDL to relational model" importer to create a view model, based on the web service created.

3) Now, you can front this model created with the *user* model with you showed in your diagram that converts back to relational aspect. In the insert/update/delete panels of your transformations, use the stored procedures from the wed-service to relational model to do the actual insert/update/deletes.

3) Import "WSDL into Relational Source Model", then two models will be created automatically in Teiid (acutally, I don't know their differences).

4) Create another view model (this model is used to mapping between the model in #1 and the WS operation in #3) which "transform from an existing model" (the existing model is from #1). Here I don't know how to write the Transformation to mapping these two models (the view model transforming from #1 model, and the view model imported from WSDL in #3) together. I tried to find some examples in the doc, but failed.

For example, if my view is a table "subjet", how can I mapping it the a WS call getAllSubjects()? Do I need to use XQuery here? If so, how to write it? Any example?

We have updated the WS model generation newly in 7.1 Release, and seems like we have not added any documentation as to how use it. We will fix this for before 7.2 release. Thanks for bringing it to our attention. I added this TEIID-1277.

Now, back to Designer for tooling, when a operation exposed by WS it may have input parameters, the import WS operation generates two separate virtual procedures and one common physical procedure called "invoke" for WS execution.

1) create_xxx = This takes the input parameters in scalar format, and generates a corresponding input XML to execute the WS.

2) extract_xxx = converts the response xml back to relational format

3) invoke = this procedure actually invokes the WS operation.

Now the magic lies in how these methods are used together to gets the results. An example can be shown as

(EXEC extract_xxx(response.result) as t); -- converting the result into table

Note: I skipped correct input paramters for the "invoke", but the correct usage you can find in the above article I mentioned above. If do not have input paramters, then there is no request, but invoke and extract still be there.

If you want, you can add a base table in your view that represents the columns from query, and use the above query as the transformation. Then user can issue queries like "select * from Table", rather then using the above somewhat complicated SQL in client applications. Also, it abstracts the complexity from end user.

The issue is Designer makes validation check to make sure that there is valid SQL that is inserting a row. But seems like that validation failed to account for stored procedure calls. We noted and logged as https://jira.jboss.org/browse/TEIIDDES-657

To work around this restriction, do something like this

VARIABLES.ROWS_UPDATED = UPDATE TableX SET colA = 'value' WHERE 1 = 2

your query here..

TableX can be any table in your VDB, since the criteria is never going to be true, it will never execute but get you out of this validation error. Once the above JIRA is fixed then, you do not have to do the above workaround.