MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » November 2006

RE: Storing Guids in DB2/400



fixed

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







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact