RE: Create Procedure in I4GL

Christian Brauer, (Brauer.Christian@dresdnerbank.com) wrote: >Natesan, Ramki wrote: >>How can I write a simple "Create procedure" in I4GL and >>make it available to the Trigger in the database? > > reading the 'Informix Guide to SQL: Tutorial' [Informix 7.21] I found > the following information: > > To use ... INFORMIX-4GL... to create a stored procedure, put the text > of the CREATE PROCEDURE statement in a file. Use the CREATE PROCEDURE > FROM statement, and refer to that file to compile the procedure. The only version of I4GL in which that could conceivably work is the 7.20 product, and the last information I had strongly suggested that the I4GL compiler would not understand the syntax of statements introduced since the 4.10 product release - which includes CREATE PROCEDURE FROM statements. However, it would be trivial to write a piece of ESQL/C code which could be compiled into your I4GL which would do as you suggest; so trivial, in fact, that the code follows: /* If you don't have these headers, you've got an antique version of I4GL - or it has been changed recently */ #include "fglsys.h" #include "fglusr.h" int i4gl_create_procedure(int nargs) { EXEC SQL BEGIN DECLARE SECTION; char filename[1024]; EXEC SQL END DECLARE SECTION; if (nargs != 1) fgl_fatal("i4gl_create_procedure", 1, -1318); popquote(filename, sizeof(filename); filename[stleng(filename, sizeof(filename)-1)] = '\\0'; EXEC SQL CREATE PROCEDURE FROM :filename; return 0; } Alternatives to the popquote() function call would include the function for popping varchars (popvchar?) or popstring() if that still exists. The standard idiom is: filename[stleng(filename, strlen(filename))] = '\\0'; I think that the version I used is valid, but I haven't got an I4GL compiler at hand to test it on; the code above is uncompiled and could therefore have errors, but I don't think so. In your I4GL code, you use: CALL i4gl_create_procedure("storedproc.spl") You'd inspect SQLCA.SQLCODE to see whether it worked. If you are using the 4.20 I4GL, then I don't think you can use this technique; you are forced to create a string containing the CREATE PROCEDURE statement and then prepare and execute that. This also works in 7.20 I4GL. Indeed, it is the general technique for dealing with statements not recognized by I4GL - prepare and execute it. The only statements for which this does not work are the non-preparable CONNECT, DISCONNECT, SET CONNECTION statements. Substitute code for these is available in the IIUG archives. Informix Doc Team: can you verify whether I4GL 7.20 supports CREATE PROCEDURE FROM directly, and if not, get the appropriate documentation amended? Yours, Jonathan Leffler (jleffler@visa.com) #include <bother.ms-exchange.h>