convert blob to clob

fadace · #1 (**permalink**) 07-16-04, 03:20

How to convert a blob field to a clob field ?

RDBMS: DB2-400 R5V2

The alter table doesn't run.
The insert select failed with conversion problem.

Tank · #2 (**permalink**) 07-16-04, 04:17

Is this DB2??

- I've never seen an error message like it

fadace · #3 (**permalink**) 07-20-04, 04:21

RDBMS: DB2-400 R5V2 is not the error message, it's the version. DB2 Release 5 version 2 on OS400.

If you want a repro:

Code:

create table fcelaia/t (i int, b blob(1024000)) 
insert into fcelaia/t values(1,blob('ABCDE'))
ALTER TABLE FCELAIA/T ALTER COLUMN B SET DATA TYPE CLOB ( 2048000)

=> Incompatible attribute of the column B of T in FCELAIA. (it's a conversion from a French error msg !)

Code:

ALTER TABLE FCELAIA/T ADD C CLOB ( 2048000) 
UPDATE FCELAIA/T set C=B

=> incompatible value with column datatype or value C.

Code:

UPDATE FCELAIA/T set C=clob(B)

=> The argument of the function clob is incorrect

Tank · #4 (**permalink**) 07-20-04, 04:28

Hi

Hav you tried using the Char function to convert
the binary code to Ascii chars? i.e.

update yourtable set c=char(b) ??

I'm not sure it will work, as I'm not very
much into AS400

HTH

fadace · #5 (**permalink**) 07-20-04, 04:56

I'm not sure it's AS400 relevant. Except the limitation of the language, the SQL behavior should be the same in any Db2 ("I have a dream !")

Code:

select char(b) from fcelaia/t

=> the argument 1 of the function char is incorrect

I'm not really surprised, because the blob/clob are storing a pointer, and not really a value. Your tip is trying to convert an address to a char.

Tank · #6 (**permalink**) 07-20-04, 05:58

Well I certainly get different error messages than you,
and I can display the ascii content of a clob in the
stout of my Korn shell - but I guess i've reached the
extent of my knowledge - hopefully someone else can help you!!

Internet Services

Web Development

Digital Marketing

Consumer Tech