Very Computer

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.

Thanks, Victor

variable myvar char(10)

let myvar = '^L' would give you problems
but
let myvar = ascii 12 won't

Bob
--
 ---------------------------------------------------------------------------


|4640-B Meridian                           Telephone:  (408) 978-0808       |
|San Jose, CA  95124                       Facsimile:  (408) 978-7024       |
|+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++|
|             Serving all your travel needs from start to finish            |
|                            $$$  Best Fares  $$$                           |
|                               Great Service                               |
|                     Specializing in International Travel                  |
 ---------------------------------------------------------------------------

We consider it a limitation, but we work around it.  I'd be interested
to hear a response from one of you Informix support-types.  Will future
versions of Informix continue to behave this way?  Can the import/export
utilities be enhanced to handle binary data in character fields?

Quote:

>  - 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).

Quote:

>  - 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.

Quote:> 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?

Quote:

>  - Is it safe to store non-printable chars in CHAR columns?  i.e.

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

Now if you need to store a binary value, whose first byte may be '\0'
(say COBOL low-value), in a CHAR column, how do you work-around
it so that Informix does not consider the value a database NULL
and be able to retrieve the column back with the original value?

Thanks, Victor

Quote:>Now if you need to store a binary value, whose first byte may be '\0'
>(say COBOL low-value), in a CHAR column, how do you work-around
>it so that Informix does not consider the value a database NULL
>and be able to retrieve the column back with the original value?

>Thanks, Victor

Todd

Stefan

Cheers
--

 Co-Cam Computer Group,  Tel: +61 3 286 3456    
 Melbourne, Australia    Fax: +61 3 417 7857

Quote:>Here at Bi-Tech Software, we also use COBOL and store binary (non-printable)
>values in character fields.  We have found that, if the first character
>of a field is NULL (low values), the column is considered NULL by Informix.
>Note that a NULL column means a value which was not supplied, not just
>a value of binary "0".  We also experienced problems using the import/export
>utilities.  For this reason, we do not use the import/export utilities.

>We consider it a limitation, but we work around it.  I'd be interested
>to hear a response from one of you Informix support-types.  Will future
>versions of Informix continue to behave this way?  Can the import/export
>utilities be enhanced to handle binary data in character fields?

It doesn't solve the 'first byte not equal \0' problem but it adds
an option to the tools (load/unload, dbexport/dbimport, dbload) in order to
handle binary information correctly: Every unprintable character will
be represented as hex value escaped by a '\' (eg. \FF).

I hope that info helps...

I might demo it to interested people during the Informix Users Conference
in San Jose this week (I will be at the SAP booth in th exhibit hall).

Greetings,

Alexander
Consultant, Informix Software Germany