I have questions about storing non-printable chars in CHAR columns.
Because of the way our COBOL programs work, we need to store all
255 possible ASCII chars (some non-printable) in CHAR columns of
Informix Online 5.01 database under HP-UX 9.0. For example,
programs may store COBOL HIGH-VALUE, LOW-VALUE, a data structure
(sometimes union) that contains chars and integers (binary data),
uninitialized fields. Usually such CHAR columns we use aren't
very long (10-100 chars, a few 2000 chars), and a table may have
several such 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.
Since the approach we are using is not recommended, we were
suggested to use BLOB to store such data. However, from what I
heard, accessing a BLOB column requires similar logic as if
accessing a file in 3GL, which in COBOL requires a lot of
declaraction and logic. Thus we did not take the BLOB approach.
So far we don't have any problem storing/retrieving non-printable
chars to/from CHAR columns using embedded SQLs in COBOL, except
we found that some Informix utilities which create ASCII file
version of the database are not working properly. For example,
dbimport/dbexport, SQL UNLOAD/LOAD. The problems are:
- 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).
- 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.
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?
- Is it safe to store non-printable chars in CHAR columns? i.e.
- Is it likely future Informix Online versions continue to work
with non-printable chars in CHAR columns (with limitations)?
(or maybe other utilities will be enhanced to work perfectly)?
- Does it break anything important (archive, logs, etc) other than
dbimport/dbexport and SQL UNLOAD/LOAD?
- Is it really that bad to access BLOB using embedded SQL in 3GL?
- Other than TEXT/BLOB, what other options can we use to store
non-printable chars as need by our application?
I appreciate any comments you have.