Informix

 View Only
Expand all | Collapse all

Stored Procedure with Commit and Rollback

Jump to Best Answer

  • 1.  Stored Procedure with Commit and Rollback

    Posted Fri April 22, 2022 08:44 PM
    Hi,

    I'm Informix Newbies. I want to insert into 2 table. Parent table & Child table

    I've table as following,

    CREATE TABLE teststudent (
    id serial NOT NULL,
    firstname varchar(100) NOT NULL,
    lastname varchar(100) NOT NULL,
    enrollmentdate date NOT NULL,
    PRIMARY KEY (id)
    );

    CREATE TABLE teststudentdetail (
    id serial NOT NULL,
    teststudentid integer NOT NULL,
    remark varchar(100) NOT NULL,
    PRIMARY KEY (id)
    );

    ALTER TABLE teststudentdetail
    ADD CONSTRAINT FOREIGN KEY (teststudentid)
    REFERENCES teststudent (id);


    Stored Procedure is following,

    CREATE PROCEDURE teststudent_AddStudent (p_firstname varchar(100), p_lastname varchar(100), p_enrollmentdate date, p_remark varchar(100))

    BEGIN WORK;
    BEGIN
    ON EXCEPTION
    ROLLBACK WORK;
    END EXCEPTION;

    INSERT INTO teststudent
    (firstname, lastname, enrollmentdate)
    VALUES
    (p_firstname, p_lastname, p_enrollmentdate);

    -- <inserted(id)>
    select dbinfo('sqlca.sqlerrd1') from sysmaster:sysdual;


    INSERT INTO teststudentdetail
    (teststudentid, remark)
    VALUES
    (<inserted(id)>, p_remark);

    COMMIT WORK;
    END;

    END PROCEDURE


    I need help to get <inserted(id)> from

    select dbinfo('sqlca.sqlerrd1') from sysmaster:sysdual;

    , and this teststudent(id) will be inserted into Child Table as following,

    INSERT INTO teststudentdetail
    (teststudentid, remark)
    VALUES
    (<inserted(id)>, p_remark);


    How to fix and complete my Stored Procedure ? Please help

    ------------------------------
    Dalia Hudson
    ------------------------------


  • 2.  RE: Stored Procedure with Commit and Rollback
    Best Answer

    IBM Champion
    Posted Fri April 22, 2022 11:18 PM
    CREATE PROCEDURE teststudent_AddStudent (p_firstname varchar(100), p_lastname varchar(100), p_enrollmentdate date, p_remark varchar(100))
    define newid integer;

    BEGIN WORK;
    BEGIN
    ON EXCEPTION
    ROLLBACK WORK;
    END EXCEPTION;

    INSERT INTO teststudent
    (firstname, lastname, enrollmentdate)
    VALUES
    (p_firstname, p_lastname, p_enrollmentdate);

    -- <inserted(id)>
    let newid=dbinfo('sqlca.sqlerrd1');

    INSERT INTO teststudentdetail
    (teststudentid, remark)
    VALUES
    (newid, p_remark);

    COMMIT WORK;
    END;

    END PROCEDURE

    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------

    Original Message