return to PRS Technologies website


Bug in apage.4gl

This bug sometimes caused sqlerror -710 (at line 784) when running an A/P Aged report by vendor name (aprp.4ge).


Problem: When running an A/P Aged report by vendor name (aprp.4ge), we received this error:

Program error at "apage.4gl", line number 784. SQL statement error number -710. Table (root.t_age1[username]) has been dropped, altered or renamed.

It apparently didn't happen every time the program was run, but once it happened, it failed 100% of the time from then on. We received this error in both EFW and "Character" mode.

Resolution: This was a tough cookie. Informix sqlerror -710 is a little involved, but it basically boils down to the following problem (See detailed explaination below).

Ok - Fine! But - the program isn't dropping. altering or renaming the temp table after it is created!! So what now? So after a little research on the Informix newsgroup, I found this little note:

Adding an index to the table after preparing the statement can also invalidate the statement.

Hmmmmmm - OK - The program DOES create a index AFTER the prepared insert statement. Like this:

Original Code

~Line 553:

IF orderby = 'N' THEN LET agetab1 = "t_age1",p_userid LET prepstr = "CREATE TEMP TABLE ",agetab1 CLIPPED," (apnum CHAR(8),", "apname CHAR(30),apsort CHAR(30),apstat CHAR(1),aptrm CHAR(8),", "apbal DECIMAL(14,2),apct1 CHAR(8),currency CHAR(4),", #KB30420 " vo_id CHAR(8),udtype CHAR(2),voinv CHAR(16),", "vopyst CHAR(1),votype CHAR(2),voindt DATE,vodudt DATE,", "vobal DECIMAL(14,2),apaddid CHAR(8),", "votot1 DECIMAL(14,2),votot2 DECIMAL(14,2),", "votot3 DECIMAL(14,2),votot4 DECIMAL(14,2),", "votot5 DECIMAL(14,2))" PREPARE s_01 FROM prepstr EXECUTE s_01 IF status = -310 THEN {Table already exists} WHENEVER ERROR STOP ERROR "Temp table ",agetab1 CLIPPED," already exists." SLEEP 2 RETURN ELSE IF status < 0 THEN LET errmsg = err_get(status) CALL errlog(0,"agetab1",errmsg) RETURN END IF END IF END IF

~Line 598:

LET prepstr = "INSERT INTO ",agetab1 CLIPPED," VALUES(?,?,?,?,",#KB31123 "?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" #KB31123 PREPARE in_age1 FROM prepstr

~Line 617:

LET ageidx = "i_age",p_userid CLIPPED LET ageidx1 = "i_age1",p_userid CLIPPED IF p_trconvert = 'Y' THEN LET curr_text = "currency," ELSE LET curr_text = NULL END IF . . . . PREPARE s_2 FROM prepstr EXECUTE s_2 IF orderby = "N" THEN LET prepstr = "CREATE INDEX ",ageidx1 CLIPPED," ON ", agetab1 CLIPPED,"(",curr_text CLIPPED, "apsort,apnum,apaddid)" PREPARE s_2a FROM prepstr EXECUTE s_2a END IF

New Code

So I just moved the CREATE INDEX section to run BEFORE the PREPARE statement (right after the CREATE TABLE statement), like this:

~Line 562:

