Working with LOB parameters in CLR

  • dale.burnett

    Valued Member

    Points: 70

    I'm trying to make a hashbytes function in CLR for a nvarchar(max) parameter value that is > 8000 characters and I'm having trouble getting it to work.

    Here is the CLR snippet -

    public partial class UserDefinedFunctions

    {

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlBinary BigHash(SqlString hashtype, [SqlFacet(MaxSize = -1)] SqlChars data)

    {

    System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();

    var hash = HashAlgorithm.Create(hashtype.Value);

    var bytes = Encoding.UTF8.GetBytes(data.Value);

    return new SqlBinary(hash.ComputeHash(bytes));

    }

    };

    This is the function definition:

    CREATE FUNCTION dbo.BigHash(@hash nvarchar(30),@data nvarchar(MAX))

    RETURNS varbinary(1000)

    EXTERNAL NAME [BigHash].[UserDefinedFunctions].BigHash

    GO

    And this is the test code:

    DECLARE @val nvarchar(MAX)

    SET @val = REPLICATE('A',8001)

    SELECT dbo.BigHash('md5',@val)

    Any number of characters 8000 bytes and greater will return the same hash.

    I change the c# code to this so I could see how long the the paramater is after it gets passed in.

    public partial class UserDefinedFunctions

    {

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlInt32 BigHash(SqlString hashtype, [SqlFacet(MaxSize = -1)] SqlChars data)

    {

    System.Text.UTF8Encoding encoding = new System.Text.UTF8Encoding();

    var hash = HashAlgorithm.Create(hashtype.Value);

    var bytes = Encoding.UTF8.GetBytes(data.Value);

    return (SqlInt32)data.Length;

    }

    };

    Anything greater then 8000 characters long is getting truncated to 8000

    So, @data is being truncated going into the function. I read in the MS guide to working with Large objects that I should use SqlChars and I saw several forum posts saying that [SqlFacet(MaxSize = -1)] can be used to solve this problem. Nothing is working so far.

    Any hints, solutions or otherwise useful suggestions?

  • dale.burnett

    Valued Member

    Points: 70

    The problem was with REPLICATE truncating the data before it ever got to CLR.

    Per the documentation:

    If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

    DECLARE @val nvarchar(MAX)

    DECLARE @val2 nvarchar(MAX)

    SET @val = N'A'

    SET @val2 = REPLICATE(@val,8002)

    SELECT dbo.BigHash('md5',@val2)

    while

    DECLARE @val2 nvarchar(MAX)

    SET @val2 = REPLICATE(N'A',8002)

    SELECT dbo.BigHash('md5',@val2)

    does not.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply