Insert Parent Table that have Id(Serial). Then this Id(Serial) being used in Child Table
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
------------------------------
#Informix
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 ------------------------------
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