Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2014
    Posts
    5

    Unanswered: Inserting Hex value into Table using Native Stored Procedure

    Hi All,

    We need help in inserting a Hex Value into table.

    Hex value is defined as Char(30).
    and insert into the table column having Char(15).

    Ex.

    . . ? 8 . ' E . . . . . - visible value
    0C6F117C2006044 - Hex Format
    0CF88BD56018300

    HEX(TRANS_ID) will generate 00CC6FF8181B7DC526000168034040.

    What we need is a way to convert 00CC6FF8181B7DC526000168034040 to ..?8..'E.....


    We have tried For BIT data, but it is only working for IN parameter, Which in our case came from a whole lenght of data from the IN.

    EX. Value1 Varchar(100)

    Which compose of name,age,adress,sex,number

    and we only need the number to be in hex value.

    Thanks in Advance

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about X2C user-defined-function
    in Sample UDFs for Migration

  3. #3
    Join Date
    Feb 2014
    Posts
    5
    I tried it and getting this error.


    CREATE FUNCTION X2C (HX VARCHAR(768))
    RETURNS VARCHAR(256)
    SPECIFIC X2C
    LANGUAGE SQL
    CONTAINS SQL
    EXTERNAL ACTION
    DETERMINISTIC
    BEGIN ATOMIC
    DECLARE HXN VARCHAR(512);
    ---------+---------+---------+---------+---------+---------+---------+---------+
    DSNT408I SQLCODE = -775, ERROR: STATEMENT SPECIFIED IN SQL ROUTINE IS NOT
    ALLOWED WITHIN A COMPOUND STATEMENT
    DSNT418I SQLSTATE = 42910 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNHSMS1 SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = 40 0 0 -1 511 3225 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'00000028' X'00000000' X'00000000' X'FFFFFFFF'
    X'000001FF' X'00000C99' SQL DIAGNOSTIC INFORMATION

  4. #4
    Join Date
    Feb 2014
    Posts
    5
    Hi Tried it and getting below error:

    DSNT408I SQLCODE = -775, ERROR: STATEMENT SPECIFIED IN SQL ROUTINE IS NOT
    ALLOWED WITHIN A COMPOUND STATEMENT

  5. #5
    Join Date
    Feb 2014
    Posts
    5
    Sorry, i got it deployed, but when i tried it in actual code, the value become null, any ideas??

  6. #6
    Join Date
    Feb 2014
    Posts
    5
    Hi we Have tried something and it worked.


    DECLARE V_SQL VARCHAR(1000);
    DECLARE S_SQL VARCHAR(1000);
    DECLARE GLOBAL TEMPORARY TABLE SESSION.HEX_NM
    (C1 VARCHAR(100));

    SET V_SQL =
    'INSERT INTO SESSION.HEX_NM (C1) VALUES (X'''||V_INPUT||''')';
    PREPARE S_SQL FROM V_SQL;
    EXECUTE S_SQL ;

    SELECT C1 INTO V_OUTPUT_HEX FROM SESSION.HEX_NM;


    Just Hoping DBA will approved.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What exact code did you executed?
    and against what source data did you execued your code?

    How did you know the value become null?


    Here is my test result on DB2/NT 9.7.5.
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT trans_id
         , VARCHAR( X2C(trans_id) , 15 )        AS x2c_trans_id
         , VARCHAR( HEX( X2C(trans_id) ) , 30 ) AS hex_x2c_trans_id
     FROM  (VALUES '00CC6FF8181B7DC526000168034040' ) AS p(trans_id)
    ;
    ------------------------------------------------------------------------------
    
    TRANS_ID                       X2C_TRANS_ID    HEX_X2C_TRANS_ID              
    ------------------------------ --------------- ------------------------------
    00CC6FF8181B7DC526000168034040                 00CC6FF8181B7DC526000168034040
    
      1 record(s) selected.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Or

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT trans_id
         , VARCHAR( X2C(trans_id) , 15 )        AS "X2C(trans_id)"
         , CASE
           WHEN X2C(trans_id) IS NULL THEN
                'The result was NULL.' 
           ELSE 'The result was NOT NULL'
           END  AS "Result of X2C(trans_id)"
     FROM  (VALUES '00CC6FF8181B7DC526000168034040' ) AS p(trans_id)
    ;
    ------------------------------------------------------------------------------
    
    TRANS_ID                       X2C(trans_id)   Result of X2C(trans_id)
    ------------------------------ --------------- -----------------------
    00CC6FF8181B7DC526000168034040                 The result was NOT NULL
    
      1 record(s) selected.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •