4GL Error -410 Revisited

As promised, this is an update on the problem with 4GL Error -410 (Prepare statement failed or was not executed) as it relates to the AT&T 3B2 600/G platform (may be applicable elsewhere - read on!). This insidious error continues to occur, but at long last the specific circumstances for its occurance have been isolated. On the specified platform, this problem occurs ONLY (so far as we have been able to determine) in the following circumstance - as described below in "pidgeon code". FUNCTION FUNCTION_NAME BEGIN LOOP CONTROL - ANY TYPE SQL INSERT, DELETE, OR UPDATE STATEMENT (possibly others) END LOOP CONTROL STRUCTURE END FUNCTION "LOOP CONTROL" can be any of the following: FOR / END FOR; FOREACH / END FOREACH; WHILE <CONDITION> / END WHILE. Example: FUNCTION foo_bar() FOR i = 1 TO 20 INSERT INTO tablename (user_id, amount) VALUES (1, i) END FOR END FUNCTION This error will typically NOT occur on the first call to the function. It typically occurs on successive calls to its containing function. When the error does occur, it appears to be as a result of a shortage of system resources; which is still unknown, but stack space shortage is suspected. The good news is that there IS a workaround. Prepare a generic-type function for handling prepare/execute statements. Example: FUNCTION prep_execute() PREPARE prep_exe FROM sg_string -- sg_string is GLOBAL to SYSTEM EXECUTE prep_exe FREE prep_exe INITIALIZE sg_string TO NULL END FUNCTION The next step is to imbed the use of this function within the loop control structure. Example: FUNCTION foo_bar() FOR i = 1 TO 20 LET sg_string = "INSERT INTO tablename (user_id, amount) VALUES (", 1, ", ", i, ")" CALL prep_execute() END FOR END FUNCTION This effectively eliminates the problem. I would be extremely interested to know if this occurs on platforms other than the AT&T 3B2 600/G. Caveat for testing: this error almost NEVER occurs in the first call to the function! It takes successive CALLs for it to occur. It may take two separate functions and successive CALLs to BOTH functions for it to occur. Patience is recommended when testing. To programmers using ESQL/C: does this problem occur with the "C" version? It should be easy to set up a similar C function call and test this situation. If you test this out, please return your findings direct to me via E-Mail. I am working with Informix to get the problem resolved. It is not known whether this problem is platform specific, machine specific, or if it is a configuration error. If it is a problem with 4GL, it will be fixed. Test results will be posted here on the Informix-List. TSgt Bill Williams Database Programmer/Analyst Standard Systems Center Gunter AFB, AL 36114 bwilliams@ssplan.ssc.af.mil (205) 416-3664 or 416-3139