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.
- SQL Anywhere
- Visual Studio 2008
- Visual Studio 2008 C# Samples - also available here: http://code.msdn.microsoft.com/csharpsamples
Running the Sample
- Start Visual Studio and open the solution LanguageSamples\Libraries\Libraries.sln.
- 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.
- 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).
- Open a Command Prompt and create a new SQL Anywhere database by running this command:
- Start the database by running this command:
- Launch Interactive SQL and connect to the database by running this command:
dbisql -c "uid=dba;pwd=sql;eng=clrtest"
- 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.
- 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)
EXTERNAL NAME 'Functions.dll::Functions.DigitCount.NumberOfDigits(string) int'
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.
- 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)
EXTERNAL NAME 'Functions.dll::Functions.Factorial.Calc(int) int'
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.
- 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()
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;
This stored procedure calls the CLR functions and inserts their returned values into the "clr_test" table.
- Now execute the stored procedure in Interactive SQL:
- 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.
- 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.
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.