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

sql insert blob informix isql
CheeseConQueso picture CheeseConQueso · Jul 2, 2009 · Viewed 9.9k times · Source

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

Jonathan Leffler picture Jonathan Leffler · Jul 2, 2009

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.

Related questions

Inserting multiple rows in a single SQL query?

I have multiple set of data to insert at once, say 4 rows. My table has three columns: Person, Id and Office. INSERT INTO MyTable VALUES ("John", 123, "Lloyds Office"); INSERT INTO MyTable VALUES ("Jane", 124, "Lloyds Office"); INSERT INTO MyTable VALUES ("Billy", 125, "…

Read More
Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run update command ELSE run insert command What's the …

Read More
How to copy a row and insert in same table with a autoincrement field in MySQL?

In MySQL I am trying to copy a row with an autoincrement column ID=1 and insert the data into same table as a new row with column ID=2. How can I do this in a single query?

Read More