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.

Answer 1

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.

Answer 2

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