Wednesday, October 28, 2020

[Tool] Display contents of an X++ container from SQL database field

Almost 15 years ago AndyD from AxForum blew me away with a post that was giving a glimpse into how the X++ containers are stored in the database. 

Ever since then I had a TODO for myself to write a blog post explaining this in detail and covering the various types that can be stored, basically allowing to review the contents of the container from DB without writing X++ code.

Luckily, today is the day! As they say, 


A colleague of mine, Maciej Plaza, has written a stored procedure, that shows the contents of a container stored in a database field. 

Some inspiration was taken from the following blog post too: http://abraaxapta.blogspot.com/2011/06/accessing-dynamics-ax-containers-from.html

Here's an example of how you could use this to see the batch task's infolog message contents:

declare @bin as varbinary(max);

select top(1) @bin = info from batch where recid = <recid>

exec ##PRINT_CONTAINER @bin

Here is how the output looks like:

Browsing the X++ container contents in SQL


Here is the stored proc (Also available as a file in OneDrive):

CREATE PROCEDURE ##PRINT_CONTAINER @container VARBINARY(MAX)
AS
BEGIN
DECLARE @pos AS int;
SET @pos = 1;
DECLARE @offset AS int;
DECLARE @indent as int;
set @indent = 0;
declare @result as varchar(max);
IF SUBSTRING(@container, @pos, 2) = 0x07FD
	BEGIN
	set @result = '<container>' + char(13);
	set @indent = @indent + 2;
	SET @pos = @pos + 2;
	WHILE @indent > 0
		BEGIN
			IF SUBSTRING(@container, @pos, 1) = 0x00 --STRING
				BEGIN
					set @result = @result + replicate(' ', @indent) + '<string>'
					SET @pos = @pos + 1;
					SET @offset = 0;
					declare @string as varchar(max);
					set @string = '';
					WHILE SUBSTRING(@container, @pos + @offset, 2) <> 0x0000
						BEGIN
							SET @string = @string + 
								CHAR(CAST(REVERSE(SUBSTRING(@container, @pos + @offset, 2)) AS binary(2)))
							SET @offset = @offset + 2;
						END
					SET @pos = @pos + @offset + 2;
					set @result = @result + @string + '</string>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x01 --INT
				BEGIN
					set @result = @result + replicate(' ', @indent) + '<int>'
					SET @pos = @pos + 1;
					declare @int as int;
					SET @int = CAST(CAST(REVERSE(SUBSTRING(@container, @pos, 4)) AS binary(4)) as int);
					SET @pos = @pos + 4;
					set @result = @result + cast(@int as varchar(max)) + '</int>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x02 --REAL
				BEGIN
					set @result = @result + replicate(' ', @indent) + '<real>'
					SET @pos = @pos + 1;
					DECLARE @temp as binary(8);
					SET @temp = CAST(REVERSE(SUBSTRING(@container, @pos + 2, 8)) AS binary(8));
					DECLARE @val bigint;
					SET @val = 0;
					DECLARE @dec AS int;
					SET @offset = 1;
					WHILE (@offset <= 8)
						BEGIN
							SET @val = (@val * 100) +
								(CAST(SUBSTRING(@temp, @offset, 1) AS int) / 0x10 * 10) +
								(CAST(SUBSTRING(@temp, @offset, 1) AS int) % 0x10);
							SET @offset = @offset + 1;
						END
					WHILE @val <> 0 AND @val % 10 = 0
						SET @val = @val / 10;
					SET @dec = (CAST(SUBSTRING(@container, @pos, 1) AS int) + 0x80) % 0x100;
					declare @real as real;
					SET @real = CAST(@val AS real);
					WHILE @dec >= LEN(CAST(@val AS varchar)) + 0x80
						BEGIN
							SET @real = CAST(@real AS real) * 10.0;
							SET @dec = @dec - 1;
						END
					SET @dec = (CAST(SUBSTRING(@container, @pos, 1) AS int) + 0x80) % 0x100 + 1;
					WHILE @dec < LEN(CAST(@val AS varchar)) + 0x80
						BEGIN
							SET @real = CAST(@real AS real) / 10.0;
							SET @dec = @dec + 1;
						END
					IF SUBSTRING(@container, @pos + 1, 1) = 0x80
						SET @real = 0 - CAST(@real AS real);
					SET @pos = @pos + 10;
					set @result = @result + @real + '</real>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x03 --DATE
				BEGIN
					set @result = @result + replicate(' ', @indent) + '<date>'
					SET @pos = @pos + 1;
					DECLARE @year char(4);
					DECLARE @month char(2);
					DECLARE @day char(2);
					SET @year = SUBSTRING(@container, @pos, 1) + 1900;
					SET @month = SUBSTRING(@container, @pos + 1, 1) + 1;
					SET @day = SUBSTRING(@container, @pos + 2, 1) + 1;
					IF LEN(@month) < 2
						SET @month = '0' + @month;
					IF LEN(@day) < 2
						SET @day = '0' + @day;
					declare @date as date;
					SET @date = CAST(@year + '-' + @month + '-' + @day AS date);
					SET @pos = @pos + 3;
					set @result = @result + convert(varchar(max), @date) + '</date>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x04 --ENUM
				BEGIN
					set @result = @result + replicate(' ', @indent) + '<enum>'
					SET @pos = @pos + 1;
					declare @enum as int;
					SET @enum = CAST(SUBSTRING(@container, @pos, 1) AS int);
					SET @pos = @pos + 3;
					set @result = @result + @enum + '</enum>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x06 --DATETIME
				BEGIN
					set @result = @result + replicate(' ', @indent) + '<datetime>'
					SET @pos = @pos + 1;
					DECLARE @year2 char(4);
					DECLARE @month2 char(2);
					DECLARE @day2 char(2);
					DECLARE @hour char(2);
					DECLARE @min char(2);
					DECLARE @sec char(2);
					SET @year2 = SUBSTRING(@container, @pos, 1) + 1900;
					SET @month2 = SUBSTRING(@container, @pos + 1, 1) + 1;
					SET @day2 = SUBSTRING(@container, @pos + 2, 1) + 1;
					SET @hour = SUBSTRING(@container, @pos + 3, 1) + 0;
					SET @min = SUBSTRING(@container, @pos + 4, 1) + 0;
					SET @sec = SUBSTRING(@container, @pos + 5, 1) + 0;
					IF LEN(@month2) < 2
						SET @month2 = '0' + @month2;
					IF LEN(@day2) < 2
						SET @day2 = '0' + @day2;
					IF LEN(@hour) < 2
						SET @hour = '0' + @hour;
					IF LEN(@min) < 2
						SET @min = '0' + @min;
					IF LEN(@sec) < 2
						SET @sec = '0' + @sec;
					declare @datetime as datetime;
					SET @datetime = CAST(@year2 + '-' + @month2 + '-' + @day2 + ' ' + @hour + ':' + @min + ':' + @sec AS datetime);
					SET @pos = @pos + 12;
					set @result = @result + @datetime + '</datetime>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x07 --CONTAINER
				BEGIN
					set @result = @result + replicate(' ', @indent) + '<container>' + char(13);
					SET @pos = @pos + 3;
					set @indent = @indent + 2;
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0xFF --CONTAINER END
				BEGIN
					SET @pos = @pos + 1;
					set @indent = @indent - 2;
					set @result = @result + replicate(' ', @indent) + '</container>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x2D --GUID
				BEGIN
					SET @pos = @pos + 1;
					SET @offset = 0;
					declare @guid as uniqueidentifier
					SET @guid = CAST(CAST(
						REVERSE(SUBSTRING(@container, @pos, 4)) +
						REVERSE(SUBSTRING(@container, @pos + 4, 4)) +
						REVERSE(SUBSTRING(@container, @pos + 8, 4)) +
						REVERSE(SUBSTRING(@container, @pos + 12, 4)
					) AS binary(16))AS uniqueidentifier);
					SET @pos = @pos + 16;
					set @result = @result + replicate(' ', @indent) + '<guid>' + cast(@guid as varchar(80)) + '</guid>' + char(13)
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x30 --BLOB
				BEGIN
					SET @pos = @pos + 1;
					SET @offset = CAST(CAST(REVERSE(SUBSTRING(@container, @pos, 4)) AS binary(4)) AS int);
					SET @pos = @pos + 4;
					declare @blob as varbinary(max);
					SET @blob = CAST(SUBSTRING(@container, @pos, @offset) AS varbinary(max));
					SET @pos = @pos + @offset;
					set @result = @result + replicate(' ', @indent) + '<blob>' + CONVERT(VARCHAR(max), @blob, 2) + '</blob>' + char(13);
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0x31 --INT64
				BEGIN
					SET @pos = @pos + 1;
					declare @bigint as bigint;
					SET @bigint = CAST(CAST(REVERSE(SUBSTRING(@container, @pos, 8)) AS binary(8)) AS bigint);
					SET @pos = @pos + 8;
					set @result = @result + replicate(' ', @indent) + '<int64>' + cast(@bigint as varchar(max)) + '</int64>' + char(13)
				END
			ELSE IF SUBSTRING(@container, @pos, 1) = 0xFC --ENUMLABEL
				BEGIN
					SET @pos = @pos + 1;
					DECLARE @value int;
					DECLARE @name varchar(40);
					SET @value = SUBSTRING(@container, @pos, 1);
					SET @pos = @pos + 1;
					SET @offset = 0;
					SET @name = '';
					WHILE SUBSTRING(@container, @pos + @offset, 2) <> 0x0000
						BEGIN
							SET @name = CAST(@name AS varchar(40)) + 
								CHAR(CAST(REVERSE(SUBSTRING(@container, @pos + @offset, 2)) AS binary(2)))
							SET @offset = @offset + 2;
						END
					declare @enumlabel as varchar(max);
					SET @enumlabel = CAST(@name + ':' + CAST(@value as varchar(3)) as varchar(44));
					SET @pos = @pos + @offset + 2;
					set @result = @result + replicate(' ', @indent) + '<enumlabel>' + @enumlabel + '</enumlabel>' + char(13)
				END
			ELSE
			begin
			declare @errormsg varchar(max);
			set @errormsg = 'Unexpected type ' + CONVERT(VARCHAR(1000), SUBSTRING(@container, @pos, 1), 2);
			throw 50000, @errormsg, 1;
			end
		END
	END
print @result
END

Hope this helps!

1 comment:

  1. CREATE PROCEDURE ##PRINT_CONTAINER throws the following error:

    Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in add operator.

    Any ideas?

    ReplyDelete

Please don't forget to leave your contact details if you expect a reply from me. Thank you