Public static SqlBytes Decompress(SqlBytes inBytes) Private static readonly byte BlobMagic = new byte
Blob field with compression data is not valid code#
Here is the code of the class to compile: using System.Linq 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). The library can be uploaded to the server as an assembly, which can serve as a foundation for our custom T-SQL function.įollowing the Getting Started with CLR Integration article, I needed a. 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. Once these two problems are addressed in SQL Server, we should be able to query the database directly. 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. For example, Accessing Compressed Blobs from outside NAV (NAV2013) (Revisited) | deV.ch - man vs.
There are multiple articles showing successful implementation of reading text from image fields. The built-in DECOMPRESS T-SQL function can look to be useful, but it's using the GZIP algorithm and not working for us. We need to decompress the byte data before converting it to text. 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. 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. Or maybe it's a third-party product that we have no control over. Maybe the reporting team is not responsible for the code and it's a dependency on other teams. 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. "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. The property that is responsible for BLOB value compression is Compressed, its default value is true. }[ Ï/ÊVHI-N.Ê,(ÉÌÏ, which we might doubt is useful, obviously.
The saved Work description text value into the field would be returned by the SQL query as. The bytes are compressed by Business Central before they are sent to the SQL server. SQL query to convert an image column value to text The SQL query would be as follows: SELECT CONVERT(varchar(max), CONVERT(varbinary(max), )) as įROM with(nolock) It wouldn't be a big problem to convert the saved value from bytes to text with Transact-SQL. As a result, it's stored as raw bytes in the SQL server database. The text value is saved by creating a stream for the BLOB field and writing the text into it. "Work Description".CreateOutStream(OutStream, TEXTENCODING::UTF8) Procedure SetWorkDescription(NewWorkDescription: Text) Message(ReadingDataSkippedMsg, FieldCaption("Work Description")) If not TypeHelper.TryReadAsTextWithSeparator(InStream, TypeHelper.LFSeparator(), WorkDescription) then "Work Description".CreateInStream(InStream, TEXTENCODING::UTF8) 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 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. It can hold text values far longer than a table field of type Text. The ProblemĪ BLOB field in Business Central corresponds to a database column of type image.
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. One interesting issue I've come across recently is reading a text value from a BLOB field. 8 min read Photo by Campaign Creators / Unsplashīuilding 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.
0 kommentar(er)
