Sql-server – SQL Function, get value to send to CLR

functionssql serversql-clr

I have a CLR to use the powershell to have access to the RichTextBox Object from .net.

On the SQL Function I have created like this:

CREATE FUNCTION [dbo].[FicheiroChines](@rtf [nvarchar](max), @path [nvarchar](500))
RETURNS 
   nvarchar(max) 
WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [abcSQLHelper].[abcSQLHelper.Chinese].TextFromRTF

Now need some help is to take out the @path as argument on the SQL Function (But still need it on the CLR) and inside the function to select a value from the table and send it to the CLR.

How can I achieve this? Can't find any info about this.

Edit:
To try to help understanding what is going on here, I have a CLR in C# so I can be able to transform a RTF to string (example of the RTF below):

{\rtf1\ansi\ansicpg1252\deff0\deflang2070{\fonttbl{\f0\fmodern\fprq6\fcharset134 SimSun;}{\f1\fnil\fcharset0 MS Sans Serif;}} \viewkind4\uc1\pard\lang2052\f0\fs17\'b7\'f2\'c8\'cb\'b7\'eb\'b7\'f2\'c8\'cb6346\'b8\'f6\'ba\'ec\'b5\'c4\'b9\'f0\'bb\'a8\lang2070\f1 \par }

To be able to achieve this, since it has been asked to be done inside a SQL Query, I did have to put this in CLR in C# because of the chinese characters.

This is what is being send on the @rtf.

Now the @path, is needed to be send also to the CLR that is done in C#, so he knows where to write the temp txt file (again need to write the output of the script on powershell to use the RichTextBox of .Net to transform the RTF).

Now the function is called in a query by doing this as ex: select dbo.RTF2Text(RTFText, (select filePath from tablexpto)) as teste from RTFTest but has been asked to be only has select dbo.RTF2Text(RTFText) as teste from RTFTest

So in on first code, I need to select from there the path from the table, and need to send the argument inside the CLR

Hope that helped to clear what I need to be done, if not, I will try to improve it more

Edit 2:

I needed this because it was asking for me to do a function on SQL that could receive a RTF and return a string, the problem is the Chinese character, did try several things that I could find online and such but without success.

So I told to my self if I could have access to the RichTextBox from .Net I could do this more easy, so I do use the powershell (since I can't add WinForms Assembly to do this … well I could but required alot more stuff) to access the Class RichTextBox of .Net and insert there the RTF and ask for the Text and put it into a file and read it from there (must be a file again because of the chinese caracters, if I just read the output comes as ?????)

This is the CLR that I have, sorry for not providing it at first.

public class Chinese
{
    /// <summary>
    /// It will receive the source of an RTF and transform into readable text
    /// </summary>
    /// <param name="rtf">Source of the RTF</param>
    /// <param name="path">Directory where the file must be created</param>
    /// <returns>Readable text from RTF</returns>
    [Microsoft.SqlServer.Server.SqlFunction]
    public static string TextFromRTF(string rtf, string path)
    {
        var output = String.Empty;
        try
        {
            var proc = new Process();
            proc.StartInfo.FileName = "cmd.exe";
            proc.StartInfo.Arguments = "/C powershell.exe -ExecutionPolicy ByPass -Command Add-Type \"-AssemblyName System.Windows.Forms; $Rtb = New-Object -TypeName System.Windows.Forms.RichTextBox; $stringRTF = \\\"" + rtf + "\\\"; $Rtb.Rtf = $stringRTF; $Retorno = $Rtb.Text; Write-Output $Retorno > " + path + "\\chines.txt;\"";
            proc.StartInfo.UseShellExecute = true;
            proc.StartInfo.CreateNoWindow = true;
            proc.Start();
            proc.WaitForExit();
            output = File.ReadAllText(path + "chines.txt");
        }
        catch (Exception ex)
        {
            return ex.Message;
        }
        return output;
    }
}

So if I provide the RTF that i mention, it will return to me

夫人冯夫人6346个红的桂花

Edit 3:

After implementing the solution given by the write answer, I get this "Error"

Blockquote

Data access is not allowed in this context. Either the context is a
function or method not marked with DataAccessKind.Read or
SystemDataAccessKind.Read, is a callback to obtain data from FillRow
method of a Table Valued Function, or is a UDT validation method.

Did add this to the CLR:

using (var con = new SqlConnection("Context Connection = true;"))
{
    using (var cmd = con.CreateCommand())
    {
        cmd.CommandText = "select @FilePath = filePath from dbo.tablexpto;";
        var sqlParam = new SqlParameter("@FilePath", SqlDbType.NVarChar, 500)
        {
            Direction = ParameterDirection.Output
        };
        con.Open();
        cmd.ExecuteNonQuery();
        if (sqlParam.Value != DBNull.Value)
        {
            path = sqlParam.Value.ToString();
        }
    }
}

Best Answer

@ScottHodgin, in a comment on the question, is correct that you cannot simply hide an input parameter. You need to:

  1. Remove the path parameter from the SQLCLR code (not shown in the question)
  2. Remove the @path parameter from the T-SQL wrapper code (shown in the question)

  3. get the value from a query in the .NET method. Something like (the following code is approximate; I have not tested it but it is very close and should, at most, have only minor syntax issues):

    string _FilePath = string.Empty;
using (SqlConnection _Connection = new SqlConnection("Context Connection = true;"))
{
  using (SqlCommand _Command = _Connection.CreateCommand())
  {
    _Command.CommandText = "SELECT @FilePath = filePath FROM dbo.tablexpto;";

    SqlParameter _ParamFilePath =
           new SqlParameter("@FilePath", SqlDbType.NVarChar, 500);
    // adjust the 500 to the actual size of the filepath column
    _ParamFilePath.Direction = ParameterDirection.Output;

    _Connection.Open();

    _Command.ExecuteNonQuery();
    if (_ParamFilePath.Value != DBNull.Value)
    {
      _FilePath = _ParamFilePath.Value.ToString();
    }

  }
}

  4. Update the method decorator as follows:

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]

HOWEVER, it is not clear that you even need this .NET code in the first place. What is the output supposed to look like? You only provided the input. You might be able to get away with using pure T-SQL, or if not, then most likely some simple .NET code that does not require an external library or writing to the file system.

Other notes:

  1. Use SqlString instead of string for input params / return types. Get the .NET string from it using the Value property (e.g.

    method(SqlString path)
{
   path.Value // to get the string
   path.IsNull // returns bool to test if a NULL was passed in
}

  2. You shouldn't be doing this by calling an external process. The command-line has a maximum size of only a few thousand characters, so any RTF document over that size will cause an error as it will be an incomplete command line being submitted.

    This should be easy enough in standard .NET methods to parse the simple RTF format. The RTF specification is available on Microsoft.com, and you just need to filter out the meta-data and return the rest. You might be able to get away with using pure T-SQL even.

    Look at \fonttbl{\f0. This is the Font Table, Font # 0, which is used right before the text you want to convert — \lang2052\f0\fs17\ with "lang2052" meaning "Chinese", "f0" referring back to the font defined as f0 in the \fonttbl group, and "fs17" is the font size, so it can be ignored. In this font ("f0") definition, it specifies \fcharset134 which is GB2312, which means "ANSI/OEM Simplified Chinese (PRC, Singapore); Chinese Simplified (GB2312)" and corresponds to Code Page 936 (see here – search for "gb2312"). Using the following query:

    SELECT *, COLLATIONPROPERTY([name], 'Version')
FROM   sys.fn_helpcollations()
WHERE  COLLATIONPROPERTY([name], 'CodePage') = 936;

    You can see that there are several options for Chinese_PRC_* and Chinese_Simplified_*. The Chinese_Simplified_* Collations are newer, so pick something like Chinese_Simplified_Pinyin_100_BIN2. We can then convert the

    \'b7\'f2\'c8\'cb\'b7\'eb\'b7\'f2\'c8\'cb6346\'b8\'f6\'ba\'ec\'b5\'c4\'b9\'f0\'bb\'a8

    by using:

    DECLARE @RTF VARCHAR(MAX) = 
'\''b7\''f2\''c8\''cb\''b7\''eb\''b7\''f2\''c8\''cb6346\''b8\''f6\''ba\''ec\''b5\''c4\''b9\''f0\''bb\''a8'; -- ' (CSS bug work-around)

SELECT CONVERT(VARBINARY(MAX), REPLACE(@RTF, '\''', ''), 2); -- ' (CSS bug work-around)

DECLARE @ConvertRTF TABLE
(
   [ToChinese] VARCHAR(MAX) COLLATE Chinese_Simplified_Pinyin_100_BIN2
);
INSERT INTO @ConvertRTF ([ToChinese])
VALUES (CONVERT(VARBINARY(MAX), REPLACE(@RTF, '\''', ''), 2));

SELECT * FROM @ConvertRTF;

    which returns:

    夫人冯夫人cF个红的桂花

    Close, but not perfect. The non-escaped sequences get converted as well. Working on that part still...

    UPDATE
    Looking at this in more detail, it would take quite a bit of work to get this fully working using T-SQL. It is possible, but probably not worth the time. Still, I don't think you need to call PowerShell using a command shell (which requires UNSAFE). I would highly recommend writing a simple parser in C#. There is a code example (for C++, but close) at the end of the RTF specification document. That should provide enough of the logic to find the encoding, translate the escape sequences, and even get Unicode escape sequences and predefined labels such as \ldblquote and \rdblquote.

    As far as I can tell, this is the most recent RTF specification document (takes you to a Microsoft download page): Word 2007: Rich Text Format (RTF) Specification, version 1.9.1 (published 2008-03-20). Look at "Appendix A" (starts on Page 208) which is the sample RTF reader code. OR, just search for some open source RTF reader code and integrate that into your assembly.

    UPDATE 2
    O.P. did originally try to find an open source parser, and found one, but it did not work with Double-Byte Characters Sets. I just created a Pull Request (Work with DBCS encodings #2) to fix the issue. The fix, running locally on my laptop, produces the desired output. End result: no need for UNSAFE code that spawns a command shell to execute PowerShell (yikes ?), no writing to / reading from the file system, and this can be done in a SAFE Assembly.

  3. For more info on working with SQLCLR in general, please visit SQLCLR Info
Related Question