RE: Storing Guids in DB2/400

Walden, are you just teasing or do you really want to know :)

To the best of my knowledge, there is no SQL built-in function to convert
hexadecimal string to char data so you'd have to write your own UDF (I just
wrote one to test this).

CREATE FUNCTION QGPL/CvtHex32ToBit16(IN CHAR(32))       
       RETURNS CHAR(16) FOR BIT DATA                    
       NO SQL                                           
       NO EXTERNAL ACTION                               
       LANGUAGE C                                       
       PARAMETER STYLE DB2SQL                           
       EXTERNAL NAME 'QGPL/CVT32TO16(CvtHex32ToBit16)'

Once you have it in place, something like this should work:

INSERT INTO QGPL/T1 
  VALUES
  (  
    qgpl/CvtHex32ToBit16
    (
      CHAR
      (
        REPLACE('7AF7EAA1-D035-4E22-BAD8-7F5640ECC4A7','-','')
      )
    )
  )
 
To reverse it (i.e. for Web service consumption), use sql HEX built-in in
combination with SUBSTR:

SELECT 
  SUBSTR(HEX(f1),1,8)  || '-' || SUBSTR(HEX(f1),9,4)  || '-' ||   
  SUBSTR(HEX(f1),13,4) || '-' || SUBSTR(HEX(f1),17,4) || '-' || 
  SUBSTR(HEX(f1),21)                                            
FROM qgpl/t1                                                  


As you can see, this is more work than you probably bargained for.

Anyway, I don't see why you need to store it as bit 16 in your case...
especially if you need to communicate back to the Web service. It'll
probably expect the same input as one given to you.  
Simply store it in the format you receive it (human-readable).

Elvis

-----Original Message-----
Subject: RE: Storing Guids in DB2/400

 F1 CHAR ( 16) FOR BIT DATA NOT NULL, 

Since these are referenced to other tables (on other systems) they're
likely to be non-unique, so the unique isn't needed. Let's say I go
w/the char(16) for bit data approach, how to I translate the
7AF7EAA1-D035-4E22-BAD8-7F5640ECC4A7 I get from (in this case) a web
service to the correct bit data? And then translate it back when I need
to send it out again?

-Walden