Re: comments on ESQL/C dynamic update problem reply

>From: "Greg Nikoloff" <gmn@geac.co.nz> >Date: Tue, 8 Aug 95 12:02:32 PDT > >John, thanks for the prompt reply, >I have some comments on your comments. I have some comments on both your comments and my comments. First of all, I hope you saw the correction I sent yesterday about the INSERT statement setting sqlda correctly. I'd forgotten that, and Scott Ellard (cse@netware.com) pointed it out to me, and I sent a correction to the news group as soon as I could. >>The DESCRIBE does not give you the details of the parameters of an UPDATE >>statement. It only ever gives you the description of the output data from >>the database, which means that only two statements ever have descriptions >>returned: SELECT and EXECUTE PROCEDURE. And even for these, you do not get >>a description of the input parameters! >Sorry to contradict you John, BUT, if I prepare a dynamic SQL statement >such as 'insert into orders (order_date) values (?)' >and then DESCRIBE it, Informix quite correctly builds me a sqlda >which has a single parameter required, whose data type and length etc >[and name!] will match those of the field(s) being inserted (in this case a >DATE data type called 'order_date'!). Correct -- this is the substance of my correction. >It is my understanding that the DESCRIBE statement gives information about >both INPUT & OUTPUT parameters required by the prepared statement, No. The Informix versions of DESCRIBE (up to 7.1, anyway) do not do this. SQL-92 mandates the DESCRIBE INPUT and DESCRIBE OUTPUT, which is what we would all like to deal with this. Informix does not claim full compliance with SQL-92. FWIW, I am told that Sybase does support it -- I have no version information or personal experience or documentation to substantiate this, but I have no reason to disbelieve it. >and as the INSERT statement most definately takes only input parameters >your assertion about describe only supporting output fields is quite >incorrect. I agree. but INSERT is a special case. Specifically, you do not get that information for UPDATE or DELETE -- see the attached ESQL/C and output from said ESQL/C. And most other statements do not accept parameters at all. GRANT doesn't; REVOKE doesn't; CREATE and DROP (for tables, views, synonyms, etc) don't; BEGIN, COMMIT, ROLLBACK don't. The DATABASE statement does accept a single parameter. CREATE DATABASE and DROP DATABASE may or may not accept the database name as a parameter -- you can experiment as well as I can to establish that, and you can establish whether they are described into an sqlda structure. >Your example was a complicated one because you are supplying both input >AND output parameters in the prepared statement, the input parameters are >required when the 'OPEN' statement is executed, the output parameters when >the 'FETCH' is executed, in that case I too would expect Informix to only >give the output parameters since there is no way I know to support both >input and output paramters in the same sqlda at the same time. > >I have no problems if Informix now qualify themselves by saying 'you cannot >issue dynamic update statements and expect the DESCRIBE statement to >correctly describe the fields being updated' however it begs the question as >to why dynamic inserts can be described yet dynamic updates cannot! > >But lets acknowledge it as a shortcoming/bug not a design 'feature' >since I have not read of this limitation anywhere else in any Informix >manuals. TO be fair thought while it is not explicitly stated as a supported >operation is not explicityly stated as not-supported either, >and my past experience has been that the ESQL/C manuals often fail to >mention all the possible things you can do, so ommitting to say it is >supported/allowed in the manuals is not the same as saying 'its not allowed/ >supported'! Let's ask ourselves which manuals you have looked at. Informix Guide to SQL: Reference (Version 4.1 and 5.0) says: Use the DESCRIBE statement to obtain information about a prepared statement before you execute it. DESCRIBE returns the prepared statment type, and, for a SELECT statement, the number, data types and size of the values returned by the query. Informix Guide to SQL: Syntax (Version 6.0 and 7.1) says: Use the DESCRIBE statement to obtain information about a prepared statement before you execute it. DESCRIBE returns the prepared statment type, and, for a SELECT, EXECUTE PROCEDURE or INSERT statement, the number, data types and size of the values, and the name of the column or expression returned by the query. The Version 4.1 manual could not document EXECUTE PROCEDURE as stored procedures were not available -- the 5.0 manual should have documented this. Both the 4.1 and 5.0 versions omit the INSERT behaviour, though the actual behaviour is supported. >[and this is especially a problem since my application code did not create >the SQL being prepared or described so it has no way of knowing >the data types (or column names etc), so it cannot easily do a >dummy 'select' to create a valid sqlda, and in any case, even if the >'select' ... method worked, I would find it difficult to be able to >include in this query the fields used in the where clause of the update >statement (e.g. what kind of select would you do to create a sqlda >via select that allows this update statement: >'update orders set order_date = ? where order_num = ? >and order_date = ?' I agree that this is pretty unpleasant -- I don't like it either -- but that is the best option I have to offer. If someone else knows better, listen to them. >especially since its not allowabale to mention a column twice in a select >statement Says who? B/S! Try your SELECT statement on a Stores database; it works! >(so select order_date,order_num,order_date from orders >is not a valid option here, sure we could alias the column but I am sure >that there are update statements with where clauses that would be impossible >to create a valid select statement for). > >> >>Given that this is the correct behaviour, there is no question of >>reporting bugs or finding fixed versions. >I hope that perhaps Informix could (a) clarify the situation I hope I have done that, even though this is not an official Informix statement. It is a statement by a moderately well informed employee of Informix, but this statement, and others I make, should never be confused with official statements. >and (b) consider allowing updates to be described fully as inserts are >rather than just saying 'thats how it is' especially as other RDBMS >products do not appear to have this same limitation. When Informix supports SQL-92 properly, it will have to do that. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> #include <sqlda.h> #include <stdio.h> main() { struct sqlda *p_sqlda; EXEC SQL DATABASE stores; printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode); EXEC SQL PREPARE p_select FROM "SELECT Customer_num FROM Customer WHERE lname = ?"; printf("sqlca.sqlcode = %ld\\n", sqlca.sqlcode); EXEC SQL DESCRIBE p_select INTO p_sqlda; printf("sqlca.sql