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 |
