How can I INSERT data in TEXT datatype field? (Informix)

Question

 CREATE TABLE updater
 (
    nzp_up SERIAL PRIMARY KEY,
    version VARCHAR(50),
    status INT,
    report TEXT
 );

INSERT INTO updater (version, status,report) values ('TestVersion' , 0,"123123123");

-617 SQL error: A blob data type must be supplied within this context.

You can use the `LOAD` command. Refer to my similar question here: http://stackoverflow.com/questions/1074364/informix-7-3-isql-insert-statement-text-blob-clob-field-insert-error — CheeseConQueso, Nov 10 '11 at 15:28

score 2 · Accepted Answer · answered Nov 10 '11 at 15:31

2

Using a | (pipe) delimited file, you can use the LOAD command to insert values into blob & text data types. I had the same problem in the past - go to link in my comment

answered Nov 10 '11 at 15:31

CheeseConQueso

5,831
29
93
126

score 1 · Answer 2 · edited May 23 '17 at 12:31

1

See my question: Consistent method of inserting TEXT column to Informix database using JDBC and ODBC

It seems that some tools like ODBC drivers can insert text as TEXT while others like JDBC drivers must use PreparedStatent or other techniques.

edited May 23 '17 at 12:31

Community

1
1

answered Nov 10 '11 at 17:20

Michał Niklas

53,067
18
70
114

Some drivers spot the relevant error message and then convert the SQL to use a placeholder in place of the string literal that does not work as BYTE or TEXT value, and convert the string literal into a blob structure (a loc_t structure). Others don't - not unreasonably, since the parsing involved is contorted. If you have support for Informix, please register a request with Tech Support. It's been a problem for over 20 years now. – Jonathan Leffler Nov 11 '11 at 17:21

score 0 · Answer 3 · edited Nov 10 '11 at 13:23

0

INSERT INTO updater (version, status,report) 
values ('TestVersion' , 0,"123123123");

and

INSERT INTO updater (version, status,report) 
values ('TestVersion' , 0,'123123123');

have the same effect in mySql.So lets try without double quotes in SQL.

edited Nov 10 '11 at 13:23

Christian Specht

35,843
15
128
182

answered Nov 10 '11 at 07:14

Suj

31
1
6

I asked some of professionals and they told that it impossible without specific tools (like stream in C#) – Oleg Nov 10 '11 at 07:21
Sadly, that does not work with Informix; it does not support a string literal representation for TEXT or BYTE values. – Jonathan Leffler Nov 11 '11 at 17:18

How can I INSERT data in TEXT datatype field? (Informix)

3 Answers3