Can I write like that?
VARIABLES.ROWS_UPDATED = UPDATE TableX SET colA = 'value' WHERE 1 = 2;
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;
Since I need the rows_updated virable in my interface.
You can, but note that on the second line where
VARIABLES.ROWS_UPDATED = SELECT t.* FROM ..
the t.* MUST be returning exactly one single row of integer, as this is a scalar assignment operation, not a cursor on resultset.
Also, note that in Teiid 7.2 this is being modified to emphasize like
VARIABLES.ROWS_UPDATED = (SELECT t.* FROM ..);
see https://jira.jboss.org/browse/TEIID-1237 for more details. However, it will be backwards compatible in 7.x series, so above will work in 7.2.
The workaround is ok. But I met another problem in 'update' procedure through WS.
My procedure is:
VARIABLES.ROWS_UPDATED = SELECT t.* FROM TABLE(EXEC jalenPocDrWsXML.updateSubject.create_updateSubject(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.updateSubject.extract_updateSubjectResponse(RESPONSE.result)) AS t;
My client sql is "update subject set sub_type = 'my_subtype' where sub_id = 7".
I track the http package and find that sub_id is 'null' instead of '7'. I read from doc and think that "WHERE TRANSLATE CRITERIA" should be set.
But I don't know where to put them in the above WS procedure. Could you help me for that? Thanks.
I can not think of any solutions to this one at this moment, anybody?
This should be a common case for updating/deleting through WS. Any suggestion is greately appreciated.
INPUTS.sub_id will be set based upon the INSERT values or the UPDATE set clause. So depending upon what you are trying to represent with your update, you would instead use "update subject set sub_type = 'my_subtype', sub_id = 7".
Do you mean we use SQL "update subject set sub_type='my_subtype', sub_id=7" on client side? This SQL doesn't conform with the common one: "update subject set sub_type='my_subtype' where sub_id=7" so that our customers may need to change their SQL logic.
And if it can be solved by using this workaround, how about the 'delete' clause "delete from subject where sub_id=7"?
PS. I remember in normal case if we use DB instead of WS call, the update procedure will be"ROWS_UPDATED = UPDATE jalenCis.JALEN1.SUBJECT SET SUB_ID = INPUTS.sub_id, SUB_TYPE = INPUTS.sub_type, SUB_KEYWORD = INPUTS.sub_keyword WHERE TRANSLATE CRITERIA;". In this case, we can use normal SQL "update subject set sub_type='my_subtype' where sub_id=7" on client side.
I think this is new usecase we have not encountered before. The problem is TRANSLATE CRITERIA defines any complex criteria like IN , BETWEEN, LIKE etc with multiple columns. The above scenario is looking for simple variable from TRANSLATE CRITERIA, which is not always possible.
Thinking out loud, it looks like, Teiid needs some way to express/convert this criteria into something like a xpath/xquery that can be in turn passed into procedure as a parameter. Even that seems falls apart, if you are dealing with regular stored procedures instead of web service procedures. Or there is something else wrong with this style of approach to begin with.
So it seems like you do need "update subject set sub_type='my_subtype' where sub_id=7", which would necessitate TRANSLATE CRITERIA. The translate criteria logic by default is based upon the mapping from the view transformation. So you'll need a more complex TRANSLATE that also uses the procedural relational style invocation of the request procedure to pass the parameters through predicates:
VARIABLES.ROWS_UPDATED = SELECT t.* FROM jalenPocDrWsXML.updateSubject.create_updateSubject AS REQUEST, TABLE(EXEC jalenPocDrWs.invoke(null, null, REQUEST.xml_out, null)) AS RESPONSE, TABLE(EXEC jalenPocDrWsXML.updateSubject.extract_updateSubjectResponse(RESPONSE.result)) AS t WHERE TRANSLATE CRITERIA ON (sub_id) WITH (sub_id = REQUEST.<sub_id_param>) AND REQUEST.<sub_type_param> = INPUTS.sub_type AND REQUEST.<sub_keyword_param> = INPUTS.sub_keyword;
You'll replace the respective <xxx_param> with the parameter name from the request procedure.
This will work if:
- id/keyword/type are non-lob types
- input values (or defaults are available) for sub_type and sub_keyword, and a '=' or 'is null' predicate is used with sub_id in the client update.
Thanks, Steven. It works well.
Sorry for still bothering you on this question.
I know from the document that "translate criteria" is used to convert user SQL into transformation ones. Are there more examples for us to learn about it?
Regarding the above transformation Steven provided (Thank Steven very much), we just don't know:
1) Why INPUTS.sub_id is null but request.subId can retrieve the value from the SQL "update subject set ... where sub_id = 7".
2) What's the difference between "...jalenPocDrWsXML.updateSubject.create_updateSubject AS REQUEST..." (why no 'table(exec ...)' here?) and "...TABLE(EXEC jalenPocDrWsXML.updateSubject.create_updateSubject(INPUTS.sub_id, INPUTS.sub_keyword, INPUTS.sub_type)) AS REQUEST..."?
Thanks for your great help!
The reference has small examples, which we will be expanding on from forum and wiki posts, so have a look there first. The topics we're covering here are procedural relational syntax and update procedures / translate criteria.
1) INPUTS come from the UPDATE set clause, or INSERT VALUES / each row of an INSERT with a query expression, or the applicable default / null value. The respective CHANGING boolean value will tell you whether the INPUT was supplied or is the default value. Whereas TRANSLATE CRITERIA is used to convert predicates in the user criteria of UPDATE/DELETES into something that the procedure can use. In this case we converted sub_id = 7 into REQUEST.subid = 7, which due to the procedure relational syntax (see 2) was turned into an input value for the request procedure.
2) The TABLE keyword is used to create a nested table, which is different from a normal table reference in that it allows correlated variables to preceding from clause items. Normal correlated variables are only allowed from an outer to an inner query scope. Using the procedure name as a table is a Teiid specific construct called procedural relational syntax. Here the procedure emulates a table by projecting not only the out parameters and the resultset, but the input parameter columns as well. Then as long as equality, in, or is null predicates are used against the input columns we can infer a scalar value for the parameter. As you'll see in the reference this has many built-in assumptions/limitations.
In general it's a bit of a mismatch to make procedures look like tables but can be required depending upon the consuming applications. In many circumstances these nuances can be avoided by just exposing higher level procedures rather than converting into table form.
Thanks Steven. It is clear.
Another question prompts in using WS to retrieve data.
Since there is only one place in the Designer to write the "SELECT" procedure. So if I call "select * from subject" and "select * from subject where sub_id = 7", they both call the same WS. For performance consideration, "select * from subject where sub_id=7" will cause the WS to retrieve all data. It is a performance issue.
Is there any trick for this case? Or I need to write a procedure to judge whether client is retrieving some rows or all rows?
My current WS "SELECT" procedure is that:
(EXEC jalenPocDrWs.invoke(binding => 'SOAP11', action => null, request => '<ns:getSubjectContainer xmlns:ns="http://ws.dr.poc.jalen"/>', endpoint => null)) AS fr, XMLTABLE(XMLNAMESPACES('http://ws.dr.poc.jalen' AS ns, 'http://ws.dr.poc.jalen/xsd' AS ax21), '$d/ns:getSubjectContainerResponse/ns:return/ax21:subjects' PASSING result AS d COLUMNS sub_id integer PATH 'ax21:subId', sub_keyword string PATH 'ax21:subKeyword', sub_type string PATH 'ax21:subType') AS t
I've written a couple of blog entries showing some simple procedure implementations for creating requests with parameters. Perhaps they will help you.