Skip to content

Accessing Compressed Blobs from outside NAV (NAV2013) (Revisited)

January 21, 2014

I already bloged about this some time ago: https://devch.wordpress.com/2013/01/17/accessing-compressed-blobs-from-outside-nav-nav2013/
But this was in the beta phase of NAV2013, and since then i did not use the code for any real project. Since there were a few comments about it not realy working i revisited the solution and encountered that it infact was not a completly working solution.

So here is the working solution for this, it enables you to read compressed & non-compressed BLOBs. I tested it with the Object & the Object Metadata Table.

class NAV2013BLOBReader
    {
        public static int GetDecompressedStream2013(SqlDataReader reader, bool decompress, ref MemoryStream ResultStream)
        {
            int fieldIndex = 0;
            int num2 = reader.IsDBNull(fieldIndex) ? 0 : ((int)reader.GetBytes(fieldIndex, 0L, null, 0, 0));
            if (num2 > 0)
            {
                using (Stream stream = new BlobReaderStream(reader, fieldIndex, num2))
                {
                    if (decompress)
                    {
                        byte[] array = new byte[4];
                        long num3 = (long)stream.Read(array, 0, 4);
                        if (num3 < 4L || !BlobMagicOk(array))
                        {
                            int result = 22926086;
                            return result;
                        }

                        using (DeflateStream deflateStream = new DeflateStream(stream, CompressionMode.Decompress))
                        {
                            deflateStream.CopyTo(ResultStream);
                        }
                    }
                    else
                        stream.CopyTo(ResultStream);
                    return 1;
                }
            }

            return 0;
        }

        private static bool BlobMagicOk(byte[] checkMagic)
        {
            for (int i = 0; i < 4; i++)
            {
                if (checkMagic[i] != BlobMagic[i])
                {
                    return false;
                }
            }
            return true;
        }

        private static byte[] BlobMagic = new byte[]
        {
	        2,
	        69,
	        125,
	        91
        };
    }
    public class BlobReaderStream : System.IO.Stream
    {
        private System.Data.SqlClient.SqlDataReader reader;
        private int fieldIndex;
        private int length;
        private int position;
        public override bool CanRead
        {
            get
            {
                return true;
            }
        }
        public override bool CanSeek
        {
            get
            {
                return false;
            }
        }
        public override bool CanWrite
        {
            get
            {
                return false;
            }
        }
        public override long Length
        {
            get
            {
                return (long)this.length;
            }
        }
        public override long Position
        {
            get
            {
                return (long)this.position;
            }
            set
            {
                this.position = (int)value;
            }
        }
        public BlobReaderStream(System.Data.SqlClient.SqlDataReader reader, int fieldIndex, int length)
        {
            this.reader = reader;
            this.fieldIndex = fieldIndex;
            this.length = length;
        }
        public override void Flush()
        {
        }
        public override long Seek(long offset, System.IO.SeekOrigin origin)
        {
            throw new System.NotSupportedException();
        }
        public override void SetLength(long value)
        {
            throw new System.NotSupportedException();
        }
        public override int Read(byte[] buffer, int offset, int count)
        {
            if (this.position < this.length)
            {
                int num = (int)this.reader.GetBytes(this.fieldIndex, (long)this.position, buffer, offset, count);
                this.position += num;
                return num;
            }
            return 0;
        }
        public override void Write(byte[] buffer, int offset, int count)
        {
            throw new System.NotSupportedException();
        }
    }

To call the method simply use something like this:

            string SQLServer = "<Your Instance>";
            string Database = "<Your Database>";
            using (SqlConnection connection = new SqlConnection(string.Format("server={0};Trusted_Connection=yes;database={1};connection timeout=5", SQLServer, Database)))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(string.Format("select [Metadata] from [Object Metadata] where [Object Type] = 1 AND [Object ID] = {0}", ID), connection);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.Read())
                {

                    var ms = new MemoryStream();
                    if (NAV2013_Compressed_Stream.GetDecompressedStream2013(reader, true, ref ms) == 1)
                    {
                        ms.Position = 0;
                        StreamReader Sreader = new StreamReader(ms);
                        string text = Sreader.ReadToEnd();
                        return text;
                    }
                }
            }
Advertisements

From → C# .NET, Dynamics NAV, SQL

45 Comments
  1. Wonderful, I found that if I remove the first four bytes from the Object Metadata blob field, the standard .Net DeflateStream Decompress works properly. It looks like NAV is using the first four bytes to store a “magic number” that determines the custom NAV blob type. In the case of the compressed Object Metadata, this magic number is 0x02457d5b (hex).

    In my case, since I know these metadata fields are in this format I can just throw away the first four “magic number” bytes. Or alternately I can check that the first four bytes match 0x02457d5b, throw them away, and then use the standard .Net DeflateStream.

    Many thanks for your help, you have some incredible reverse-engineering skills! Much appreciated :-).

    • Thanks, good to hear it’s working for you! Yes indeed, you can “throw away” the first 4 bytes and it will work anyway. The code i posted does some more checking, like it’s done by the nav server but since you know wat you want i guess it’s ok to just skip them.

      About the reverse-engeneering skills, thanks but the honor goes too the creator of tools like ILSpy which are so easy to use and are for free! 🙂

    • Hello NotesbyTom
      Could you please show me your code for achieving the display of images from the deflatestream?
      It’s so frustrating not knowing like you guys do

  2. Teldin permalink

    Hi, this is great. Can you tell me how can I do this with NAV 2009?

    • Thank you! From what i know, NAV2009 uses the “classic” compression which is a proprietary one that we don’t know exactly how it works. But if you can modify the table that contains the blob, you can disable the compression and read the content without decompression logic.

      • Teldin permalink

        Thank you for your reply. Yes I have the ability to modify the table but I’m not sure how. In SSMS 2008 R2, I right click the [Record Link] table, click “Manage Compression” but the “compression type” is currently set to NONE.

        • You need to set the compression inside NAV. there is a property on BLOB fields called “Compressed”, set this one to No and you should be able to read the content from external sources.

          • Teldin permalink

            Ok I see how to do that now. Can I make the change to a production server without any consequences? Will it immediately decompress all of the blob values currently in the table? Thanks for all the help!

            • From my experiences you can apply that to production with no issues. but as always first test it on the test environment. And yes it should decompress the existing contents.

  3. Rob_K permalink

    Can someone please tell me where to get the BlobReaderStream class?
    As VS2012 doesn’t seem to know anything about this class, as does google.

    • Yes Rob_K your right, i’am ver sorry but i missed to add the code for the BlobReaderStream class.
      Basicly its just a wrapper around the Sql Data Reader object, to use it like a stream.
      I know added the code to the original post. I hope it’s ok now.

      Let me know if it works for you!

  4. Rob_K permalink

    Great! Thank you very much for your prompt reply; this did solve my problem 🙂

  5. c4r10k4 permalink

    Hi!

    I can descompress the text of Object Metadata (field Metadata). But, can i modify this text and import my modified text on field Metadata? How?

    Regards.

    • I haven’t done that yet, but i guess you can do it by simply reversing the steps discribed here. But i would not recomend it, because your object would be inconsistend. The state of the metadata and the blob in the Object table would be different.
      Why do you want to import to the object metadata table directly?
      Regards Marco

      • c4r10k4 permalink

        My intention is to renumber a field to a numbering off my license. With objects works if you change the object table, but for the fields must be done in the metadata. I think if you change a field in the 50000 metadata to, say, 501 will work properly. do not?

        • Well it could work, but i from my understanding, nav does not directly write object metadat, but first import the table structure somewhere else. The object definition is written in the “BLOB Reference” for example in the Table “Object”. So i guess when you import to the metadata you skip some parts of object loading structure. I would definitly not recomend doing it!
          But you can test it and see if it works.
          If it does, let me know 🙂

          • c4r10k4 permalink

            Right. My idea is to test. But further testing I need to know how I can do the reverse of the code. Ie re-compress the text to put it in the metadata field. Is it possible that you make the code in VisualStudio backwards?

            In NAV2009 i access to field with this code:
            Stream data = reader.GetStream(0);
            StreamReader Sreader = new StreamReader(data);
            string text = Sreader.ReadToEnd();

            It would be interesting to have this functionality in reverse. It is possible?

            In NAV2013 its very dificult for me……I think with this we can do to create any field in any table.

  6. mina permalink

    Hi, i need to read an image from Company info table, i tried the above code but it doesn’t work, i’m new to integration with nav so i’m a little confused here.

    • Well i need more info about your situation, what exactly does not work? Do you have an error? Which Version of NAV?

  7. mina permalink

    It’s Nav 5.0
    i’m trying to read the picture field of the company information table, the column type is “image”
    when trying to parse the response into byte[] then converting this byte[] to a memorystream then converting this stream into an image i get the below error
    “System.ArgumentException: Parameter is not valid.
    at System.Drawing.Image.FromStream(Stream stream, Boolean useEmbeddedColorManagement, Boolean validateImageData)
    at System.Drawing.Image.FromStream(Stream stream)”

    when trying to read the response using SqlDataReader then writing the response to a file, it succeeds but on opening the image file it displays a message “this is not a valid bitmap file”

    the image first few bytes are “0x01447C5A1C108751801943B499E30406814500C002B000026C60810C0000000140841033”

    any guidance would be very appreciated.

  8. mina permalink

    Don’t know how to thank you 🙂 really thanks alot for you support, it worked.

  9. kayess permalink

    devch. great job but i cant get it working with object table. It works fine for object metadata table. Any idea?

    • Hedi permalink

      Same issue here, but I think that on the object table it is a compiled version of the .net metafield, did u have any luck with the object metafield ?

  10. Hedi permalink

    Great job, Honestly this is one of the best insider’s article I have ever red
    I still get few question
    -Your method can read/decrypt blob metadata that are strings :
    >>on the table “Object Metadata” we can read the infos of the fields {“Metadata”,”User Code”,”User AL Code”}
    But on the table “Object” the field “BLOB Reference” witch is also a blob (compressed) but don’t seem to be a string (I may be wrong”) ; when using your method we get an unreadable string

    I suspect the this field is a compiled version of the “User Code” on the “Object Metadata” table witch is a “C# code” since Nav is basicly .net code (all the C/AL code is converted to C#)

    First can you provide a function that can actualy write (compress/encrypt) a string to a ‘Nav blob’ I know that a Powershell version exist : http://mibuso.com/blogs/kine/2014/12/09/readingwriting-nav-blob-data-from-powershell/
    Second I suspect that even if we are able to edit the the fields values on the “Object Metadata” table, we still need to find how it they are converted/compiled to the “BLOB Reference” on the “Object” table.
    What are your thoughts about the second point?
    Thank you again

    • Thank you, I realy appreciate that you like this article.
      From my own observations, i would say that the BLOB Reference Field on the Object table, holds the compile “FOB-Style” source code.
      In the past i observed this field and its content and i noticed that the content was part of what you get when you export a FOB.
      I guess its the “payload” of the FOB and when you would add like the id, name, date, time and modified, that you would get the content of the FOB file.
      So in theory it could be possible to make some sort of a fob import/export with this information. But doing this is beyond my limits in both time & knowledge ;-).

      On your other question, about writing to a compressed blob field, with the information given here and in the link you posted, it should be easy do write code to do so.
      I’am currently involved heavily in implementing Git with TFS for all our NAV solutions (I wrote my own connector for NAV for tracking object changes and import/export sync-job).
      This is time consuming and the reason i can’t blog a lot.

  11. My first four bytes are 1, 68, 124 and 90 – yours are 2, 69, 125 and 91. If I remove the BlobMagic-check the DeflateStream returns some nice bytes to me – but not those I expected. Any idea if the first four bytes means a different version of Navision (I don’t know which version of Navision the content is from, I just got the exported database)?

    • Interesting that your bytes are simply always one bit higher then the one i posted.
      All i can say is that that blob magic came from the nav client source code.
      Is it a 2013 or 2015 database (platform version)? I had a look at the 2015 binaries and the blobmagic is still the same.
      There must be something else…

      • Yes, it’s a bit strange. I don’t know which version of the database the data come from. I just got a export of some of the tables from the database.

        Well, I can’t figure out how to interpret the data and I think I’ll leave it here – not that important.

        But thanks for a great blog post! 🙂

      • MannyC permalink

        Hello. I am not seeing my first post so I’m sorry if this doubles up. working with serenic NAV 2013 and trying to display “image” from table. I see the 0x02457d5b (hex) at start of each image and have attempted to position the memory stream at 5th byte, deflatestream and using the rest as an image for a picturebox. It errors out. Is a character conversion required?
        I appreciate any help you can provide
        thx

      • MannyC permalink

        Hello
        I am not seeing my previews posts even after registering on the site. I am trying again.
        working with NAV2013 (Serenic). I am able to see the code 0x02457d5b (hex) on each “image” field. I am just trying to use vb.net (newbie) to display that binary data in a picturebox. I tried to reposition memory stream to 5th byte and display the remaining after a deflatestream. I error out. Is there some sort of character conversion required?
        can someone help ?
        thx

        • I think you need to use a binary reader to read the deflated stream. After that you should be able to use the result with your picturebox.
          What is the error you get?

          • Thank you for replying and I am sorry I duplicated so many times – I was getting an error posting.
            I get An unhandled exception of type ‘System.ArgumentException’ occurred in System.Drawing.dll
            Additional information: Parameter is not valid.

            • Have you created an image object from the stream which you then assigned to the picture box? I’am a c# guy but if you could provide a code snippet i could possibly help you.
              In the meantime, you could have a look at this example for proper code to show a image (stream) in a picture box:
              http://stackoverflow.com/questions/2540750/load-picturebox-image-from-memory

              • Hello
                Been busy and I just saw replies this morning. Thank you for that. Please remember that I am not a VB programmer-just an admin trying to simplify user’s needs. Here is my code – it’s messy and probably not efficient but-I’m trying. What I am attempting to do (as a test before I try to move to the next step if my goal) is to grab an ‘image’ field content (just one) from the table, “skip” the first 4 bytes-since they represent that 0x0…code and then deflate and display the remainder as an image in a picturebox. I have some tests along the way which I tried as I read up on how to do this. If they confuse matters – my apologies.

                Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
                Dim strCn As String = “Data Source = NAVSERV08\NAVSQLSERVER08;Initial Catalog=Clark_Test_07012015_NAV_71_38053;Integrated Security=True”
                Dim cn As New SqlConnection(strCn)
                Dim strcnstatus As String
                cn.Open()

                ‘Check connection (2 different ways)

                ‘1)
                strcnstatus = “is ” & cn.State
                MessageBox.Show(“Cn.state.. “, strcnstatus)

                ‘2)
                If cn.State = ConnectionState.Open Then MessageBox.Show(“open!”, “Connection is..”)

                ‘enclosing in [] to handle space in table name

                Dim cmd As New SqlCommand(“Select Attachment FROM [TCEstates$File Attachment] where No_ = ‘FATTCH000010′”, cn)

                Dim da As New SqlDataAdapter(cmd)
                Dim ds As New DataSet()

                ‘”NAVBlobTest” is just an indentifier for the data set
                da.Fill(ds, “NAVBlobTest”)

                If ds.Tables(“NAVBlobTest”).Rows.Count > 0 Then MessageBox.Show(“A Table In the Data Set!”, “I see…”)

                Dim c As Integer = ds.Tables(0).Rows.Count

                Dim strvalct As String
                strvalct = “The value Of c Is ” & c
                MessageBox.Show(“ds.Tables(0).Rows.Count Is…”, strvalct)

                If c > 0 Then

                Dim bytBLOBData() As Byte = ds.Tables(0).Rows(c – 1).Item(0)

                ‘Get size of memory stream in bytes
                Dim stmBLOBData As New MemoryStream(bytBLOBData)
                Dim Strmemstrm As String
                Dim Intmemstrm As Integer
                Intmemstrm = stmBLOBData.Length

                ‘write memory stream to file
                Dim stmfile As New FileStream(“c:\temp\stmblobdata.txt”, FileMode.Create, FileAccess.Write)
                stmBLOBData.WriteTo(stmfile)
                stmfile.Close()

                Strmemstrm = stmBLOBData.Length.ToString()
                MessageBox.Show(“Size of the memory stream is “, Strmemstrm)

                ‘Test memory stream for support of ‘seeking’ (.position property)
                Dim SeekVal As Boolean
                Dim strseekval As String

                SeekVal = stmBLOBData.CanSeek
                strseekval = “value is ” & SeekVal

                MessageBox.Show(“canseek “, strseekval)

                ‘Check memory stream position
                Dim strmemstreampos As String
                strmemstreampos = stmBLOBData.Position.ToString()
                MessageBox.Show(“position of memory stream”, strmemstreampos)

                ‘Set new position within stmBLOBData and read remaining data into a new memory stream. We want to skip First 4 bytes

                stmBLOBData.Position = 4

                ‘check new position
                strmemstreampos = stmBLOBData.Position.ToString()
                MessageBox.Show(“new position of memory stream”, strmemstreampos)

                ‘99631 is the size given for that image in the table
                Dim ms As New MemoryStream(99631)

                For i As Integer = 0 To 99626
                ms.WriteByte(stmBLOBData.ReadByte())
                Next i
                ‘stmBLOBData.Close()

                ‘Check size of shortened memory stream
                Dim Strms As String
                Strms = ms.Length.ToString()
                MessageBox.Show(“Size of shortened memory stream is “, Strms)

                ‘********Some decompression is required

                Dim objinflate As New DeflateStream(ms, CompressionMode.Decompress)

                Dim Buffer(99631) As Byte
                Dim offset As Integer = 0
                Dim totalCount As Integer = 0
                While True
                Dim bytesRead As Integer = objinflate.Read(Buffer, offset, 0)

                If bytesRead = 0 Then
                Exit While
                End If
                offset += bytesRead
                totalCount += bytesRead
                End While

                MessageBox.Show(“total count”, totalCount.ToString)

                Dim myImage = Image.FromStream(objinflate)
                PicBLOB.Image = myImage

                End If
                cn.Close()

                End Sub

  12. MannyC permalink

    hello
    I am kinda new to vb but I can figure it out with a little effort. That being said. we have 2013 nav (serenic). I am also trying to display the “image” field and I do see the 0x02457d5b (hex) code at beginning of each one of the stored images. However, I do not understand the deflatestream part and whether some sort of character set conversion is needed. I have Dim objinflate As New DeflateStream(ms, CompressionMode.Decompress) I then try PicBLOB.BackgroundImage = Image.FromStream(objinflate, True)
    and it yields an error. Can someone help ?

  13. Hello
    can someone shed light for me? I have this line of code:
    Dim objinflate As New DeflateStream(ms, CompressionMode.Decompress)

    This does not actually populate objinflate with the data in ms (a memory stream with verified content)
    so what does it actually do since the ms is a required parameter?
    thx

  14. I forgot to mention that in a hex editor, my deflated stream is just showing zeros???

  15. hello
    can someone explain the line

    SqlCommand command = new SqlCommand(string.Format(“select [Metadata] from [Object Metadata] where [Object Type] = 1 AND [Object ID] = {0}”, ID), connection);

    I don’t see how this extracts any “image” data. What is all that “metadata” stuff?

  16. There is no image extracted. Here we query the object metadata tabke for the “metadata” field which is a blob field. It contains binary data (xml content).

Trackbacks & Pingbacks

  1. Accessing Compressed Blobs from outside NAV (NAV2013) | deV.ch - man vs. code
  2. andreas04: close to attraction
  3. Reading/Writing NAV Blob data from PowerShell - Kine's Info

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s