update statement with WHERE clause is splitted

[katherine]

Hi,

I have created a view model vdb using Teiid Designer and deployed it to JBOSS server.

I overwrote the default Update transformation in the view model.

I am testing the Update using Squirrel.

My update statement looks like:

UPDATE well SET comment='test' WHERE well.name = 'test_well'

However, Teiid splitted the update statement into two statements and sent to my translator.

The first statement is:

Select name, comment from well where well.name='test_well'

The second statement is:

Update well SET comment='test'

My translator is expecting one Update statement with the WHERE clause, not two.

What have I done wrong? Why Teiid splits the update statement?

Please help. Thanks.

Katherine

[rareddy]

If this is custom translator, in the ExecutionFactory class, there are various "supports" calls with "criteria" in them, those calls define what kind of capabilities that your translator have. If your translator can support it then, turn it on. If you writing a custom translator to a JDBC source that Teiid does NOT already provide, then try extending the JDBCExecutionFactory class instead of ExecutionFactory as it has most basic functionality already turned on.

Also take look at the https://docs.jboss.org/author/display/TEIID/Translator+Development

Ramesh..

[shawkins]

Also when using the non-default view instead of triggers the system does not currently optimize the default processing, which is literally FOR EACH ROW ...  So the first thing that Teiid does is determine the affected row set with a select and then for each row will process the sql block from the trigger.

Steve

[katherine]

Hi Ramesh,

Thank you for your response.

I deployed a dynamic VDB to JBOSS server.

I did the same test with the same update statement. Teiid did not split the statement. It passed the entire update statement with the where clause to my translator.

I do not know why? it is the same translator.

I am new to the teiid world. Thank you.

Katherine

[rareddy]

Post both vdbs may be we can tell, but that is not expected behavior.

[katherine]

Hi Steven,

Thank you very much for your response.

Do you mean if I use the default update transformation in the view model, then Teiid will not split the update statement?

I tried to use the default update transformation in the view model. It gave me QueryValidatorException:

Error: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 TEIID30061 Update is not allowed on the view fieldPros.  A procedure may be needed to handle the Update since: The updatable view has no valid target for UPDATEs.

My non default update transformation looks something like this:

FOR

EACH

ROW

BEGIN

ATOMIC

IF

(CHANGING.well_name)

BEGIN

UPDATE NativeModel.WELL SET Name = "NEW"

.well_name;

END

END

In my translator, when it received the second statement (update statement without where clause), it will update all wells in my native database since it did not know what was the where clause at this point.

Do you have any suggestions to fix this problem?  Is there any ways to know what was the where clause at the point of executing the second statement?

Thank you.

Katherine

[shawkins]

> Do you mean if I use the default update transformation in the view model, then Teiid will not split the update statement?

In most situations it will not.  However depending upon transformation and other factors it may need to do for each row processing to ensure correctness.

> I tried to use the default update transformation in the view model. It gave me QueryValidatorException:

What does the view definition look like?  And do the tables involved have primary keys?

> My non default update transformation looks something like this:

Note that you are telling it to update all rows for each row of the update.  Do you have a primary key?

You probably want something more like:

UPDATE NativeModel.WELL SET Name = "NEW".well_name WHERE pkCol = "OLD".pkCol;

To make the update specific to the affected row.

Steve

[katherine]

Hi Steven,

Thank you a lot.

Your answers are all making a lot of senses to me now. I have updated my procedure with specific where clause. It works.

Thank you again for your help.

Katherine