IF orderby = 'N' THEN LET agetab1 = "t_age1",p_userid LET prepstr = "CREATE TEMP TABLE ",agetab1 CLIPPED," (apnum CHAR(8),", "apname CHAR(30),apsort CHAR(30),apstat CHAR(1),aptrm CHAR(8),", "apbal DECIMAL(14,2),apct1 CHAR(8),currency CHAR(4),", #KB30420 " vo_id CHAR(8),udtype CHAR(2),voinv CHAR(16),", "vopyst CHAR(1),votype CHAR(2),voindt DATE,vodudt DATE,", "vobal DECIMAL(14,2),apaddid CHAR(8),", "votot1 DECIMAL(14,2),votot2 DECIMAL(14,2),", "votot3 DECIMAL(14,2),votot4 DECIMAL(14,2),", "votot5 DECIMAL(14,2))" IF windows_mode THEN DISPLAY prepstr CLIPPED ELSE call errorlog(prepstr CLIPPED) END IF PREPARE s_01 FROM prepstr EXECUTE s_01 IF status = -310 THEN {Table already exists} WHENEVER ERROR STOP ERROR "Temp table ",agetab1 CLIPPED," already exists." SLEEP 2 RETURN ELSE IF status < 0 THEN LET errmsg = err_get(status) CALL errlog(0,"agetab1",errmsg) RETURN END IF END IF LET ageidx = "i_age",p_userid CLIPPED LET ageidx1 = "i_age1",p_userid CLIPPED IF p_trconvert = 'Y' THEN LET curr_text = "currency," ELSE LET curr_text = NULL END IF LET prepstr = "CREATE INDEX ",ageidx1 CLIPPED," ON ", agetab1 CLIPPED,"(",curr_text CLIPPED, "apsort,apnum,apaddid)" PREPARE s_2a FROM prepstr EXECUTE s_2a IF windows_mode THEN DISPLAY prepstr CLIPPED ELSE call errorlog(prepstr CLIPPED) END IF END IF

This fixed the problem!

Note: I never did discover why this program did not fail 100% of the time to begin with.


Detailed text of Informix sql error -710


-710 Table has been dropped, altered or renamed.

This error can occur with explicitly prepared statements. These statements have the following form:

PREPARE statement id FROM quoted string

After a statement has been prepared in the database server and before execution of the statement, a table to which the statement refers might have been renamed or altered, possibly changing the structure of the table. Problems might occur as a result. Adding an index to the table after preparing the statement can also invalidate the statement. A subsequent OPEN command for a cursor will fail if the cursor refers to the invalid prepared statement; the failure will occur even if the OPEN command has the WITH REOPTIMIZATION clause.

If an index was added after the statement was prepared, you must prepare the statement again and declare the cursor again. You cannot simply reopen the cursor if it was based on a prepared statement that is no longer valid.

This error can also occur with stored procedures. Before the database server executes a new stored procedure the first time, it optimizes the code (statements) in the stored procedure. Optimization makes the code depend on the structure of the tables that the procedure references. If the table structure changes after the procedure is optimized, but before it is executed, this error can occur.

Each stored procedure is optimized the first time that it is run (not when it is created). This behavior means that a stored procedure might succeed the first time it is run but fail later under virtually identical circumstances. The failure of a stored procedure can also be intermittent because failure during one execution forces an internal warning to reoptimize the procedure before the next execution.

The database server keeps a list of tables that the stored procedure references explicitly. Whenever any of these explicitly referenced tables is modified, the database server reoptimizes the procedure the next time the procedure is executed.

However, if the stored procedure depends on a table that is referenced only indirectly, the database server cannot detect the need to reoptimize the procedure after that table is changed. For example, a table can be referenced indirectly if the stored procedure invokes a trigger. If a table that is referenced by the trigger (but not directly by the stored procedure) is changed, the database server does not know that it should reoptimize the stored procedure before running it. When the procedure is run after the table has been changed, this error can occur.

Use one of two methods to recover from this error:

* Issue the UPDATE STATISTICS statement to force reoptimization of the procedure.

* Rerun the procedure.

To prevent this error, you can force reoptimization of the stored procedure.

To force reoptimization, execute the following statement:

UPDATE STATISTICS FOR PROCEDURE procedure name

You can add this statement to your program in either of the following ways:

* Place the UPDATE STATISTICS statement after each statement that changes the mode of an object.

* Place the UPDATE STATISTICS statement before each execution of the stored procedure.

For efficiency, you can put the UPDATE STATISTICS statement with the action that occurs less frequently in the program (change of object mode or execution of the procedure). In most cases, the action that occurs less frequently in the program is the change of object mode.

When you follow this method of recovering from this error, you must execute the UPDATE STATISTICS statement for each procedure that references the changed tables indirectly unless the procedure also references the tables explicitly.

You can also recover from this error by simply rerunning the stored procedure. The first time that the stored procedure fails, the database server marks the procedure as in need of reoptimization. The next time that you run the procedure, the database server reoptimizes the procedure before running it. However, running the stored procedure twice might be neither practical nor safe. A safer choice is to use the UPDATE STATISTICS statement to force reoptimization of the procedure.