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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy