Function for adding 1 to a hex column

[Odd B Andersen]

I am trying to create a function that adds 1 to the value in a
hexadecimalcolu mn. This function I want to use inside an SQL PL procedure.

I have a table with a column with char(16) for bit data. A 'select hex(col1)
from tab1' may return x'FFF59E344C596 84CA17BB1056773 43BE'. I have tried to
create a function that takes the hexadecimal value (the in parameter is also
char(16) for bit data) and adds 1 to the input value. I have managed to add
1 to the value of the input parameter and get this:
x'FFF59E344C596 84CA17BB1056773 43BF'. And this seems to work for any
hexadecimal value I try. But how do I convert this text string back to a
hexdecimal value so that I am able to update the column i db2? If I return
it as CHAR(35) - as a character string and try to update I get this value in
the column: x'7827464646353 945333434433539 3638' ....

I try to do this from Command Center or a Command Window: update tab1 set
col1 = x'FFF59E344C596 84CA17BB1056773 43BF' then it works. But why will it
not work inside a SQL PL procedure when I do: update tab1 set col1 = var1
(where var1 is CHAR(35) and contains x'FFF59E344C596 84CA17BB1056773 43BF').

Regards

Odd Bjørn Andersen
ErgoGroup AS
Postboks 4364 Nydalen, N-0402 Oslo, Norway
Telefon +4723 14 50 00, Telefaks +4723 14 50 01
Mobilnr +47970 84597

https://www.ergogroup.no

[Serge Rielau]

Re: Function for adding 1 to a hex column

Go through dynamic SQL and let the SQL parser do the conversion:
like:
E.g. in DB2 9.5 you could do something like:

txt = 'SET ? = x''' || hexstring || '''';
PREPARE stmt FROM txt;
EXECUTE stmt INTO myval;

Prior to 9.5 use a dynamic cursor definition with SELECT FROM
SYSIBM.SYSDUMMY 1

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab