SQL Anywhere includes support for CLR stored procedures and functions. A CLR stored procedure or function behaves the same as a SQL stored procedure or function except that the code for the procedure or function is written in a .NET language such as C# or Visual Basic, and the execution of the procedure or function takes place outside the database server (that is, within a separate .NET executable). There is only one instance of this .NET executable per database. All connections executing CLR functions and stored procedures use the same .NET executable instance, but the namespaces for each connection are separate. Statics persist for the duration of the connection, but are not shareable across connections. Only .NET version 2.0 is supported.

To call an external CLR function or procedure, you define a corresponding stored procedure or function with an EXTERNAL NAME string defining which DLL to load and which function within the assembly to call. You must also specify LANGUAGE CLR when defining the stored procedure or function. An example declaration follows:

CREATE PROCEDURE clr_stored_proc( 
    IN p1 INT, 
    IN p2 UNSIGNED SMALLINT, 
    OUT p3 LONG VARCHAR) 
EXTERNAL NAME 'MyCLRTest.dll::MyCLRTest.Run( int, ushort, out string )' 
LANGUAGE CLR;

In this example, the stored procedure called clr_stored_proc, when executed, loads the DLL MyCLRTest.dll and calls the function MyCLRTest.Run. The clr_stored_proc procedure takes three SQL parameters, two IN parameters, one of type INT and one of type UNSIGNED SMALLINT, and one OUT parameter of type LONG VARCHAR. On the .NET side, these three parameters translate to input arguments of type int and ushort and an output argument of type string. In addition to out arguments, the CLR function can also have ref arguments. A user must declared a ref CLR argument if the corresponding stored procedure has an INOUT parameter.

The following table lists the various CLR argument types and the corresponding suggested SQL data type:

The declaration of the DLL can be either a relative or absolute path. If the specified path is relative, then the external .NET executable searches the path, and other locations, for the DLL. The executable does not search the Global Assembly Cache (GAC) for the DLL.

Like the existing Java stored procedures and functions, CLR stored procedures and functions can make server-side requests back to the database, and they can return result sets. Also, like Java, any information output to Console.Out and Console.Error is automatically redirected to the database server messages window.

For more information about how to make server-side requests and how to return result sets from a CLR function or stored procedure, refer to the samples located in the samples-dir\SQLAnywhere\ExternalEnvironments\CLR directory.

To use CLR in the database, make sure the database server is able to locate and start the CLR executable. You can verify if the database server is able to locate and start the CLR executable by executing the following statement:

START EXTERNAL ENVIRONMENT CLR;

If the database server fails to start CLR, then the database server is likely not able to locate the CLR executable. The CLR executable is dbextclr12.exe. Make sure that this file is present in the install-dir\Bin32 or install-dir\Bin64 folder, depending on which version of the database server you are using.

Note that the START EXTERNAL ENVIRONMENT CLR statement is not necessary other than to verify that the database server can launch CLR executables. In general, making a CLR stored procedure or function call starts CLR automatically.

Similarly, the STOP EXTERNAL ENVIRONMENT CLR statement is not necessary to stop an instance of CLR since the instance automatically goes away when the connection terminates. However, if you are completely done with CLR and you want to free up some resources, then the STOP EXTERNAL ENVIRONMENT CLR statement releases the CLR instance for your connection.

Unlike the Perl, PHP, and Java external environments, the CLR environment does not require the installation of anything in the database. As a result, you do not need to execute any INSTALL statements before using the CLR external environment.

Here is an example of a function written in C# that can be run within an external environment.

public class StaticTest
{
    private static int val = 0;

    public static int GetValue() {
        val += 1;
        return val;
    }
}

When compiled into a dynamic link library, this function can be called from an external environment. An executable image called dbextclr12.exe is started by the database server and it loads the dynamic link library for you. Different versions of this executable are included with SQL Anywhere. For example, on Windows you may have both 32-bit and 64-bit executables. One is for use with the 32-bit version of the database server and the other for the 64-bit version of the database server.

To build this application into a dynamic link library using the Microsoft C# compiler, use a command like the following. The source code for the above example is assumed to reside in a file called StaticTest.cs.

csc /target:library /out:clrtest.dll StaticTest.cs

This command places the compiled code in a DLL called clrtest.dll. To call the compiled C# function, GetValue, a wrapper is defined as follows using Interactive SQL:

CREATE FUNCTION stc_get_value() 
RETURNS INT 
EXTERNAL NAME 'clrtest.dll::StaticTest.GetValue() int' 
LANGUAGE CLR;

For CLR, the EXTERNAL NAME string is specified in a single line of SQL. You may be required to include the path to the DLL as part of the EXTERNAL NAME string so that it can be located. For dependent assemblies (for example, if myLib.dll has code that calls functions in, or in some way depends on, myOtherLib.dll) then it is up to the .NET Framework to load the dependencies. The CLR External Environment will take care of loading the specified assembly, but extra steps might be required to ensure that dependent assemblies are loaded. One solution is to register all dependencies in the Global Assembly Cache (GAC) by using the Microsoft gacutil utility installed with the .NET Framework. For custom-developed libraries, gacutil requires that these be signed with a strong name key before they can be registered in the GAC.

To execute the sample compiled C# function, execute the following statement.

SELECT stc_get_value();

Each time the C# function is called, a new integer result is produced. The sequence of values returned is 1, 2, 3, and so on.

For additional information and examples on using the CLR in the database support, refer to the examples located in the samples-dir\SQLAnywhere\ExternalEnvironments\CLR directory.