Re: Update a text field

>From: Jim Kern <jkern@saturn5-atm.nrl.navy.mil> >Date: Tue, 11 Mar 1997 18:03:31 -0500 >X-Informix-List-Id: <news.35078> > >I am using Netscape and LiveWire and LivewirePro . >I have a field in the a table that is of type TEXT >How do update this field? * You start your transaction if you aren't already in one. * You SELECT the whole row into memory with a FOR UPDATE cursor. * You change the value of the TEXT field. * You DELETE the old row. * You INSERT the new row. * You commit your changes if you started a new transaction, otherwise continuing until you'd have terminated your transaction anyway. No, I didn't say it was elegant. It nothing short of repugnant. But it will work, whereas nothing else I know of will. If you have an alternative solution, please let me know! >Am I missing something? Just the POD which covers the problems with updating blobs with DBD::Informix. Please read the documentation in Informix.pm. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> PS: I also covered this on 5th March 1997, with an email with the subject "Re: DBD-Informix-0.50 and variables in UPDATE". Someone else also asked me privately about updating TEXT and BYTE columns this morning, and I replied with the following text which may as well be made public -- it'll save me repeating myself too often (maybe). >slightly off topic: > >I am trying to use DBD/DBI because my isqlperl interface does not allow >me to update blobs using perl. If ever I get DBD/DBI working, will I be >allowed to update text/byte columns through the perl interface? There is a note in the POD (Informix.pm) about this. Basically, Informix doesn't give me any help on determining the types on the RHS of the SET clause in an UPDATE statement, and without that help, placing a blob place-holder on the RHS doesn't work because DBD::Informix doesn't know that the value needs to be treated specially. This is a highly irritating feature of Informix-ESQL/C! I don't have a good workaround. I'm vaguely contemplating parsing an UPDATE statement to see, first, whether it has any place holders in the SET clause, and second, if it does, whether these are blob types. The first step is horrendously tricky: UPDATE SomeTable SET * = ((SELECT * FROM SomeWhere WHERE SomeThing = ?), ?, '?') WHERE SomeOtherThing = ? Only the second of those question marks counts (the first is input to the sub-select, the third's a string literal, and the fourth's an input to the WHERE clause), but to handle that properly, DBD::Informix has to determine the number of columns (and their types) represented by the '*' on the LHS of the SET clause, and then expand the '*' for the sub-select to find out how many columns it represents, so that it can determine which column on the LHS corresponds to the second question mark, which will then allow it to determine whether there's a blob to be worried about. Ugh!!!! And painful. It may not all be necessary, but some of it is, and I can dream up (nightmare up:-) more contorted UPDATE statements than that. I'm not yet convinced that the pain is worth the gain -- I'd have to write a significant portion of a complete SQL parser to handle this properly.