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.
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
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:
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.
The property that is responsible for BLOB value compression is Compressed, its default value is
"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:
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:
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.
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.
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
If the script runs without errors, you now should be able to check if the
DecompressBcBlob function works by running the following examples:
The results should look as follows.
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
The SQL query that would return text value from a Business Central BLOB field may look like as follows:
It's working but slightly unreadable. How about creating another function for this? To do that, run the following SQL script.
With that last enhancement, our query becomes as elegant as follows:
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.