Sql clr


(pthompson) #1

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.


(michaelthuma) #2

Why do you think it does not work?

Deployment could be an issue …


(marc hoffman) #3

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.


(pthompson) #4

Thanks Michael / Marc.

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.


(Vyacheslav Dulnev) #5

Hi,

I’ll log this issue as #63893, but can’t give you now when this template will be available.


(Dmitrii) #6

Are there are news on SQL CLR with Oxygene?

Short question, If there is no way to use Oxygene assembly directly for CLR routines,
can C# assembly interface and use Oxygene assembly?


(antonk) #7

Oxygene creates IL like any other .NET language.

Take a look at this article: https://www.codeproject.com/Tips/791953/SQL-CLR-functions

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]

(Dmitrii) #9

Awesome Anton!!
I’ve checked now it works in MSSQL 2017