SQL Function, get value to send to 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 retur

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

• Remove the path parameter from the SQLCLR code (not shown in the question)

• Remove the @path parameter from the T-SQL wrapper code (shown in the question)

-
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();
    }

  }
}

-
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:

-
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
}

-
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 y

StackExchange Database Administrators Q#224997, answer score: 4