1

This one has kept me stumped for a couple of days now. It's my first dabble with CLR & UDF ...

I have created a user defined function that takes a multiline String as input, scans it and replaces a certain line in the string with an alternative if found. If it is not found, it simply appends the desired line at the end. (See code)

The problem, it seems, comes when the final String (or Stringbuilder) is converted to an SqlString or SqlChars. The converted, returned String always contains the Nul character as every second character (viewing via console output, they are displayed as spaces).

I'm probably missing something fundamental on UDF and/or CLR.

Please Help!!

Code (I leave in the commented Stringbuilder which was my initial attempt... changed to normal String in a desperate attempt to find the issue):

 [Microsoft.SqlServer.Server.SqlFunction]
[return: SqlFacet(MaxSize = -1, IsFixedLength = false)]
//public static SqlString udf_OmaChangeJob(String omaIn, SqlInt32 jobNumber) {
public static SqlChars udf_OmaChangeJob(String omaIn, SqlInt32 jobNumber) {

    if (omaIn == null || omaIn.ToString().Length <= 0) return new SqlChars("");
    String[] lines = Regex.Split(omaIn.ToString(), "\r\n");

    Regex JobTag = new Regex(@"^JOB=.+$");
    //StringBuilder buffer = new StringBuilder();
    String buffer = String.Empty;
    bool matched = false;

    foreach (var line in lines) {
        if (!JobTag.IsMatch(line))
            //buffer.AppendLine(line);
            buffer += line + "\r\n";
        else {
            //buffer.AppendLine("JOB=" + jobNumber);
            buffer += ("JOB=" + jobNumber + "\r\n");
            matched = true;
        }
    }
    if (!matched) //buffer.AppendLine("JOB=" + jobNumber);
        buffer += ("JOB=" + jobNumber) + "\r\n";

    //return new SqlString(buffer.ToString().Replace("\0",String.Empty)) + "blablabla";
   // buffer = buffer.Replace("\0", "|");
    return new SqlChars(buffer + "\r\nTheEnd");

}
Ricky
  • 15
  • 3
  • .Net strings are [encoded UTF16](http://stackoverflow.com/q/1018915/314291), viz 2 bytes per character? – StuartLC Apr 23 '14 at 10:56
  • Sounds like a potential cause. I am giving the function a varchar (so 8 bit) value... C# String wants 16 bits... so perhaps somewhere the nul's are added in during conversion to 16 bit? – Ricky Apr 23 '14 at 13:02
  • Sorry all... I think this is a total red-herring... looking for the problem in the wrong place. I'll double check that before deleting this question. – Ricky Apr 23 '14 at 16:23
  • Nope, not a red-herring... I have now circumventing the CLR... if anyone comes across this thread with an idea or two... please do share. – Ricky Apr 30 '14 at 08:45
  • You need call ".IsNull" property of variable as this link: [Handling null input in CLR][1] [1]: http://stackoverflow.com/questions/4541886/sql-server-handling-null-input-in-clr-user-defined-function-udf-with-onnull – ASama Jun 02 '15 at 12:47

1 Answers1

0

I know in my experiences, the omaIn parameter should be of type SqlString and when you go to collect its value/process it, set a local variable:

string omaString = omaIn != SqlString.Null ? omaIn.Value : string.empty;

Then when you return on any code path, to rewrap the string in C#, you'd need to set

return omaString == string.empty ? new SqlString.Null : new SqlString(omaString);

I have had some fun wrestling matches learning the intricate hand-off between local and outbound types, especially with CLR TVFs.

Hope that can help!

Nathan Teague
  • 825
  • 6
  • 11