Update to Child before Parent Insert

Update to Child before Parent Insert

Postby zsessary » Thu 12 May 2011 17:34

I've found what i believe is a bug in the order the dataContext submits SQL to the server. If you have a child record with a nullable FK to the Parent in the database already, then attempt to insert the parent and associate to the child in one transaction, the update to the child hits prior to the parent being inserted. This ultimately results in a FK violation because PK 0 is not found in the parent table. See code and schema below for my test.

Here's my console app code:
Code: Select all
Module Module1

    Sub Main()
        Try
            Dim databaseMonitor As New Devart.Data.Oracle.OracleMonitor()
            databaseMonitor.IsActive = True

            ' This first section happens some time before the user will return (web application)
            Dim insertLastWeek As New TestDataContext.TestDataContext()
            Dim aChild As New TestDataContext.Childtable()
            aChild.Mydata = "Hello"
            insertLastWeek.Childtables.InsertOnSubmit(aChild)

            insertLastWeek.SubmitChanges()



            ' ========================================================================
            ' ========================================================================



            ' the user has returned to website and we need
            ' to create the parentTable record
            Dim dc As New TestDataContext.TestDataContext()

            Dim c = dc.Childtables.FirstOrDefault(Function(w) w.Parenttableid Is Nothing)
            Dim p As New TestDataContext.Parenttable()
            p.Mydata = "Good Bye"

            '' Test One - fail
            'c.Parenttable = p

            '' Test Two - fail
            'p.Childtables.Add(c)

            '' Test Three - fail
            'dc.Parenttables.InsertOnSubmit(p)
            'c.Parenttable = p

            ' Test Four - Success... but in two database transactions.
            dc.Parenttables.InsertOnSubmit(p)
            dc.SubmitChanges()
            p.Childtables.Add(c)

            ' Always submit
            dc.SubmitChanges()

        Catch ex As Exception
            Console.WriteLine("Error Occured!!")
            showError(ex, 1)
        End Try

        Console.WriteLine()
        Console.WriteLine("-- End of program. Press any key to close --")
        Console.ReadKey(True)
    End Sub

    Private Sub showError(ByVal ex As Exception, ByVal count As Integer)
        Console.WriteLine()
        Console.WriteLine(count.ToString() & ": " & ex.Message)
        If ex.InnerException IsNot Nothing Then showError(ex.InnerException, count + 1)
    End Sub
End Module


Here's SQL to generate simple test schema used:
Code: Select all

/*
-- Drop talbes and values
DROP TABLE PARENTTABLE CASCADE CONSTRAINTS PURGE;
DROP TABLE CHILDTABLE CASCADE CONSTRAINTS PURGE;

-- Drop sequences
DROP SEQUENCE PARENTTABLE_SEQ;
DROP SEQUENCE CHILDTABLE_SEQ;
*/

-- Create the Tables
CREATE TABLE parenttable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL
);

CREATE TABLE childtable (
id INTEGER PRIMARY KEY,
mydata VARCHAR2(50) NOT NULL,
parenttableid NUMBER,
CONSTRAINT parenttable_childtable
FOREIGN KEY (parenttableid)
REFERENCES parenttable (id)
);

-- Create the Sequences
CREATE SEQUENCE PARENTTABLE_SEQ NOCACHE;
CREATE SEQUENCE CHILDTABLE_SEQ NOCACHE;

-- Create the Triggers
CREATE TRIGGER PARENTTABLE_BEFORE_INSERT
BEFORE INSERT ON PARENTTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT PARENTTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/

CREATE TRIGGER CHILDTABLE_BEFORE_INSERT
BEFORE INSERT ON CHILDTABLE FOR EACH ROW
BEGIN
-- If not provided, auto-generate ID
IF :NEW.ID IS NULL THEN
SELECT CHILDTABLE_SEQ.NEXTVAL
INTO :NEW.ID FROM DUAL;
END IF;
END;
/


Thanks,
Zach
zsessary
 
Posts: 10
Joined: Thu 25 Mar 2010 20:05

Postby StanislavK » Fri 13 May 2011 09:39

Please specify the version of LinqConnect you are using.

This is a know issue that was fixed in the latest 2.50.22 Beta build of LinqConnect. If you are using a prior version, please try updating to LinqConnect Beta 2.50.22.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby zsessary » Fri 13 May 2011 18:12

ok, thanks again. I think this is my third post in a week that you've helped with.

currently running 2.20.19.0. I don't think we want to run a beta version. Do you have any idea when this fix will be available as a production release?
zsessary
 
Posts: 10
Joined: Thu 25 Mar 2010 20:05

Postby StanislavK » Mon 16 May 2011 10:19

We plan to release the new version in several days. We will post here when it is available.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Postby StanislavK » Thu 19 May 2011 11:31

We have released the new 6.30.160 build of dotConnect for Oracle. It can be dowloaded from
http://www.devart.com/dotconnect/oracle/download.html
(the trial version) or from Registered Users' Area (for users with active subscription only):
http://secure.devart.com/

For the detailed information about the improvements and fixes available in dotConnect for Oracle 6.30.160, please refer to
http://www.devart.com/forums/viewtopic.php?t=21027
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48


Return to dotConnect for Oracle