I want to write a Java User-defined aggregate (UDA). Shall I use J#?
I converted the C# example given in books online to J#. I am getting this error (as well as whole bunch of warning when I create the assembly.)
Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
btw, I have the use unsafe assembly options when creating the assembly otherwise I get this error:
Msg 6265, Level 16, State 1, Line 1
CREATE ASSEMBLY failed because type "com.ms.vjsharp.cor.COMUtils" in safe assembly "vjscor" has a pinvokeimpl method. P/Invoke is not allowed in safe assemblies.
Warning: The Microsoft .Net frameworks assembly 'vjscor, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.
From the warning, I can tell the J# UDA is not tested.
Can someone confirm whether J# UDA is supported or not?
Thanks!
-
Here is my code:
ALTER DATABASE MEDIO set TRUSTWORTHY ON
CREATE ASSEMBLY MyAgg FROM 'C:\code\console\PriceUDA\obj\Debug\PriceUDA.dll' WITH PERMISSION_SET = unsafe
CREATE AGGREGATE MyAgg (@.input nvarchar(200)) RETURNS nvarchar(max) EXTERNAL NAME MyAgg.Concatenate
GO
import System.*;
import System.Data.*;
import Microsoft.SqlServer.Server.*;
import System.Data.SqlTypes.*;
import System.IO.*;
import System.Text.*;
/** @.attribute Serializable() */
/** @.attribute SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
*/
public class Concatenate implements IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;
/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}
/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.get_IsNull())
{
return;
}
this.intermediateResult.Append(value.get_Value()).Append(',');
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}
/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
String output = String.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.get_Length() > 0)
{
output = this.intermediateResult.ToString(0, this.intermediateResult.get_Length() - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
No responses... Has anyone else been able to get this to work?|||
Hi Chang!
In SQL Server 2005 we support C#, Visual Basic and managed C++ with SQL CLR.
We haven't tested integration with other languages. However, I can imagine that other languages' vendors have tested their languages with SQL CLR, or are going to do this. In this case, they may have published their recommendations on how to use their languages' features with SQL CLR, what problems you may have and so on.
I'm not aware of such recommendations for J#.
|||Vadim -- We are asking about Microsoft J#, not some 3rd party tool.Microsoft Visual J# is a .net language that uses the CLR. I've seen dozens of quotes that say it 'should work', but I have yet to find anybody who actually got it to work.
I have a bunch of database-neutral java stored procedures, that I recently ported to work with mssql, that I now need to implement as CLR stored procedures. I'd *really* like to port my java to j# using .net, as opposed to porting to c# (Yes, I know about JLCA, I'm working my way throught that now). Unfortunately, Visual Studio 2005 in J# mode has no support for SQL Server, and every document I find on the internet only talks about using VS to generate SQL code. So far, I have not found 1 single example that shows J# running as a CLR stored proc, or what you might have to do to get it to work.|||
Hi!
You are right; I should have phrased this better. Anyway, due to a variety of reasons we haven’t put significant effort into supporting J#. According to my knowledge, you have chances that functions and procedures will work, but you most probably will have problems with user-defined types and aggregates. Have you tried to wrap your J# logic into, say, C# aggregate?
The fact that a language is a .NET language doesn’t automatically mean that it will go well with SQL CLR. The reason is that SQL CLR puts additional restrictions and requirements to the IL code, compared to those necessary just to run IL executable. Not all code generated by .NET compilers satisfies them, not all run-time libraries of these languages satisfy them. I’d guess the way J# compiles classes is not compliant with SQL CLR.
|||I can't recall the exact problems off the top of my head, but I do recall there being problems with J# in SQL CLR. You may be able to get this working if you register the assembly as UNSAFEan unfortunate move.
I'll see if I can dredge up more.
Cheers,
-Isaac
