none
T-SQL CREATE FUNCTION AS EXTERNAL NAME

    Question

  • i've tried to import so RegEx functionalities in my SQL SERVER Database with success but one 

    member function resists with This : Could not find method 'Extract' for type 'SMDRDataParser.RegExFunctions' in assembly 'SMDRDataParser'

    Here`s the vb code

    The assembly name is SMDRDataParser


    Public Class RegExFunctions


     
        <SqlFunction(IsDeterministic:=True, IsPrecise:=True)>
        Public Shared Function RegexMatch(input As SqlString, pattern As SqlString, Options As SqlInt32As SqlBoolean

            If input.IsNull OrElse pattern.IsNull Then
                Return False
            End If
            Try
                Return Regex.IsMatch(input.Value, pattern.Value, CType(Options, RegexOptions))
            Catch ex As Exception
                Return False
            End Try

        End Function


        <SqlFunction(IsDeterministic:=False, IsPrecise:=False, Name:="Extract")>
        Public Shared Function Extract(input As SqlString, pattern As SqlString, StartAt As SqlInt32As SqlString
            If input.IsNull OrElse pattern.IsNull OrElse StartAt < 0 OrElse StartAt > input.Value.Length Then
                Return False
            End If

            Try
                Dim Rx As New Regex(pattern.Value)
                If Rx.IsMatch(input.Value, StartAt.Value) Then
                    Return CType(Rx.Match(pattern.Value).Value, SqlString).Value
                End If
                Return SqlString.Null
            Catch ex As Exception
                Return SqlString.Null
            End Try

        End Function

    End Class

    Here's the T-SQL Script For the assembly import


    IF EXISTS ( SELECT 1
       FROM  sys.assemblies asms
       WHERE asms.name = N'SMDRDataParser' )
     DROP ASSEMBLY [SMDRDataParser]
     
    CREATE ASSEMBLY [SMDRDataParser]
    AUTHORIZATION [dbo]
    FROM <Path> -- You wont see the path my friends!<br/>WITH PERMISSION_SET = SAFE
    
    

    And the one for the Functions


    CREATE FUNCTION [dbo].[RegExMatch](@Input [nvarchar](max), @Pattern [nvarchar](4000), @Options [int])
    RETURNS [bit] WITH EXECUTE AS CALLER
    AS 
    EXTERNAL NAME [SMDRDataParser].[SMDRDataParser.RegExFunctions].[RegexMatch]
     
    
    
    CREATE FUNCTION [dbo].[Extract](@input [nvarchar](max),@pattern [nvarchar](4000),@StartAt [int])
    RETURNS [NVARCHAR](4000) WITH EXECUTE AS CALLER
    AS 
    EXTERNAL NAME [SMDRDataParser].[SMDRDataParser.RegExFunctions].[Extract]
    

    RegExMatch Works good, but Extract do not! I'm clueless for this one pals!

     

     

     


    Monday, April 25, 2011 4:17 AM

Answers

  • Not sure what is going. MDX functions at least has nothing to do with it!

    I copied your code, added the Imports statments needed and some missing underscores. I loaded the assembly, but I cheated a bit, since I didn't recall what the "namespace" statement in C# translates to in VB. I then created the functions successfully. I use SQL 2008 SP2.

    I used the command-line to compile the file. I suspect that you use Visual Studio, and I believe VS is know to add its own things which can mess things up.  I note that for Extract you have a Name attribute that you don't have for the other function. Could that be the answer.

    Here is the code I used.

    Imports System
    Imports System.Text.RegularExpressions
    Imports System.Data
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    
    Public Class RegExFunctions
    
        <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
        Public Shared Function RegexMatch(input As SqlString, pattern As SqlString, Options As SqlInt32) As SqlBoolean
    
            If input.IsNull OrElse pattern.IsNull Then
                Return False
            End If
            Try
                Return Regex.IsMatch(input.Value, pattern.Value, CType(Options, RegexOptions))
            Catch ex As Exception
                Return False
            End Try
    
        End Function
    
        <SqlFunction(IsDeterministic:=False, IsPrecise:=False, Name:="Extract")> _
        Public Shared Function Extract(input As SqlString, pattern As SqlString, StartAt As SqlInt32) As SqlString
            If input.IsNull OrElse pattern.IsNull OrElse StartAt < 0 OrElse StartAt > input.Value.Length Then
                Return False
            End If
    
            Try
                Dim Rx As New Regex(pattern.Value)
                If Rx.IsMatch(input.Value, StartAt.Value) Then
                    Return CType(Rx.Match(pattern.Value).Value, SqlString).Value
                End If
                Return SqlString.Null
            Catch ex As Exception
                Return SqlString.Null
            End Try
    
        End Function
    
    End Class

    And there are the SQL statements:

    CREATE ASSEMBLY SMDRDataParser FROM 'c:\temp\bludder.dll'
    go
    CREATE FUNCTION [dbo].[RegExMatch](@Input [nvarchar](max), @Pattern [nvarchar](4000), @Options [int])
    RETURNS [bit] WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [SMDRDataParser].[RegExFunctions].[RegexMatch]
    go
    CREATE FUNCTION [dbo].[Extract](@input [nvarchar](max),@pattern [nvarchar](4000),@StartAt [int])
    RETURNS [NVARCHAR](4000) WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [SMDRDataParser].[RegExFunctions].[Extract]
    go
    select dbo.Extract('ABCdef', 'ABC', 0)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Monday, May 02, 2011 6:45 AM
    Monday, April 25, 2011 9:35 AM

All replies

  • Are you getting any error ? Please post the error.
    Monday, April 25, 2011 4:55 AM
  • Extract is an built in MDX function in SQL Server, please try to use some other name and try again.


    If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR
    Monday, April 25, 2011 6:27 AM
  • Not sure what is going. MDX functions at least has nothing to do with it!

    I copied your code, added the Imports statments needed and some missing underscores. I loaded the assembly, but I cheated a bit, since I didn't recall what the "namespace" statement in C# translates to in VB. I then created the functions successfully. I use SQL 2008 SP2.

    I used the command-line to compile the file. I suspect that you use Visual Studio, and I believe VS is know to add its own things which can mess things up.  I note that for Extract you have a Name attribute that you don't have for the other function. Could that be the answer.

    Here is the code I used.

    Imports System
    Imports System.Text.RegularExpressions
    Imports System.Data
    Imports System.Data.SqlTypes
    Imports Microsoft.SqlServer.Server
    
    Public Class RegExFunctions
    
        <SqlFunction(IsDeterministic:=True, IsPrecise:=True)> _
        Public Shared Function RegexMatch(input As SqlString, pattern As SqlString, Options As SqlInt32) As SqlBoolean
    
            If input.IsNull OrElse pattern.IsNull Then
                Return False
            End If
            Try
                Return Regex.IsMatch(input.Value, pattern.Value, CType(Options, RegexOptions))
            Catch ex As Exception
                Return False
            End Try
    
        End Function
    
        <SqlFunction(IsDeterministic:=False, IsPrecise:=False, Name:="Extract")> _
        Public Shared Function Extract(input As SqlString, pattern As SqlString, StartAt As SqlInt32) As SqlString
            If input.IsNull OrElse pattern.IsNull OrElse StartAt < 0 OrElse StartAt > input.Value.Length Then
                Return False
            End If
    
            Try
                Dim Rx As New Regex(pattern.Value)
                If Rx.IsMatch(input.Value, StartAt.Value) Then
                    Return CType(Rx.Match(pattern.Value).Value, SqlString).Value
                End If
                Return SqlString.Null
            Catch ex As Exception
                Return SqlString.Null
            End Try
    
        End Function
    
    End Class

    And there are the SQL statements:

    CREATE ASSEMBLY SMDRDataParser FROM 'c:\temp\bludder.dll'
    go
    CREATE FUNCTION [dbo].[RegExMatch](@Input [nvarchar](max), @Pattern [nvarchar](4000), @Options [int])
    RETURNS [bit] WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [SMDRDataParser].[RegExFunctions].[RegexMatch]
    go
    CREATE FUNCTION [dbo].[Extract](@input [nvarchar](max),@pattern [nvarchar](4000),@StartAt [int])
    RETURNS [NVARCHAR](4000) WITH EXECUTE AS CALLER
    AS
    EXTERNAL NAME [SMDRDataParser].[RegExFunctions].[Extract]
    go
    select dbo.Extract('ABCdef', 'ABC', 0)

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by KJian_ Monday, May 02, 2011 6:45 AM
    Monday, April 25, 2011 9:35 AM