# 9. SQL Procedure Language Guide

Chapter Contents | Prev | Next
Keyword Search:
www.openlinksw.com
docs.openlinksw.com
Book Home
Contents
Preface
SQL Procedure Language Guide
General Principles
Scope of Declarations
Data Types
Handling Result Sets
Result Sets and Array Parameters
Exception Semantics
Virtuoso/PL Syntax
CREATE ASSEMBLY Syntax - External Libraries
CREATE PROCEDURE Syntax - External hosted procedures
Asynchronous Execution and Multithreading in Virtuoso/PL
Performance Tips
Procedures and Transactions
Distributed Transaction & Two Phase Commit
Triggers
Character Escaping
Virtuoso/PL Scrollable Cursors
Virtuoso PL Modules
Handling Conditions In Virtuoso/PL Procedures
Procedure Language Debugger
Row Level Security

## 9.8. CREATE ASSEMBLY Syntax - External Libraries

External CLR libraries can be hosted inside Virtuoso by creating an assembly from the library itself using the syntax as follows:

    CREATE ASSEMBLY <assembly_name> FROM <assembly_location>
      [WITH PERMISSION_SET = <perm>] [WITH AUTOREGISTER];

- assembly_name - is how Virtuoso will reference the library.
- assembly_location - is where Virtuoso will find the library within the CLR.

Every .NET assembly deployed inside Virtuoso will be verifiable, which means it will contain code the CLR can verify to be safe in the way it writes to memory.

Virtuoso also respects the Common Language Runtime's code access security model. By default, code does not have any permissions to create a graphical user interface, create threads, access the file system, or call unmanaged code. The only permissions implemented are those granted for in-process data access.

Administrators will control the permissions granted to assemblies using a standard .NET machine and user-level security policy. At runtime, any code accessing protected resources produces a stack walk that triggers a permissions check against that code and any code that called it.

To simplify security administration, Virtuoso supports these standard permission sets for .NET assemblies:

- SAFE - This is the default permission set. It allows internal computation and data access. There is no access to resources outside of Virtuoso. Calls to unmanaged code are not allowed. Code must be verifiable.
- UNRESTRICTED - Code can access any resource. Only system administrators (dba group/role) can run unrestricted code. This level allows calls to unmanaged code, and can be unverifiable.

The restricted assemblies (SAFE mode) are not permitted to execute any code that infringes upon any of the following permissions:

- AspNetHostingPermission
- EnvironmentPermission
- FileIOPermission
- IsolatedStoragePermission
- ReflectionPermission
- RegistryPermission
- SecurityPermission
- SocketPermission
- WebPermissionPermission
- DNSPermission
- PrintingPermission
- OleDBPermissionPermission
- SqlClientPermissionPermission
- EventLogPermission
- MessageQueuePermission
- ServiceControllerPermission
- PerformanceCountersPermission
- DirectoryServicePermission

If the assembly generates a security exception the error text will be returned to the client.

Note:

Currently on the Microsoft .Net Framework implementation supports permission sets. Virtuoso does not currently support the EXTERNAL_ACCESS permission set.

WITH AUTOREGISTER marks the assembly as a stored procedure, trigger, user-defined function, etc., based on custom attributes you add to your .NET code.

Assemblies are stored in the database and are therefore backed-up and restored with the data. Once assemblies are registered using the CREATE ASSEMBLY syntax there will be no further dependancy on the library file (dll or exe) itself.

You can remove assemblies using the familiar SQL DROP statement:

    DROP ASSEMBLY <assembly_name>;

See Also:

import_clr()

Working with assemblies

This example is based on the tutorial HO_S_10. we start by obtaining a C# library compile from the following code (included in the tutorial):

    using System;

    [Serializable]
    public class Point_10
    {
      public Double x;
      public Double y;

      public Point_10 ()
        {
          x = 0;
          y = 0;
        }
      public Point_10 (Double new_x, Double new_y)
        {
          x = new_x;
          y = new_y;
        }

      public Double distance (Point_10 p)
        {
          Double ret;

          ret =  Math.Sqrt ((p.x - this.x) * (p.x - this.x) + (p.y - this.y) * (p.y - this.y));

          return ret;
        }
    }

This gives us the Point_10 class with two constructors and one method for finding the distance between two points.

Now we must create the library reference in Virtuoso using the following:

    DROP ASSEMBLY "myPoint";

    CREATE ASSEMBLY "myPoint" as concat (http_root() , '\\tutorial\\hosting\\ho_s_10\\Point_ho_s_10.dll')
      WITH PERMISSION_SET = SAFE WITH AUTOREGISTER;

Now for a quick test, we will find the distance between two points:

    SQL> select new Point_10(0,0).distance(Point_10(3,4));
    callret
    DOUBLE PRECISION
    _______________________________________________________

                   5

Now we will create a table with a column of type Point_10 and then insert some test data:

    drop table CLR..Supplier_ho_s_10;

    create table CLR..Supplier_ho_s_10 (id integer primary key, name varchar (20), location Point_10);

    insert into CLR..Supplier_ho_s_10 (id, name, location) values (1, 'S1', new Point_10 (1, 1));
    insert into CLR..Supplier_ho_s_10 (id, name, location) values (2, 'S2', new Point_10 (3, 3));
    insert into CLR..Supplier_ho_s_10 (id, name, location) values (3, 'S3', new Point_10 (5, 5));

Now we will demonstrate how this assembly's class can be used in SQL by showing some queries on the sample data:

    SQL> select name, s.location.x from CLR..Supplier_ho_s_10 s;
    name                  callret
    VARCHAR               DOUBLE PRECISION
    _______________________________________________________________________________

    S1                                   1
    S2                                   3
    S3                                   5

The distances from (0, 0):

    SQL> select name, s.location.distance(Point_10(0,0)) from CLR..Supplier_ho_s_10 s ;
    name                  callret
    VARCHAR               DOUBLE PRECISION
    _______________________________________________________________________________

    S1                    1.414213562373095
    S2                    4.242640687119285
    S3                    7.071067811865476

Now, the points that are more than 3 units away from it:

    SQL> select name from CLR..Supplier_ho_s_10 s where s.location.distance(Point_10(0,0)) > 3;
    name
    VARCHAR
    _______________________________________________________________________________

    S2
    S3

Using CREATE ASSEMBLY

This example demonstrates the creation of trivial CLR classes and referencing them from Virtuoso.

- lib.cs

      namespace lib
      {
        public class t1
        {
           public static int addit (int a1, int a2) { return a1 + a2; }
        }
      }

- exe.cs

      using lib;

      public class exe
      {
         public static int call_addit (int a1, int a2)
         {
            return t1.addit (a1, a2);
         }

         public static void Main (String [] args)
         {
            Console.WriteLine ("result=" + call_addit (12, 13));
         }
      }

- compilation

      csc /t:library lib.cs
      csc /r:lib.dll exe.cs

- Now Virtuoso can use

      create assembly sql_lib from 'c:\sample\lib.dll'
      create assembly sql_exe from 'c:\sample\exe.exe'

Creating Assemblies with Permission Sets

These examples will use an assembly called test.dll, whose source code is:

    using System;
    using System.IO;

    public class Sample
    {
       public static String GetEnv ()
         {
           return Environment.GetEnvironmentVariable("PATH");
         }
    }

The assembly will be registered using:

    CREATE ASSEMBLY "test" from 'test.dll' WITH PERMISSION_SET = SAFE WITH AUTOREGISTER;

and subsequently called using:

    SQL> select Sample::GetEnv ();

returning the following error for attempting to exceed the SAFE permission set.

    *** Error 42000: [Virtuoso Driver][Virtuoso Server]CLR05: Request for the permission of type System.Security.Permissions.EnvironmentPermission,
          mscorlib, Version=1.0.3300.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 failed.
    in
    __udt_method_call:(BIF),
    <Top Level>
    at line 4 of Top-Level:
    select Sample::GetEnv ()

Now we can try the same sample using PERMISSION_SET = UNRESTRICTED.

    drop ASSEMBLY "test";

    CREATE ASSEMBLY "test" from 'test.dll' WITH PERMISSION_SET = UNRESTRICTED WITH AUTOREGISTER;

    SQL> select Sample::GetEnv ();
    callret
    VARCHAR
    _______________________________________________________________________________

    D:\Virtuoso\bin...;

    1 Rows. -- 32 msec.
    <

Unrestricted assemblies do not have any restrictions on usage.

Previous Virtuoso/PL Syntax | Chapter Contents | Next CREATE PROCEDURE Syntax - External hosted procedures