How to create a stored procedure in INFORMIX-SE 1

[Gerilee]

I am trying to create a stored procedure in INFORMIX-SE. I've never done this before and the books aren't helping me much. I get the syntax and how to write the actual statement, but where do I execute that statement from? After entering isql to start INFORMIX, I picked the Query and then the New menu options as if I was writing a query. I tried the CREATE PROCEDURE and CREATE PROCEDURE FROM 'filename' statements where the procedure was actually in a separate file but I got a syntax error. I also tried it before I typed in isql like where I would execute a dbschema command. Can anybody help?

 

[olded]

Hi:

I don't have access to SE anymore, but creating a stored procedure shouldn't be that much different than creating it in Online.

A stored procedure isn't entered into the database a line at a time. It's easiest to create your SP in an ASCII file with an SQL extension, and then create it in dbaccess/isql. Below is an sample SP that trims zeros from
the beginning of a 20-character string. From dbaccess/isql execute Query language, press "C" for choose. Choose the file name that contains the trim_zeros SP. Execute it, and if no SP of the same name and if there's no snytax error the stored procedure is created.

if the SP already exists, from query language, drop it:

drop procedure trim_zeros


If there's a syntax error, edit the sql file and try it again.

If I've misidentified your issue, please post the SP code so I can look at it.

Regards,


Ed

--
-- This procedure eliminates the preceding zeros
-- from character string 'buffer' and returns a string without
-- the preceding zeros. 'buffer' can be up to 20 characters long.
CREATE PROCEDURE trim_zeros(buffer CHAR(20))
RETURNING CHAR(20);
DEFINE ret_val INTEGER;
DEFINE p_status INTEGER;
DEFINE newbuffer CHAR(20);

ON EXCEPTION
set p_status
END EXCEPTION WITH RESUME;

LET p_status = 0;
-- do the conversion, stripping the zeros
LET ret_val = buffer;

IF p_status = 0
THEN -- conversion worked
LET newbuffer = ret_val;
RETURN newbuffer;
END IF

SELECT TRIM(LEADING '0' FROM buffer) INTO newbuffer FROM systables WHERE tabname = "systables";

RETURN newbuffer;
END PROCEDURE;
DOCUMENT "Trim leading zeros from a character string";