November 16, 2009 at 10:26 am
Hi there,
How would you use the following function in C# to create a CLR function in TSQL to use as a translator function?
http://dnknormark.net/post/Translate-text-in-C-using-Google-Translate.aspx
Thank you!
November 16, 2009 at 10:35 am
Hi
You can use the same code as shown in the sample.
As a little side information:
If I'm not wrong, google blocks IPs which send too much requests and don't look like a web browser
Greets
Flo
November 16, 2009 at 10:37 am
That is correct Google will block you in an instant if you hit their servers with too many requests per minute.
This assuming that the reason for writing a CLR is to do a mass update of a large table.
November 16, 2009 at 10:55 am
sweet! thank you very much
November 16, 2009 at 5:06 pm
ok it loosk I can also use the new Microsoft translator. anyways I have created an assembly, but how do I create a function or sproc based on this CLR
CREATE function translate
AS
EXTERNAL NAME Translator.TranslateText
does not seem to do the trick.
November 17, 2009 at 2:17 am
Hi
You don't have to create a usual .NET assembly like a console application or a class library. You have to create a SQL Server Project which is a special project type.
Greets
Flo
November 17, 2009 at 10:54 am
ok got it done with no SQL project, straight C# using Visual Express C#. It works nice, send out the text in English, translates and brings back an html string with the translation in French.
Thanks y'all
November 17, 2009 at 11:43 am
First, thanks for the feedback. Glad that we could help. 🙂
Second, didn't know that SQLCLR assemblies can be developed with Express Edition. Thanks for sharing this information!
Greets
Flo
November 17, 2009 at 11:48 am
well as one famous sleepy cat once said "The more I learn, the more I know what I do not know"
here is the code
using System;
using System.Net;
using System.Text;
using System.Text.RegularExpressions;
public static class Translator
{
/// <summary>
/// Translates the text.
/// </summary>
/// <param name="input">The input.</param>
/// <param name="languagePair">The language pair.</param>
/// <returns></returns>
public static void Main(string[] args)
{
TranslateText(args[1], args[2]);
}
/// <summary>
/// Translate Text using Google Translate
/// </summary>
/// <param name="input">The string you want translated</param>
/// <param name="languagePair">2 letter Language Pair, delimited by "|".
/// e.g. "en|da" language pair means to translate from English to Danish</param>
/// <param name="encoding">The encoding.</param>
/// <returns>Translated to String</returns>
public static string TranslateText(string input, string languagePair)
{
string url = String.Format("http://www.google.com/translate_t?hl=en&ie=UTF8&text={0}&langpair={1}", input, languagePair);
string result = String.Empty;
using (WebClient webClient = new WebClient())
{
webClient.Encoding = System.Text.Encoding.UTF7;
result = webClient.DownloadString(url);
}
Match m = Regex.Match(result, "(?<=<div id=result_box dir=\"ltr\">)(.*?)(?=</div>)");
if (m.Success)
result = m.Value;
return result;
}
}
November 17, 2009 at 12:08 pm
Hi yosiasz
Thanks for sharing! But I'm a little confused :-P. Thought you are looking for a SQLCLR procedure to handle this. Your source code looks like a console application with command line arguments.
Thought you are talking about something like this (one of my CLR procedures):
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
/// <summary>
/// Compress a specified file using GZip
/// </summary>
/// <param name="inputFile">The input file to be compressed.</param>
/// <param name="outputFile">The compressed output file</param>
/// <param name="overwrite">Specifies if an existing output file
/// shall or shall not be overwriten. If false and the specified output file
/// already exists the procedure will throw an exception.</param>
/// <example>
/// <code>
/// EXECUTE CompressFile 'D:\mssql\Test\MobyDick.txt', 'D:\mssql\Test\MobyDick.gz', 0
/// </code>
/// </example>
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CompressFile(
[SqlFacet(
MaxSize = 512,
IsNullable = false
)
]
SqlString inputFile,
[SqlFacet(
MaxSize = 512,
IsNullable = false
)
]
SqlString outputFile,
[SqlFacet()]
SqlBoolean overwrite
) {
// validate
if (inputFile.IsNull)
throw new ArgumentNullException("inputFile");
if (outputFile.IsNull)
throw new ArgumentNullException("outputFile");
if (File.Exists(outputFile.Value)
&& (overwrite.IsNull
|| overwrite.Value == false)
)
throw new IOException("Specified output file already exists.");
// start compression
using (FileStream strmIn = new FileStream(inputFile.Value, FileMode.Open, FileAccess.Read)) {
using (FileStream strmOut = new FileStream(outputFile.Value, FileMode.Create, FileAccess.Write)) {
using (GZipStream strmGz = new GZipStream(strmOut, CompressionMode.Compress)) {
// use a 1/2 kb buffer
byte[] buffer = new byte[512];
while (true) {
// get next chunk
int count = strmIn.Read(buffer, 0, buffer.Length);
if (count == 0)
break;
// write chunk
strmGz.Write(buffer, 0, count);
if (count < buffer.Length)
break;
}
// flush last bytes
strmGz.Flush();
}
// flush
strmOut.Flush();
}
}
}
};
Greets
Flo
November 17, 2009 at 12:25 pm
well after I create that css file. I compile it csc /target:library translate.css which creates me a dll.
Then I
CREATE ASSEMBLY translate from 'C:\translate.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
CREATE FUNCTION [dbo].[translate]
(@input nvarchar(4000), @languagePair nvarchar(4000)
)
RETURNS nvarchar(max)
WITH EXECUTE AS CALLER
EXTERNAL NAME [translate].[Translator].[TranslateText]
GO
SELECT [dbo].[translate] (
'Hello beautiful French lady. Could I have this dance with you?'
,'EN|FR')
Merci
November 17, 2009 at 12:27 pm
Ah.. thanks for explanation. 🙂
Greets to Denmark!
Flo
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply