Problem bulk inserting hexadecimal data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

Problem bulk inserting hexadecimal data

Author

Topic

MuffinMan
Posting Yak Master

107 Posts

Posted - 2001-12-18 : 14:21:49

I am trying to bulk insert a file that contains a hexadecimal value (and other fields). {The data is polled from the routers on our network every five minutes and needs to be quickly inserted into the database.}

Here is a typical line from the file:
0x3c179b41,192.192.161.188,1.3.6.1.4.1.9.2.2.1.1.8.1,0,1,11197000

The first field is a hex representation of a Unix timestamp. Using the trusty Windows Calculator in scientific mode, we find that 0x3c179b41 (hex) = 1008180033 (decimal). That is 1008180033 seconds since January 1, 1970. Using the SQL Server DatAdd function, this number converts to 2001-12-12 18:00:33.000 GMT {i.e., select DateAdd(s,1008180033,'1/1/1970') as GMT_Date}.

My problem is I am unable to get the Hex value into a VarBinary field in the database. I tried storing the data as VarChar, but converting from VarChar to VarBinary returns incorrect results

Example:


create table #tmp (HexValue varbinary(20) , HexAsChar varchar(20) )

insert #tmp
select 0x3c179b41 , '0x3c179b41'

select
 convert(int, convert(varbinary,HexValue)) as Binary_To_Int
 , DateAdd(s,convert(int, convert(varbinary,HexValue)),'1/1/1970') as Binary_To_Int_To_Date
 , convert(int, convert(varbinary,HexAsChar)) as Char_To_Binary_To_Int
 , DateAdd(s,convert(int, convert(varbinary,HexAsChar)),'1/1/1970') as Char_To_Binary_To_Int_To_Date
from #tmp

Results:


Binary_    Binary_To_Int_          Char_To_       Char_To__Binary_
To_Int     To_Date                 Binary_To_Int  To_Int_To_Date
---------- ----------------------- -------------  -----------------------
1008180033 2001-12-12 18:00:33.000 962737201      2000-07-04 19:00:01.000

Can anyone tell me how to get this data in the database? The table I am inserting the data into is a staging table. From this table I will clean up the data and dump it into another table. Thus, it is perfectly acceptable to store the Hex timestamp as a VarChar in the staging table as long as I can successfully convert it and store it as a SQL Server datetime datatype in the final table.

MuffinMan

MuffinMan
Posting Yak Master

107 Posts

Posted - 2001-12-20 : 09:06:20

Well, try as I might, I could not get the data into the table in hexadecimal format using Bulk Insert. So, I settled on inserting the field as VarChar and then using a user defined function to convert it from hexidecimal to integer.

Here is the function:


CREATE FUNCTION ufn_ConvertHexToInt
(   @HexValue varchar(20))
RETURNS int
AS  
BEGIN 

declare @HexChar varchar(20) , @xPos int, @loop int , @MaxLen int
 , @decimal int , @char char(1) , @number int

select @HexChar = '' , @xPos = 0 , @loop = 0 , @MaxLen = 0
 , @decimal = 0 , @char = '' , @number = 0

select @HexChar = @HexValue

select @xPos =  charIndex('x',@HexChar)

if @xPos > 0
	select @HexChar = Substring(@HexChar,@xPos+1,20)

select @MaxLen = len(@HexChar)
select @HexChar = reverse(@HexChar)

if (@HexChar = '0')
	return(0)

while @loop < @MaxLen
begin
	select @char = Substring(@HexChar,@loop+1,1)
	select @number = Case
			when @char = 'a' then 10
			when @char = 'b' then 11
			when @char = 'c' then 12
			when @char = 'd' then 13
			when @char = 'e' then 14
			when @char = 'f' then 15
			else convert(int, @char)
	end

	select @decimal = @decimal + (@number * (power(16,@loop)))
	select @loop = (@loop + 1)

end

return(@decimal)

END

Usage:


select MyDb.dbo.ufn_ConvertHexToInt('0x3c179b41') as TheInteger
 ,  DateAdd(s, MyDb.dbo.ufn_ConvertHexToInt('0x3c179b41'), '1/1/1970') as TheGmtDate

Results:


TheInteger  TheGmtDate
----------- -----------------------
1008180033  2001-12-12 18:00:33.000

MuffinMan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources