none
System.Numerics SQL 2012

    Вопрос

  • I've got an assembly in SQL 2008 R2, it uses Oyster / IntX (from CodePlex.com) to do bitwise on very large varchar fields.. and it's been working pretty well... it's a tiny bit slow..

    So I want to change this to native BigInteger class from System.Numerics and use SQL 4.0 / SQL 2012 natively.. I Just having a tiny bit of trouble getting my hands on SQL 2012 CLR information..

    I just can't figure out how to get System.Numerics uploaded to SQL Server, I keep on getting this message

    Msg 6596, Level 16, State 1, Line 1
    CREATE ASSEMBLY failed because assembly 'System.Numerics' is a system assembly. Consider creating a user assembly to wrap desired functionality.

    Or maybe my algorithm just sucks, I don't know.. I think that I basically need to reinvent the wheel-- provide my own bitwise operations (because it's not built in).. I've had to do that enough.. If I want to check to see if 4 is in 3213456, then I divide by 8 and see if the result is greater than 4.. I don't know where I came up with that algorithm, but it's working well enough- it's just SLOW.

    PS – note, I have created this as a varchar(875), and it supports up toe 875 bits- which is enough for us for the forseeable future – because I want to be able to include it as an index, I’ll probably drop that requirement eventually.

    -------------------------------------------------------
    Option Explicit On

    Imports Oyster.Math.IntX

    Public Class Class1
    -------------------------------------------------------
    Public Function TestClass() As String

    Dim X As New Oyster.Math.IntX

    Return "Hello World"

    End Function
    -------------------------------------------------------
    Public Function fnIntToCSV(ByVal J As Long) As String

    Dim I As Integer
    Dim thePower As Long
    Dim S As Text.StringBuilder = New Text.StringBuilder


    For I = 0 To 30
    thePower = 2 ^ I
    If J And (thePower) Then
    S.Append(I)
    S.Append(";")
    End If
    Next I

    Return S.ToString

    End Function
    -------------------------------------------------------
    Public Function fnIntXToCSV(ByVal J As Oyster.Math.IntX) As String

    Dim I As Integer
    Dim thePower As Oyster.Math.IntX
    Dim remainder As Oyster.Math.IntX
    Dim S As Text.StringBuilder = New Text.StringBuilder

    For I = 0 To 875

    If I > 0 Then
    thePower = Oyster.Math.IntX.Pow(2, I)
    Else
    thePower = 1
    End If

    If I > J Then Exit For
    If thePower > J Then Exit For

    remainder = J Mod thePower * 2

    If remainder >= thePower Then
    'strOut = strOut & thePower.ToString & ";"
    S.Append(I.ToString)
    S.Append(";")
    End If

    Next I

    Return S.ToString

    End Function
    -------------------------------------------------------
    Public Function fnCSVToIntX(ByVal strIn As String) As Oyster.Math.IntX

    Dim I As Integer
    Dim C As Integer
    Dim J As Oyster.Math.IntX = 1
    Dim K As Oyster.Math.IntX = 0

    Dim str As String()

    str = Split(strIn, ";")

    C = UBound(str)


    For I = 0 To C
    'If IsNumeric(Trim(str(I))) = False Then
    ' GoTo ReturnFailed
    'End If

    If Len(str(I) & "") > 0 Then
    J = Oyster.Math.IntX.Parse(str(I))
    K = K + Oyster.Math.IntX.Pow(2, J)
    End If
    Next

    Return K

    End Function
    -------------------------------------------------------
    Public Function fnIntXToBit(ByVal J As Oyster.Math.IntX, BitLocation As Integer) As Boolean

    Dim thePower As Oyster.Math.IntX

    If BitLocation > 0 Then
    thePower = Oyster.Math.IntX.Pow(2, BitLocation)
    Else
    thePower = 1
    End If


    If J Mod thePower * 2 > thePower Then
    'strOut = strOut & thePower.ToString & ";"
    Return 1
    Else
    Return 0
    End If

    End Function
    -------------------------------------------------------
    Public Function fnIntXToBit_Old(ByVal J As Oyster.Math.IntX, BitLocation As Integer) As Boolean

    Dim thePower As Oyster.Math.IntX
    Dim theRemainder As Oyster.Math.IntX

    If BitLocation > 0 Then
    thePower = Oyster.Math.IntX.Pow(2, BitLocation)
    Else
    thePower = 1
    End If

    If BitLocation > J Then Return 0
    If thePower > J Then Return 0

    theRemainder = J Mod thePower * 2

    If theRemainder > thePower Then
    'strOut = strOut & thePower.ToString & ";"
    Return 1
    Else
    Return 0
    End If

    End Function



    End Class


     
    • Изменено aaron.kempf 13 марта 2012 г. 19:39
    13 марта 2012 г. 19:32

Ответы

  • Yep, that's the error you get. Not sure what the difference is between this system assembly and System.Net.dll, which catalogs with permission_set = unsafe and a warning that it's untested. But the error suggests that the SQL Server code is looking for specific system assemblies that they won't allow in SQL Server with CREATE ASSEMBLY. That's the first time I've run across that type of assembly (maybe they didn't want folks to confuse System.Numerics.BigInteger with System.Int64, which is "equivalent to" SQL Server's BIGINT data type), I'll see if I can find out why, no promises.

    Did you try wrapping the functionality in a user assembly, as the error message suggests? Seems like that wouldn't help, if you need to reference System.Numerics.dll. Or use the equivalent of the System.Numerics.BigInteger class into a UDT (i.e. without relying on their code, reverse engineering would likely be frowned upon ;-)? 

    Cheers, Bob

    • Помечено в качестве ответа KJian_ 20 марта 2012 г. 5:41
    14 марта 2012 г. 4:54