Re: -410 Prepare statement failed... BUT NO PREPARE!

}From: kerry@kcbbs.gen.nz (Kerry Sainsbury) }Subject: Re: -410 Prepare statement failed... BUT NO PREPARE! }Date: 7 Oct 94 00:37:39 GMT }X-Informix-List-Id: <news.9131> } }Kerry Sainsbury (kerry@kcbbs.gen.nz) wrote: }: My 4GL-RDS (4.10.UC1) program, running against a SE 4.10.UG1 database, }: told me today: }: "SQL Statement Error -410, Prepare statement failed or was not executed" }: The line it barfed on did NOT have a PREPARE statement, only an INSERT. } }Here's a small program which illustrates the bug: }--------------------------------------------------- }MAIN }DEFINE i,j SMALLINT } }# CREATE TABLE kerry(kjs CHAR(5)) # <---- Create this first } } FOR j = 1 TO 2 } DATABASE eunice # <----- Your database goes here } } DELETE FROM kerry } } FOR i = 1 TO 2 } INSERT INTO kerry } VALUES ("hello") } END FOR } } CLOSE DATABASE } END FOR }END MAIN }--------------------------------------- }Anybody else get this bug? }Please DON'T state the obvious work-around for the above program! (1) Although there is no explicit prepare here, the way that RDS handles SQL statements is through PREPARE and DECLARE (for SELECT) or EXECUTE (for INSERT etc). There are some complex reasons why this cannot be avoided. The RDS system does its best to mask the differences between what it does and what the compiled C code does, but sometimes the differences show through -- this is an example. (2) The p-code interpreters both try to optimise your database access by not re-preparing statements for you. This means that the insert is probably prepared just once in the loop. This would give you better performance than the equivalent c-code version, in theory. (3) The version 4.12.UE1 p-code system executes the program without failing. I haven't investigated when the code was added (but it was in a release prior to the 4.12 releases), but there is now code in the p-code interpreters which notes when its prepared statements are clobbered. Events such as CLOSE DATABASE are among the statements which mean that all previously prepared statements are valid. This suggests that upgrading to either 4.12 (or, if necessary, 4.11) should get rid of the problem. Unless you upgrade, you're in danger of being 3 versions behind -- 4.13 is due out by the end of the year. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>