Hi,

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

I've table as following,

<sub>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);</sub>


Stored Procedure is following,

<sub>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</sub>


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
------------------------------

#Informix

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
------------------------------