ESQL/C dynamic update problem

I have a problem which while not life threatening, is a worry, and in any case someone has probably already had this before. I have some ESQL/C code which performs fully dynamic SQL, and its been working fine for 3+ years, recently a colleague decided to change his code [which calls my code to perform the dynamic preparation and execution etc] from a non-parameterized update to a parameterized update, i.e. he now says [using the ubiquitous stores database, and table 'orders']: 'update orders set order_date = ? where order_num = ?' instead of [as an example of what he said before]: 'update orders set order_date = 1995/08/08 where order_num = 1000' This is valid SQL, the only problem is that a DESCRIBE of this prepared statement should return in the first case a sqlda containing 2 parameters, one is for the new value of order_date, the second is for the value of order_num to update. However DESCRIBE (which allocates and fills in SQLDA for me) returns sqlda->sqld (i.e. number of sqlvar structs in the sqlda) of 0 , i.e. the DESCRIBE statement thinks there are NO parameters to be processed, despite the '?'s in the prepared statement! [The SQLCODE returned from the DESCRIBE indicates that the prepare knows the statement to be an update with a 'where' clause (i.e. SQLCODE = 4 after the describe). So its not Informix thinking its some other statement type!]. If I try to execute this statement now, then the execute will fail with a -254 which means, incorrect number of parameters passed [i.e. your prepared statement wants 2 parameters and you didn't give me that number!] Which considering that the sqlda->sqld field is 0 it is not surprising it gets upset! I have tested this with a simple esql/c program on both esqlc version 4.00 and esqlc version 5.00uc2, both with a Informix SE (v 4) and a Informix Online (v 5) database engine. In ALL cases I get sqlda->sqld of 0 (i.e. 0 parameters). If I make the update a insert statement [parameterized insert], then the sqlda is filled in correctly. [interestingly enough I have some other code which has been working for 3 years which does use a parameterized update, however it simply prepares the update, BUT doesn't describe it, and gives it a already prepared sqlda properly filled in when the prepared statement is executed, this update works fine, so the problem is not I think with the prepare, but rather with the describe statement!]. Two questions arise: 1. Is this a problem in ESQL/C or a problem in the Informix Database engine? [I think ESQL/C is at fault here but can't be sure]. [i.e. which bit of the software do I need to get 'fixed' to be able to do parameterized updates?] 2. What release of what software do I need to work around this problem for my platform? The platform I am running is a RS/6000 model 250S (PowerPC chip) running AIX 3.2.5. The highest Online database version I have tested is version 5.01.UD3, the highest ESQL/C version I have tested is version 5.00.UC2. So if anyone could test or report back their similar problems (or even quote me a informix problem number to quote to the local Informix people so they can confirm its a known bug!) will be a big help, you can reply directly to me via gmn@geac.co.nz or post into this newsgroup. [for those of you interested I have included a ESQL/C sample program which you can cut and compile to see if you too have this problem on your system - see below for instructions on use]. BTW: I fully accept that I can use a non-paramerized update statement, the only problem is that there is a performance penalty if a lot of updates on a table are going to be performed, since a parametrised update will require only 1 statement to be prepared for many updates instead of one prepare/execute for each update! So I am keen to get the problem fixed. TIA Greg Nikoloff Here is the example code I have been using, it is a altered copy of the file 'rtypwidth.ec' found in the demo/esqlc directory, I have altered it to allow selection of database and SQL statement. If you cut from here to the end of this post into a file and preprocess and execute it you can enter any database name when prompted, and enter any SQL statement when prompted, if the SQL statement is valid, it will list out the column names etc the describe statement filled in, in the sqlda. ------------cut here-------------------- /* * update1.ec * This program attempts to demonstrate a bug with UPDATE statements in ESQL/C. SImply compile this program with ESQL/C and execute, enter a database name when prompted and then any VALID SQL statement (including '?'s for parameter place holders if desired], the program will then prepare and then describe the statement and then print out the field names and sizes of the parameters it is expecting. If you use the 'stores' database and table orders, you can try these two SQL statements: 'insert into orders (order_date) values (?)' and 'update orders set order_date = ?' the first will return 1 paramter in the sqlda, the second 0 parameters. if the second returns 1 paramater, then your version of ESQL/C and/or Informix database appears to not have the update problem. If the code is sloppy, then so be it, its a quick and dirty test program, not a shining example of how to write C (or ESQL/C) code! */ #include <stdio.h> $char errmsg[400]; main() { int i, numchars; struct sqlda *sql_desc; struct sqlvar_struct *col; printf("Enter a valid database name->"); if (gets(errmsg+100) == (char *) 0) { exit(1); } sprintf(errmsg,"database %s",errmsg+100); $execute immediate $errmsg; err_chk("Open"); printf("Enter a SQL statement ->"); if (gets(errmsg) == (char *) 0) { $close database; exit(1); } $ prepare query_1 from $errmsg; /* prepare */ err_chk("Prepare"); $ describe query_1 into sql_desc; /* setup sqlda */ printf("Describe returned SQLCODE (statement type) of %d\\n",SQLCODE); printf("\\n\\tColumn Name \\t# chars\\n"); /* * For each column in orders print the column name and the minimum * number of characters required to convert the SQL type to * a character * data type */ for (i = 0, col = sql_desc->sqlvar; i < sql_desc->sqld; i++, col++) { numchars = rtypwidth(col->sqltype, col->sqllen); printf("\\t%-15s\\t%d\\n", col->sqlname, numchars); } $close database; printf("All done\\n"); } /* err_chk() checks sqlca.sqlcode and if an error has occurred, it uses rgetmsg() to display the message for the error number in sqlca.sqlcode. */ err_chk(name) char *name; { if(sqlca.sqlcode != 0) { rgetmsg((short)sqlca.sqlcode, errmsg, sizeof(errmsg)); prin