Back to Questions
3.5k
views5
comments
SQL Server and F# : problem with user defined aggregate
f#server

Hi,
I'm playing with Sql Server and F#
I know there has been a post on this topic some time ago, but just to sum up, here is how to define SqlProcedures and SqlFunctions 

  • 1
2
3

    [<SqlProcedure>]
let printToday() =
   SqlContext.Pipe.Send(DateTime.Now.ToString())
  • 1
2
3
4
5
6
7
8
9
10
11

    [<SqlFunction(DataAccess = DataAccessKind.Read)>]
let simple_prod(annee_mois:int) =
    let (conn:SqlConnection ) = new SqlConnection("context connection=true")
    conn.Open();
    let sql_cmd = sprintf "SELECT mystuff from sandbox..MyTable where annee_mois = %d" annee_mois
    let comm = new SqlCommand(sql_cmd,conn)
    let v = seq { use reader = comm.ExecuteReader()
                    while reader.Read() do
                    yield (reader.GetDouble 0) }
    let res = Seq.fold ( * )  1. v
    new SqlDouble(res)

If you compile this into a file named my_udf.fs, without mentioning a module name,
the code is referenced as if you created a "My_udf" module (note the uppercase).
Run a compilation as a dll (fsc -a my_udf.fs)
Then you run the following code in Sql Server:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

sp_configure 'clr enabled', 1
go
reconfigure
go
alter database sandbox set trustworthy on
go
use sandbox
go
create assembly fslib from 'e:\bin\fsharp\bin\FSharp.Core.dll' with permission_set = unsafe
go
create assembly toy_functions from 'E:\usr\f#\UDagg\my_udf.dll' with permission_set = unsafe
go
create procedure myTodayFunc as external name toy_functions.My_udf.printToday;
go
CREATE FUNCTION simple_prod(@annee_mois  int) RETURNS FLOAT
AS EXTERNAL NAME toy_functions.My_udf.simple_prod
go

So far so good.
Now User Defined Aggregate. It's a cool feature, say to run statistical functions (Gini coefficient, median, inter-quartile range, whatever)
Here's a dummy function for it : 

1
2
3
4
5
6
7
8
9
10
11
12
13
14

[<Serializable;SqlUserDefinedAggregate(Format.Native)>]
type dummyAgg =
    class
        val mutable value : string
        new () = {value="a"}
        member x.Init() =
            x.value<-  "a"
        member x.Accumulate(v: SqlString) =
            x.value <- "b"
        member x.Merge(other : dummyAgg) =
            x.value <- "c"
        member x.Terminate () =
            new System.Data.SqlTypes.SqlString("pop")
end

The problem :

I cannot reference the type : it's not seen by Sql Server
When trying to reference it : 

1
2

CREATE AGGREGATE dummyAgg (@input nvarchar(200)) RETURNS  nvarchar(max)
EXTERNAL NAME [toy_functions].[My_udf.dummyAgg]

it throws a

Msg 6556, Level 16, State 1, Line 1
Échec de 'CREATE AGGREGATE', le type 'My_udf.dummyAgg' étant introuvable dans l'assembly ''toy_functions'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE a échoué.

Which means : cannot find your type dummyAgg
I think I exhausted all the combinaisons in the name (the part after EXTERNAL NAME), but the problem seems elsewhere.

Do you have any ideas?

Many thanks in advance
Regards,
Julien

.

Julien,

I've recreated everything on one of my servers here and confirm that the error does occur.

Here's what I know so far (discovered using Reflector):

  • If a SqlAggregate is constructed in C#, it inherits from System.ValueType. F# by default inherits from System.Object.
  • A C# SqlAggregate produces a class with the following IL declaration ".class public sequential ansi serializable sealed beforefieldinit".
  • An F# SqlAggregate produces a class with the following IL declaration ".class auto ansi serializable nested public beforefieldinit".
  • There are other slight differences in the IL that may/may not be pertinent.

For reference, I created the following C# and F# code to compare. The C# is the standard SQLCLR Aggregate wizard constructed code. I've also included the IL as captured by Reflector. Note that I left the methods collapsed, i.e. the IL within each method has been suppressed as the IL is verbose and method bodies are likely not a cause.

C#----------------------------------------------------

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct Aggregate1
{
    public void Init()
    {
        // Put your code here
    }
    public void Accumulate(SqlString Value)
    {
        // Put your code here
    }
    public void Merge(Aggregate1 Group)
    {
        // Put your code here
    }
    public SqlString Terminate()
    {
        // Put your code here
        return new SqlString("");
    }
    // This is a place-holder member field
    private int var1;
}

F#----------------------------------------------------

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

module fsqlj
#light
open Microsoft.SqlServer.Server
open System
open System.Data.SqlClient
open System.Data.SqlTypes

[<Serializable>]
[<SqlUserDefinedAggregate(Format.Native)>]
type dummyAgg =
  class
    inherit System.ValueType
    val mutable value : string
    new () = {value="a"}
    member x.Init() =
      x.value <- "a"
    member x.Accumulate(v: SqlString) =
      x.value <- "b"
    member x.Merge(other : dummyAgg) =
      x.value <- "c"
    member x.Terminate () =
      new System.Data.SqlTypes.SqlString("pop")
(*    interface Microsoft.SqlServer.Server.IBinarySerialize with
    end*)
  end

C# IL----------------------------------------------------

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

.class public sequential ansi serializable sealed beforefieldinit Aggregate1
    extends [mscorlib]System.ValueType
{
    .custom instance void [System.Data]Microsoft.SqlServer.Server.SqlUserDefinedAggregateAttribute::.ctor(valuetype [System.Data]Microsoft.SqlServer.Server.Format) = { int32(1) }
    .method public hidebysig instance void Accumulate(valuetype [System.Data]System.Data.SqlTypes.SqlString 'Value') cil managed
    {
    }

    .method public hidebysig instance void Init() cil managed
    {
    }

    .method public hidebysig instance void Merge(valuetype Aggregate1 Group) cil managed
    {
    }

    .method public hidebysig instance valuetype [System.Data]System.Data.SqlTypes.SqlString Terminate() cil managed
    {
    }

    .field private int32 var1

}

F# IL----------------------------------------------------

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

.class auto ansi serializable nested public beforefieldinit dummyAgg
    extends [mscorlib]System.ValueType
{
    .custom instance void [System.Data]Microsoft.SqlServer.Server.SqlUserDefinedAggregateAttribute::.ctor(valuetype [System.Data]Microsoft.SqlServer.Server.Format) = { int32(1) }
    .custom instance void Microsoft.FSharp.Core.CompilationMappingAttribute::.ctor(valuetype Microsoft.FSharp.Core.SourceLevelConstruct) = { int32(3) }
    .method public specialname rtspecialname instance void .ctor() cil managed
    {
    }

    .method public instance void Accumulate(valuetype [System.Data]System.Data.SqlTypes.SqlString v) cil managed
    {
    }

    .method public instance void Init() cil managed
    {
    }

    .method public instance void Merge(valuetype fsqlj/dummyAgg other) cil managed
    {
    }

    .method public instance valuetype [System.Data]System.Data.SqlTypes.SqlString Terminate() cil managed
    {
    }

    .property instance string value
    {
        .get instance string fsqlj/dummyAgg::get_value()
        .set instance void fsqlj/dummyAgg::set_value(string)
        .custom instance void Microsoft.FSharp.Core.CompilationMappingAttribute::.ctor(valuetype Microsoft.FSharp.Core.SourceLevelConstruct, int32) = { int32(4) int32(0) }
    }

    .field public string _value

}

I'll do a bit more digging in the next day or so.

Regards,

---O

By on 1/7/2008 2:33 AM ()

Could just be a naming problem. Class in F# within a module are created as inner classes in the class that represents the module. In CLR talk an inner class is seperated from it's parent via a + and not a . Which means you might want to try something like:

EXTERNAL NAME [toy_functions].[My_udf+dummyAgg]

The easiest way to check the exact CLR name of dummyAgg is using reflector ([link:www.aisto.com])

And you can also stop dummyAgg being defined as an inner class by adding a namespace declarion to the top of your my_udf.fs file: 

1

namespace my_udf
By on 1/7/2008 4:05 AM ()

Hi ,
I tried the "+" notation, but it didn't work unfortunately.
And the explicit naming (adding a namespace + module name) didn't make it either.
I also quickly tried to replace the class by a struct (there are examples do that in SQL Server 2005 and c# code, see [link:blogs.msdn.com] )

1
2
3
4
5
6
7
8
9
10
11
12
13
14

[<Serializable;SqlUserDefinedAggregate(Format.Native)>]
type dummyAggStruct =
    struct
        val mutable value : string
        new(v:string) = {value=v}
        member x.Init() =
            x.value <- "a"
        member x.Accumulate(v: SqlString) =
            x.value <- "b"
        member x.Merge(other : dummyAgg) =
            x.value <- "c"
        member x.Terminate () =
            new System.Data.SqlTypes.SqlString("pop")
end

But I can't make be seen either...

By the way, is it possible to use mutable values in a struct ?
I must do something dumb, but with the previous struct, "value" seems immutable

If you : 

1
2
3

let test =new dummyAggStruct("myValue")
test.Init();
test.value is still "myValue", and not "a"

( Sorry, I didn't want a side-effet in the thread ;) )
Julien

By on 1/7/2008 2:52 PM ()

I hoped to reply to this sooner, but I couldn't get to a working SQL installation until today. I had run into similar issues when I tried to complete my ancient blog entries, but I got sidetracked on figuring out how to load the f# library as a safe assembly and then lost my notes :-(

The root problem for the CREATE AGGREGATE error is due to the class being defined as an inner class when in an F# module. As Robert pointed out, this can be fixed by using a namespace. Note that you can't define "standalone" functions in that scope; you'll have to have them in a separate module. CLR UDTs in SQL have the same limitation.

There are some further changes necessary to make your initial example work. The first is to add a System.Runtime.InteropServices.StructLayout attribute with LayoutKind.Sequential. This allows native serialization to work "as expected". The second is limitation of the native serialization to only work with value types; in particular, not strings. You can either work with value types or implement the IBinarySerialize interface as described in [link:msdn2.microsoft.com]. The example is for a UDT so you don't have implement the rest of the UDT-specific functions, just Read() and Write(). The pickler combinators (see [link:www.strangelights.com]) should make this pretty easy. There might be a way using XML serialization, but I can't imagine that it would perform very well.

Let us know how it works.

Lewis

By on 1/7/2008 5:51 PM ()

Hi and thanks for your help !

The solution was :
a namespace without module (which means : you cannot mix SqlProcedure or SqlFunction in the same file as your used defined aggregates, as functions are not allowed outside of a module.)
Indeed there were this System.Runtime.InteropServices.StructLayout(LayoutKind.Sequential) attribute to add
So the code (which is a used-defined product) is : 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

#light
namespace Stuff
open System
open System.Data
open System.Data.Sql
open System.Data.SqlTypes
open Microsoft.SqlServer.Server
open System.Runtime.InteropServices
open System.IO

[<Serializable;SqlUserDefinedAggregate(Format.Native);System.Runtime.InteropServices.StructLayout(LayoutKind.Sequential)>]
type MyProd =
    class
        val mutable accum : float
        member x.Init() =
            x.accum<-  1.0
        member x.Accumulate( v: float) =
            x.accum <- x.accum * v
        member x.Merge(other : MyProd) =
            x.accum <- x.accum * other.accum
        member x.Terminate () =
            new System.Data.SqlTypes.SqlDouble(x.accum)
end

Then the SQL Code is : 

1
2
3
4

create assembly other from 'D:\usr\f#\other.dll' with permission_set = unsafe
go
CREATE AGGREGATE myproduct (@input FLOAT) RETURNS FLOAT
EXTERNAL NAME [other].[Stuff.MyProd]

In the future I'll try an example with a user-defined serializer.

Thanks again for your help
Regards
Julien

By on 1/8/2008 2:00 AM ()

    Topic tags

    IntelliFactory Offices Copyright (c) 2011-2012 IntelliFactory. All rights reserved.
    Home | Products | Consulting | Trainings | Blogs | Jobs | Contact Us | Terms of Use | Privacy Policy | Cookie Policy     		Built with WebSharper