6 Replies - 538 Views - Last Post: 13 December 2018 - 11:14 AM Rate Topic: -----

#1 thamathar   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 05-December 18

RTF Control with chinese characters?

Posted 05 December 2018 - 09:51 AM

Hello every one.

Need some advice here, for example, using the Control RTF to save the chinese characters into the SQL DB it will save them like this.

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


So I can the RTF Control and past that easy and it will show the characters as it should, but now here is when I need some help, how could I get them directly from the SQL query (a SQL function or something like that) so they would be for example

四阿婆天然色的人纷纷夫人


Thanks in advance

Is This A Good Question/Topic? 0
  • +

Replies To: RTF Control with chinese characters?

#2 thamathar   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 05-December 18

Re: RTF Control with chinese characters?

Posted 07 December 2018 - 08:30 AM

Hello every one.

I have this code in SQL:

DECLARE 
@StringRTF VARCHAR(MAX),
@Posh VARCHAR(8000)
SET @StringRTF = (select tradu鏰o from artigos where Cod_Art='01010066')
select @StringRTF
SET @Posh = 'powershell.exe -ExecutionPolicy ByPass -Command Add-Type -AssemblyName System.Windows.Forms; $Rtb = New-Object -TypeName System.Windows.Forms.RichTextBox; $stringRTF = \"' + (select @StringRTF) + '\"; $Rtb.Rtf = $stringRTF; $Retorno = $Rtb.Text; Write-Host($Retorno);'
select @Posh
EXEC master.dbo.xp_cmdshell @Posh





The

Quote

select @StringRTF
is returning correctly information

Quote

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


and the

Quote

select @Posh
is returning this information:

Quote

powershell.exe -ExecutionPolicy ByPass -Command Add-Type -AssemblyName System.Windows.Forms; $Rtb = New-Object -TypeName System.Windows.Forms.RichTextBox; $stringRTF = \"{\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 }\"; $Rtb.Rtf = $stringRTF; $Retorno = $Rtb.Text; Write-Host($Retorno);


So if I pickup the last quote and past into the CMD.exe it will return all the information correctly

Quote

夫人冯夫人6346个红的桂花


But if I run the SQl (inside the code tag) it will give me this error:

The string is missing the terminator: ". + CategoryInfo : ParserError: (:)/>/>/> [], ParentContainsErrorRecordEx ception + FullyQualifiedErrorId : TerminatorExpectedAtEndOfString


What is inside of @Posh is correct or it would not work in the CMD.exe, what could be wrong here ?!

Thanks in advance
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14685
  • View blog
  • Posts: 58,688
  • Joined: 12-June 08

Re: RTF Control with chinese characters?

Posted 07 December 2018 - 09:13 AM

First question for the newly curious, why are you calling powershell from inside a sql statement?

Also do not create duplicate topics. Merging.

Also seen here:

https://pt.stackover...v%C3%A9s-de-sql
https://pt.stackover...l-e-xp-cmdshell
Was This Post Helpful? 0
  • +
  • -

#4 thamathar   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 05-December 18

Re: RTF Control with chinese characters?

Posted 07 December 2018 - 09:30 AM

Because I need to convert the information of the RTF in SQL (unfortunately it must be done there), since all I did try did fail, with the powershell I can access the System.WinFormfs.RichTextBox and covert it there. If it was my decision on this, I would simple create a Function to create the RichTextBox do the conversion and return the Text, but is not my call

Did try the first 2 on this blog, https://www.dirceure...-ou-powershell/ (is in portuguese) and failed also, so I'm using the last one there, trying to use powershell for it.

The last one rly did got my hopes, it would be done in SQL, with resource to powershell for it, only with the need some changes on the security on the SQL Server and would create a function for the convertion, but don't know why isn't working as it should

And sorry about the 2 topics, since the last question was more directly to SQL stuff I found creating there would be better.

Yep does are my topics, have been for 3 days trying to do this without success so getting a little desperate :P

Sorry for the double post but can't see the option to edit, the Function I mention on the first paragraph, would be done in the program it self (don't know that much of VB 6, C#/Java Programmer here) even I did require to learn VB just for this hehe.

BtW I did create the first topic here, because the program is done in VB 6

BtW, I also did try this one, https://www.codeproj...t-Revised-Again , did need to do some changes because of the "\'" that the RichTextBox creates on the chinese characters, and it would return me 夫人冯夫人6346个红的桂花, this return also happens when using https://github.com/n...ichTextStripper , but every time I use the Object RichTextBox from the WinForms it self it will create the string correctly
Was This Post Helpful? 0
  • +
  • -

#5 modi123_1   User is offline

  • Suitor #2
  • member icon



Reputation: 14685
  • View blog
  • Posts: 58,688
  • Joined: 12-June 08

Re: RTF Control with chinese characters?

Posted 07 December 2018 - 09:48 AM

Have you considered making your SQL columns nvarchar? I remember that doing better than varchar on non-english characters.
Was This Post Helpful? 0
  • +
  • -

#6 thamathar   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 05-December 18

Re: RTF Control with chinese characters?

Posted 07 December 2018 - 10:02 AM

They are, but for the powershell it must be varchar, got an error by using nvarchar in here as example:

DECLARE 
    @StringRTF nVARCHAR(MAX),
    @Posh nVARCHAR(max)


I will get this error:

Msg 214, Level 16, State 201, Procedure xp_cmdshell, Line 1 [Batch Start Line 0]
Procedure expects parameter 'command_string' of type 'varchar'.



Thats why on the declare they are as varchar, but on the table they are nvarchar

if I put the declare of @Posh to nvarchar(4000) it will execute but it still gives me the error that I mention:

The string is missing the terminator: ". + CategoryInfo : ParserError: (:)/>/>/>/> [], ParentContainsErrorRecordEx ception + FullyQualifiedErrorId : TerminatorExpectedAtEndOfString

Was This Post Helpful? 0
  • +
  • -

#7 thamathar   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 05-December 18

Re: RTF Control with chinese characters?

Posted 13 December 2018 - 11:14 AM

Found the solution for this, after all this time banging my head on the wall hehe, it works and this could be done to all type of RTF

I have a DLL CLR to take care of this for me, that starts a process to call powershell.exe to use the .net System.WinForms.RichTextBox transform there.

This is what I have inside the CLR DLL

using System;
using System.Diagnostics;
using System.IO;

namespace SQLHelper
{
    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;
        }
    }
}



And then just create a function inside the SQL to call this DLL

USE [SQLDB]
GO
/****** Object:  UserDefinedFunction [dbo].[RTF2Text]    Script Date: 13-12-2018 09:32:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

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



After that just use it like ex:

Quote

select dbo.FicheiroChines(RTFText, 'c:\temp\') as teste from RTFTest


By doing this, I can get the text of the RTF inside a function without any problems

Add using inside a select (why can't I edit this ? :P)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1