Hey there!
It has been quite a long time since I asked this question so I want to share my progress with you.
Introduction and Context:
There is a measurement system that is measuring 20 different values like temperature or pressure every minute and stores them into a database. My task is to access these values directly. So far, so easy, theoretically...
Tables of Interest in the Database
In order to access the measurement values directly, I only need two tables in the database. The measurements are saved in the first table. Every measurement has a start- and stoptime and a discrete number of measurementvalues ("valueCount") stored in the column "value":
Header: analogChannelRef | startTime | stopTime | valueCount | value | crc
DBKey: PK, FK | PK | | | |
Type: bigint | datetime | datetime | int | varbinary | int
Exmpl: 10181 | 03.08.2018 00:01:00 | 03.08.2018 00:10:00 | 10 | (BLOB) | 61776375
The column "analogChannelRef" is in a relationship to the second Table that contains informations about the measured value. The column "channel number" states which input of the the measurement system is used for a specific measurement. The columns "tag" and "unit" are used to give further informations about the measurement. The column "precision" defines how many decimals are shown to the enduser:
Header: id | channel number | tag | unit | precision | crc
DBKey: PK | | | | |
Type: bigint | int | nvarchar(255) | nvarchar(255) | tinyint | int
Exmpl: 10181 | 15 | Pressure | bar | 2 | -740224624
So, where is the Problem?
There isn't one value for each minute, so i cannot simply read out the values. The table with the measurement data condenses ten values into one row, which is indicated by a "ValueCount"-Column with a Value of "10" in each row. For the actual "Value"-Column I can just see "(BLOB)". After a bit of research I found the datatype of the "Value"-Column is "varbinary(max)". So i tried to convert it via sql, this is the result for one example value:
SELECT CONVERT(nvarchar(max), value, 0) FROM mydatabase
_ᩲ웹퓤_耀㣽_____렀᳝ࣔ_愀·_____ꃾ은퓤_ 㤈_____䐀摤ࣔ_ꈀ8_____➊있퓤_뀀㤻_____퀀ꯪࣔ_珀8_____긖쟏퓤_䀀㥅_____尀ࣔ_畀9_____㒢젗퓤__㥳_____㫷ࣔ_ꤠ8_____
(Underscore represents Space in result)
SELECT CONVERT(nvarchar(max), value, 1) FROM mydatabase
0x0100721AF9C6E4D408000080FD380000000000000000000000B8DD1CC7E4D40800000061B70000000000000000000000FEA040C7E4D40800002008390000000000000000000000446464C7E4D408000000A23800000000000000000000008A2788C7E4D4080000B03B390000000000000000000000D0EAABC7E4D4080000C07338000000000000000000000016AECFC7E4D408000040453900000000000000000000005C71F3C7E4D40800004075390000000000000000000000A23417C8E4D40800000073390000000000000000000000E8F73AC8E4D408000020A93800000000000000000000
As Max Vernon answered (thanks again your help), it looks like the result is structured in 10 packages, what would fit to the number of measurements stored into one "value"-cell. But I couldn't find a solution on how to get readable data based on that information.
Possible Solution
To find a solution on how to get the actual values, I further investigated the Microsoft SQL Server Management Studio and found so called "procedures". After a bit of research on procedures I found one that looked really promesing for my task:
USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[report_getAnalogvalue](
@channelId BIGINT,
@startDate DATETIME,
@endDate DATETIME,
@configurationId BIGINT,
@userId BIGINT,
@maxNumber BIGINT)
AS
DECLARE @AnalogValueTable TABLE
(
"timestamp" DATETIME,
"timestampoffset" BIGINT,
"value" INT,
"valueState" VARBINARY,
"dataIntegrity" TINYINT -- 1 = true, 0 = false
)
DECLARE @values VARBINARY(MAX),
@record_length INT,
@valueCount INT,
@crc INT,
@record_data VARBINARY(23),
@record_timestamp DATETIME,
@record_value VARBINARY(4),
@record_valuestate VARBINARY,
@record_timestampoffset BIGINT
DECLARE @record_dataIntegrity TINYINT
SET @record_dataIntegrity = 0
DECLARE @rawTime VARBINARY(8)
DECLARE @record_counter BIGINT
SET @record_counter = 1
DECLARE @Ticks BIGINT,
@Days FLOAT
DECLARE @isAdministrator BIT
SET @isAdministrator = 0
BEGIN
SET NOCOUNT ON
EXECUTE @isAdministrator = report_checkAccess @configurationId, @userId
DECLARE c CURSOR FOR
SELECT a.value, a.valueCount, a.crc
FROM "AnalogValue" a INNER JOIN "AnalogChannel" b ON a."analogChannelRef" = b."id"
WHERE a."analogChannelRef" = @channelId
AND (@startDate BETWEEN a."startTime" AND a."stopTime"
OR @endDate BETWEEN a."startTime" AND a."stopTime"
OR a."startTime" BETWEEN @startDate AND @endDate
OR a."stopTime" BETWEEN @startDate AND @endDate)
AND (b."configurationRef" = @configurationId)
AND (@isAdministrator = 1)
OPEN c
FETCH NEXT FROM c INTO @values, @valueCount, @crc
WHILE @@FETCH_STATUS = 0 BEGIN
SET @record_length = DATALENGTH(@values)
IF (@record_length <> @valueCount * 23 + 1 OR @record_length < 24)
BREAK
IF dbo.GET_CRC32(@values) = @crc
SET @record_dataIntegrity = 1
SET @values = SUBSTRING(@values, 2, @record_length-1)
SET @record_length = DATALENGTH(@values)
WHILE (@record_length >= 23)
BEGIN
IF (@record_counter > @maxNumber)
BEGIN
CLOSE c
DEALLOCATE c
SELECT * FROM @AnalogValueTable
RETURN
END
SET @record_data = SUBSTRING(@values, 1, 23)
SET @rawTime = SUBSTRING(@record_data, 1, 8)
EXECUTE ParseTimestamp @rawTime, @timestamp=@record_timestamp OUTPUT
IF (@record_timestamp > @endDate)
BREAK
IF (@record_timestamp BETWEEN @startDate AND @endDate)
BEGIN
-- use REVERSE to convert from little endian to big endian
SET @record_value = CAST(CONVERT(BINARY(4), REVERSE(SUBSTRING(@record_data, 10, 4))) AS INT)
SET @record_valuestate = SUBSTRING(@record_data, 14, 1)
-- use reverse to convert from little endian to big endian
SET @record_timestampoffset = CAST(CONVERT (BINARY(8), REVERSE (SUBSTRING(@record_data, 16, 8))) AS BIGINT)
INSERT INTO @AnalogValueTable("timestamp", timestampoffset, value, valueState, dataIntegrity)
VALUES(@record_timestamp, @record_timestampoffset, @record_value, @record_valuestate, @record_dataIntegrity)
SET @record_counter = @record_counter + 1
END
SET @record_length = @record_length - 23
SET @values = SUBSTRING(@values, 24, @record_length)
END
FETCH NEXT FROM c INTO @values, @valueCount, @crc
END
CLOSE c
DEALLOCATE c
SELECT * FROM @AnalogValueTable
END
If I call this procedure with the right input parameters I actually get values the values for each minute.
So my first question is: How does this procedure work?
So, everything is fine?
I'm not sure. The Values that I get by calling the procedure are in form of Integers. But my measurements are numbers like 80,51 °C. So I have to devide the Integer I get by a specific number to get the real value.
Hence, my second question is: Why are the Values stored as Integers?
Finally, the correlation between the Integer-Values and the real Values is quite interesting. In order to find the correlation, I plotted the Integer-Values over the real Values. I expected a linear function, but i got multiply linear functions for specific values of the real value:
These regions seem to follow a pattern: 0,5 - 1; 1 - 2; 2 - 4; 4 - 8; and so on.
So my last question is: Is there any reason, from the perspective of an database expert, to store values like this?
Lars
