I have a Hexadecimal value in VARCHAR(2) FOR BIT DATA, I need the Hexadecimal Value to be converted to Integer.
For Eg:
Code:
DECLARE tempvar VARCHAR(4) FOR BIT DATA. This contains value x'00005721', This needs to be converted as
(5*16^3)+(7*16^2)+(2* 16^1)+(1* 16^0) = 22305 (Integer)
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
Hello,
Quote:
Please help me in finding the solution.
Once you have read the varchar data into a variable, you could redefine the variable as a cobol binary number (COMP) and you would not need to do any calculation.
I'm not sure how this is a DB2 question. Possibly there is something i misunderstand.
If you clarify your requirement someone here may have a suggestion.
I think i have not clearly raised my query...Actually we are dealing in the stored Proc conversion which is coded in the SQL SERVER those PROC's needs to be converted to the DB2 and we are using the IBM DB2 Migratoin Tool kit(MKT) to do so.
There we found an issue where a variable declared in SQL server PROC has the data type of VARBINARY which the MKT converted it to the VARCHAR(4) BIT DATA which is a Equivalent data type in DB2.
The actual Problem here is, in SQLSERVER Stored PROC they CAST the VARBINARY to the INTEGER data type for the equivalent the MKT tool has to convert from the VARCHAR(4) for bit data to the Integer data type.The MKT thows an error that "The translation not succeeded" Can you give me a suggestion how we able to do this in DB2.
Here comes the CODE:
"We are facing the PROBLEM in the CASTING" I'm not sure casting is supported in DB2...
Code:
SQL SERVER STORED PROC
--| CREATE PROCEDURE GetUniqueNumFromBinary
--| -- Add the parameters for the stored procedure here
--| @bindata varbinary(8) ,
--| @uniquenum int output,
--| @userid tinyint output,
--| @databaseid tinyint output
--| AS
--| BEGIN
--| -- SET NOCOUNT ON added to prevent extra result sets from
--| -- interfering with SELECT statements.
--| SET NOCOUNT ON;
--| declare @tempunqbin varbinary(4)
--| declare @tempuserbin varbinary(2)
--| declare @tempdbbin varbinary(2)
--| -- Insert statements for procedure here
--|
--| set @tempunqbin= substring(@bindata,8,1)+substring(@bindata,7,1)+substring(@bindata,6,1)+substring(@bindata,5,1)
--| set @uniquenum = convert(int,@tempunqbin)
--|
--| set @tempuserbin = substring(@bindata,4,1)+substring(@bindata,3,1)
--| set @userid = convert(int,@tempuserbin)
--|
--| set @tempdbbin = substring(@bindata,2,1)+substring(@bindata,1,1)
--| set @databaseid = convert(int,@tempdbbin)
--| END
DB2 Stored Proc:
DROP PROCEDURE GetUniqueNumFromBinary (VARCHAR(8) FOR BIT DATA,
INTEGER,
SMALLINT,
SMALLINT )!
CREATE PROCEDURE GetUniqueNumFromBinary (v_bindata VARCHAR(8) FOR BIT DATA,
OUT v_uniquenum INTEGER,
OUT v_userid SMALLINT,
OUT v_databaseid SMALLINT )
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE l_error CHAR(5) DEFAULT '00000';
DECLARE v_tempunqbin VARCHAR(4) FOR BIT DATA;
DECLARE v_tempuserbin VARCHAR(2) FOR BIT DATA;
DECLARE v_tempdbbin VARCHAR(2) FOR BIT DATA;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET l_error = '00000';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING
BEGIN
SET l_error = SQLSTATE;
IF SUBSTR(l_error, 1, 1) >= '5'
AND SUBSTR(l_error, 1, 1) <= '9' THEN
RESIGNAL;
END IF;
END;
SET v_tempunqbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 8, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 7, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 6, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 5, 1) AS VARCHAR(1) FOR BIT DATA);
SET v_uniquenum = CAST (v_tempunqbin AS INTEGER);
SET v_tempuserbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 4, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 3, 1) AS VARCHAR(1) FOR BIT DATA);
SET v_userid = CAST (v_tempuserbin AS INTEGER);
SET v_tempdbbin = CAST (MSSQL.SUBSTRINGBIN(v_bindata, 2, 1) AS VARCHAR(1) FOR BIT DATA) || CAST (MSSQL.SUBSTRINGBIN(v_bindata, 1, 1) AS VARCHAR(1) FOR BIT DATA);
CREATE FUNCTION HexStringToINT (
@Hex varchar(255))
RETURNS int
AS
BEGIN
declare @Char char(1), @Multiply int, @Value int, @Ans int
set @Hex = reverse(@Hex)
select @Ans = 0
, @Multiply = 0
while @Multiply + 1 <= datalength(@Hex) begin
set @Char = substring(@Hex,@Multiply + 1,1)
if @Char between '0' and '9'
set @Value = cast(@Char as int)
else
select @Value = case @Char when 'A' then 10
when 'B' then 11
when 'C' then 12
when 'D' then 13
when 'E' then 14
when 'F' then 15
else 0
END
set @Ans = @Ans + (@Value * power(16,@Multiply))
set @Multiply = @Multiply + 1
end
return @Ans
END
GO
