<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="9. SQL Procedure Language Guide" />
<meta name="dc.subject" content="9. SQL Procedure Language Guide" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="sqlprocedures.html" title="Chapter Contents" />
<link rel="prev" href="EXECSQLPROCFORK.html" title="Execute Stored Procedures In Background" />
<link rel="next" href="createexthostproc.html" title="CREATE PROCEDURE Syntax - External hosted procedures" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>9. SQL Procedure Language Guide</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="createassembly" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>9. SQL Procedure Language Guide</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlprocedures.html">Chapter Contents</a> | <a class="link" href="EXECSQLPROCFORK.html" title="Execute Stored Procedures In Background">Prev</a> | <a class="link" href="createexthostproc.html" title="CREATE PROCEDURE Syntax - External hosted procedures">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="sqlprocedures.html">SQL Procedure Language Guide</a>
</div>
<br />
<div>
<a href="GENERALPRINCIPLES.html">General Principles</a>
</div>
<div>
<a href="SCOPEOFDECLARATION.html">Scope of Declarations</a>
</div>
<div>
<a href="sqlplDATATYPES.html">Data Types</a>
</div>
<div>
<a href="RESULTSETS.html">Handling Result Sets</a>
</div>
<div>
<a href="ARRAYS.html">Result Sets and Array Parameters</a>
</div>
<div>
<a href="EXCEPTIONS.html">Exception Semantics</a>
</div>
<div>
<a href="PLREF.html">Virtuoso/PL Syntax</a>
</div>
<div>
<a href="EXECSQLPROCSELECT.html">Execute Stored Procedures via SELECT statement</a>
</div>
<div>
<a href="EXECSQLPROCFORK.html">Execute Stored Procedures In Background</a>
</div>
<div class="selected">
<a href="createassembly.html">CREATE ASSEMBLY Syntax - External Libraries</a>
</div>
<div>
<a href="createexthostproc.html">CREATE PROCEDURE Syntax - External hosted procedures</a>
</div>
<div>
<a href="ASYNCEXECMULTITHREAD.html">Asynchronous Execution and Multithreading in Virtuoso/PL</a>
</div>
<div>
<a href="PERFTIPS.html">Performance Tips</a>
</div>
<div>
<a href="PROCEDURES_TRANSACTIONS.html">Procedures and Transactions</a>
</div>
<div>
<a href="twopcimplementation.html">Distributed Transaction & Two Phase Commit</a>
</div>
<div>
<a href="TRIGGERS.html">Triggers</a>
</div>
<div>
<a href="charescaping.html">Character Escaping</a>
</div>
<div>
<a href="PLSCROLLCRSRS.html">Virtuoso/PL Scrollable Cursors</a>
</div>
<div>
<a href="plmodules.html">Virtuoso PL Modules</a>
</div>
<div>
<a href="handlingplcondit.html">Handling Conditions In Virtuoso/PL Procedures</a>
</div>
<div>
<a href="pldebugger.html">Procedure Language Debugger</a>
</div>
<div>
<a href="rowlevelsecurity.html">Row Level Security</a>
</div>
<br />
</div>
<div id="text">
<a name="createassembly" />
<h2>9.10. CREATE ASSEMBLY Syntax - External Libraries</h2>
<p>External CLR libraries can be hosted inside Virtuoso by creating an assembly
from the library itself using the syntax as follows:</p>
<div>
<pre class="programlisting">
CREATE ASSEMBLY <assembly_name> FROM <assembly_location>
[WITH PERMISSION_SET = <perm>] [WITH AUTOREGISTER];
</pre>
</div>
<ul>
<li>
<strong>assembly_name</strong> - is how Virtuoso will reference the library.</li>
<li>
<strong>assembly_location</strong> - is where Virtuoso will find the library within the CLR.</li>
</ul>
<p>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. </p>
<p>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.</p>
<p>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.</p>
<p>To simplify security administration, Virtuoso supports these standard
permission sets for .NET assemblies:</p>
<ul>
<li>
<strong>SAFE</strong> - 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.</li>
<li>
<strong>UNRESTRICTED</strong> - 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.</li>
</ul>
<p>The restricted assemblies (SAFE mode) are not permitted to execute
any code that infringes upon any of the following permissions:</p>
<ul>
<li>AspNetHostingPermission</li>
<li>EnvironmentPermission </li>
<li>FileIOPermission</li>
<li>IsolatedStoragePermission</li>
<li>ReflectionPermission</li>
<li>RegistryPermission</li>
<li>SecurityPermission</li>
<li>SocketPermission</li>
<li>WebPermissionPermission</li>
<li>DNSPermission</li>
<li>PrintingPermission</li>
<li>OleDBPermissionPermission</li>
<li>SqlClientPermissionPermission</li>
<li>EventLogPermission</li>
<li>MessageQueuePermission</li>
<li>ServiceControllerPermission</li>
<li>PerformanceCountersPermission </li>
<li>DirectoryServicePermission</li>
</ul>
<p>If the assembly generates a security exception the error text
will be returned to the client.</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>Currently on the Microsoft .Net Framework implementation supports
permission sets. Virtuoso does not currently support the
<span class="computeroutput">EXTERNAL_ACCESS</span> permission set.</p>
</div>
<p>
<strong>WITH AUTOREGISTER</strong> marks the assembly as a stored
procedure, trigger, user-defined function, etc., based on custom attributes you add
to your .NET code.</p>
<p>Assemblies are stored in the database and are therefore backed-up
and restored with the data. Once assemblies are registered using the
<span class="computeroutput">CREATE ASSEMBLY</span> syntax there will be no
further dependency on the library file (dll or exe) itself. </p>
<p>You can remove assemblies using the familiar SQL DROP statement: </p>
<div>
<pre class="programlisting">
DROP ASSEMBLY <assembly_name>;
</pre>
</div>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="fn_import_clr.html">import_clr()</a>
</p>
</div>
<a name="ex_createassembly" />
<div class="example">
<div class="exampletitle">Working with assemblies</div>
<p>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):</p>
<div>
<pre class="programlisting">
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;
}
}
</pre>
</div>
<p>This gives us the Point_10 class with two constructors and one method
for finding the distance between two points.</p>
<p>Now we must create the library reference in Virtuoso using the following:</p>
<div>
<pre class="programlisting">
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;
</pre>
</div>
<p>Now for a quick test, we will find the distance between two points:</p>
<div>
<pre class="programlisting">
SQL> select new Point_10(0,0).distance(Point_10(3,4));
callret
DOUBLE PRECISION
_______________________________________________________
5
</pre>
</div>
<p>Now we will create a table with a column of type Point_10 and then insert
some test data:</p>
<div>
<pre class="programlisting">
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));
</pre>
</div>
<p>Now we will demonstrate how this assembly's class can be used in SQL by
showing some queries on the sample data:</p>
<div>
<pre class="programlisting">
SQL> select name, s.location.x from CLR..Supplier_ho_s_10 s;
name callret
VARCHAR DOUBLE PRECISION
_______________________________________________________________________________
S1 1
S2 3
S3 5
</pre>
</div>
<p>The distances from (0, 0):</p>
<div>
<pre class="programlisting">
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
</pre>
</div>
<p>Now, the points that are more than 3 units away from it:</p>
<div>
<pre class="programlisting">
SQL> select name from CLR..Supplier_ho_s_10 s where s.location.distance(Point_10(0,0)) > 3;
name
VARCHAR
_______________________________________________________________________________
S2
S3
</pre>
</div>
</div>
<a name="ex_createassembly2" />
<div class="example">
<div class="exampletitle">Using CREATE ASSEMBLY</div>
<p>This example demonstrates the creation of trivial CLR
classes and referencing them from Virtuoso.</p>
<ul>
<li>
<strong>lib.cs</strong>
<div>
<pre class="programlisting">
namespace lib
{
public class t1
{
public static int addit (int a1, int a2) { return a1 + a2; }
}
}
</pre>
</div>
</li>
<li>
<strong>exe.cs</strong>
<div>
<pre class="programlisting">
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));
}
}
</pre>
</div>
</li>
<li>
<strong>compilation</strong>
<div>
<pre class="programlisting">
csc /t:library lib.cs
csc /r:lib.dll exe.cs
</pre>
</div>
</li>
<li>
<strong>Now Virtuoso can use</strong>
<div>
<pre class="programlisting">
create assembly sql_lib from 'c:\sample\lib.dll'
create assembly sql_exe from 'c:\sample\exe.exe'
</pre>
</div>
</li>
</ul>
</div>
<a name="ex_ctasspermset" />
<div class="example">
<div class="exampletitle">Creating Assemblies with Permission Sets</div>
<p>These examples will use an assembly called test.dll, whose source code is:</p>
<div>
<pre class="programlisting">
using System;
using System.IO;
public class Sample
{
public static String GetEnv ()
{
return Environment.GetEnvironmentVariable("PATH");
}
}
</pre>
</div>
<p>The assembly will be registered using:</p>
<div>
<pre class="programlisting">
CREATE ASSEMBLY "test" from 'test.dll' WITH PERMISSION_SET = SAFE WITH AUTOREGISTER;
</pre>
</div>
<p>and subsequently called using:</p>
<div>
<pre class="programlisting">
SQL> select Sample::GetEnv ();
</pre>
</div>
<p>returning the following error for attempting to exceed the SAFE permission set.</p>
<div>
<pre class="programlisting">
*** 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 ()
</pre>
</div>
<p>Now we can try the same sample using PERMISSION_SET = UNRESTRICTED.</p>
<div>
<pre class="programlisting">
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.
<</pre>
</div>
<p>Unrestricted assemblies do not have any restrictions on usage.</p>
</div>
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="EXECSQLPROCFORK.html" title="Execute Stored Procedures In Background">Previous</a>
<br />Execute Stored Procedures In Background</td>
<td align="center" width="34%">
<a href="sqlprocedures.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="createexthostproc.html" title="CREATE PROCEDURE Syntax - External hosted procedures">Next</a>
<br />CREATE PROCEDURE Syntax - External hosted procedures</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
75
