Working with LOB parameters in CLR

• dale.burnett

  SSC Enthusiast

  Points: 100

  More actions

  November 25, 2013 at 1:37 pm

  #150524

  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

  SSC Enthusiast

  Points: 100

  More actions

  November 26, 2013 at 9:28 am

  #1669198

  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.