распарсить из SQL bin поля с содерж. контейнер AX

[oleggy]

Привет.
Возможно ли из SQL распарить состав контейнера, если сама структура контейнера известна? Т.е. кол-во элементов в нем, типы данных каждого элемента.

[SRF]

гугл в помощь https://kashperuk.blogspot.com/2020/...container.html

[dech]

Как-то надо было распарсить контейнер из SysDatabaseLog для SSRS-отчета.
Вот кусок хранимой процедуры.
Пользуйтесь на здоровье!

PHP код:

DECLARE @Data            VARBINARY(MAX)
      , @FieldId        INT
      , @FieldType        TINYINT
      , @NewValue        NVARCHAR(MAX)
      , @OldValue        NVARCHAR(MAX)
      , @Marker            INT
      , @Array            INT
      , @Id                INT
      , @Tmp            VARBINARY(MAX)
      , @CreateDate        DATETIME
      , @CreateTime        INT
      , @CreateBy        VARCHAR(5)
      , @LogRecId        BIGINT
      , @LogType        INT
      , @Description    VARCHAR(60)

DECLARE DBLCursor CURSOR FOR
SELECT [DBL].[CreatedDate]
     , [DBL].[CreatedTime]
     , [DBL].[CreatedBy]
     , [DBL].[Description]
     , [DBL].[LogType]
     , [DBL].[LogRecId]
     , [DBL].[Data]
FROM SysDataBaseLog AS [DBL]
WHERE [DBL].DataAreaId = N'CLT'
  AND [DBL].Table_ = 315
  AND [DBL].CreatedDate BETWEEN @FromDate AND @ToDate

DECLARE    @DataSet TABLE (
            [Id]            INT PRIMARY KEY
          , [FieldId]        INT
          , [Array]            INT
          , [LogType]        INT
          , [Description]    VARCHAR(60)
          , [CreatedDate]    DATETIME
          , [CreatedTime]    INT
          , [CreatedBy]        VARCHAR(5)
          , [LogRecId]        BIGINT
          , [NewValue]        NVARCHAR(MAX)
          , [OldValue]        NVARCHAR(MAX)
        )

SET @Id = 0

OPEN DBLCursor

FETCH NEXT FROM DBLCursor
INTO @CreateDate, @CreateTime, @CreateBy, @Description, @LogType, @LogRecId, @Data

WHILE @@FETCH_STATUS = 0
BEGIN

  IF (@Data IS NOT NULL AND ASCII(SUBSTRING(@Data, 1, 1)) = 0x07 AND ASCII(SUBSTRING(@Data, 2, 1)) = 0xFD)
  BEGIN -- Inside container
    SET @Marker = 3

    WHILE (@Marker <= DATALENGTH(@Data) 
      AND ASCII(SUBSTRING(@Data, @Marker, 1)) = 0x07
      AND ASCII(SUBSTRING(@Data, @Marker+1, 1)) = 0x07
      AND ASCII(SUBSTRING(@Data, @Marker+2, 1)) = 0xFD)
    BEGIN
      SET @Marker = @Marker + 3
      SET @Tmp = SUBSTRING(@Data, @Marker, 5)

      --  @Tmp[1] = 0x01 (int)
      SET @FieldId = ASCII(SUBSTRING(@Tmp, 2, 1)) + 256 * ASCII(SUBSTRING(@Tmp, 3, 1))
      SET @Array = ASCII(SUBSTRING(@Tmp, 4, 1)) + 256 * ASCII(SUBSTRING(@Tmp, 5, 1))

      SET @Marker = @Marker + 5
      SET @FieldType = ASCII(SUBSTRING(@Data, @Marker, 1))
      SET @Marker = @Marker + 1
      
      SET @NewValue = dbo.PPOPeekValue(@FieldId, @FieldType, @Data, @Marker)
      SET @Marker = @Marker + CASE @FieldType
        WHEN 0 THEN DATALENGTH(@NewValue)+2        -- str
        WHEN 1 THEN 4                            -- int/time
        WHEN 2 THEN 10                            -- real
        WHEN 3 THEN 3                            -- date
        WHEN 4 THEN 3                            -- enum
        WHEN 8 THEN DATALENGTH(@NewValue)+2        -- text
        WHEN 45 THEN 16                            -- guid
        WHEN 49 THEN 8                            -- int64
      END

      SET @OldValue = 0
      IF ASCII(SUBSTRING(@Data, @Marker, 1)) <> 0xFF
      BEGIN
        SET @FieldType = ASCII(SUBSTRING(@Data, @Marker, 1))
        SET @Marker = @Marker + 1

        SET @OldValue = dbo.PPOPeekValue(@FieldId, @FieldType, @Data, @Marker)
        SET @Marker = @Marker + CASE @FieldType
          WHEN 0 THEN DATALENGTH(@NewValue)+2        -- str
          WHEN 1 THEN 4                                -- int/time
          WHEN 2 THEN 10                            -- real
          WHEN 3 THEN 3                                -- date
          WHEN 4 THEN 3                                -- enum
          WHEN 8 THEN DATALENGTH(@NewValue)+2        -- text
          WHEN 45 THEN 16                            -- guid
          WHEN 49 THEN 8                            -- int64
        END
      END

      IF @FieldId <> 61450        -- Skip RecVersion fields
      BEGIN
        SET @Id = @Id + 1
        INSERT INTO @DataSet ([Id], [FieldId], [Array], [CreatedDate], [CreatedTime], [CreatedBy], [Description], [LogType], [LogRecId], [NewValue], [OldValue])
          VALUES (@Id, @FieldId, @Array, @CreateDate, @CreateTime, @CreateBy, @Description, @LogType, @LogRecId, @NewValue, @OldValue)
      END
      
      -- Skip last byte of container (0xFF)
      SET @Marker = @Marker + 1
    END
  END

  FETCH NEXT FROM DBLCursor
  INTO @CreateDate, @CreateTime, @CreateBy, @Description, @LogType, @LogRecId, @Data
