DB Best blog

Here I will describe the migration of functions created as external C libraries in Oracle.  Usually these Oracle functions are defined as CREATE FUNCTION … AS external library … LANGUAGE C. The above statement publishes an external C library through a call specification.

The similar specification in SQL Server 2005/2008 can be written by means of CREATE FUNCTION… AS EXTERNAL NAME statement.  It specifies the binding of a C# assembly method with a function. In our case it is necessary to create an assembly which will be used instead of a definite C library. Then this assembly should be deployed in SQL Server and a function calling the necessary assembly method should be created.  I must make it clear that you do not need to rewrite your C library into a C# assembly. The only code to write is a code of importing the library to an assembly.

Below, I will describe how to import a C library and deploy it in SQL Server.

Suppose, we have the following user-defined function defined as language C module in Oracle:

CREATE OR REPLACE FUNCTION Work.ExecCmd (command_str VARCHAR2)
RETURN BINARY_INTEGER AS EXTERNAL
LIBRARY c_CmdShell
NAME “CmdShell”
LANGUAGE C;

The library was created in Oracle as:

CREATE LIBRARY c_CmdShell AS ‘/DLLs/CmdShell.dll’;

A C# .NET assembly can be viewed as a SQL Server counterpart of the Oracle library. Thus, we should create a C# assembly first. Follow the steps described below to create an assembly importing dll:

1) Create a project in Visual Studio 2005.

2) In Solution Explorer, right-click on the project name in tree view, and choose Add > User-Defined Function.

3) Print code for importing a dll in the class definition.

To declare a method as having an implementation from a DLL import, do the following:

- Declare the method with the static and extern C# keywords.
- Attach the DllImport attribute to the method. The DllImport attribute allows specifying the name of the DLL that contains the method. The common practice is to name the C# method the same way as the imported method, but nevertheless a different name for the C# method can be also used.
- Optionally, specify custom marshaling information for the method’s parameters and return value, which will override the .NET Framework default marshaling.

4) Specify the full path to your C library. The full path for the imported dll is specified as a parameter of the DllImport function.  Note that the imported dll should be placed in the folder specified in the path.

NOTE:  If the path is not specified in the function parameter CLR will search the dll in the Binn directory first (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn), then it will search the dll in directories listed in PATH environment variable.

The following is the example of C# class code for importing a dll in C# assembly:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Security.Permissions;
using System.Runtime.InteropServices;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
          
    [DllImport("c:\\WINDOWS\\system32\\CmdShell.dll")] public static extern int CmdShell(string cmd);    
};

The next step is the deploying the assembly on SQL Server. Visual Studio can deploy the assembly automatically when you build, start, or step through the project or use the Build/Deploy function on Visual Studio’s main menu (if the Deploy Code option is checked). For deploying the project from SQL Server Management Studio (suppose it is ‘D:\WORK\C#\Projects\cmd_shell’), execute the following T-SQL command from within Management Studio:

USE <database_name>
GO

CREATE ASSEMBLY cmd_shell
FROM ‘D:\WORK\C#\Projects\cmd_shell\cmd_shell\bin\Debug\cmd_shell.dll’
GO

And the last step is creating a special SQL Server user-defined function (so called CLR function).  Deployment of such functions may be automatic in Visual Studio (the Deploy Code option is checked).
To create a CLR function manually by means of SQL Server Management Studio, use the T-SQL CREATE FUNCTION command. For example, to create the CmdShell function, use this command:

USE <database_name>
GO

CREATE FUNCTION CmdShell
(@cmd nvarchar(100))
RETURNS INT
AS EXTERNAL NAME cmd_shell.UserDefinedFunctions.CmdShell

Thus we have a function in SQL Server which can be called from database level just like the original Oracle function.

Written by Galina Snurnikova

Tagged as C functions, Conversion, external, Migration, Oracle, SQL CLR, SQL Server + Categorized as Newsletters, Newsletter #5, January, 22, 2009