Results 1 to 8 of 8

Thread: Insert question

  1. #1
    Join Date
    Nov 2010
    Posts
    17

    Unanswered: Insert question

    Hi!
    Can anybody help?
    I have a parent child hierarchy I model with 2 tables
    CMS_ITEM - items can have child items
    CMS_DEP - dependency data (parent child)

    CMS Item holds the item information, like id, file name, file type
    CMS_DEP holds teh dependency data parent id, child (file name and type - cant be ID as the child might not yet be on the system, but we do know its name and type which together will uniquely identify an item)
    Items can have 0 or more children
    Items can have 0 or more parents (usually 1 or 0)

    CREATE TABLE CMS_ITEM (
    ID VARCHAR(37) NOT NULL,
    FILE_TYPE INTEGER NOT NULL,
    FILE_NAME VARCHAR(255) NOT NULL
    )

    CREATE TABLE CMS_DEP (
    PARENT VARCHAR(37) NOT NULL,
    CHILD_TYPE SMALLINT NOT NULL, -- really FILE_TYPE
    CHILD_NAME VARCHAR(255) NOT NULL
    )

    -- PRIMARY KEYS
    ALTER TABLE CMS_ITEM ADD CONSTRAINT PK_CMS_ITEM PRIMARY KEY (ID)
    ALTER TABLE CMS_DEP ADD CONSTRAINT PK_CMS_DEP PRIMARY KEY (PARENT, CHILD_TYPE, CHILD_NAME)

    -- UNIQUE CONSTRAINTS
    ALTER TABLE CMS_ITEM ADD CONSTRAINT UQ_CMS_DEP_TYPE_NAME UNIQUE (FILE_TYPE, FILE_NAME)<

    -- FOREIGN KEYS
    ALTER TABLE CMS_DEP ADD CONSTRAINT FK_CMS_DEP_CMS_ITEM FOREIGN KEY (PARENT_UID) REFERENCES CMS_ITEM (ID) ON DELETE CASCADE;
    ALTER TABLE CMS_DEP ADD CONSTRAINT FK_CMS_DEP_CMS_ITEM_CHILD FOREIGN KEY (CHILD_TYPE, CHILD_NAME) REFERENCES CMS_ITEM (FILE_TYPE, FILE_NAME) NOT ENFORCED;

    Now I have a procedure that takes a set of dependencies (in a string) is supposed only insert dependencies in the CMS_DEP table if they do not already exist... and indeed this is the case if a row already exists in the table before then it will not insert it.
    However -- if the 'set' of dependencies supplied to the stored procedure (SP)
    has a duplicate then th einser is attempted - and fails with a duplicate warning.

    Here is a simplified version of the SP
    Please note that << is used here as a statement terminator

    CREATE PROCEDURE CMS_SP_UPDATE_FILE_DEPENDENCIES (
    IN P_PARENT_FILE_ID VARCHAR(37),
    IN P_PARENT_FILE_NAME VARCHAR(255),
    IN P_PARENT_FILE_TYPE INTEGER,
    IN P_DEPENDENCIES VARCHAR(4000) ) -- set of dependencies (file name, file type pairs separated by : and | like 'CHILD.1:14|CHILd.2:15'
    SPECIFIC CMS_SP_UPDATE_FILE_DEPENDENCIES
    LANGUAGE SQL
    BEGIN
    IF NOT EXISTS (SELECT ID FROM CMS_ITEM WHERE ID = P_PARENT_FILE_ID)
    THEN
    INSERT INTO CMS_ITEM (ID, FILE_TYPE, FILE_NAME, CREATION_TIME_STAMP, PLAN_DESCRIPTION) VALUES
    (P_PARENT_FILE_ID, SMALLINT(P_PARENT_FILE_TYPE), P_PARENT_FILE_NAME, P_CREATION_TIME, P_PLAN_DESCRIPTION);
    END IF;

    IF LENGTH(P_DEPENDENCIES) <> 0
    THEN
    -- This is the problem bit:
    INSERT INTO CMS_DEP (PARENT, CHILD_NAME, CHILD_TYPE)
    -- create a temporay table of dependencies from the P_DEPENDENCIES param
    WITH T(P_PARENT_FILE_ID, NM, VAL) AS
    (
    -- CMS_TF_GET_NV_PAIRS is a general purpose UDF and splits the :|separated pairs out of P_DEPENDENCIES and returns them as a table (NM = child name, VAL = child type)
    SELECT P_PARENT_FILE_UID, NM, VAL FROM TABLE(CMS_TF_GET_NV_PAIRS( P_DEPENDENCIES))
    )
    SELECT P_PARENT_FILE_UID, NM, VAL
    FROM T
    WHERE NOT EXISTS -- I.E. dont insert if already exists in CMS_DEP
    (
    SELECT 1
    FROM CMS_DEP C
    WHERE P_PARENT_FILE_ID = C.PARENT AND NM = C.CHILD_NAME AND VAL = C.CHILD_TYPE
    );
    END IF;
    END<<

    Test:
    CALL CMS_SP_UPDATE_FILE_DEPENDENCIES (
    'MCF.1', --IN P_PARENT_FILE_UID VARCHAR(37),
    'MCF.1.NAME', --IN P_PARENT_FILE_NAME VARCHAR(255),
    12, --IN P_PARENT_FILE_TYPE INTEGER,
    'ECF.1:14|ECF.2:14')<< --IN P_DEPENDENCIES VARCHAR(4000) )<<

    -- that works - adds the rows to the 2 tables as expected

    CALL CMS_SP_UPDATE_FILE_DEPENDENCIES (
    'MCF.1', --IN P_PARENT_FILE_UID VARCHAR(37),
    'MCF.1.NAME', --IN P_PARENT_FILE_NAME VARCHAR(255),
    12, --IN P_PARENT_FILE_TYPE INTEGER,
    'ECF.2:14|ECF.3:14|')<< --IN P_DEPENDENCIES VARCHAR(4000) )<<

    -- that works - succeeds but does NOT add any rows to the tables - as expected.

    -- this below fails:
    CALL CMS_SP_UPDATE_FILE_DEPENDENCIES (
    'MCF.1', 'MCF.1.NAME', 12, 'ECF.4:14|ECF.4:14|')<<

    I get:
    One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "CMS_DEP" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.61.65


    It seems that if there are duplicates in the paremeter rather than the table already having the duplicate then the insert is attempted and it fails.
    Not sure what is happeneing here - is the update done in batch, but the check at outset?

    ?? Ouch! Do I have to use a cursor - which I would avoid for efficiency purposes if at all possible


    Help!

    T
    Last edited by 10Pints; 11-07-11 at 15:03. Reason: SP and clarification

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Use the MERGE statement.

    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by 10Pints View Post

    It seems that if there are duplicatea in the paremeter rather than the table already having the duplicate then the insert is attempted
    So, why is that a surprise? You check for duplicates, don't find any, then attempt to insert duplicates. Clearly, you also need to eliminate duplicates from the set that you are inserting.

  4. #4
    Join Date
    Nov 2010
    Posts
    17
    thanks folks -
    the Merge idea is interesting and should sort it - thanks Andy - I will have a go at that
    I reckon my existing SQL should detect the duplicate in the parameter
    as essentially it is 'merging' the two tables (CMS_DEP and the temporary T)

    So I would have expected the if not exists to have dissallowed the second (duplicate) insert. I am not sure how the check is applied - as it clearly is not for the second parameter item.

    If anyone can explain that I would appreciate it

    T

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by 10Pints View Post
    I reckon my existing SQL should detect the duplicate in the parameter
    as essentially it is 'merging' the two tables (CMS_DEP and the temporary T)
    Are you familiar with the DISTINCT operator?


    Quote Originally Posted by 10Pints View Post
    So I would have expected the if not exists to have dissallowed the second (duplicate) insert. I am not sure how the check is applied - as it clearly is not for the second parameter item.

    If anyone can explain that I would appreciate it
    The set of rows to insert is built before the insert, if not physically, at least logically, so there are no duplicates at that point.

  6. #6
    Join Date
    Nov 2010
    Posts
    17
    right tried the merge
    works as expected on the first 2 tests but fails on the third as the insert did
    i.e.
    CALL CMS_SP_UPDATE_FILE_DEPENDENCIES ('MCF.1', 'MCF.1.NAME', 12, '12-05-2010_16:20:30', 'PLN DESC', 'ECF.4:14|ECF.4:14|')<<

    yields:
    One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "CMS_DEP" from having duplicate values for the index key.. SQLCODE=-803, SQLSTATE=23505, DRIVER=3.61.65


    I modified the SP like:
    CREATE OR REPLACE PROCEDURE CMS_SP_UPDATE_FILE_DEPENDENCIES (
    IN P_PARENT_FILE_ID VARCHAR(37),
    IN P_PARENT_FILE_NAME VARCHAR(255),
    IN P_PARENT_FILE_TYPE INTEGER,
    IN P_DEPENDENCIES VARCHAR(4000) )
    SPECIFIC CMS_SP_UPDATE_FILE_DEPENDENCIES
    LANGUAGE SQL
    BEGIN
    IF NOT EXISTS (SELECT ID FROM CMS_ITEM WHERE ID = P_PARENT_FILE_ID)
    THEN
    INSERT INTO CMS_ITEM (ID, FILE_TYPE, FILE_NAME, CREATION_TIME_STAMP, PLAN_DESCRIPTION)
    VALUES (P_PARENT_FILE_UID, SMALLINT(P_PARENT_FILE_TYPE), P_PARENT_FILE_NAME, P_CREATION_TIME, P_PLAN_DESCRIPTION);
    END IF;

    IF LENGTH(P_DEPENDENCIES) <> 0
    THEN
    MERGE INTO CMS_DEP AS D
    USING ( SELECT VAL, NM FROM TABLE(CMS_TF_GET_NV_PAIRS( P_DEPENDENCIES))) AS S
    ON
    D.CHILD_NAME = S.NM AND
    D.CHILD_TYPE = S.VAL AND
    D.PARENT_ID = P_PARENT_FILE_ID
    WHEN NOT MATCHED THEN
    INSERT VALUES(P_PARENT_FILE_ID, S.VAL, S.NM);
    END IF;
    END<<

  7. #7
    Join Date
    Nov 2010
    Posts
    17
    Ah Captain Smollett I presume
    yes if I use distinct on the merge - then I can eliminate the duplicate in the parameter

    will try now

    ta

    T

  8. #8
    Join Date
    Nov 2010
    Posts
    17

    Cool Success!

    Thanks Captain Smollett and Andy

    using the merge coupled with a select distinct as below sorts it.
    I aggree it should be a precondition, but in the application event the belts and braces need belt and braces!

    I modified the SP like:
    CREATE OR REPLACE PROCEDURE CMS_SP_UPDATE_FILE_DEPENDENCIES (
    IN P_PARENT_FILE_ID VARCHAR(37),
    IN P_PARENT_FILE_NAME VARCHAR(255),
    IN P_PARENT_FILE_TYPE INTEGER,
    IN P_DEPENDENCIES VARCHAR(4000) )
    SPECIFIC CMS_SP_UPDATE_FILE_DEPENDENCIES
    LANGUAGE SQL
    BEGIN
    IF NOT EXISTS (SELECT ID FROM CMS_ITEM WHERE ID = P_PARENT_FILE_ID)
    THEN
    INSERT INTO CMS_ITEM (ID, FILE_TYPE, FILE_NAME, CREATION_TIME_STAMP, PLAN_DESCRIPTION)
    VALUES (P_PARENT_FILE_UID, SMALLINT(P_PARENT_FILE_TYPE), P_PARENT_FILE_NAME, P_CREATION_TIME, P_PLAN_DESCRIPTION);
    END IF;

    IF LENGTH(P_DEPENDENCIES) <> 0
    THEN
    MERGE INTO CMS_DEP AS D
    USING ( SELECT DISTINCT VAL, NM FROM TABLE(CMS_TF_GET_NV_PAIRS( P_DEPENDENCIES))) AS S
    ON
    D.CHILD_NAME = S.NM AND
    D.CHILD_TYPE = S.VAL AND
    D.PARENT_ID = P_PARENT_FILE_ID
    WHEN NOT MATCHED THEN
    INSERT VALUES(P_PARENT_FILE_ID, S.VAL, S.NM);
    END IF;
    END<<

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •