November 25, 2013 at 1:37 pm
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?
November 26, 2013 at 9:28 am
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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply