2

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:

enter image description here

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

  • Have you looked for details from the manufacturer? That would clearly be the easiest solution. – Max Vernon Jul 5 '18 at 14:51
  • What do the values in the "other" tables suggest for precision etc? – Max Vernon Jul 6 '18 at 15:29
4

This is like shooting in the dark without further context around what the varbinary value might be encoding.

Having said that, if you ignore the first byte (the 01), it looks like the value is structured into 10 individual components of 23 bytes each, which lines up to your expectation of 10 values; reformatting the varbinary value like this helps bring out some patterns:

01
00721AF9C6E4D408000080FD3800000000000000000000
00B8DD1CC7E4D40800000061B700000000000000000000
00FEA040C7E4D408000020083900000000000000000000
00446464C7E4D408000000A23800000000000000000000
008A2788C7E4D4080000B03B3900000000000000000000
00D0EAABC7E4D4080000C0733800000000000000000000
0016AECFC7E4D408000040453900000000000000000000
005C71F3C7E4D408000040753900000000000000000000
00A23417C8E4D408000000733900000000000000000000
00E8F73AC8E4D408000020A93800000000000000000000

Ignoring the first line which consists of a single 01 by itself, and thinking about 32-bit boundaries, you might see this pattern:

00 721AF9C6 E4D40800 0080FD38 00000000000000000000
00 B8DD1CC7 E4D40800 000061B7 00000000000000000000
00 FEA040C7 E4D40800 00200839 00000000000000000000
00 446464C7 E4D40800 0000A238 00000000000000000000
00 8A2788C7 E4D40800 00B03B39 00000000000000000000
00 D0EAABC7 E4D40800 00C07338 00000000000000000000
00 16AECFC7 E4D40800 00404539 00000000000000000000
00 5C71F3C7 E4D40800 00407539 00000000000000000000
00 A23417C8 E4D40800 00007339 00000000000000000000
00 E8F73AC8 E4D40800 0020A938 00000000000000000000

If you can provide a bit more context about what might be stored in the varbinary value, I might be able to help you decode the values. For instance, the value in the 3rd column, E4D40800, might be the IP address of the instrument where the temperature is being read - that would translate to 228.212.8.0 - I translated that by getting the integer value of each byte:

SELECT CONVERT(int, 0xE4);  --returns 228
SELECT CONVERT(int, 0xD4);  --returns 212
SELECT CONVERT(int, 0x08);  --returns 8
SELECT CONVERT(int, 0x00);  --returns 0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.