return to PRS Technologies website
Bug in apage.4gl |
Problem: When running an A/P Aged report by vendor name (aprp.4ge), we received this error:
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).
-710 Table
This error can occur with explicitly prepared statements. These statements
have the following form:
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.
PREPARE statement id FROM quoted string
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:
So I just moved the CREATE INDEX section to run BEFORE the PREPARE statement (right after the CREATE TABLE statement), like this:
This fixed the problem!
Note: I never did discover why this program did not fail 100% of the time to begin with.
-710 Table 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.