Forum @ SIB Visions

Hello,

I've set a sql-string as a writebacktable that selects from different db tables.

Now I am trying to update a row and I get the error:
ORA-01779: cannot modify a column which maps to a non key-preserved table

How can I achieve an update in this case?

Thank you.

Regards,
Julia

Yes, setting a SQL-String as Writeback-Table will not allow you to run an update against it. Example:

Code: Select all

DBStorage storage = new DBStorage();
storage.setDBAccess(dbAccess);
storage.setWritebackTable("test");
storage.open();


Now when execute a select and an update on this storage the following queries will be executed on the database (with "a" being the primary key):

Code: Select all

select m.a, m.b, m.c from test m;
update test set b=?, c=? where a=?;


If we set a SQL-String as writeback table:

Code: Select all

DBStorage storage = new DBStorage();
storage.setDBAccess(dbAccess);
storage.setWritebackTable("(select * from test)");
storage.open();


We get the following queries:

Code: Select all

select m.a, m.b, m.c from (select * from test) m;
update (select * from test) set b=?, c=? where a=?;


And we see that the update statement cannot work. What you want is to set the from clause and the writeback table to appropriate values:

Code: Select all

DBStorage storage = new DBStorage();
storage.setDBAccess(dbAccess);
storage.setFromClause("(select * from test)");
storage.setWritebackTable("test");
storage.open();


Which will yield the expected results:

Code: Select all

select m.a, m.b, m.c from (select * from test) m;
update test set b=?, c=? where a=?;


Thank you for your reply.

In our case we have two tables joint together like this

"select .... from table_a a, table_b b1, table_b b2 ..."

Following your advice we would use our select statement as the argument in a call of the setFromClause function.

But now we have difficulties to understand what the argument for the setWritebackTable should be.

My idea was to use a insteadOfUpdate event handler. There I wanted to update the two tables manually.

Is this posible to do this by using the already established db connection?

Thank you.

Best Regards
Andreas

Why do you update two tables?
But anyway, it's possible to use a view as writeback table and implement the instead of trigger directly in the database or, yes, it's also possible to use the eventInsteadOf... to do the task manually.

Here's a short info: InsteadOf Trigger

Why do you update two tables?

Because a user could have changed data from both tables.
Would you advice that we should avoid doing this? And if so, why?

Thank you!

Best regards,
Andreas

I think what the question was how your data model looks like, how it operates and how the user is interacting with it. Am I assuming correctly that these tables, which are in your select statement, are in some form 1:1 relation? So that you have basically one row split out over multiple tables?