Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184

    Unanswered: Functions to manipulate BLOB

    DB2 UDB v7.2

    Given a table data that also contains BLOB, can we use functions to edit the contents of the BLOB based on a search condition on the same BLOB data?

    Example:

    db2 describe table blobtab

    id integer
    stats BLOB


    Now, the SQL...

    update blobtab set stats= <something> where stats = <something>

    TIA.

  2. #2
    Join Date
    Apr 2004
    Posts
    54
    do like this:

    update blobtab set stats= blob('Here is new text')
    where stats = blob('Here is oldtext')

    or like this:

    update blobtab set stats= blob(x'01234567890ABCDEF0')
    where stats = blob(x'0E1F')

    x'65' - is hexadecimal representation of character 'A'

  3. #3
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by gardenman
    do like this:

    update blobtab set stats= blob('Here is new text')
    where stats = blob('Here is oldtext')

    or like this:

    update blobtab set stats= blob(x'01234567890ABCDEF0')
    where stats = blob(x'0E1F')

    x'65' - is hexadecimal representation of character 'A'

    Thanks for the input.

    Can you execute the following and see as I'm gettting SQL0408N error.

    Code:
    db2 "create table blobtab(id integer, stats BLOB(1048576))"
    db2 "insert into blobtab values(1, '65')"

  4. #4
    Join Date
    Apr 2004
    Posts
    54
    use:
    db2 "insert into blobtab values(1, blob('65'))"

  5. #5
    Join Date
    Aug 2002
    Location
    Chennai, India
    Posts
    184
    Quote Originally Posted by gardenman
    use:
    db2 "insert into blobtab values(1, blob('65'))"
    Thanks again, it works... However, when trying to update what I've just inserted it fails!

    Code:
    $ db2 "insert into blobtab values(1, blob('75'))"
    DB20000I  The SQL command completed successfully.
    Code:
    $ db2 "update blobtab set stats=blob('85') where stats = blob('75')"
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0401N  The data types of the operands for the operation "=" are not
    compatible.  SQLSTATE=42818

    Code:
    $ db2 "update blobtab set stats= blob('95') where stats = blob(x'3735')"
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0401N  The data types of the operands for the operation "=" are not
    compatible.  SQLSTATE=42818
    TIA.

  6. #6
    Join Date
    Apr 2004
    Posts
    54
    you cannot specify blob field in predicate.

    do like this:

    create table xxx (
    id int,
    note blob(10K)
    )

    insert into xxx values (1,blob('xxxxxxxx'))

    update xxx set note='yyyyy' where id=1

Posting Permissions

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