END

CLOSE DBLCursor
DEALLOCATE DBLCursor 


[dech]

Там еще идет функция вдовесок. В принципе не сложная, думаю.

PHP код:

CREATE FUNCTION [dbo].[PPOPeekValue](@FieldId INT, @FieldType TINYINT, @Data VARBINARY(MAX), @Position BIGINT) RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result        NVARCHAR(MAX)
          , @N            INT
          , @IntValue    INT
          , @Exp        INT
          , @Sign        INT
          , @FloatValue    FLOAT
          , @Year        INT
          , @Month        TINYINT
          , @Day        TINYINT
          , @BigValue    BIGINT

    -- decode string/text values
    IF @FieldType = 0 OR @FieldType = 8
    BEGIN
        SET @N = 0
        WHILE CAST(SUBSTRING(@Data, @Position + @N, 2) AS SMALLINT) <> 0
            SET @N = @N + 2
        SET @Result = CAST(SUBSTRING(@Data, @Position, @N) AS NVARCHAR(MAX))
    END ELSE

    -- decode integer/time values
    IF @FieldType = 1
    BEGIN
        SET @IntValue = 0
        SET @N = 3
        WHILE @N >= 0
        BEGIN
            SET @IntValue = @IntValue * 256 + ASCII(SUBSTRING(@Data, @Position + @N, 1))
            SET @N = @N - 1
        END
        IF @FieldId = 61441 OR @FieldId = 61445
        BEGIN
            SET @Result = CAST(dbo.PPOInt2Time(@IntValue) AS NVARCHAR(MAX));
        END
        ELSE
            SET @Result = CAST(@IntValue AS NVARCHAR(MAX))
    END ELSE

      -- decode float values
      IF @FieldType = 2
      BEGIN
        SET @N = 9
        SET @FloatValue = 0;
        WHILE @N > 1
        BEGIN
            SET @Exp = ASCII(SUBSTRING(@Data, @Position + @N, 1))
            SET @FloatValue = 100 * @FloatValue + (@Exp & 0xF0) / 16 * 10 + (@Exp & 0x0F)
            SET @N = @N - 1
        END
        SET @Sign = CASE WHEN ASCII(SUBSTRING(@Data, @Position + 1, 1)) & 0x80 = 0 THEN 1 ELSE -1 END
        SET @Exp = ASCII(SUBSTRING(@Data, @Position, 1))
        IF (@Exp >= 0x80)
            SET @Exp = @Exp - 0x100
        SET @FloatValue = @FloatValue * @Sign * POWER(CAST(10 AS FLOAT), @Exp - 15)
        SET @Result = CAST(STR(@FloatValue, 16, 3) AS NVARCHAR(MAX))
    END ELSE
    
    -- decode date values
    IF @FieldType = 3
    BEGIN
        SET @Year   = 1900 + ASCII(SUBSTRING(@Data, @Position, 1))
        SET @Month  =    1 + ASCII(SUBSTRING(@Data, @Position + 1, 1))
        SET @Day    =    1 + ASCII(SUBSTRING(@Data, @Position + 2, 1))
        SET @Result = CAST(@Month AS VARCHAR(2)) + '/' + CAST(@Day AS VARCHAR(2)) + '/' + CAST(@Year AS VARCHAR(4))
    END ELSE

    -- decode enum values
    IF @FieldType = 4
    BEGIN
        SET @N = ASCII(SUBSTRING(@Data, @Position, 1))
        SET @IntValue = ASCII(SUBSTRING(@Data, @Position+1, 1)) + 256 * ASCII(SUBSTRING(@Data, @Position+2, 1))
        SELECT @Result = EnumItemLabel FROM SRSAnalysisEnums AS [AE]
            WHERE [AE].EnumId = @IntValue
              AND [AE].EnumItemValue = @N
    END ELSE

    -- decode int64 values
    IF @FieldType = 49
    BEGIN
        SET @BigValue = 0
        SET @N = 7
        WHILE @N >= 0
        BEGIN
            SET @BigValue = @BigValue * 256 + ASCII(SUBSTRING(@Data, @Position + @N, 1))
            SET @N = @N - 1
        END
        SET @Result = CAST(@BigValue AS VARCHAR(MAX))
    END

    RETURN @Result
END