Informix

 View Only
Expand all | Collapse all

Insert Parent Table that have Id(Serial). Then this Id(Serial) being used in Child Table

Jump to Best Answer

  • 1.  Insert Parent Table that have Id(Serial). Then this Id(Serial) being used in Child Table

    Posted Fri April 22, 2022 03:24 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);


    Let say, I run this

    INSERT INTO teststudent
    (firstname, lastname, enrollmentdate)
    VALUES
    ('hanna', 'dalia', '2022-04-21');

    How to get this inserted(id) in teststudent, then this inserted(id) is used to perform as following

    INSERT INTO teststudentdetail
    (teststudentid, remark)
    VALUES
    ( <inserted(id)>, 'Yay Yen');


    Please help

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


  • 2.  RE: Insert Parent Table that have Id(Serial). Then this Id(Serial) being used in Child Table
    Best Answer

    IBM Champion
    Posted Fri April 22, 2022 03:35 PM
    You can get the serialid assigned from the insert by running the following immediately after the insert:

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

    If you will be doing this in some programming language, then search for the sqlca record structure.  It contains various values updated after each operation, including the return code to number of rows impacted by an insert/update/delete statement.

    Mike

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

    Original Message


  • 3.  RE: Insert Parent Table that have Id(Serial). Then this Id(Serial) being used in Child Table

    Posted Fri April 22, 2022 03:39 PM

    It depends on what programming language you are using.

     

    Java example:

     

    insertContactStatement.executeUpdate();

    ResultSet rs = insertContactStatement.getGeneratedKeys();  

    rs.next();

    id = rs.getInt(1);

     

    If Informix-4GL/SQL, then you can use the SQLCA record's SQLERRD[1] value.

     

    Hal Maner

    M Systems International, Inc.

    https://www.msystemsintl.com

     

     

     




    Original Message