Skip to content

Read Content of NAV Notes/BLOBS from SQL

January 12, 2012

Today somebody asked about extracting the values of a note field in Table Record Link from SQL (http://www.mibuso.com/forum/viewtopic.php?f=32&t=51435&p=249029#p249029)

As we had that question coming up earlier this week (funny ha?) i did some testing and heres a possible solution for this:

Firstly you need a dot net assembly with a static method that uses a binary reader to read the data stored in the blob field. Here is the sample:

public class NAVNoteHandler
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static string GetNoteAsString(string NoteID)
{
try
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();
SqlCommand command = new SqlCommand(string.Format("select [Note] FROM [Record Link] WHERE [Link ID] = {0}", NoteID), connection);

SqlDataReader reader = command.ExecuteReader();

if (reader.Read())
{
SqlBytes bytes = reader.GetSqlBytes(0);
MemoryStream ms = new MemoryStream(bytes.Value);

BinaryReader BinReader = new BinaryReader(ms);
return BinReader.ReadString();
}
}
return "null";
}
catch (Exception ex)
{
return string.Empty;
}
}
}

Note: The Assembly must be compiled for the same platform as the sql server is running. So make sure you create a 32-bit assembly if you have a 32-bit sql server but a 64-bit if your sql-server is a 64-bit one.

This assembly can be now registered to sql server so you can use it like a built in function. You can read more about that here: http://www.codeproject.com/KB/database/ … n_SQL.aspx

The required sql statements to register the assembly are the following:

exec sp_configure 'clr enabled',1
reconfigure
go

CREATE ASSEMBLY NAVNoteHandler
AUTHORIZATION dbo
FROM 'C:\Assemblies\NAVNoteHandler.dll'
GO

use [Demo Database NAV (6-0)]
go
CREATE FUNCTION f_NAVNoteHandler_GetNoteAsString
(
 @id as nvarchar(200)
)
RETURNS nvarchar(200)
AS EXTERNAL NAME NAVNoteHandler.[SQLProcedures.NAVNoteHandler].GetNoteAsString
GO

You can now read the Note as a string with this call:

Select [Demo Database NAV (6-0)].[dbo].[f_NAVNoteHandler_GetNoteAsString]('1')  as [Text]

As you may notice if you tried to call the function right now, it will return you garbage, because BLOB fields in NAV are compressed by default. To retrieve a readable version of the BLOB’s content you need to set the property “compressed” of the field in NAV to false!

Advertisements

From → C# .NET, SQL

7 Comments
  1. Johan van den Brink permalink

    How do I call this code from another C# function in order to put the results into a C# string in de main program?
    I would like to use it in a SSIS script task and put the result of the above function into a string variable and then read that from SSIS.

    • I’am not sure if I understand you corectly, but you can simply call the GetNoteAsString() Method and receive the text. All you need to do is handle the connection, i don’t know if you re-use the connection of your SSIS script session, otherwise open a new connection and pass the parameters needed to the GetNoteAsString() method.

      • Johan van den Brink permalink

        Thanks for your response. I need to write it to an SSIS string variable. So I used Dts.Variables[“variableName”].Value = GetNoteAsString(); This gave me no errors but an empty string got returned.

        Then I tried your standard method of registering an assembly and creating a function. This all worked too, but when I call the function on my table it always returns 1 character: ‘D’. Do you have any idea why this happens? As the content of the Note field clearly differs.

  2. Johan van den Brink permalink

    Thanks for your response. I need to write it to an SSIS string variable. So I used Dts.Variables[“variableName”].Value = GetNoteAsString(); This gave me no errors but an empty string got returned.

    Then I tried your standard method of registering an assembly and creating a function. This all worked too, but when I call the function on my table it always returns 1 character: ‘D’. Do you have any idea why this happens? As the content of the Note field clearly differs.

    • Which NAV Version? Are you sure your blob field is set to compressed = false?
      Does the content of the field start with a D? is the content a string?

  3. Johan van den Brink permalink

    I am checking with my client to make sure the compressed property is set to false.
    The reader.Read gets this from database: 0x01447C5A0020434F4A05C18E86B596804B4C8774A042B4DCED02D1175D9980CA16046E6461E78E7D1AA1B65B00
    A bitconverter.ToString shows this: 01-44-7C-5A-00-20-43-4F-4A-05-C1-8E-86-B5-96-80-4B-4C-87-74-A0-42-B4-DC-ED-02-D1-17-5D-99-80-CA-16-04-6E-64-61-E7-8E-7D-1A-A1-B6-5B-00
    The memoryreader reads this:
    D|Z COJ������KL�t�B����]���nda�}��[
    So very probably it is still compressed!
    The memoryreader also gives an “unable to read beyond the end of the stream” error.
    Maybe that is the cause that the BinaryReader doesn’t get further than “D”.

    NAV-version = 2009

Trackbacks & Pingbacks

  1. Accessing Compressed Blobs from outside NAV (NAV2013) « deV.ch – man vs. code

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