Building a custom reporting system with direct SQL queries to the Business Central can be done with a good understanding of the application data types and how values are persisted in the database. One interesting issue I've come across recently is reading a text value from a BLOB field. This blog post is for you if you're looking for a way to get the text values of a BLOB field directly from a SQL query without having to modify the Business Central code base.

The Problem

A BLOB field in Business Central corresponds to a database column of type image. It can hold text values far longer than a table field of type Text. Therefore, BLOB has been a reliable choice of a column type when we need to provide a way for adding detailed comments or descriptions where the length limitation of 250 characters would be an issue.

If a BLOB field is for storing a text value, it usually has its handler methods in the table which might look as follows (example from the Sales Header table, field Work Description):

    procedure GetWorkDescription() WorkDescription: Text
    var
        TypeHelper: Codeunit "Type Helper";
        InStream: InStream;
    begin
        CalcFields("Work Description");
        "Work Description".CreateInStream(InStream, TEXTENCODING::UTF8);
        if not TypeHelper.TryReadAsTextWithSeparator(InStream, TypeHelper.LFSeparator(), WorkDescription) then
            Message(ReadingDataSkippedMsg, FieldCaption("Work Description"));
    end;
    
    procedure SetWorkDescription(NewWorkDescription: Text)
    var
        OutStream: OutStream;
    begin
        Clear("Work Description");
        "Work Description".CreateOutStream(OutStream, TEXTENCODING::UTF8);
        OutStream.WriteText(NewWorkDescription);
        Modify;
    end;
Getting and setting BLOB value in AL

The text value is saved by creating a stream for the BLOB field and writing the text into it. As a result, it's stored as raw bytes in the SQL server database. It wouldn't be a big problem to convert the saved value from bytes to text with Transact-SQL. The SQL query would be as follows:

SELECT CONVERT(varchar(max), CONVERT(varbinary(max), [Work Description])) as [Work Description]
FROM [CRONUS Australia Pty_ Ltd_$Sales Header] with(nolock)
WHERE [Work Description] IS NOT NULL
SQL query to convert an image column value to text

There is one little detail, however. The bytes are compressed by Business Central before they are sent to the SQL server. The saved Work description text value into the field would be returned by the SQL query as E}[ Ï/ÊVHI-N.Ê,(ÉÌÏ, which we might doubt is useful, obviously.

An Option

The property that is responsible for BLOB value compression is Compressed, its default value is true.

"Aha! We just need to change that property to false and it's solved! And here are some Stack Overflow discussions that prove that it's working.", we might get excited. But then we remind ourselves that every non-default behavior implemented in source code requires maintenance in the future, and we should find the right solution for ourselves.

Maybe the reporting team is not responsible for the code and it's a dependency on other teams. Or maybe it's a third-party product that we have no control over. We should keep away from customizing the third-party or standard base application code, and there is no option to extend a BLOB table field by overriding the Compressed property value in an AL app. Even if we make the field change happen now, how about all the rest compressed by default BLOB fields that we have to alter the source code for? The way to make it work on the SQL side without having to change anything in the application could be a good solution in these cases.

The SQL CLR Solution

The task is simple. We need to decompress the byte data before converting it to text.  The built-in DECOMPRESS T-SQL function can look to be useful, but it's using the GZIP algorithm and not working for us.

There are multiple articles showing successful implementation of reading text from image fields. For example, Accessing Compressed Blobs from outside NAV (NAV2013) (Revisited) | deV.ch - man vs. code (wordpress.com). The conclusion that we can make from that C# code is that we're missing 2 pieces in T-SQL: there are magic bytes in compressed BLOB values that we have to check, and the values are compressed with the Deflate algorithm. Once these two problems are addressed in SQL Server, we should be able to query the database directly.

Now, how can we use .NET code to extend SQL Server functionality? With SQL Server CLR integration, a small class can be written for value handling which can be compiled as a library. The library can be uploaded to the server as an assembly, which can serve as a foundation for our custom T-SQL function.

Following the Getting Started with CLR Integration article, I needed a .NET Framework 4.8 installed that is compatible with the SQL Server 2019 on my computer (the framework version should be compatible with SQL Server versions 2012+ according to this source). Here is the code of the class to compile:

using System.Linq;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;

public class BcBlobDecompressor
{
    private static readonly byte[] BlobMagic = new byte[] { 2, 69, 125, 91 };

    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
    public static SqlBytes Decompress(SqlBytes inBytes)
    {
        if (inBytes == null || inBytes.Length < 4)
            return inBytes;

        byte[] firstFourBytes = inBytes.Buffer.Take(4).ToArray();
        if (!BlobMagicOk(firstFourBytes))
        {
            return inBytes;
        }

        using (Stream inBytesStream = inBytes.Stream)
        {
            inBytesStream.Read(firstFourBytes, 0, 4);
            var outStream = new MemoryStream();
            using (DeflateStream deflateStream = new DeflateStream(inBytesStream, CompressionMode.Decompress))
            {
                deflateStream.CopyTo(outStream);
                return new SqlBytes(outStream);
            }
        }                
    }
    private static bool BlobMagicOk(byte[] checkMagic)
    {
        return checkMagic.SequenceEqual(BlobMagic);
    }
}
BcBlobDecompressor class

The code can be copy-pasted into a file BcBlobDecompression.cs which can be put in, let's say, C:\temp\sqlclr\ folder. If we run cd C:\temp\sqlclr in the terminal to make it the current folder, the PowerShell compilation command then would look like as follows:

C:\temp\sqlclr\> C:\Windows\Microsoft.NET\Framework\v4.0.30319\csc.exe /target:library .\BcBlobDecompression.cs
PowerShell command to compile BcBlobDecompressor class

The result of the steps is the BcBlobDecompression.dll file that will be used for our BC BLOB decompression function.

The next step is uploading the assembly to the SQL Server. Firstly, let's enable CLR on the server by following the Microsoft docs article.

EXEC sp_configure 'clr enabled', 1;  
RECONFIGURE;  
GO  
SQL script to enable CLR

The SQL Server 2019 has clr strict security enabled by default and the CREATE ASSEMBLY request stated in the guide failed for me with the error: CREATE or ALTER ASSEMBLY for assembly 'BcBlobDecompression' with the SAFE or EXTERNAL_ACCESS option failed because the 'clr strict security' option of sp_configure is set to 1. Microsoft recommends that you sign the assembly with a certificate or asymmetric key that has a corresponding login with UNSAFE ASSEMBLY permission. Alternatively, you can trust the assembly using sp_add_trusted_assembly.

If we use our decompression library on a production server, we'd better not turn off security features for the sake of it. Having looked at the best practices following this blog post, the following assembly installation script was created.

-- Source: https://pastebin.com/mwi5BidL

use [master]
-- Verify that "clr strict security" is 1 (i.e. ON), and that TRUSTWORTHY is 0 (i.e. OFF):
PRINT CHAR(13) + CHAR(10) + 'Verifying settings...';
SELECT *
FROM   sys.configurations sc
WHERE  sc.[configuration_id] = 1587 -- "clr strict security"
 
SELECT [name], [is_trustworthy_on], [collation_name]
FROM sys.databases WHERE [database_id] = DB_ID(N'YourDatabaseName');
GO

USE [YourDatabaseName]

-----------------------------------------------------------
-- Create asembly and function:

IF (OBJECT_ID(N'dbo.DecompressBcBlob') IS NULL)
BEGIN

	PRINT CHAR(13) + CHAR(10) + 'SQLCLR UDF [DecompressBcBlob] does not exist:';

	PRINT ' TEMPORARILY Altering Database to set TRUSTWORTHY=ON...';
		ALTER DATABASE [YourDatabaseName]
			SET TRUSTWORTHY ON;

	IF (ASSEMBLYPROPERTY(N'BcBlobDecompression', N'MvID') IS NULL)
	BEGIN
		PRINT ' Creating [BcBlobDecompression] Assembly...';

		CREATE ASSEMBLY BcBlobDecompression 
		AUTHORIZATION  dbo
		FROM 'C:\temp\sqlclr\BcBlobDecompression.dll' WITH PERMISSION_SET = SAFE;
	END;

	PRINT ' Altering Database to set TRUSTWORTHY back to OFF...';
	ALTER DATABASE [YourDatabaseName]
		SET TRUSTWORTHY OFF;

	PRINT N'    Creating [dbo].[DecompressBcBlob]...';
		EXEC(N'
	CREATE FUNCTION [dbo].[DecompressBcBlob]
	(
		@BlobValue VARBINARY(MAX)
	)
	RETURNS VARBINARY(MAX)
	WITH    EXECUTE AS CALLER,
			RETURNS NULL ON NULL INPUT
	AS EXTERNAL NAME [BcBlobDecompression].[BcBlobDecompressor].[Decompress];
	');

END;
GO

-----------------------------------------------------------
-- Sign assembly for the database:
 
IF (SUSER_ID(N'YourDatabaseName-BcBlobDecompression-Login') IS NULL)
BEGIN
    PRINT CHAR(13) + CHAR(10) + 'Permission Login does NOT exist:';
 
    IF (CERT_ID(N'YourDatabaseName-BcBlobDecompression-Cert') IS NULL)
    BEGIN
        PRINT ' Creating Certificate in [YourDatabaseName]...';
        CREATE CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert]
            ENCRYPTION BY PASSWORD = 'CiKqB9wv88wYCB'
            WITH SUBJECT = 'Sql Quantum Leap',
            EXPIRY_DATE = '2099-12-31';
    END;
 
    IF (NOT EXISTS(
                    SELECT *
                    FROM  sys.crypt_properties cp
                    INNER JOIN sys.assemblies sa
                            ON sa.[assembly_id] = cp.[major_id]
                    WHERE sa.[name] = N'BcBlobDecompression'
                ))
    BEGIN
        PRINT ' Signing the Assembly...';
        ADD SIGNATURE
            TO Assembly::[BcBlobDecompression]
            BY CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert]
            WITH PASSWORD = 'CiKqB9wv88wYCB';
 
    END;        
 
    IF (NOT EXISTS(
                    SELECT *
                    FROM   [master].[sys].[certificates] crt
                    WHERE  crt.[name] = N'YourDatabaseName-BcBlobDecompression-Cert'
                ))
    BEGIN
        PRINT ' Copying the Certificate to [master]...';
        DECLARE @PublicKey VARBINARY(MAX),
                @SQL NVARCHAR(MAX);
 
        SET @PublicKey = CERTENCODED(CERT_ID(N'YourDatabaseName-BcBlobDecompression-Cert'));
 
        SET @SQL = N'
CREATE CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert]
    FROM BINARY = ' + CONVERT(NVARCHAR(MAX), @PublicKey, 1) + N';';
        --SELECT @PublicKey AS [@PublicKey]; -- DEBUG
        --PRINT @SQL; -- DEBUG
 
        EXEC [master].[sys].[sp_executesql] @SQL;
    END;
 
 
    PRINT ' Creating permissions Login...';
    EXEC [master].[sys].[sp_executesql] N'
