Informix 7.3 isql insert statement - text/blob/clob field insert error

Question

Is a way around this??

I am trying to insert some data into a table whose structure is:

Column name          Type                                    Nulls

crs_no               char(12)                                no
cat                  char(4)                                 no
pr_cat               char(1)                                 yes
pr_sch               char(1)                                 yes
abstr                text                                    yes

The type of the last field reads 'text', but when trying to insert into this table, I get this error:

insert into crsabstr_rec values ("COMS110","UG09","Y","Y","CHEESE");

  617: A blob data type must be supplied within this context.
Error in line 1
Near character position 66

So this field is some sort of blob apparently, but won't take inserts (or updates). Normally, these records are inserted into a GUI form, then C code handles the insertions.

Narrative

Answer 1

in iSQL....

Load from desc.txt insert into crsabstr_rec;

3 row(s) loaded.

desc.txt is a | (pipe) delimited text file and the number of fields in the txt have to match the number of fields in the table

Narrative

Answer 2

There are no blob (BYTE or TEXT) literals in Informix Dynamic Server (IDS) - nor for CLOB or BLOB types in IDS 9.00 and later. It is an ongoing source of frustration to me; I've had the feature request in the system for years, but it never reaches the pain threshold internally that means it gets fixed -- other things get given a higher priority.

Nevertheless, it bites people all the time.

In IDS 7.3 (which you should aim to upgrade - it goes out of service in September 2009 after a decade or so), you are pretty much stuck with using C to get the data into the TEXT field of a database. You have to use the approved C type 'loc_t' to store the information about the BYTE or TEXT data, and pass that to the server.

If you need examples in ESQL/C, look at the International Informix User Group web site, and especially the Software Repository. Amongst other things, you'll find the original SQLCMD program (Microsoft's program of the same name is a Johnny-Come-Lately) in source form. It also includes a set of programs that I dub 'vignettes'; they manipulate blobs in various ways, and are designed to show how to use 'loc_t' structures in various scenarios.

Amr

bad news... i thought all the stuff i found on the web for this topic was just out of date, but if you're telling me this is still the case, then so be it. thanks for the info about that and the informix service ending – Narrative