'describe into sqlda'. From which version does it work for updates?

Hi, I wonder if anyone can help with an esql problem. Running IDS 7.30.UC3, Client SDK 2.02.UC2 on Solaris2.6. The documentation says that a 'describe statement_id into sqlda_pointer' works for update statements where there are dynamic parameters in the sql statement. For me it gives no errors, sqlcode returns the correct value but the sqlda structure is empty. Would a later version work? Thanks. Andy Lennard andy@kontron.demon.co.uk

Andy Lennard wrote: > I wonder if anyone can help with an esql problem. > > Running IDS 7.30.UC3, Client SDK 2.02.UC2 on Solaris2.6. > > The documentation says that a > 'describe statement_id into sqlda_pointer' > works for update statements where there are dynamic parameters in > the sql statement. > > For me it gives no errors, sqlcode returns the correct value but the > sqlda structure is empty. > > Would a later version work? Your version is 'OK', but see the ranting below... *** These are my views and not those of Informix *** There are several tricks involved in getting it to work, to the extent that it can be gotten to work: 1. The database server has to be started with a specific environment variable set to make it describe UPDATE statements. 2. The environment variable isn't necessarily identical to the one cited in the release notes -- I've reported this as a bug. The real one is something like IFMXUPDDESC; I discovered it by using strings(1) on the oninit executable and grepping for UPD. <RANT> 3. Once you've got the engine ready to tell you what you want to know, you find it only tells you what it thinks you want to know and not what you actually want to know. :-( *** These are my views and not those of Informix *** Consider the following UPDATE statements: {U1} UPDATE SomeTable SET Col1 = ?, Col2 = ? WHERE PKCol = ?; {U2} UPDATE SomeTable SET (Col1, Col2) = (?, ?) WHERE PKCol = ?; {U3} UPDATE SomeTable SET Col1 = ?, Col2 = SomeFunction(Col2, ?), Col3 = ? WHERE PKCol = ?; {U4} UPDATE SomeTable SET (Col1, Col2, Col3) = (?, SomeFunction(Col2, ?), ?) WHERE PKCol = ?; U1 and U2 are equivalent (bar typos); U1 uses strict ANSI SQL notation, and U2 uses an Informix extension of ANSI SQL. Similarly, U3 and U4 are equivalent, barring typos. Arguably, I don't need to cite U2 or U4 in the diatribe which follows. In each case, the output descriptor will only describe 2 (two, as in one plus one equals two) values. In U1 and U2, these are the two parameters listed in the SET clause; it does not include the parameter listed in the WHERE clause. In cases U3 and U4, the two parameters described are those associated with Col1 and Col3, not the ones associated with SomeFunction() or the WHERE clause. *** These are my views and not those of Informix *** As far as I can see, there is no good reason for this. There are 3 input parameters in U1 and U2; there are 4 input parameters in U3 and U4. Consequently, they should all be described. The fact that they aren't means that any code which uses the described output has to know exactly how to deal with the missing parameters, and has to build its own descriptor with the two values provided by the describe and one or two values determined by the program using some greater or lesser magic. *** These are my views and not those of Informix *** The stupidity of this implementation blows my mind. SQL-92 (that's about 7 years old, now!) defines two ESQL/C operations: DESCRIBE [OUTPUT] DESCRIBE INPUT The keyword OUTPUT is optional for backwards compatability. Obviously, the described data for a SELECT statement (or EXECUTE PROCEDURE) is equivalent to DESCRIBE OUTPUT. Informix provides the information that should be provided by DESCRIBE INPUT for an INSERT...VALUES statement. You would have thought that those implementing the system would know by now about SQL-92 and would have taken the opportunity to implement DESCRIBE INPUT and DESCRIBE OUTPUT properly. However, DESCRIBE INPUT would require either 3 (U1, U2) or 4 (U3, U4) parameters to be described. Further, it would require (very useful) information about the input parameters for a SELECT statement, or an INSERT...SELECT statement with parameters in the WHERE clause, or a DELETE statement. *** These are my views and not those of Informix *** <EXTREMERANT> So, they ducked! Dammit! They deliberately invented a way of implementing the feature so that it was unusable and went ahead and implemented it so that it was unusable. And, personally, I'd like to commit bodily violence on the people involved in the decision-making process that lead to the release of such a brain-dead, half-arsed botch of a job. </EXTREMERANT> *** These are my views and not those of Informix *** OK, so maybe bodily violence isn't appropriate in this non-Medieval world as we approach the start of the 3rd millenium; sometimes I feel that is a pity. But I'm really, truly, extremely irked by the design. *** These are my views and not those of Informix *** I had plans for it. I was going to be able to upgrade DBD::Informix so that it would be able to update blobs properly. I can't, because the feature that such an upgrade depends on is broken. It pains me that I advertised that such an upgrade would be possible; it pains me even more that I now have to go back and tell people "Well, I thought I'd be able to do it but Informix f**ked it up and it is not possible". I'm sorry, but that notice is not going to pull any punches, any more than this email pulls any punches. *** These are my views and not those of Informix *** I was livid when I found out about the pusillanimosity of the chosen implementation; I am still livid about it; I am likely to remain unrepentantly livid about it until it is fixed. I've entered a bug about it. Because I entered it, it has the lowest available priority. After all, I only know what the hell I'm talking about and work for the company and want the products to be usable, so my views don't actually count in such matters! Yes, I'm bitter about this. Well spotted! You could do the world a favour and find the bug, make it into a priority 1 case (because you work for a big enough customer of Informix's that you can do this), and then it will be fixed. But unless someone does something along those lines, it is doomed to remain in the "Oh, one day, maybe, perhaps" category. </RANT> *** These are my views and not those of Informix *** <RAVE> ...Oh, I've given up raving. I would have raved if the feature had been implemented correctly, even for just UPDATE statements. I'd have really raved if DESCRIBE INPUT and DESCRIBE OUTPUT had been implemented per SQL-92. But, given the disappointment of finding out how the feature is actually implemented, I've given up raving. </RAVE> OK, two deep breaths... So, it works provided you know what to expect and can work around the limitations of the feature. You have to be able to parse the UPDATE statement, which can be highly non-trivial, and then determine which items would have been described for you and which would not. You then have to manufacture a descriptor (you cannot, in general, use the one from DESCRIBE, unlike any other statement) and use that to pass data to the UPDATE statement when you execute it. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>