Informix

 View Only
Expand all | Collapse all

Need help to execute procedure. Having an error

  • 1.  Need help to execute procedure. Having an error

    Posted Sat April 23, 2022 11:58 AM
    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


  • 2.  RE: Need help to execute procedure. Having an error

    IBM Champion
    Posted Sat April 23, 2022 05:12 PM
    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
    ------------------------------