I'd suggest that you try some basic diagnostics first.
Can you call the procedure in dbaccess?
dbaccess
your_db_name <<!
execute procedure teststudent_AddStudent ("AA", "BB", "01/01/2022", "Some Text");
!
If you have DELIMIDENT set in the environment, it can make names case sensitive if you are quoting the call, so that teststudent_AddStudent may work, but teststudent_addstudent may not. I would suggest being very careful when creating tables or procedures in mixed case for this reason.
You may also get that message if you are calling the procedure with the wrong number of arguments, or the arguments are defined differently. However, it looks as if you have the correct arguments being passed in.
You should also make sure that the owner of the procedure is the same as the one you are using when you call it. If the procedure is, say,
someuser.teststudent_addstudent then it may not be able to find it depending on the user that you are connecting as. Run: dbschema -f teststudent_addstudent -d
your_db_name just to verify that it was created as you expected and the owner that you expected.
------------------------------
Mike Walker
xDB Systems, Inc
www.xdbsystems.com------------------------------
Original Message:
Sent: Sat April 23, 2022 11:58 AM
From: Dalia Hudson
Subject: Need help to execute procedure. Having an error
I'm a informix newbies. Below is my table. Parent and child
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);
This is my procedure
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
I try to execute using RazorSQL as following,
The error was arise
Please help
------------------------------
Dalia Hudson
------------------------------
#Informix