set verify;

create module HEX_STRING_PACKAGE
    comment is 'Hex string support routines'

    procedure OTS$CVT_L_TZ
        (in :varying_input_value bigint by reference
        ,out :fixed_length_resultant_string char(16) by descriptor
        ,in :number_of_digits integer default 16 by value
        ,in :input_value_size integer default 8 by value
        );
    external
        location 'SYS$SHARE:LIBRTL.EXE'
        language GENERAL
        parameter style GENERAL
        usage is LOCAL
        comment is 'Convert Integer to Hexadecimal Text';

/*
    Definition of OTS$CVT_L_TZ from OpenVMS V7.3 HELP RTL OTS$

         The Convert Integer to Hexadecimal Text routine converts an
         unsigned integer to a hexadecimal ASCII text string. OTS$CVT_
         L_TZ supports Fortran Zw and Zw.m output conversion formats.

         Format

           OTS$CVT_L_TZ  varying-input-value
                         ,fixed-length-resultant-string
                         [,number-of-digits] [,input-value-size]
*/

    function INTEGER_TO_HEX_STRING
        (in :v bigint
	,in :s_size integer = 16)
        returns varchar(16)
	comment is 'Simple jacket to the procedure OTS$CVT_L_TZ'
	/	   'Pass the integer value (64 bits) and optional result string size';
    begin
    declare :tz char(16);
    call OTS$CVT_L_TZ (:v, :tz, :s_size);
    return TRIM(:tz);
    end;

    procedure OTS$CVT_TZ_L
        (in :fixed_or_dynamic_input_string char(20) by descriptor
        ,out :varying_output_value bigint by reference
        ,in :output_value_size integer default 8 by value
        ,in :flags_value integer default 1 by value
        );
    external
        location 'SYS$SHARE:LIBRTL.EXE'
        language GENERAL
        parameter style GENERAL
        usage is LOCAL
        comment is 'Convert Hexadecimal Text to Unsigned Integer';

/*
    Definition of OTS$CVT_TZ_L from OpenVMS V7.3 HELP RTL OTS$

         The Convert Hexadecimal Text to Unsigned Integer routine converts
         an ASCII text string representation of an unsigned hexadecimal
         value to an unsigned integer. The integer value can be of
         arbitrary length but is typically a byte, word, longword, or
         quadword. The default size of the result is a longword.

         Format

           OTS$CVT_TZ_L  fixed-or-dynamic-input-string
                         ,varying-output-value [,output-value-size]
                         [,flags-value]
*/

    function HEX_STRING_TO_INTEGER
        (in :tz varchar(20))
        returns bigint
	comment is 'Simple jacket to the procedure OTS$CVT_TZ_L';
    begin
    -- include the symbolic names for the SQLSTATE values
    -- note: SQL will discard any definitions we don't actually use
    @SYS$SHARE:SQLSTATE.SQL
    declare :v bigint;
    call OTS$CVT_TZ_L (:tz, :v);
    -- SQL doesn't allow us to test the status of this routine
    -- so validate the result.  We get zero on failure.
    if :v = 0
    then
        -- the routine may have failed to convert the string
        -- double check before raising an error
        if INTEGER_TO_HEX_STRING (:v) not containing TRIM(:tz)
        then
            -- invalid data in the string, report error
            signal :SQLSTATE_DATA_CAST;
        end if;
    end if;
    return :v;
    end;

end module;

select INTEGER_TO_HEX_STRING (cast(employee_id as bigint), 8)
    from employees limit to 10 rows;
select HEX_STRING_TO_INTEGER ('FACE')
    from rdb$database;

-- this illegal result will cause an error
select HEX_STRING_TO_INTEGER ('zzCE')
    from rdb$database;
-- test long hex string (requires quadword result)
select HEX_STRING_TO_INTEGER ('100000000280D501')
    from rdb$database;

select INTEGER_TO_HEX_STRING (HEX_STRING_TO_INTEGER ('100000000280D501'))
    from rdb$database;

show module HEX_STRING_PACKAGE;

rollback;