Can Oxygene be used to produce SQL CLR projects e.g. user defined functions, stored procedures etc…
I think the answer is no, but can you confirm this. Assuming the answer is no, are there any plans to supprot this at some point. We have no desperate need, but if it isn’t in your roadmap, we can make use of C# or VB if necessary.
Oxygene compiles to plain IL assemblies — virtually indistinguishable from those created with C# or VB. There’s no good reason why an Oxygene-built assembly should not host into SQL Server just fine.
I’d taken a quick look at the project template in VS for an SQL server project and stopped at the langauge combo box - it lists only C# and VB. At least there is a route for us to use Oxygene syntax when we meed it, although it does look as if it would be more straight forward if there was Oxygene listed in that combo box.
I’ve created a very simple CLR function using the Oxygene Class Library template and this code:
namespace SqlFunctionSample;
uses
System.Data.SqlClient,
Microsoft.SqlServer.Server;
type
SqlExtension = public static class
public
[SqlFunction(Name = "ClrUpperCase", IsDeterministic = true)]
class method UpperCase(value: String): String;
begin
exit value:ToUpperInvariant();
end;
end;
end.
Then I enabled CLR programmability like it is described in the article, then registered the built assembly using SQL query
create assembly [SqlFunctions]
from 'r:\rofx-temp\SqlFunctionSample\SqlFunctionSample\Bin\Release\SqlFunctionSample.dll'
Then I defined the function in the SQL Server using
CREATE FUNCTION [dbo].ClrUpperCase(@Input nvarchar(max)) RETURNS nvarchar(max)
EXTERNAL NAME [SqlFunctions].[SqlFunctionSample.SqlExtension].UpperCase;
And then I was able to use the CLR function in my queries:
select dbo.ClrUpperCase([Name]) from [dbo].[Videos]