Re: QUERIES ON STORED PROCEDURES

Harinath / Parviz, In answer to your first question, try calling the procedure (from 4GL) like this: DEFINE sqlstmt CHAR(128) DEFINE username CHAR(25) DEFINE retval INTEGER LET sqlstmt = "EXECUTE PROCEDURE check_user(?)" PREPARE p_check FROM sqlstmt <check for errors> DECLARE c_check CURSOR FOR p_check <check for errors> LET username = "Tom" OPEN c_check USING username <check for errors> FETCH c_check INTO retval <check for errors> CLOSE c_check A few notes: 1. 4GL does not yet support the "execute procedure" syntax. Until it does, plan on preparing each procedure call. 2. This example will work for a procedure which returns one row. For a procedure that can return more, place the fetch in a loop. 3. You don't ~have~ to use the ? place holder. You could build a statement like LET sqlstmt = "EXECUTE PROCEDURE (", username, ")" It depends upon your application/preferences. For your second question, I don't have any good answers. Perhaps someone else will step up with a solution? --Craig (craig@chemistry.utah.edu) > >Hello informixusers, > > I have few questions on informix stored procedures. > >Q : 1 >------ >I am writing a stored procedure to be called from INFORMIX-4GL. Here is >the piece of code for creating a procedure. > >May I know, how to call from a 4GL program ? > >______________________________CODE STARTS HERE_______________________________ >DROP PROCEDURE check_user;> >CREATE PROCEDURE check_user(plo_user_name CHAR(8)) RETURNING SMALLINT;>DEFINE plo_users SMALLINT; > >SELECT count(*) INTO plo_users FROM security >WHERE user_name = plo_user_name;> >IF plo_users = 0 THEN > RETURN 0 >ELSE > RETURN plo_users >END IF > >END PROCEDURE; >______________________________CODE ENDS HERE__________________________________ > >Q.2 >--- > To add on , How to get value of the environment variable inside a stored > procedure ? >