-
1. Re: How to use web service to update/query data
rareddy Sep 20, 2010 1:42 PM (in response to jalen)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
-
2. Re: How to use web service to update/query data
rareddy Sep 20, 2010 1:49 PM (in response to rareddy)You can not drag, but it should work when you type the SQL in, however in my testing with current 7.1 Designer it is not working correctly.
-
3. Re: How to use web service to update/query data
jalen Sep 21, 2010 3:31 AM (in response to rareddy)Hi Ramesh,
I need to clarify my problem clearly. Thanks.
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, but I 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.
Thanks.
Jalen -
4. Re: How to use web service to update/query data
jalen Sep 21, 2010 5:38 AM (in response to rareddy) -
5. Re: How to use web service to update/query data
rareddy Sep 21, 2010 9:33 AM (in response to jalen)Jalen,
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.
Ramesh..
-
6. Re: How to use web service to update/query data
rareddy Sep 21, 2010 9:38 AM (in response to jalen) -
7. Re: How to use web service to update/query data
jalen Sep 25, 2010 4:52 AM (in response to rareddy)Hi Ramesh,
I am not sure what's wrong with my usage.
Here are my steps:
1) Import source model from DB schema (This is "user" model, the real insert/update will go to the WS operation)
2) Implement a WS containing methods:
insertSubject(subject), updateSubject(subject), deleteSubject(id), getSubject(id), getAllSubjects()
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?
Thanks for your greate patience!
Jalen
-
8. Re: How to use web service to update/query data
rareddy Sep 25, 2010 7:59 PM (in response to jalen)It is totally confusing what you are doing, it does not match to what you described earlier.
1) If I understand correctly, from your picture delete the "jalenDr.xmi" file. As you created this to create the "jalenMapping.xmi" file.
2) Edit "jalenMapping.xmi" model, and add transformations for select, insert, update and delete using objects in "jalenPocDrWsXML.xmi" model.
3) Importing of Web Service does create two models, one physical and one view. You should only use view model in your views.
To call a procedure just use as "exec proc()", no XQuery needed, all SQL. Check out the Teiid procedure language in the Reference Guide.
Hope this helps.
Ramesh..
-
9. Re: How to use web service to update/query data
jalen Sep 26, 2010 4:45 AM (in response to rareddy)Hi Ramesh,
You are righ, "jalenDr.xmi" can be deleted, I only use it as bak-up.
There is no WS related examples in doc. I read the Reference Guide but still cannot fully understand.
Here I met some problem when querying in the "SQL Scrapbook" to fetch data using the WS VDB I deployed:
select * from jalenMapping.JALEN1.SUBJECT
Remote org.teiid.api.exception.query.QueryPlannerException: No valid criteria specified for procedure parameter jalenPocDrWsXML.getSubjects.extract_return.xml_in
My mapping definition is that:
Its code is:
SELECT
subId AS sub_id, subType AS sub_type, subKeyword AS sub_keyword
FROM
jalenPocDrWsXML.getSubjects.EXTRACT_RETURNMy WS definition is that:
Its procedure is:
CREATE VIRTUAL PROCEDURE
BEGIN
SELECT t.* FROM XMLTABLE(XMLNAMESPACES(DEFAULT 'http://ws.dr.poc.jalen'), '/getSubjectsResponse/return' PASSING JALENPOCDRWSXML.GETSUBJECTS.EXTRACT_RETURN.xml_in COLUMNS subId integer, subKeyword string, subType string) AS t;
END -
10. Re: How to use web service to update/query data
rareddy Sep 26, 2010 3:32 PM (in response to jalen)Jalen,
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.
First you need to read Convert XML data into Relational Table data using Teiid, to understand how XML is converted to relational table, and how a web-service is invoked in Teiid.
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
SELECT t.* FROM
(EXEC create_xxx('param1', 'param2') ) as request, -- generating the input xml
(EXEC invoke (request.result.xml_out)) as response, -- executing the WS
(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.
Hope this helps.
Ramesh..
-
11. Re: How to use web service to update/query data
rareddy Oct 1, 2010 4:44 PM (in response to rareddy)Correction to above SQL statement, it should read as
SELECT t.* FROM
TABLE(EXEC create_xxx('param1', 'param2') ) as request,
TABLE(EXEC invoke (null, null, request.xml_out, null)) as response,
TABLE(EXEC extract_xxx(response.result)) as t;
I did not knew that you MUST include the TABLE prefix around the query so that query planner would execute them in order.
-
12. Re: How to use web service to update/query data
jalen Oct 9, 2010 8:09 AM (in response to rareddy)Ramesh,
It works, thanks for your help.
-
13. Re: How to use web service to update/query data
jalen Oct 12, 2010 4:29 AM (in response to jalen)Hi Ramesh,
I still met some problem when using WS to update DB.
The "insert" procedure I write is:
CREATE PROCEDURE
BEGIN
VARIABLES.ROWS_UPDATED = SELECT t.* FROM TABLE(EXEC jalenPocDrWsXML.insertSubject.create_insertSubject(INPUTS.sub_id, INPUTS.sub_keyword, INPUTS.sub_type)) AS request, TABLE(EXEC jalenPocDrWs.invoke(null, null, request.xml_out, null)) AS response, TABLE(EXEC jalenPocDrWsXML.insertSubject.extract_insertSubjectResponse(RESPONSE.result)) AS t;
ENDIt calls a WS to update the DB and parse the result from the WS.
The error message is "The transformation defining an updatable virtual group should include at least one updatable source."
-
14. Re: How to use web service to update/query data
rareddy Oct 12, 2010 10:13 AM (in response to jalen)Jalen,
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.
Thanks
Ramesh..