This website uses cookies for visitor traffic analysis. By using the website, you agree with storing the cookies on your computer.More information

update row fails with error ORA-01779

General questions regarding the development with JVx.

update row fails with error ORA-01779

Postby JuFi » Mon Oct 22, 2018 2:05 pm

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
JuFi
 
Posts: 10
Joined: Thu Oct 11, 2018 2:20 pm

Re: update row fails with error ORA-01779

Postby rzenz » Mon Oct 22, 2018 2:29 pm

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=?;
User avatar
rzenz
 
Posts: 31
Joined: Mon Dec 12, 2016 1:40 pm
Location: Vienna, Austria

Re: update row fails with error ORA-01779

Postby ANH » Mon Oct 22, 2018 4:46 pm

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
ANH
 
Posts: 10
Joined: Fri Jun 29, 2018 11:42 am

Re: update row fails with error ORA-01779

Postby Support@SIB » Mon Oct 22, 2018 9:28 pm

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
User avatar
Support@SIB
 
Posts: 241
Joined: Mon Sep 28, 2009 1:56 pm

Re: update row fails with error ORA-01779

Postby ANH » Tue Oct 23, 2018 8:58 am

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
ANH
 
Posts: 10
Joined: Fri Jun 29, 2018 11:42 am

Re: update row fails with error ORA-01779

Postby rzenz » Tue Oct 23, 2018 9:17 am

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?
User avatar
rzenz
 
Posts: 31
Joined: Mon Dec 12, 2016 1:40 pm
Location: Vienna, Austria


Return to Development