Functions to manipulate BLOB

ggnanaraj · #1 (**permalink**) 12-15-05, 04:19

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.

gardenman · #2 (**permalink**) 12-16-05, 02:22

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'

ggnanaraj · #3 (**permalink**) 12-16-05, 08:29

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')"

gardenman · #4 (**permalink**) 12-19-05, 07:08

use:
db2 "insert into blobtab values(1, blob('65'))"

ggnanaraj · #5 (**permalink**) 12-19-05, 07:38

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.

gardenman · #6 (**permalink**) 12-20-05, 04:28

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

Internet Services

Web Development

Digital Marketing

Consumer Tech