Re: Stored Procedures and Describe with V5

Dear David, You can describe any prepared statement. You will get sqlca.sqlcode == 0 if it is a SELECT which returns data (as opposed to SELECT INTO TEMP), and sqlca.sqlcode > 0 for other statements. The values are defined in <sqlstype.h> and the code for EXECUTE PROCEDURE is SQ_EXECPROC. If the procedure returns no values, sqlda->sqld is zero and the prepared EXECUTE PROCEDURE statement should be simply EXECUTED. Otherwise, you should treat it exactly as if it was a SELECT statement, declaring a cursor, etc. Interesting thought: could you declare a croll cursor with hold for EXECUTE PROCEDURE? I don't see why not, but I haven't tried it. It just so happens that I was upgrading my SQLCMD tool (which is written in ESQL/C) last night, and it now handles stored procedures. Specifically, I had to worry about the CREATE PROCEDURE syntax which is a pain because all other statements end at the first unquoted semi-colon, and about how to decide between EXECUTE and DECLARE to handle the statement. So this information is hot off the press, so to speak. Yours, Jonathan Leffler (johnl@obelix) }From: uunet!informix.com!cortesi (David Cortesi) }Subject: Re: Stored Procedures and Describe with V5 }Date: 17 May 93 20:21:07 GMT }X-Informix-List-Id: <news.3364> } }(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.)