Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524

    Thumbs down Unanswered: convert blob to clob

    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.
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Exclamation Sybase ?!

    Is this DB2??

    - I've never seen an error message like it
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    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
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  4. #4
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Red face Now I see

    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
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  5. #5
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    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.
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  6. #6
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow Ok

    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!!
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •