Re: Inserting Blob TEXT data
>From: Tauren Mills <tauren@groovee.com>
>Date: Wed, 25 Jun 1997 02:07:45 -0700
>X-Informix-List-Id: <news.39648>
>
>How do I include Blob data in an Insert statement? In the following SQL
>statement, the description field in the categories table is a TEXT data
>type.
>
>INSERT INTO categories ( name, parent_cat_id, fileroot,
>short_description, description, active_flag, sort_order ) VALUES
>('Test', 1, 'testroot', 'short description', 'description', 1, 1);
There are no BYTE or TEXT literals in Informix (other than the keyword
NULL).
This means that there is no easy way to get blobs into the database. Using
ESQL/C or equivalent, you can create a loc_t structure and initialize it
correctly to read the contents of the file, and you pass this structure to
the INSERT statement, but that's no help to you in DB-Access or the
equivalents. There is also a general purpose ESQL/C BYTE/TEXT blob update
program in the IIUG archives (http://www.iiug.org) which can be used to
change the value of a blob field after the record is inserted (I wonder if
it does transactions? I can't remember). However, that only saves you
writing the ESQL/C; you still need the ESQL/C compiler to compile it.
IUS does provide functions for handling its large objects (BLOBs and CLOBs
corresponding to BYTE and TEXT respectively, but distinctly different from
them).
>Checking the syntax or executing this SQL gives the following error:
>
>[Informix][OnLine Dynamic Server][videoranch] SQL Error (-617) : A blob
>data type must be supplied within this context.
This looks suspiciously like ODBC or Informix-CLI.
I haven't read the manuals from cover to cover, but I suspect that ODBC has
no concept of the BYTE or TEXT types, which will limit your options.
>Taking out the description field and the description value (since they
>are not required) makes the statement work just fine:
>
>INSERT INTO categories ( name, parent_cat_id, fileroot,
>short_description, active_flag, sort_order ) VALUES ('Test', 1,>'testroot', 'short description', 1, 1);
>
>I am running an Informix Online Workgroup Server ver 7.12 on Sun Solaris
>2.5.1
>
>How do I do this? How would I go about adding a graphic file if I had a
>BYTE data field?
The other way to load data is via a LOAD command -- but that requires the
data in the appropriate format. The BYTE data format is a sequence of
hexadecimal digits (upper case if my memory is correct), with the first of
each pair of hex digits being the more significant 4 bits in the byte, and
the second being the less significant 4 bits in the byte. No newlines or
anything allowed in the field. Ugh!!
Yours,
Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>