I am collecting telemetry messages into a SQL trigger, which used to work fine for several weeks (about 5 millions events ingested correctly), and I am experiencing unexpected behavior since a couple days, even though I did not modify this particular function.
The function decodes a raw payload to extract GPS coordinates, and is as such:
ALTER FUNCTION [getLongitude](@Firmware varchar(30), @RawData varchar(24))
RETURNS float
AS
BEGIN
DECLARE @EventID varchar(1),
@Longitude float;
SET @EventID = SUBSTRING(@RawData, 1, 1);
IF @Firmware = 'V1'
BEGIN
IF @EventID = '1'
BEGIN
IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
BEGIN
SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
END
ELSE
BEGIN
SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
END
END
ELSE SET @Longitude = 100; -- other events do not include GPS information
END
ELSE
BEGIN
IF @Firmware = 'V2'
BEGIN
IF @EventID = '1' OR @EventID = '2'
BEGIN
IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
BEGIN
SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
END
ELSE
BEGIN
SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
END
END
ELSE SET @Longitude = 100; -- other events do not include GPS information
END
ELSE SET @Longitude = 100; -- other firmwares are unknown
END
RETURN @Longitude;
END;
For a specific device which has firmware V1, the function returns 100 as a value for the longitude. I have included "SELECT" lines to make sure the conditions were correctly applied, and this is where I do not understand the output:
DECLARE
@RawData varchar(24),
@Firmware varchar(30),
@EventID varchar(1),
@Longitude float;
SET @RawData = '1a330000003437cd3bfe95a7';
SET @Firmware = 'V1';
SET @EventID = SUBSTRING(@RawData, 1, 1);
IF @Firmware = 'V1'
BEGIN
SELECT 'Cond1'
IF @EventID = '1'
BEGIN
SELECT 'Cond 1.1';
IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
BEGIN
select 'Cond 1.1.1';
SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
END
ELSE
BEGIN
SELECT 'Cond 1.1.2';
SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
SELECT @Longitude
END
END
ELSE
SELECT 'Cond 1.2'
SET @Longitude = 100;
END
ELSE
BEGIN
SELECT 'Cond 2'
IF @Firmware = 'V2'
BEGIN
IF @EventID = '1' OR @EventID = '2'
BEGIN
IF CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) < 8388608
BEGIN
SET @Longitude = CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int) * 215.0 / 10000000;
END
ELSE
BEGIN
SET @Longitude = -1 * (16777216 - CAST(fct.hexstrtovarbin(SUBSTRING(@RawData, 19, 6)) AS int)) * 215.0 / 10000000;
END
END
ELSE
SELECT 'Cond 2.1'
SET @Longitude = 100;
END
ELSE
SELECT 'Cond 2.2'
SET @Longitude = 100;
SELECT 'Here?'
END
SELECT @Longitude
My outputs are:
Cond 1
Cond 1.1
Cond 1.1.2
-1,9943615 -- the correct value that I expect as function output
100
How come the longitude is finally set to 100 while neither Cond 1.2, Cond 2.1 nor Cond 2.2 are displayed?
I forgot to mention that fct.hexstrtovarbin is function that changes an hexadecimal string to a varbinary:
CREATE FUNCTION fct.hexstrtovarbin(@input varchar(8000)) RETURNS varbinary(8000)
AS
BEGIN
DECLARE @Result AS varbinary(8000)
IF LEN(@input) % 2 <> 0
BEGIN
SET @Result = 0000000000;
END
ELSE
BEGIN
SET @Result = CONVERT(VARBINARY(8000), @input, 2);
END
RETURN @Result
END;
Aucun commentaire:
Enregistrer un commentaire