Re: Stored Procedures and Describe with V5

(Ein Teeist) and (Colonel Panic) discuss Stored Procedures: >> >In the manuals of Informix V5.0 I can't see any >> >possibility of describing a stored procedure. >> > >> >Is this right? >> >> Not unless you have the wrong manuals. Chapter 8 of the SQL Reference is >> all about stored procedures and SPL. Also see CREATE PROCEDURE syntax on page >> 7-53, and discussions about creating and using stored procedures in chapter >> 11 of the SQL Tutorial. >> >My question was perhaps to short. I wan't PREPARE and DESCRIBE >a Stored Procedure like a dynamic SQL-Statement. I want all >the parameters and their types from a Stored Procedures returned >from the DBMS and not an optional comment. >"Describe" of a Stored Procedures in the Manuals is only a >help or usage text but not a dynamic DESCRIBE-Statememt. It isn't clear to me yet what you want. Alan told you how to get a description of stored procedures from the manual. Ted Dinh posted the SQL to get the actual source text out of the system catalog. But possibly what you want to know is this. The following things are true although they are not perfectly clear in the manuals (in this edition). * you can use PREPARE "EXECUTE PROCEDURE...." * the prepared statement can include "?" place-holders for host values * you can use EXECUTE...USING... to execute a prepared EXECUTE PROCEDURE * you can associate such a prepared statement with a cursor using DECLARE and you can open it with OPEN...USING... * you can DESCRIBE such a prepared statement and the returned descriptors will be based on the RETURNING clause of the stored procedure header. In short, a stored procedure with a RETURNING clause can be used just like a SELECT statement with its projection-list. (Note: I have not personally tried the DESCRIBE step. However, I have done the first 4 steps successfully in 4GL version 4.1. I am confident that the 4GL runtime -- which doesn't know anything about stored procedures -- would have blown up if the DESCRIBE had not worked in a transparent way.) Dave Cortesi cortesi@informix.com