Re: Storing non-printable chars in CHAR columns

In article <11941@cimshop.UUCP>, vchow@HP-UX (Victor Chow) writes: > > I have questions about storing non-printable chars in CHAR columns. > > However, the Informix User's Guide and Reference manuals mention > that CHAR column should store only printable characters. Storing > non-printable chars in CHAR column using 3GL with embedded SQLs > is possible, but not recommended. TEXT/BLOB should be used. We started using Informix well before TEXT/BLOB was available - the interpretation of char hasn't changed, so we haven't recoded our many lines of code. > > - if there's a '\\0' (null byte) in a CHAR column, anything after > the null byte in the CHAR column and all the columns after > that CHAR column will be truncated and not possible to be > reloaded back to the database after the database is unloaded. > (Perhaps the data were not unloaded to ASCII data files in > the first place). Correct, if you use the informix provided routines. Unfortunately, they seem to think of multi-char data as a C string, where the null terminates the string. > > - if there's a '\\n' (newline) in a CHAR column, the record > becomes two records when the ASCII data file is read. And of > course both records are incorrect in the database. > Also correct - and here's another GOTCH'YA -what if you have data with that translates to the DBDELIMITER [usually '|' ]. > Now my questions are: > > - Is there anyone out there also storing non-printable chars in CHAR columns > with work arounds? > > - Are there other non-printable chars that may cause problems? See above - also, a backslash will cause havoc if it's the last char. > > - Is it safe to store non-printable chars in CHAR columns? i.e. > I believe it's safe, just as noted above, you can't use the normal isql unload commands. Note that it is possible to dump these fields, if you do the following: 1). Dump the entire length of the field [normally, you can skip trailing blanks]. 2). ESCAPE with '\\' the newline, the DBDELIMITER, and the backslash itself. Unfortunately, this requires some moderately sophisticated dynamic ESQL coding to handle the general case for all files. If, however, you only need a tool for a specific file, then it becomes a normal ESQL hack. Once dumped with such a tool, data is re-loadable with normal isql load commands. Hope that helps. Standard disclaimers apply, Ihor Kinal att!trumpet!ijk