Raising the dead

Actually, more like raising a dead subject... John Glassman asked: >From: John Glassman <MantisMan@atlas.loadstone.com> >Subject: How do I insert/update >256 CHAR? >Date: Sun, 16 Feb 1997 05:52:23 +0100 >X-Informix-List-Id: <news.33986> > >Being new to Informix, I hope that someone here can help explain to me >how you can Update or Insert Quoted Strings longer than 256 characters >into a CHAR(N) data type when a Quoted String in a SQL statement can not >exceed 256 Char's? > >My purpose is to pass Textdata from an html <TEXTAREA> form element >through Netscape LiveWire to the Informix WG 7.12 server on AIX 4.14. > >Is my only way to write the data first to a file and then LOAD it into >the DB? (that seems like a big step backwards, if so). Please tell me >that I'm just missing something. It seems that this should be an >elementary thing to accomplish, but I just can't see how. Paul Mansfield responded with a mangled email address, which is normally cause for being ignored completely, at least in my netiquette book: }From: Paul Mansfield <p.mansfield@motiv.co.uk.AntiSpamText.informix.com> }Date: Tue, 18 Feb 1997 12:29:44 +0100 }X-Informix-List-Id: <news.34062> } }John Glassman wrote: }> Being new to Informix, [...] } }You forgot to say whether you were using SE or Online? } }SE has specific limits, like lack of BLOB space, varchar etc. } }We found when using SE, which has no VARCHAR space that the best way of }handling arbitrarily long strings was to create a file, whose name }is derived from the serial number of the table row, and then access }the file separately in the code. Mind you, this is with ESQL/C and }NSAPI, but I'm sure you could do the same with LiveWire and a native }library, or even CGI-BIN. } }Paul }P.S. note the mangled email address to trip-up spam-'bots! }-------------------------------------------------------------------------- } Paul Mansfield, Internet Consultant @ Motiv Systems Ltd } snailto:Orwell House.Cowley Road.Cambridge.CB4 4WY.UK } Webto: http://www.motiv.co.uk and http://java.motiv.co.uk }mailto:p.mansfield@motiv.co.uk phoneto:[+44+223] 576318 faxto:~319 }-------------------------------------------------------------------------- John Glassman clarified what he is using with: >Date: Wed, 19 Feb 1997 00:00:15 +0100 >X-Informix-List-Id: <news.34099> > >Paul, My server is Online, so I have a choice to display text from >either VARCHAR, CHAR(N), or BLOB(TEXT)...the retrieval of the data isn't >my problem... What I'm looking for is a way to place text data larger >then 256 Charaters into a CHAR(N) data field, but I can't pass the data >that large using a SQL statment. I can live with moving a consistant >datablock, but I'm not too hot on the Idea of having to write the data to >a file first since that might add a lot more system overhead in some >cases and it would also creates cleanup and possibly security issues. > >On the other hand..how would you pass text directly to the server and >have it stored as a BLOB(text) type..(does the file have to first >exist?) I know this is probably In the manual somewhere, but I could >realy use a Bootstrap right now And Paul-of-the-mangled-mail-address responded for a second time: }Date: Wed, 19 Feb 1997 14:43:02 +0100 }X-Informix-List-Id: <news.34112> } }John Glassman wrote: }> Paul, My serever is Online, so I have a choice to display text from }>[...] } }Ok, we use ESQL/C 7.1 on Solaris/Sparc InformixOnline, and I had no }problem in creating a char(2560) field in a database table and storing }and retrieving data from it. } }If you're using ESQL/C on Wintel, then perhaps there's an oddity }with the way you're writing data or the tools you're using... a }barrier of 256 seems kind of small! Have you tried a range }of sizes just in case, e.g. 255, 511, 1023, etc? And then Andy Lennard chipped in with: >From: andy@kontron.demon.co.uk (andy lennard) >Date: Mon, 24 Feb 97 07:37:11 GMT >X-Informix-List-Id: <news.34313> > >I've just come up against the same problem, or perhaps it is.... > >It's reared its ugly head in a tcl/tk utility of ours. I haven't had >time to track it back through the code, but I think it may that the >string passed to 'prepare' is what's causing the grief. > >We've got 5.0.3.uc1 on sunos. Since none of this really addresses the problem, and I'm in Lazarus mode (recently resurrected from the dead, or at least ill, for the biblically illiterate), I offer the following comments... Informix software does not allow literal strings in SQL statements which are longer than 256 characters, upon pain of error -280 'A quoted string exceeds 256 bytes'. The technique which Paul is undoubtedly using successfully to get around this problem is to reference a variable in the INSERT or UPDATE statement (I can't think of anywhere else where this is an issue, though I suppose EXECUTE PROCEDURE and some esoteric CREATE operations might run foul of ittoo). For example, in ESQL/C, you'd write: EXEC SQL BEGIN DECLARE SECTION; char longbuffer[16384]; EXEC SQL END DECLARE SECTION; EXEC SQL INSERT INTO SomeTable(LongCharColumn) VALUES(:longbuffer); This works without any problem. Similarly in any other embedded language (I4GL, NewEra, Perl DBI/DBD::Informix, etc). If you are preparing statements and then executing them, you use the variant notation: EXEC SQL PREPARE p_insert FROM "INSERT INTO SomeTable(LongCharColumn) VALUES(?)"; EXEC SQL EXECUTE p_insert USING :longbuffer; Whether you have any such facility available to you in LiveWire (or tcl/tk) is something which I do not know, but if you cannot use a host variable in some way, then you are severely stuck, and using a LOAD statement may be your best alternative. The only way of inserting BYTE or TEXT values is to use a locator variable. There are no BYTE or TEXT literals other than NULL which can be used in an INSERT or UPDATE statement. Incidentally, Informix-Universal Server, IUS, has introduced two new data types, amongst others: CLOBs for Character Large OBjects, and BLOBs for Binary Large OBjects, both of which are variants on Smart Large OBjects (which are emphatically not called SLOBs). This means that using the older term blob to mean either BYTE or TEXT, which are emphatically not Smart Large Objects, is no longer officially approved for IUS-aware people. Note that the literal string constraint has existed in all versions of Informix on all platforms dating back to Version 1.10, the first released version of ISQL. As far as I know, it has not been lifted in 9.x, the IUS release, but I have not formally verified that. It still exists in 7.2x. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>