CUBA tries to update my database view when I want to update the underlying table

Support
#1

Hi there,

I have been working sometime now with CUBA platform and I like it very much!
I ran into a problem I could not find back on your forum.

I have created a view in my database, and based an cuba entity on it, ItemReview.
The view uses a underlying table, Items.

I created a itemreview-edit.xml screen to see the data of the view and to update some fields in the underlying table. So, from this UI view, I try to update the underlying table, Items.

I have created some code for this purpose, that ultimately ends up in a Service that does:

dataManager.commit(item);

Somehow this goes wrong because I get the following Exception:

Internal Exception: java.sql.SQLException: Incorrect usage of UPDATE and ORDER BY
Error Code: 1221
Call: UPDATE vw_item_review SET COMMENTS = ?, LEVEL_AFTER_REVIEW = ?, stage = ? WHERE (item_id = ?)

CUBA tries to update my view vw_item_review, while it should be aware (by the class type) that I want to update the item table.
How do I instruct CUBA to update the Item table instead of the ItemReview view?

I use latest CUBA (7.2.7)

Hope this is a newbie question that can be answered very easily :smile:

All the best, Thanks in advance,

Henk

#2

Hi,

If your entity is based on the view, CUBA will try to update the view. Some databases allow underlying table updates if you try to execute update or delete statement against the view, but it really depends on RDBMS and view definition.

If you want to update the underlying table, you should build an entity based on the table, but not on a view. Or use instead of triggers on the view if your RDBMS allows it. For example, it is available in PostgreSQL