Switch to standard view 
  Sybase logo
 
 
 



CLR External Environment

SQL Anywhere includes support for six external runtime environments. These include embedded SQL and ODBC applications written in C/C++ as well as applications written in Java, Perl, PHP, or languages such as C# and Visual Basic which are based on Microsoft's Common Language Runtime (CLR).

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 with the exception that the code for the procedure or function is written in C# or Visual Basic and the execution of the procedure or function takes place outside of the server (that is, within a separate .NET executable). Note, 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 will persist for the duration of the connection, but will not be shareable across connections.

Please refer to the online documentation for more information about SQL Anywhere CLR External Environment.

CLR External Environment Sample

In this example, the database server executes CLR functions that call the two methods defined in the Visual Studio 2008 Libraries C# languages sample solution. The values returned from these methods are used to populate a SQL Anywhere database.

Requirements

Running the Sample

  1. Start Visual Studio and open the solution LanguageSamples\Libraries\Libraries.sln.
  2. Build the solution to create the assembly Functions.dll. This assembly contains the two methods NumberOfDigits() and Factorial() that are called from the CLR functions.
  3. Using Windows Explorer, locate the assembly Functions.dll and copy this file into the folder C:\Program Files\SQL Anywhere 12\Bin32. The CLR external executable, dbextclr12.exe, searches the path environment variable for the DLL. Note that the executable does not search the Global Assembly Cache (GAC).
  4. Open a Command Prompt and create a new SQL Anywhere database by running this command:

    dbinit clrtest.db

  5. Start the database by running this command:

    dbeng12 clrtest.db

  6. Launch Interactive SQL and connect to the database by running this command:

    dbisql -c "uid=dba;pwd=sql;eng=clrtest"

  7. Let's create a simple table to store the results from the CLR functions. Copy and paste the following statements into Interactive SQL and execute them (press F5):

    CREATE TABLE clr_test (
        num       INTEGER NOT NULL,
        digits    INTEGER NULL,
        factorial INTEGER NULL,
        PRIMARY KEY (num)
    );

    This table has two columns, digits and factorial, that will store the values returned from the CLR functions.

  8. Let's create the first CLR function. Copy and paste the following statements into Interactive SQL and execute them (press F5):

    CREATE FUNCTION clr_DigitCount(IN num LONG VARCHAR)
    RETURNS INT
    EXTERNAL NAME 'Functions.dll::Functions.DigitCount.NumberOfDigits(string) int'
    LANGUAGE CLR;

    This function invokes the method NumberOfDigits() that is defined in the assembly Functions.dll. The method takes in an integer number formatted as a string and returns its number of digits. This is the value that is returned by the CLR function.

  9. Create the second CLR function. Copy and paste the following statements into Interactive SQL and execute them (press F5):

    CREATE FUNCTION clr_Factorial(IN num INT)
    RETURNS INT
    EXTERNAL NAME 'Functions.dll::Functions.Factorial.Calc(int) int'
    LANGUAGE CLR;

    Again, the assembly Functions.dll defines the method Factorial() and this method takes in an integer number and returns its factorial (n!). This is the value that is returned by the CLR function.

  10. To run the CLR functions, create a stored procedure that will call the two functions and use their returned values to populate the database. Copy and paste the following statements into Interactive SQL and execute them (press F5):

    CREATE PROCEDURE sp_PopulateTable()
    BEGIN
        CREATE VARIABLE i INTEGER;
        SET i = 1;

        WHILE i <= 12 LOOP
            INSERT INTO clr_test VALUES( i, clr_DigitCount( STRING(i) ), clr_Factorial(i) );
            SET i = i + 1;
        END LOOP;
    END

    This stored procedure calls the CLR functions and inserts their returned values into the "clr_test" table.

  11. Now execute the stored procedure in Interactive SQL:

    CALL sp_PopulateTable();

  12. The stored procedure uses the numbers 1 to 12 as input parameters to the CLR functions. The resulting values are then inserted into the "clr_test" table. Execute this statement to retrieve its contents:

    SELECT * FROM clr_test;

    The "digits" column is populated using the CLR function "clr_DigitCount", while the "factorial" column is populated using the CLR function "clr_Factorial". Each function calls methods defined in the assembly Functions.dll.

  13. This concludes the sample. Shut down Interactive SQL and the database server. You may also remove the file Functions.dll from the folder C:\Program Files\SQL Anywhere 12\Bin32.

Conclusion

The external runtime environment allows developers to write stored procedures or functions in languages other than SQL. This offers the benefit of re-using existing libraries and reduces implementation time as developers can leverage their existing knowledge of other programming languages, such as C# and VB.NET.



Back to Top
© Copyright 2010, Sybase Inc.