CHAR FOR BIT DATA in SQL stored procedures

[Tom Remmers]

With DB2 EE 7.2 on AIX 4.3.3, I want to set a CHAR FOR BIT DATA variable in
a SQL store procedure programmatically. I can do this,

DECLARE x1 CHAR(1);
SET x1 = x'85';

But I want to set x1 from a CHAR(2) variable, e.g. I want to set X1 from an
input parameter

another proc or DB2 client, say
SET x2 = '85';
CALL foo('85');

then,

CREATE PROCEDURE foo( IN x2 CHAR(2))
...
DECLARE x1 CHAR(1);
...
SET x1 = some process/function (x2) -- nothing works

I've tried to be creative in setting x1 from x2 but with no luck. Anybody
have an idea? I've searched through groups.google and haven't found anything
helpful yet.

Thankyou!

Tom Remmers
Software Engineer
rem...@u.washington.edu
University of Washington

[Knut Stolze]

Tom Remmers wrote on Wednesday 12 March 2003 21:35:

> With DB2 EE 7.2 on AIX 4.3.3, I want to set a CHAR FOR BIT DATA variable in
> a SQL store procedure programmatically. I can do this,
>
> DECLARE x1 CHAR(1);
> SET x1 = x'85';
>
> But I want to set x1 from a CHAR(2) variable, e.g. I want to set X1 from an
> input parameter
>
> another proc or DB2 client, say
> SET x2 = '85';
> CALL foo('85');
>
> then,
>
> CREATE PROCEDURE foo( IN x2 CHAR(2))
> ...
> DECLARE x1 CHAR(1);
> ...
> SET x1 = some process/function (x2) -- nothing works
>
> I've tried to be creative in setting x1 from x2 but with no luck. Anybody
> have an idea? I've searched through groups.google and haven't found anything
> helpful yet.

What should the resulting string be? x'85' if the given string is already in
hex encoding, or x'3835' if you want to treat the string as string and just
get its hex representation?

The second case is quite easy, just use a CAST:

VALUES CAST('85' AS CHAR(2) FOR BIT DATA)

As far as I know, there is no function for the first case. You can write your
own like this:

-- convert 2 hex-digits to a single CHAR FOR BIT DATA
CREATE FUNCTION hex2Char(hex CHAR(2))
RETURNS CHAR(1) FOR BIT DATA
SPECIFIC hex2Char
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
CONTAINS SQL
RETURN CHR(
CASE UPPER(SUBSTR(hex, 1, 1))
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END * 16 +
CASE UPPER(SUBSTR(hex, 2, 1))
WHEN '0' THEN 0
WHEN '1' THEN 1
WHEN '2' THEN 2
WHEN '3' THEN 3
WHEN '4' THEN 4
WHEN '5' THEN 5
WHEN '6' THEN 6
WHEN '7' THEN 7
WHEN '8' THEN 8
WHEN '9' THEN 9
WHEN 'A' THEN 10
WHEN 'B' THEN 11
WHEN 'C' THEN 12
WHEN 'D' THEN 13
WHEN 'E' THEN 14
WHEN 'F' THEN 15
END)@

CREATE FUNCTION str2hex(str VARCHAR(100))
RETURNS VARCHAR(50) FOR BIT DATA
SPECIFIC str2hex
LANGUAGE SQL
DETERMINISTIC
EXTERNAL ACTION
CONTAINS SQL
BEGIN ATOMIC
DECLARE result VARCHAR(50) FOR BIT DATA;
DECLARE nextChar CHAR(1) FOR BIT DATA;
DECLARE idx INTEGER;
DECLARE msg VARCHAR(100);

IF MOD(LENGTH(str), 2) <> 0 THEN
SIGNAL SQLSTATE '33IXX'
SET MESSAGE_TEXT = 'invalid input string for hex conversion';
END IF;

SET result = '';
SET idx = 1;
WHILE ( idx < LENGTH(str) ) DO
SET nextChar = hex2Char(SUBSTR(str, idx, 2));
IF nextChar IS NULL THEN
SET msg = 'invalid character ''' || SUBSTR(str, idx, 2) ||
''' in input string for hex conversion';
SIGNAL SQLSTATE '33IYY' SET MESSAGE_TEXT = msg;
END IF;
SET result = result || nextChar;
SET idx = idx + 2;
END WHILE;

RETURN result;
END@

VALUES str2hex('85')@

1
-------------------------------------------------------------
x'85'

1 record(s) selected.

VALUES str2hex('1234567890abcdef')@

1
-------------------------------------------------------------
x'1234567890ABCDEF'

1 record(s) selected.

VALUES str2hex('8y')@

1
-------------------------------------------------------------
SQL0438N Application raised error with diagnostic text: "invalid character
'8y' in input string for hex conversion". SQLSTATE=33IYY

VALUES str2hex('123')@

1
-------------------------------------------------------------
SQL0438N Application raised error with diagnostic text: "invalid input string
for hex conversion". SQLSTATE=33IXX

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Tom Remmers]

Knut,

The first case is what I needed, and the generic converter is perfect since
I'm really trying to convert uuid's. Client applications can't always use
uuid's as 16 byte so all my DB2 command and CLI apps convert CHAR(32) to
CHAR(16) FOR BIT DATA internally. For performance optimization I'm building
SQL stored procedures and ran into this problem, which you solved
beautifully.

Thankyou!!

Tom

"Knut Stolze" <sto...@de.ibm.com> wrote in message
news:b4pdp3$ebq$1...@fsuj29.rz.uni-jena.de...