1 Reply Latest reply on Feb 27, 2013 3:44 PM by james_sutherland

    How to control TopLink shallow/two-phase inserts?

      Hello. First, let me say that I am a total newbie here so please poke me if I am violating any rules or customs of these forums.

      I am seeking an advice about my issue with TopLink and handling circular references. Here is the thing, I have three tables, let's call them LEFT, MIDDLE and RIGHT. They have bidirectional references to each other, like this:

      Obviously, if I wanted to insert new records into all three tables, it would be necessary to use two-phase/shallow insert (inserting NULL FKs and then updating them later) somewhere. The best place to do so is MIDDLE table because then only one two-phase insert is required. But as I understand it, using two-phase inserts on both LEFT and MIDDLE and regular insert on MIDDLE should also work.

      Now, the thing is that I never actually need to insert into LEFT and RIGHT, those tables are being populated by other process. What my application is required to do is to insert a new row into MIDDLE and fix references to existing rows from LEFT and RIGHT. Ergo, two-phase insert is NOT needed and correct SQL code that I am trying to achieve would look like this:
      INSERT INTO middle (left_id, right_id) VALUES (17, 14)
      SELECT DISTINCT(DBINFO('sqlca.sqlerrd1')) FROM systables
      UPDATE left SET middle_id = 60 WHERE (id = 17)
      UPDATE right SET middle_id = 60 WHERE (id = 14)

      But in my case, TopLink still attempts to perform two-phase insert to MIDDLE table because it sees the circular references. And if I set foreign keys in MIDDLE table to "NOT NULL" as required by our database design, the commit fails because it's unable to insert NULL foreign keys.

      So my question is, is there any way to control where or how is TopLink going to perform two-phase insert? I'd need to either move the policy from MIDDLE table to LEFT and RIGHT (and not actually use it), or disable it all together (because I don't actually need it).

      Any advice would be greatly appreciated.

      Edited by: 987762 on 13.2.2013 0:13
      Trying to fix tables.

      Edited by: 987762 on 13.2.2013 0:16