CREATE LOGIN [YourDatabaseName-BcBlobDecompression-Login]
    FROM CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert];
';
END;
GO

PRINT CHAR(13) + CHAR(10) + 'Granting UNSAFE ASSEMBLY permission...';
EXEC [master].[sys].[sp_executesql] N'
GRANT UNSAFE ASSEMBLY TO [YourDatabaseName-BcBlobDecompression-Login]; -- REQUIRED!!!!
'; 
GO

PRINT '';
SELECT ASSEMBLYPROPERTY(N'BcBlobDecompression', 'CLRName') AS [CLRName];

/*
-----------------------------------------------------------
-- Clean up:
 
PRINT CHAR(13) + CHAR(10) + 'Cleaning up objects (to be re-runnable)...';
 
USE [YourDatabaseName]
DROP FUNCTION [dbo].[DecompressBcBlob]
DROP ASSEMBLY [BcBlobDecompression];
DROP CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert];
 
USE [master];
DROP LOGIN [YourDatabaseName-BcBlobDecompression-Login];
DROP CERTIFICATE [YourDatabaseName-BcBlobDecompression-Cert];
*/
SQL script to install the library

In order to run it, replace the YourDatabaseName string with your database name and the password for assembly signing can be changed as well. The script works per database, hence the database names utilization. The clean-up section needs to be run whenever a new version of the assembly needs to be created. I had to ensure the database owner is the sa user (or the same as the owner of the master database).

If the script runs without errors, you now should be able to check if the DecompressBcBlob function works by running the following examples:

select dbo.DecompressBcBlob(null)
select dbo.DecompressBcBlob(0x02457D5B0B29CF0700)
select dbo.DecompressBcBlob(0x54776F)
Test SQL script

The results should look as follows.

dbo.DecompressBcBlob results
dbo.DecompressBcBlob results

The first select query checks if the NULL value is handled. The second one passes a compressed text example. The third result returns the same uncompressed value as if the Compressed property of the BLOB field is set to false.

The SQL query that would return text value from a Business Central BLOB field may look like as follows:

SELECT CONVERT(varchar(max), dbo.DecompressBcBlob(CONVERT(varbinary(max), [Work Description]))) as [Work Description]
FROM [CRONUS Australia Pty_ Ltd_$Sales Header] with(nolock)
WHERE [Work Description] IS NOT NULL
SQL query to convert an image column value to text with DecompressBcBlob function

It's working but slightly unreadable. How about creating another function for this? To do that, run the following SQL script.

CREATE FUNCTION dbo.ConvertBcBlobToText
(
	@i image
)
RETURNS varchar(max)
AS
BEGIN
	RETURN CONVERT(varchar(max), dbo.DecompressBcBlob(CONVERT(varbinary(max), @i)));
END
GO
SQL script to create ConvertBcBlobToText function

With that last enhancement, our query becomes as elegant as follows:

SELECT dbo.ConvertBcBlobToText([Work Description]) as [Work Description]
FROM [CRONUS Australia Pty_ Ltd_$Sales Header] with(nolock)
WHERE [Work Description] IS NOT NULL
SQL query to convert an image column value to text with ConvertBcBlobToText function

Conclusion

Using this SQL CLR solution for Business Central BLOB field value decompression can be a good fit for a system built on a SQL Server on top of the application database using Transact-SQL. The assembly adds just the necessary function that the server needs to understand a compressed value by Business Central. It would allow you to build your own logic for reading the bytes and converting them into text.