1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
|
CREATE FUNCTION fn_cctlg_GetValuesByID(@InValueId int, @InTimeBegin nchar(32), @InTimeEnd nchar(32))
RETURNS @retTable TABLE
( Value float(53),
TimeVal varchar(32),
QualityCode int,
Flags int
)
/*Returns a result set that lists all values to given valuename directly.*/
AS
BEGIN
-- Call xp_decompress with literal parameters
declare @BLOBLength int
select @BLOBLength=2000
DECLARE @retval int
exec @retval = sp_cctlg_registerval @InValueId,@BLOBLength
if( @retval > 0 )
RETURN
DECLARE @TimeBegin datetime, @TimeEnd datetime, @BinValues varbinary(2048)
DECLARE @CTimeBegin varchar(32), @CTimeEnd varchar(32)
DECLARE @ValueNr int, @Value float(53), @Quality int, @Flags int
DECLARE @ValueAct int, @ValueCount int
SELECT @ValueCount=0, @ValueAct=0
DECLARE Value_Cursor CURSOR FOR
SELECT Timebegin,Timeend,BinValues FROM TagCompressed Where ValueID=@InValueId AND ((Timebegin <= @InTimeEnd AND Timeend >= @InTimeBegin ) OR (Timebegin <= @InTimeBegin AND Timeend >= @InTimeBegin))
OPEN Value_Cursor
FETCH NEXT FROM Value_Cursor into @TimeBegin, @TimeEnd, @BinValues
WHILE (@@FETCH_STATUS = 0 )--AND @retval = 0)
BEGIN
-- Datetime must be converted to Text, because DLL cannot use native Datetime-format
SELECT @CTimeBegin = CONVERT( VARCHAR, @TimeBegin, 21 )
SELECT @CTimeEnd = CONVERT( VARCHAR, @TimeEnd, 21 )
exec @retval = sp_cctlg_decompress @CTimeBegin, @CTimeEnd, @BinValues, @ValueAct OUTPUT
SELECT @ValueCount = @ValueCount + @ValueAct
FETCH NEXT FROM Value_Cursor into @TimeBegin, @TimeEnd, @BinValues
END
--SELECT @Value=0, @Quality=0,@Flags=0
SELECT @retval=0
SELECT @ValueAct = 0
WHILE( @retval = 0 )
BEGIN
exec @retval = sp_cctlg_getvalue @ValueAct, @CTimeBegin OUTPUT, @Value OUTPUT, @Quality OUTPUT, @Flags OUTPUT
SELECT @ValueAct = @ValueAct+1
if( @CTimeBegin >= @InTimeBegin AND @CTimeBegin <= @InTimeEnd)
BEGIN
INSERT @retTable( Value, TimeVal, QualityCode, Flags) VALUES(@Value, @CTimeBegin, @Quality, @Flags )
END
END
CLOSE Value_Cursor
DEALLOCATE Value_Cursor
exec sp_cctlg_unregisterval @InValueId
RETURN
END |
Partager