1 2 3 Previous Next 35 Replies Latest reply on Oct 27, 2010 10:42 AM by rareddy

    How to use web service to update/query data

    jalen

      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"

        • 1. Re: How to use web service to update/query data
          rareddy

          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

            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

              Hi Ramesh,

               

              I need to clarify my problem clearly. Thanks.

               

              Snap2.jpg

               

              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

                PS  I see that TEIID-625, TEIID-626 will be fixed in 7.1.1, when will 7.1.1 realease?

                • 5. Re: How to use web service to update/query data
                  rareddy

                  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

                    PS  I see that TEIID-625, TEIID-626 will be fixed in 7.1.1, when will 7.1.1 realease?

                    will be in 7.2 release. 7.1.1 is just a intermediate branch will be merged into 7.2.

                    • 7. Re: How to use web service to update/query data
                      jalen

                      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).

                      Snap2.jpg

                       

                      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

                        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

                          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:

                          Snap2.jpg

                          Its code is:

                          SELECT
                            subId AS sub_id, subType AS sub_type, subKeyword AS sub_keyword
                          FROM
                            jalenPocDrWsXML.getSubjects.EXTRACT_RETURN

                           

                          My WS definition is that:

                          Snap3.jpg

                          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

                            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

                              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

                                Ramesh,

                                 

                                It works, thanks for your help.

                                • 13. Re: How to use web service to update/query data
                                  jalen

                                  Hi Ramesh,

                                   

                                  I still met some problem when using WS to update DB.

                                  Snap1.jpg

                                  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;
                                  END

                                   

                                  It 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

                                    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..

                                    1 2 3 Previous Next