<!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.subject" content="SQL" />
<meta name="dc.subject" content="SQL Reference" />
<meta name="dc.subject" content="Select" />
<meta name="dc.subject" content="Update" />
<meta name="dc.subject" content="delete" />
<meta name="dc.subject" content="Select Statement" />
<meta name="dc.subject" content="SQL Syntax" />
<meta name="dc.subject" content="Syntax" />
<meta name="dc.title" content="8. SQL Reference" />
<meta name="dc.subject" content="8. SQL Reference" />
<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="sqlreference.html" title="Chapter Contents" />
<link rel="prev" href="sqlrefDATATYPES.html" title="Datatypes" />
<link rel="next" href="sqlrefxmldatatype.html" title="XML Column Type" />
<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>8. SQL Reference</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="SQL; SQL Reference; Select; Update; delete; Select Statement; SQL Syntax; Syntax; " />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="udt" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>8. SQL Reference</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sqlreference.html">Chapter Contents</a> | <a class="link" href="sqlrefDATATYPES.html" title="Datatypes">Prev</a> | <a class="link" href="sqlrefxmldatatype.html" title="XML Column Type">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="sqlreference.html">SQL Reference</a>
</div>
<br />
<div>
<a href="sqlrefDATATYPES.html">Datatypes</a>
</div>
<div class="selected">
<a href="udt.html">User Defined Types</a>
<div>
<a href="#udtcreatetypestmt" title="CREATE TYPE Statement">CREATE TYPE Statement</a>
<a href="#udtaltertypestmt" title="ALTER TYPE Statement">ALTER TYPE Statement</a>
<a href="#udtdroptypestmt" title="DROP TYPE Statement">DROP TYPE Statement</a>
<a href="#udtcreatemethodstmt" title="CREATE METHOD Statement">CREATE METHOD Statement</a>
<a href="#udttypeinstances" title="Type Instances">Type Instances</a>
<a href="#udtinstancerefs" title="Instance References">Instance References</a>
<a href="#udtnewop" title="NEW Operator">NEW Operator</a>
<a href="#udtfindingmethods" title="Finding Methods - Method Signatures Generation & Comparison">Finding Methods - Method Signatures Generation & Comparison</a>
<a href="#udtgetsetmembervals" title="Getting & Setting Member Values of Type Instances (member observers & mutators)">Getting & Setting Member Values of Type Instances (member observers & mutators)</a>
<a href="#udtstaticmethods" title="Calling Static Methods">Calling Static Methods</a>
<a href="#udtcallinstmethod" title="Calling Instance Methods">Calling Instance Methods</a>
<a href="#udtserilizingtypeinst" title="Serializing & Deserializing Type Instances">Serializing & Deserializing Type Instances</a>
<a href="#udtutilfuncs" title="User Defined Types Utility Functions">User Defined Types Utility Functions</a>
<a href="#udthostedforiegnobjects" title="Hosted Foreign Objects in Virtuoso">Hosted Foreign Objects in Virtuoso</a>
<a href="#udtrepressentsoapstruct" title="Using User Defined Types to Represent SOAP Structures">Using User Defined Types to Represent SOAP Structures</a>
<a href="#udtcnsmsoap" title="Consuming Third-Party SOAP Services via User Defined Types">Consuming Third-Party SOAP Services via User Defined Types</a>
<a href="#udtsecurity" title="UDT Security">UDT Security</a>
</div>
</div>
<div>
<a href="sqlrefxmldatatype.html">XML Column Type</a>
</div>
<div>
<a href="catidentifiers.html">Identifier Case & Quoting</a>
</div>
<div>
<a href="wideidentifiers.html">Wide Character Identifiers</a>
</div>
<div>
<a href="QUALIFIEDNAMES.html">Qualified Names</a>
</div>
<div>
<a href="litsbraceescs.html">Literals, Brace Escapes</a>
</div>
<div>
<a href="CREATETABLE.html">CREATE TABLE Statement</a>
</div>
<div>
<a href="DROPTABLE.html">DROP TABLE Statement</a>
</div>
<div>
<a href="CREATEINDEX.html">CREATE INDEX Statement</a>
</div>
<div>
<a href="DROPINDEX.html">DROP INDEX Statement</a>
</div>
<div>
<a href="ALTERTABLE.html">ALTER TABLE Statement</a>
</div>
<div>
<a href="CREATEVIEW.html">CREATE VIEW Statement</a>
</div>
<div>
<a href="CREATEXMLSCHEMA.html">CREATE XML SCHEMA Statement</a>
</div>
<div>
<a href="DROPXMLSCHEMA.html">DROP XML SCHEMA Statement</a>
</div>
<div>
<a href="sequenceobjects.html">Sequence Objects</a>
</div>
<div>
<a href="insertSTMT.html">INSERT Statement</a>
</div>
<div>
<a href="updatestmt.html">UPDATE Statement</a>
</div>
<div>
<a href="SELECTSTMT.html">SELECT Statement</a>
</div>
<div>
<a href="COMMIT_ROLLBACK.html">COMMIT WORK, ROLLBACK WORK Statement</a>
</div>
<div>
<a href="CHECKPOINT.html">CHECKPOINT, SHUTDOWN Statement</a>
</div>
<div>
<a href="spasviewsandtables.html">Stored Procedures as Views & Derived Tables</a>
</div>
<div>
<a href="GRANT.html">GRANT, REVOKE Statement</a>
</div>
<div>
<a href="SETstmt.html">SET Statement</a>
</div>
<div>
<a href="anytimequeries.html">Anytime Queries</a>
</div>
<div>
<a href="besteffortunion.html">Best Effort Union</a>
</div>
<div>
<a href="aggregates.html">Standard and User-Defined Aggregate Functions</a>
</div>
<div>
<a href="sqloptimizer.html">Virtuoso SQL Optimization</a>
</div>
<div>
<a href="sqlinverse.html">SQL Inverse Functions</a>
</div>
<div>
<a href="GRAMMAR.html">SQL Grammar</a>
</div>
<div>
<a href="BITMAPINDICES.html">Bitmap Indices</a>
</div>
<div>
<a href="transitivityinsQL.html">Transitivity in SQL</a>
</div>
<div>
<a href="sqlreffastphrasematch.html">Fast Phrase Match Processor</a>
</div>
<br />
</div>
<div id="text">
<a name="udt" />
<h2>8.2. User Defined Types</h2>
<p>A user-defined type is a schema object, identified by a user-defined
type name. The definition of a user-defined type specifies a number of
components, including in particular a list of attribute definitions. The
representation of a user-defined type is expressed as a list of attribute
definitions.</p>
<p>The definition of a user-defined type may include a method specification
list consisting of one or more method specifications. A method specification
is either an original method specification or an overriding method
specification. Each original method specification specifies the method name,
the SQL parameter declaration list, the returns data type, the <language clause>,
the language (if the language is not SQL), and whether it is a STATIC or
CONSTRUCTOR method.</p>
<p>Each overriding method specification specifies the method name, the SQL
parameter declaration list and the RETURNS data type. For each overriding method
specification, there must be an original method specification with the same
method name and SQL parameter declaration list in some proper supertype of
the user-defined type. Every SQL-invoked method in a schema must correspond to
exactly one original method specification or overriding method specification
associated with some user-defined type existing in that schema. A
method <span class="computeroutput">M</span> that corresponds to an original method
specification in the definition of a structured type <span class="computeroutput">T1</span>
is an original method of <span class="computeroutput">T1</span>. A
method <span class="computeroutput">M</span> that corresponds to an overriding
method specification in the definition of <span class="computeroutput">T1</span> is
an overriding method of <span class="computeroutput">T1</span>. A method
<span class="computeroutput">M</span> is a method of type
<span class="computeroutput">T1</span> if one of the following holds:</p>
<ul>
<li>
<span class="computeroutput">M</span> is an original method of <span class="computeroutput">T1</span>
</li>
<li>
<span class="computeroutput">M</span> is an overriding method of <span class="computeroutput">T1</span>
</li>
<li>There is a proper supertype <span class="computeroutput">T2</span> of
<span class="computeroutput">T1</span> such that <span class="computeroutput">M</span>
is an original or overriding method of <span class="computeroutput">T2</span>
and such that there is no method <span class="computeroutput">M3</span> such that
<span class="computeroutput">M3</span> has the same method name and SQL parameter
declaration list as <span class="computeroutput">M</span> and
<span class="computeroutput">M3</span> is an original method or overriding
method of a type <span class="computeroutput">T3</span> such that
<span class="computeroutput">T2</span> is a proper supertype of
<span class="computeroutput">T3</span> and <span class="computeroutput">T3</span> is
a supertype of <span class="computeroutput">T1</span>.</li>
</ul>
<p>A user defined type can be a direct subtype of one (and only one) user
defined type. The user defined type cannot be a subtype of itself.</p>
<p>A type <span class="computeroutput">Ta</span> is a direct subtype of a type
<span class="computeroutput">Tb</span> if <span class="computeroutput">Ta</span> is a
proper subtype of <span class="computeroutput">Tb</span> and there does not exist a
type <span class="computeroutput">Tc</span> such that <span class="computeroutput">Tc</span>
is a proper subtype of <span class="computeroutput">Tb</span> and a proper supertype
of <span class="computeroutput">Ta</span>.</p>
<p>A type <span class="computeroutput">Ta</span> is a subtype of type
<span class="computeroutput">Tb</span> if one of the following pertains:</p>
<ul>
<li>
<span class="computeroutput">Ta</span> is a direct subtype of
<span class="computeroutput">Tb</span>; or</li>
<li>
<span class="computeroutput">Ta</span> is a subtype of some type
<span class="computeroutput">Tc</span> and <span class="computeroutput">Tc</span> is
a direct subtype of <span class="computeroutput">Tb</span>.</li>
</ul>
<p>By the same token, <span class="computeroutput">Tb</span> is a supertype of
<span class="computeroutput">Ta</span> and is a direct supertype of
<span class="computeroutput">Ta</span> in the particular case where
<span class="computeroutput">Ta</span> is a direct subtype of
<span class="computeroutput">Tb</span>. If <span class="computeroutput">Ta</span>
is a subtype of <span class="computeroutput">Tb</span>, then
<span class="computeroutput">Ta</span> is proper subtype of
<span class="computeroutput">Tb</span> and <span class="computeroutput">Tb</span> is
a proper supertype of <span class="computeroutput">Ta</span>. A type cannot be a
proper supertype of itself. A type with no proper supertypes is a maximal
supertype. A type with no proper subtypes is a leaf type.</p>
<p>Let <span class="computeroutput">Ta</span> be a maximal supertype and let
<span class="computeroutput">T</span> be a subtype of <span class="computeroutput">Ta</span>.
The set of all subtypes of <span class="computeroutput">Ta</span> (which includes
<span class="computeroutput">Ta</span> itself) is called a subtype family of
<span class="computeroutput">T</span> or (equivalently) of
<span class="computeroutput">Ta</span>. A subtype family is not permitted to have
more than one maximal supertype. Every value in a type
<span class="computeroutput">T</span> is a value in every supertype of
<span class="computeroutput">T</span>. A value <span class="computeroutput">V</span>
in type <span class="computeroutput">T</span> has exactly one most specific type
<span class="computeroutput">MST</span> such that <span class="computeroutput">MST</span>
is a subtype of <span class="computeroutput">T</span> and
<span class="computeroutput">V</span> is not a value in any proper subtype of
<span class="computeroutput">MST</span>. The most specific type of value need not
be a leaf type. For example, a type structure might consist of a type
<span class="computeroutput">PERSON</span> that has
<span class="computeroutput">STUDENT</span> and <span class="computeroutput">EMPLOYEE</span>
as its two subtypes, while <span class="computeroutput">STUDENT</span> has two
direct subtypes <span class="computeroutput">UG_STUDENT</span> and
<span class="computeroutput">PG_STUDENT</span>. The invocation
<span class="computeroutput">STUDENT( )</span> of the constructor function for
<span class="computeroutput">STUDENT</span> returns a value whose most specific
type is <span class="computeroutput">STUDENT</span>, which is not a leaf type.
If <span class="computeroutput">Ta</span> is a subtype of
<span class="computeroutput">Tb</span>, then a value in
<span class="computeroutput">Ta</span> can be used wherever a value in
<span class="computeroutput">Tb</span> is expected. In particular, a value in
<span class="computeroutput">Ta</span> can be stored in a column of type
<span class="computeroutput">Tb</span>, can be substituted as an argument for an
input SQL parameter of data type <span class="computeroutput">Tb</span>, and can
be the value of an invocation of an SQL-invoked function whose result data type
is <span class="computeroutput">Tb</span>. A type <span class="computeroutput">T</span>
is said to be the minimal common supertype of a set of types
<span class="computeroutput">S</span> if <span class="computeroutput">T</span> is a
supertype of every type in <span class="computeroutput">S</span> and a subtype of
every type that is a supertype of every type in <span class="computeroutput">S</span>.</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>Because a subtype family has exactly one maximal supertype, if two types
have a common subtype, they must also have a minimal common supertype. Thus,
for every set of types drawn from the same subtype family, there is some member
of that family that is the minimal common supertype of all of the types in that
set. </p>
</div>
<p>A user-defined type is declared by a user-defined type
<a href="udt.html#udtcreatetypestmt">CREATE TYPE statement</a>.</p>
<a name="udtcreatetypestmt" />
<h3>8.2.1. CREATE TYPE Statement</h3>
<div>
<pre class="programlisting">
CREATE TYPE type_name
[ UNDER type_name ]
[ LANGUAGE language_name [ EXTERNAL NAME literal ] ]
[ AS (type_member, ...) ]
[ type_option [type option ] .... ]
[ method_specification, ... ]
type_name :
[ [ identifier .] [ identifier ] . ] identifier
type_member :
identifier data_type [ DEFAULT literal ] [ EXTERNAL NAME string ] [ EXTERNAL TYPE string ]
[ __SOAP_TYPE literal ] [ __SOAP_NAME literal ]
type_option : SELF AS REF | TEMPORARY | SOAP_TYPE literal
method_specification : original_method_specification | overriding_method_specification
original_method_specification :
[ STATIC | INSTANCE ] METHOD identifier ( [ decl_parameter, ... ] )
RETURNS data_type [ method_characteristics ]
|
CONSTRUCTOR METHOD identifier ( [ decl_parameter, ... ] ) [ method_characteristics ]
overriding_method_specification :
OVERRIDING [ INSTANCE ] METHOD identifier ( [ decl_parameter, ... ] )
RETURNS data_type
method_characteristics :
[ EXTERNAL TYPE literal ] [ EXTERNAL NAME string | EXTERNAL VARIABLE NAME string ]
language_name : SQL | CLR | JAVA
</pre>
</div>
<p>The CREATE TYPE statements declares a user defined type. Generally
speaking the user defined types can be in two states: forward-referenced,
declared and instantiable.</p>
<p>A type is in forward reference state if it's name is quoted in some other
CREATE TYPE statement (as a supertype, member type or a method parameter type
or return type). When a type is in forward reference state it's instances can be
copied, passed as parameter values and returned by functions, but it cannot be
instantiated, no type members can be accessed and no type methods can be called.
Forward references are temporary objects and they disappear at server shutdown.</p>
<p>A type moves to the declared state when a CREATE TYPE is executed for it.
In that state type methods can be called, type members can be accessed, but the type
cannot be instantiated.</p>
<p>A type goes into instantiable state from declared state when it has no
supertype or it's supertype is also in instantiable state. The server tries to
move the defined types to instantiable state on every CREATE TYPE statement.</p>
<p>Normally the type definitions are stored into the DB.DBA.SYS_USER_TYPES
system table.</p>
<p>This has the following layout:</p>
<div>
<pre class="programlisting">
CREATE TABLE SYS_USER_TYPES
(
UT_NAME VARCHAR,
UT_PARSE_TREE LONG VARCHAR,
UT_ID integer identity,
UT_MIGRATE_TO integer,
primary key (UT_NAME));
</pre>
</div>
<ul>
<li>
<strong>UT_NAME</strong> - the fully qualified user defined type name.</li>
<li>
<strong>UT_PARSE_TREE</strong> - the user defined type definition (in machine readable form).</li>
<li>
<strong>UT_ID</strong> - the ID of the type (used in persisting type instances to/from network/storage).</li>
<li>
<strong>UT_MIGRATE_TO</strong> - reserved for future use.</li>
</ul>
<p>If a TEMPORARY type_option is specified, the CREATE TYPE does not write
the type definition into the table - it declares the type only in server's
memory. TEMPORARY types are not persistable. They disappear when the server is
restarted. A TEMPORARY type cannot be a supertype or a subtype of a
non-TEMPORARY type.</p>
<p>The <span class="computeroutput">SELF AS REF</span> option directs the server
to return a reference to the type's instance when instantiating the type, as
opposed to returning the instance itself. The references are explained in
more detail in the <span class="computeroutput">NEW</span> operator.</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>The CREATE TYPE is an autocommitting statement.</p>
</div>
<a name="ex_createtype" />
<div class="example">
<div class="exampletitle">Creating User Defined Types</div>
<p>This example creates a SQL implemented user defined type UDT_TEST with
no supertype. It has two members : A and B, two constructor methods, a static
method _ADD, an ADDIT method taking either zero or two arguments and an instance
method SUB_IT.</p>
<div>
<pre class="programlisting">
create type UDT_TEST
as (A integer default 1, B integer default 2)
CONSTRUCTOR METHOD UDT_TEST(_a integer, _b integer),
CONSTRUCTOR METHOD UDT_TEST(),
STATIC METHOD _ADD(_xx integer, _yy integer) returns integer specific DB.DBA.static_add,
METHOD ADDIT() returns integer,
METHOD ADDIT(c integer) returns integer,
METHOD SUB_IT () returns integer;
</pre>
</div>
<p>This creates a subtype of UDT_TEST named UDT_TEST_SUB. UDT_TEST_SUB
extends the static method _ADD of UDT_TEST so it can also take 4 arguments,
overrides the method ADDIT from UDT_TEST and defines a new instance method
MULTIPLY_IT.</p>
<div>
<pre class="programlisting">
create type UDT_TEST_SUB under UDT_TEST
as (C integer default 12, _D integer default 32)
STATIC METHOD _ADD(_xx integer, _yy integer, _zz integer, _qq integer) returns integer,
OVERRIDING METHOD ADDIT() returns integer,
METHOD MULTIPLY_IT () returns integer;
</pre>
</div>
<p>This is a SQL wrapper for a public Java type testsuite_base
(see testsuite_base.java).</p>
<div>
<pre class="programlisting">
create type testsuite_base language java external name 'testsuite_base'
as (
protected_I integer external name 'protected_I' external type 'I',
private_I integer external name 'private_I' external type 'I',
sZ smallint external name 'Z' external type 'Z',
sfalseZ smallint external name 'falseZ' external type 'Z',
sB smallint external name 'B' external type 'B',
sC smallint external name 'C' external type 'C',
sS smallint external name 'S' external type 'S',
sI int external name 'I' external type 'I',
sJ int external name 'J' external type 'J',
sF real external name 'F' external type 'F',
sD double precision external name 'D' external type 'D',
sL any external name 'L' external type 'Ljava/lang/Short;',
sAI any external name 'AI' external type '[I',
sAL any external name 'AL' external type '[Ljava/lang/Short;',
sstr nvarchar external name 'str' external type 'Ljava/lang/String;',
sdat datetime external name 'dat' external type 'Ljava/util/Date;',
tF real external name 'F',
"F" real,
non_existant_var integer external name 'non_existant_var' external type 'I'
)
static method get_static_ro_I ()
returns integer external type 'I' external variable name 'static_ro_I',
static method get_static_I ()
returns integer external type 'I' external variable name 'static_I',
static method get_protected_static_I ()
returns integer external type 'I' external variable name 'protected_static_I',
static method get_private_static_I ()
returns integer external type 'I' external variable name 'private_static_I',
static method test_bool (x integer external type 'I')
returns smallint external type 'Z' external name 'test_bool',
constructor method testsuite_base (),
constructor method testsuite_base (i integer external type 'I'),
static method echoDouble (a double precision external type 'D')
returns any external type 'Ljava/lang/Double;' external name 'echoDouble',
static method getObjectType (a any external type 'Ljava/lang/Object;')
returns varchar external type 'Ljava/lang/String;' external name 'getObjectType',
static method echoThis (a testsuite_base external type 'Ltestsuite_base;')
returns integer external type 'I' external name 'echoThis',
static method static_echoInt (a integer external type 'I')
returns integer external type 'I' external name 'static_echoInt',
static method change_it (a testsuite_base)
returns integer external type 'I' external name 'change_it',
method "overload_method" (i integer external type 'I')
returns integer external type 'I',
method echoInt (a integer external type 'I')
returns integer external type 'I' external name 'echoInt',
method echoInt (a double precision external type 'D')
returns integer external type 'I' external name 'echoInt',
method protected_echo_int (a integer external type 'I')
returns integer external type 'I' external name 'protected_echo_int',
method private_echo_int (a integer external type 'I')
returns integer external type 'I' external name 'private_echo_int',
method "echoDbl" (a double precision)
returns double precision,
method non_existant_method (a integer external type 'I')
returns integer external type 'I' external name 'non_existant_method',
static method non_existant_static_var (a integer external type 'I')
returns integer external type 'I' external variable name 'non_existant_static_var';
</pre>
</div>
</div>
<br />
<a name="udtaltertypestmt" />
<h3>8.2.2. ALTER TYPE Statement</h3>
<div>
<pre class="programlisting">
ALTER TYPE type_name
( ADD ATTRIBUTE type_member
| DROP ATTRIBUTE identifier [ CASCADE | RESTRICT ]
| ADD method_specification
| DROP original_method_specification [ CASCADE | RESTRICT ]
type_name :
[ [ identifier .] [ identifier ] . ] identifier
type_member :
identifier data_type [ DEFAULT literal ] [ EXTERNAL NAME string ] [ EXTERNAL TYPE string ]
[ __SOAP_TYPE literal ] [ __SOAP_NAME literal ]
type_option : SELF AS REF | TEMPORARY | SOAP_TYPE literal
method_specification : original_method_specification | overriding_method_specification
original_method_specification :
[ STATIC | INSTANCE ] METHOD identifier ( [ decl_parameter, ... ] )
RETURNS data_type [ method_characteristics ]
|
CONSTRUCTOR METHOD identifier ( [ decl_parameter, ... ] ) [ method_characteristics ]
overriding_method_specification :
OVERRIDING [ INSTANCE ] METHOD identifier ( [ decl_parameter, ... ] )
RETURNS data_type
method_characteristics :
[ EXTERNAL TYPE literal ] [ EXTERNAL NAME string | EXTERNAL VARIABLE NAME string ]
language_name : SQL | CLR | JAVA
</pre>
</div>
<p>The ALTER TYPE statements modifies a user defined type. It can be used for
adding or dropping methods and members of user defined types.</p>
<div class="note">
<div class="notetitle">Note:</div>
<p>The ALTER TYPE is an autocommitting statement.</p>
</div>
<a name="ex_altertype" />
<div class="example">
<div class="exampletitle">Altering User Defined Types</div>
<p>This example uses a SQL implemented user defined type UDT_ALTER_TYPE with
no supertype defined as follows: </p>
<div>
<pre class="programlisting">
create type UDT_ALTER_TYPE as (A integer default 1)
method m1 (I integer) returns integer;
create method M1 (in I integer) returns integer for UDT_ALTER_TYPE
{
return I;
};
</pre>
</div>
<p>Then it adds an attribute B to it :</p>
<div>
<pre class="programlisting">
alter type UDT_ALTER_TYPE Add attribute B integer default 2;
</pre>
</div>
<p>Then drops the original A attribute : </p>
<div>
<pre class="programlisting">
alter type udt_ALTER_TYPE drop attribute A;
</pre>
</div>
<p>Now let's add a new method M2 to the type : </p>
<div>
<pre class="programlisting">
alter type UDT_ALTER_TYPE Add method M2 (ID integer) returns integer;
create method M2 (in ID integer) returns integer for UDT_ALTER_TYPE
{
return ID + 100;
};
</pre>
</div>
<p>And drop the M1 method : </p>
<div>
<pre class="programlisting">
alter type UDT_ALTER_TYPE drop method M1 (ID integer) returns integer;
</pre>
</div>
</div>
<br />
<a name="udtdroptypestmt" />
<h3>8.2.3. DROP TYPE Statement</h3>
<div>
<pre class="programlisting">
DROP TYPE type_name
</pre>
</div>
<p>This statement reverses the effect of CREATE TYPE statement. If the type
has methods defined they are deleted as well. Note that forward references
cannot be dropped by a DROP TYPE.</p>
<p>The DROP TYPE statement can be used only for dropping types that are
not referenced in another type's UNDER statement.</p>
<a name="ex_udtdroptype" />
<div class="example">
<div class="exampletitle">Dropping a user-defined type</div>
<p>Dropping the subtype from the previous section.</p>
<div>
<pre class="programlisting">
drop type UDT_TEST_SUB;
</pre>
</div>
</div>
<br />
<a name="udtcreatemethodstmt" />
<h3>8.2.4. CREATE METHOD Statement</h3>
<div>
<pre class="programlisting">
CREATE [ INSTANCE | STATIC | CONSTRUCTOR ] METHOD identifier
( parameter, .... ) [ RETURNS data_type ] FOR type_name
{
.....
}
parameter : { IN | OUT | INOUT } identifier data_type
</pre>
</div>
<p>For the SQL user defined types every method should be defined in order
to be callable. It is an error to call CREATE METHOD for a non-SQL type's
methods (as the methods are implemented in some other non-SQL language).</p>
<p>There is no DROP METHOD as each CREATE METHOD will override the current
method definition (if any). The method name, parameter types and the return
type should match exactly the method declaration (method_specification in
CREATE TYPE).</p>
<p>The method name for the constructors is the name of the type (without
the qualifier and the owner).</p>
<p>For the constructor and instance methods there is a variable named
<span class="computeroutput">SELF</span> (in scope for the compound statement
defining the method) representing the current type instance.</p>
<p>The method members and other methods are not in scope in the method's
compound statement. They should be accessed through the
<span class="computeroutput">SELF</span> variable.</p>
<a name="ex_methodcreation" />
<div class="example">
<div class="exampletitle">Simple method demonstration</div>
<p>This example defines the two parameter constructor for the UDT_TEST type.
It sets the values for the type members A and B to the values of the constructor
parameters. This example uses the SQL200n syntax for method mutators (see below).</p>
<div>
<pre class="programlisting">
create constructor method UDT_TEST (in _a integer, in _b integer)
for UDT_TEST
{
SELF := A(SELF, _a);
SELF := B(SELF, _b);
return SELF;
};
</pre>
</div>
<p>This defines the static method _ADD for the type UDT_TEST.
Note that it does not use SELF - it would be a syntax error to do so.</p>
<div>
<pre class="programlisting">
create static method _ADD (in a1 integer, in a2 integer)
for UDT_TEST
{
return a1 + a2;
};
</pre>
</div>
</div>
<br />
<a name="udttypeinstances" />
<h3>8.2.5. Type Instances</h3>
<p>Every user defined type can have zero or more instances. Every instance knows
it's type. The instances are SQL values. They are copyable. Instances for SQL
types contain placeholders for the type's members. Instances for non-SQL
types contain a reference to the real instance in type's hosted environment.
(Java VM for JAVA and the CLR virtual machine for CLR). So copying an
instance is different for SQL and non-SQL types. When a SQL type's instance
is copied a new set of members placeholders is created and all members values are
copied. This way the copy does not hold any link to it's original and they can
be considered as two different instances. This means that changing members'
value in the copy will not affect the original.</p>
<p>The non-SQL types instances hold only a reference to the real instance.
So copying such an instance is equivalent of making another reference to the
foreign object. Thus changing the member's value in the copy WILL affect the
original. Usually the foreign virtual machines have a means of explicitly
copying an instance, but they are not used by the Virtuoso server when copying
the SQL values.</p>
<p>As with the other SQL values, an instance gets destroyed when it is no
longer referenced.</p>
<br />
<a name="udtinstancerefs" />
<h3>8.2.6. Instance References</h3>
<p>Because the SQL types instances cannot be referenced by more than one
variable/type member Virtuoso PL implements instance references. The
references are created for the types marked with
<span class="computeroutput">SELF AS REF type_option</span>
(see <a href="udt.html#udtcreatetypestmt">CREATE TYPE</a>). For such types the
constructor(s) make a SQL value of reference type in addition to making the
instance itself. The engine places the instances in a connection specific
cache of instances and returns a SQL value of type reference which points to
that instance. Copying the reference value will not cause copying the instance
into the cache, so a <span class="computeroutput">SELF AS REF</span> type will behave
as a hosted class with respect to changing values in the copy. A new instance in
the cache will be created only when the constructor for the type is called again.
The server will accept a SQL reference value in every place where an instance
value is expected. When a reference is serialized, as in when storing into a
column of a table, the server will serialize the instance data, not the
reference itself.</p>
<p>The connection's instance cache is cleared after the completion of the
current server operation, i.e. completion of the top level state, statement
invoked by a client or the completion of processing an HTTP request. The cache
will thus survive multiple transactions if these are transacted within a single
client initiated operation.</p>
<br />
<a name="udtnewop" />
<h3>8.2.7. NEW Operator</h3>
<div>
<pre class="programlisting">
[ NEW ] type_name ( [ parameter_value, .... ] )
</pre>
</div>
<p>This returns an instance (or reference to an instance) of the user defined
type type_name. It will try to match a constructor by calculating the parameter
types at compile time and matching the so produced signature to the closest
constructor signature (see below: <a href="udt.html#udtfindingmethods">finding
methods</a>). The SQL types have an implicit constructor with no parameters
that assigns the DEFAULT values to the type members (if any, otherwise NULL).
When a SQL constructor is called it will have the <span class="computeroutput">SELF</span>
set-up to the result of calling the implicit constructor. The NEW operator
is a scalar expression and can be used wherever the SQL syntax allows scalar
expressions.</p>
<br />
<a name="udtfindingmethods" />
<h3>8.2.8. Finding Methods - Method Signatures Generation & Comparison</h3>
<p>A method of a user defined type is identified uniquely by the combination
of the following:</p>
<ul>
<li>the method name</li>
<li>number of the method's parameters</li>
<li>the method's parameter types</li>
<li>the method's return type</li>
</ul>
<p>This combination of a method attributes is called the method's signature.</p>
<p>The Virtuoso Server must know the method's types of the parameter values
and the return type at compile time to calculate the method signature and find
exactly the method to call in the types method table. This is different from
the current practice in calling stored procedures, because the compile types
are not used to find the procedure.</p>
<p>The majority of the system functions are known at compile time to return
values of a certain SQL type (e.g.: LEFT is known to return VARCHAR,
ATOI returns INTEGER etc). But there are some (e.g.: AREF) that may return
values of more than one type. The Virtuoso server does type arithmetic
for scalar expressions at compile time already (to be able to supply columns
types of a result set to ODBC clients for example), but so far the calculated
type has only informative value and was not used anywhere during the compilation.
All of the type checks are done at runtime. The method/constructor invocation
breaks that practice by using the calculated compile time types for the scalar
expressions.</p>
<a name="ex_udtpoorsignature" />
<div class="example">
<div class="exampletitle">Method Signatures</div>
<p>Consider a method <span class="computeroutput">m1</span> of type
<span class="computeroutput">t1</span> taking an INTEGER parameter and returning
an integer value:</p>
<div>
<pre class="programlisting">
CREATE METHOD m1 (in x integer)
for t1 returns integer
{ return x + 10; };
</pre>
</div>
<p>Now consider calling the method as follows (in a Virtuoso/PL procedure):</p>
<div>
<pre class="programlisting">
...
declare p float;
declare ret integer;
declare t1i t1;
t1i := new t1();
p := 1;
ret := t1i.m1(p);
....
</pre>
</div>
<p>This will yield a compilation error explaining that there is no method
<span class="computeroutput">m1</span> of user defined type <span class="computeroutput">t1</span>.
It will do that because p has a compile time type of FLOAT.</p>
<p>The following will also fail to compile:</p>
<div>
<pre class="programlisting">
...
declare p integer;
declare ret float;
declare t1i t1;
t1i := new t1();
p := 1;
ret := t1i.m1(p);
....
</pre>
</div>
<p>This time the <span class="computeroutput">ret</span> has a declared type of
FLOAT and there is no method in t1 taking 1 INTEGER parameter and returning FLOAT.</p>
<p>The most consistent way of specifying the compile time type of a scalar
expression is to enclose it in a CAST statement, as follows:</p>
<div>
<pre class="programlisting">
...
declare p float;
declare ret integer;
declare t1i t1;
t1i := new t1();
p := 1;
ret := t1i.m1(CAST (p as integer));
....
</pre>
</div>
<p>This will compile and execute correctly.</p>
</div>
<br />
<a name="udtgetsetmembervals" />
<h3>8.2.9. Getting & Setting Member Values of Type Instances (member observers & mutators)</h3>
<p>Let <span class="computeroutput">T</span> be a user defined type that has a
member <span class="computeroutput">A</span> of type <span class="computeroutput">AT</span>.
Let <span class="computeroutput">IT</span> be an instance of type
<span class="computeroutput">T</span>.</p>
<a name="udtmemobserv" />
<h4>8.2.9.1. Member Observers (Getting Values)</h4>
<p>There are two alternative syntaxes (both scalar expressions):</p>
<div>
<pre class="programlisting">
SQL200n : A(<scalar_exp>)
Virtuoso extension : <scalar_exp>.A
</pre>
</div>
<p>Both of the above will return a copy of the member's value of the
instance in <scalar_exp> when the scalar expression <scalar_exp>
has a compile time type of <span class="computeroutput">T</span>. If the compile
time type is not determined to be a user defined type
<span class="computeroutput">T</span> the first one will be compiled as a call
to the SQL function <span class="computeroutput">A</span> and the second will
either generate a syntax error or the server will consider it as reference
to a scope variable (depending on the type of <scalar_exp>).</p>
<p>These are also scalar expressions and have a compile time
type <span class="computeroutput">AT</span>.</p>
<p>To specify an explicit type of the scalar expression there is a
third syntax:</p>
<div>
<pre class="programlisting">
(<scalar_exp> as T).A
</pre>
</div>
<p>This will force the server to compile a reference to the member
<span class="computeroutput">A</span> in user defined type <span class="computeroutput">T</span>.
Whether the <scalar_exp> is indeed of type <span class="computeroutput">T</span>
will be checked at runtime.</p>
<br />
<a name="udtmemmutators" />
<h4>8.2.9.2. Member Mutators (Setting Values)</h4>
<p>There are two alternative syntaxes (both scalar expressions):</p>
<div>
<pre class="programlisting">
SQL200n : A(<scalar_exp>, <new_value_scalar_exp>)
Virtuoso extension : <scalar_exp>.A := <new_value_scalar_exp>
</pre>
</div>
<p>Both of the above will set the member's value of the instance in
<scalar_exp> to a copy of <new_value_scalar_exp> when the
scalar expression <scalar_exp> has a compile time type of
<span class="computeroutput">T</span>. If the compile time type is not
determined to be a user defined type <span class="computeroutput">T</span> the
first one will be compiled as a call to the SQL function
<span class="computeroutput">A</span> and the second will either generate
a syntax error or the server will consider it as reference to a scope variable
(depending on the type of <scalar_exp>).</p>
<p>These are also scalar expressions and have a compile time type
<span class="computeroutput">T</span> and return a copy of the <scalar_exp>.
To specify an explicit type of the scalar exp there is a third syntax:</p>
<div>
<pre class="programlisting">
(<scalar_exp> as T).A := <new_value_scalar_exp>
</pre>
</div>
<p>This will force the server to compile a reference to the member
<span class="computeroutput">A</span> in user defined type <span class="computeroutput">T</span>.
Whether the <scalar_exp> is indeed of type
<span class="computeroutput">T</span> will be checked at runtime.</p>
<a name="ex_usingconstructor" />
<div class="example">
<div class="exampletitle">Member Construction</div>
<p>This will make a new object of type <span class="computeroutput">UDT_FR_BASE</span>
by calling it's two int parameters constructor and will return the member
<span class="computeroutput">B</span> value of the instance stored in member
<span class="computeroutput">UDT_M</span> of <span class="computeroutput">UDT_FR_BASE</span>.</p>
<div>
<pre class="programlisting">
select new UDT_FR_BASE (1, 2).UDT_M.B;
</pre>
</div>
</div>
<br />
<br />
<a name="udtstaticmethods" />
<h3>8.2.10. Calling Static Methods</h3>
<p>Let <span class="computeroutput">T</span> be a user defined type that has
a static method <span class="computeroutput">SM</span>.</p>
<div>
<pre class="programlisting">
T::SM ( [ parameter, .... ] )
</pre>
</div>
<p>This will call the static method of <span class="computeroutput">SM</span>
of <span class="computeroutput">T</span> and will return whatever the static
method returns.</p>
<a name="ex_callstaticmethod" />
<div class="example">
<div class="exampletitle">Calling A Static Method</div>
<div>
<pre class="programlisting">
select UDT_TEST::_ADD (1, 2);
</pre>
</div>
</div>
<br />
<a name="udtcallinstmethod" />
<h3>8.2.11. Calling Instance Methods</h3>
<p>Let <span class="computeroutput">T</span> be a user defined type that has
an instance method <span class="computeroutput">IM</span>. Let
<span class="computeroutput">IT</span> be a scalar expression having a compile
time type of <span class="computeroutput">T</span>.</p>
<div>
<pre class="programlisting">
IT.IM ( [ parameter, .... ] )
</pre>
</div>
<p>This will call the instance method <span class="computeroutput">IM</span>
of <span class="computeroutput">T</span> and will return whatever the
<span class="computeroutput">IM</span> returns.</p>
<p>Similarly to member observers/mutators the compile time type of
<span class="computeroutput">IT</span> can be specified explicitly:</p>
<div>
<pre class="programlisting">
(IT as T).IM ( [ parameter, .... ] )
</pre>
</div>
<p>This syntax however has an additional property in that it will call the
method of the type regardless of whether it is overloaded in a subtype or not.
Let <span class="computeroutput">ST</span> be a subtype of <span class="computeroutput">T</span>
and <span class="computeroutput">ST</span> that has the method
<span class="computeroutput">IM</span> overloaded. Let <span class="computeroutput">IST</span>
be a scalar expression that represents an instance of <span class="computeroutput">ST</span>.</p>
<p>Then:</p>
<div>
<pre class="programlisting">
(IST as T).IM ( [ parameter, ... ] )
</pre>
</div>
<p>will call the method <span class="computeroutput">IM</span> as defined in
<span class="computeroutput">T</span>, whereas</p>
<div>
<pre class="programlisting">
IST.IM ( [ parameter, ... ] )
</pre>
</div>
<p>will call the method <span class="computeroutput">IM</span> as defined in
<span class="computeroutput">ST</span>.</p>
<a name="ex_callinginstmethods" />
<div class="example">
<div class="exampletitle">Calling Overloaded Instance Methods</div>
<div>
<pre class="programlisting">
CREATE TYPE UDT_BASE
method A () returns integer;
CREATE TYPE UDT_SUB under UDT_BASE
OVERRIDING method A () returns integer;
create method A () returns integer for UDT_BASE
{
return 1;
}
create method A () returns integer for UDT_SUB
{
return 2;
}
select new UDT_SUB ().A() as IMPLICIT,
(new UDT_SUB() as UDT_BASE).A() as EXPLICIT;
</pre>
</div>
<p>This will return:</p>
<div>
<pre class="programlisting">
IMPLICIT EXPLICIT
----------------------------
2 1
</pre>
</div>
<p>This is done so the overloaded methods can call the base type methods.</p>
</div>
<br />
<a name="udtserilizingtypeinst" />
<h3>8.2.12. Serializing & Deserializing Type Instances</h3>
<p>Virtuoso allows serializing and deserializing of non TEMPORARY type
instances. This means that the instances can be saved as a column value and can
be used with the serialize/deserialize SQL functions.</p>
<a name="ex_serializetypeinst" />
<div class="example">
<div class="exampletitle">Storing User Defined Types</div>
<p>This creates a type SER_UDT, a table UDT_TABLE with a DATA column
capable of storing SER_UDT instances, stores an instance of SER_UDT into the
table and demonstrates some selects using the stored instance.</p>
<div>
<pre class="programlisting">
create type SER_UDT as (A integer default 12)
method NEGATE () returns integer;
create method NEGATE () returns integer for SER_UDT
{
return SELF.A * -1;
}
create table UDT_TABLE (ID integer primary key, DATA SER_UDT);
insert into UDT_TABLE (ID, DATA) values (1, new SER_UDT ());
select C.DATA.A from UDT_TABLE C where C.ID = 1;
select C.ID from UDT_TABLE C where C.DATA.A > 10;
select C.ID from UDT_TABLE C where C.DATA.NEGATE() < -10;
</pre>
</div>
<p>Note that the table alias is mandatory here.</p>
<div>
<pre class="programlisting">
select ID from UDT_TABLE where DATA.A > 10;
</pre>
</div>
<p>and</p>
<div>
<pre class="programlisting">
select ID from UDT_TABLE where DATA.NEGATE() < -10;
</pre>
</div>
<p>will both yield a syntax error.</p>
</div>
<p>The columns of a certain type allow storing subtype instances as well.
The subtype instances will not be converted to the their supertype when stored.
</p>
<p>If we define the type <span class="computeroutput">SER_UDT_SUB</span> as:</p>
<div>
<pre class="programlisting">
create type SER_UDT_SUB under SER_UDT
as (B integer default 13);
</pre>
</div>
<p>then we can do:</p>
<div>
<pre class="programlisting">
insert into UDT_TABLE (ID, DATA) values (2, new SER_UDT_SUB ());
select (C.DATA as SER_UDT_SUB).B from UDT_TABLE C where C.ID = 2;
</pre>
</div>
<p>Type instances can be stored into an ANY column:</p>
<div>
<pre class="programlisting">
create table ANY_TABLE (ID integer primary key, DATA any);
insert into ANY_TABLE (ID, DATA) values (1, new SER_UDT());
select (C.DATA as SER_UDT).A from ANY_TABLE C where C.ID = 1;
</pre>
</div>
<a name="ex_serializebifs" />
<div class="example">
<div class="exampletitle">SERIALIZE/DESERIALIZE VSEs example</div>
<div>
<pre class="programlisting">
select (DESERIALIZE (SERIALIZE (new SER_UDT ())) as SER_UDT).A;
</pre>
</div>
<p>The SERIALIZE VSE can be used to store larger type instances into
LONG VARCHAR columns. For example:</p>
<div>
<pre class="programlisting">
create table LOB_TABLE (ID integer primary key, LOB_DATA LONG VARCHAR);
insert into LOB_TABLE (ID, LOB_DATA) values (1, SERIALIZE (new SER_UDT()));
select (DESERIALIZE (BLOB_TO_STRING (LOB_DATA)) as SER_UDT).A
from LOB_TABLE where ID = 1;
</pre>
</div>
</div>
<p>The serialization/deserialization for the non-SQL type instances is done
by the means of the hosted language (Java Object serialization API and CLR Binary
serialization API). So to be serialized/deserialized correctly the Java classes
must implement the java.io.Serializable interface and the CLR classes should
have the [Serializable] attribute set. For details refer to the respective
API documentation.</p>
<br />
<a name="udtutilfuncs" />
<h3>8.2.13. User Defined Types Utility Functions</h3>
<p>Virtuoso implements the following user defined types utility functions:</p>
<ul>
<li>
<a href="fn_udt_instance_of.html">udt_instance_of()</a>
</li>
<li>
<a href="fn_udt_defines_field.html">udt_defines_field()</a>
</li>
<li>
<a href="fn_udt_implements_method.html">udt_implements_method()</a>
</li>
<li>
<a href="fn_udt_get.html">udt_get()</a>
</li>
<li>
<a href="fn_udt_set.html">udt_set()</a>
</li>
</ul>
<br />
<a name="udthostedforiegnobjects" />
<h3>8.2.14. Hosted Foreign Objects in Virtuoso</h3>
<a name="udtjvmhost" />
<h4>8.2.14.1. Java VM Hosted Objects</h4>
<p>A special build of Virtuoso hosts a Java VM and allows manipulation of
Java classes through the SQL user defined types.</p>
<p>In order to access the Java class instances they have to be defined as Virtuoso
types using CREATE TYPE and specifying LANGUAGE JAVA. Java classes have to be
in the CLASSPATH of the hosted Java VM.</p>
<a name="ex_jvmhostobj" />
<div class="example">
<div class="exampletitle">Hosted Java Objects</div>
<p>Java (Point.java):</p>
<div>
<pre class="programlisting">
public class Point implements java.io.Serializable
{
public double x = 0;
public double y = 0;
public Point (double new_x, double new_y)
{
x = new_x;
y = new_y;
}
public double distance (Point p)
{
return Math.sqrt ((p.x - this.x) * (p.x - this.x) + (p.y - this.y) * (p.y - this.y));
}
}
</pre>
</div>
<p>This Java class should be compiled and the corresponding Point.class
should be placed in the hosted VM's classpath. Then a Virtuoso user defined
type should be created as follows:</p>
<div>
<pre class="programlisting">
create type Point language java external name 'Point'
as (
x double precision external name 'x',
y double precision external name 'y'
)
constructor method Point (new_x double precision, new_y double precision),
method distance (Point p) returns double precision external name 'distance';
</pre>
</div>
<p>From now on the SQL Point type can be used to create instances of the
Java Point class, access it's members, call it's methods and store it into
tables (since the Java Point class implements the
<span class="computeroutput">java.io.Serializable</span> interface).</p>
</div>
<p>For the hosted Java objects a LANGUAGE JAVA should be specified.
The format of EXTERNAL NAME is:</p>
<ul>
<li>the full name of the Java class for classes (ex. 'java.lang.Class')</li>
<li>the name of the methods/instance members</li>
</ul>
<p>Since Java has static members and the Virtuoso SQL types do not,
Virtuoso allows read-only access to static members through static observer
functions with EXTERNAL VARIABLE NAME instead of EXTERNAL NAME.</p>
<a name="ex_staticmembers" />
<div class="example">
<div class="exampletitle">Static Member Access</div>
<div>
<pre class="programlisting">
java (stat.java) :
public class stat
{
static stat_m double;
}
</pre>
</div>
<p>Virtuoso SQL:</p>
<div>
<pre class="programlisting">
create type stat language java external name 'stat'
static method observe_stat_m ()
returns double precision external variable name 'stat_m';
</pre>
</div>
</div>
<p>Virtuoso does automatic mapping between the Virtuoso SQL data types
and the Java data types. Since Java data types are much more primitive than
Virtuoso types it is safe to explicitly specify the Java type of an instance
member, method parameter or method return value. This is done by using the
Type Signatures format described in the Java Native Interface Specification
(chapter 3 : JNI Types and Data Structures : Table 3.2). The signatures are
supplied as string values to EXTERNAL TYPE clause.</p>
<p>To facilitate the creation of the wrapper SQL types Virtuoso uses
the Java Reflection API to get the description of the class in XML form.
This XML is then transformed using an XSL stylesheet to makes the
CREATE TYPE statements required automatically. In the process it preserves
the superclass/subclass relationships of the specified Java classes and
represents them as a supertypes/subtypes in SQL. The
<a href="fn_jvm_ref_import.html">jvm_ref_import()</a>
procedure is used to create the XML by calling the Java Reflection API.</p>
<p>The function <a href="fn_import_jar.html">import_jar()</a>
takes the same parameters as <span class="computeroutput">jvm_ref_import()</span> but will
automatically create and execute the create type statements within the Virtuoso
server.</p>
<table class="data">
<caption>Table: 8.2.14.1.1. Java Type to Virtuoso Type Conversions</caption>
<tr>
<th class="data">Java Type/Class</th>
<th class="data">Virtuoso Internal Type</th>
</tr>
<tr>
<td class="data">boolean</td>
<td class="data">smallint</td>
</tr>
<tr>
<td class="data">byte</td>
<td class="data">smallint</td>
</tr>
<tr>
<td class="data">char</td>
<td class="data">smallint</td>
</tr>
<tr>
<td class="data">short</td>
<td class="data">integer</td>
</tr>
<tr>
<td class="data">int</td>
<td class="data">integer</td>
</tr>
<tr>
<td class="data">long</td>
<td class="data">integer</td>
</tr>
<tr>
<td class="data">float</td>
<td class="data">real</td>
</tr>
<tr>
<td class="data">double</td>
<td class="data">double precision</td>
</tr>
<tr>
<td class="data">byte[]</td>
<td class="data">binary</td>
</tr>
<tr>
<td class="data">java.lang.String</td>
<td class="data">NVARCHAR</td>
</tr>
<tr>
<td class="data">java.util.Date</td>
<td class="data">DATETIME</td>
</tr>
<tr>
<td class="data">[]</td>
<td class="data">vector</td>
</tr>
</table>
<br />
<table class="data">
<caption>Table: 8.2.14.1.2. Virtuoso Type to Java Type Conversions</caption>
<tr>
<th class="data">Virtuoso Internal Type</th>
<th class="data">Java Type/Class</th>
</tr>
<tr>
<td class="data">smallint</td>
<td class="data">short</td>
</tr>
<tr>
<td class="data">integer</td>
<td class="data">integer</td>
</tr>
<tr>
<td class="data">real</td>
<td class="data">float</td>
</tr>
<tr>
<td class="data">double precision</td>
<td class="data">double</td>
</tr>
<tr>
<td class="data">varchar</td>
<td class="data">java.lang.String</td>
</tr>
<tr>
<td class="data">nvarchar</td>
<td class="data">java.lang.String</td>
</tr>
<tr>
<td class="data">datetime</td>
<td class="data">java.util.Date</td>
</tr>
<tr>
<td class="data">timestamp</td>
<td class="data">java.util.Date</td>
</tr>
<tr>
<td class="data">binary</td>
<td class="data">byte[]</td>
</tr>
</table>
<br />
<p>For all the other types encountered in the signatures of the Java
methods/members it makes a forward reference to a Virtuoso/PL user defined
type based on the java class name, replacing the dot ('.') with the
underscore ('_') character.</p>
<p>For example:</p>
<p>
<span class="computeroutput">'java.lang.System'</span>
becomes <span class="computeroutput">'java_lang_System'</span>
</p>
<p>In order to correctly map a java superclass/subclass relationship between
class A and class B when importing, it is necessary to include A, B and all
the intermediate classes in the superclass/subclass chain in a single
<span class="computeroutput">import_jar()</span> call.</p>
<p>To implement serialization/deserialization for Java object the Virtuoso
needs the __virt_helper Java class. This class contains utility functions
implementing serialization/deserialization. This class must be in the CLASSPATH.</p>
<p>The Java VM hosted inside the Virtuoso binary is not started at startup,
but when first needed. It's startup is marked by a message in the Virtuoso
log file. An application can control the initialization of the Java VM by
explicitly initializing the Java VM (preferably on server startup) by
calling the VSE: <a href="fn_java_vm_attach.html">java_vm_attach()</a>
</p>
<br />
<a name="udtclrhosted" />
<h4>8.2.14.2. CLR Hosted Objects</h4>
<p>A special virtuoso build is available to allow SQL types integration
with the CLR (Common Language Runtime) on Windows. This is achieved by
providing COM server in C# (virtclr.dll) that is called from the native code
through COM.</p>
<p>The virtclr.dll library should be registered into the CLR's Global
assembly cache.</p>
<p>The semantics of CLR hosted objects are largely the same as those
described for Java hosted objects. As before, native objects need SQL Type
wrappers, but with LANGUAGE CLR clause specified.</p>
<p>To automatically create the SQL Type wrappers based on the CLR Reflection
API the Virtuoso CLR binary has a system stored procedure:
<a href="fn_import_clr.html">import_clr()</a>
</p>
<p>There are three forms for specifying the EXTERNAL NAME of a CLR class:</p>
<ul>
<li>a) <span class="computeroutput"><Assembly public name>/<namespace-prefixed-class-name></span>
Here the Virtuoso CLR host issues Assembly.Load with <span class="computeroutput"><Assembly public
name></span> to find the assembly. After finding it, it looks
for <span class="computeroutput"><namespace-prefixed-class-name></span> in it.</li>
<li>b) <span class="computeroutput"><namespace-prefixed-class-name></span>
Here the Virtuoso CLR host issues Assembly.Load with
<span class="computeroutput"><namespace-prefixed-class-name></span>. After
finding it, it looks for <span class="computeroutput"><namespace-prefixed-class-name></span>
in it.</li>
<li>c) <span class="computeroutput"><path-to-the-assembly-binary>/<namespace-prefixed-class-name></span>
Here the Virtuoso CLR host issues Assembly.LoadFrom with <span class="computeroutput"><path-to-the-assembly-binary></span>.
After finding it, it looks for <span class="computeroutput"><namespace-prefixed-class-name></span>
in it.</li>
</ul>
<p>
The Virtuoso CLR host does the above when creating an instance of the type,
accessing static methods or properties.</p>
<p>However when it deserializes an serialized CLR instance it calls the
CLR deserialization class BinaryFormatter. The BinaryFormatter.Deserialize
calls internally Assembly.Load to find the serialized class description.
So although the classes defined with EXTERNAL name as in c) above are otherwise
accessible (and serializable) they will possibly not deserialize correctly
(as the assembly binary may not be findable through the Assembly.Load). To
avoid that CLR limitation it is advisable to use the EXTERNAL NAME forms
a) and b) wherever possible.</p>
<p>The Assembly.Load process of finding Assemblies is very well documented
on the <a href="http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconhowruntimelocatesassemblies.asp">MSDN</a>.
Note that if an assembly was loaded through Assembly.LoadFrom it is not
considered as "already loaded" by the Assembly.Load. The Virtuoso CLR is a
CLR runtime host. As such it can use the normal CLR configuration files. It also
is able of using private assemblies.</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>The <a href="createassembly.html">Create Assembly</a> Syntax</p>
</div>
<br />
<a name="udtaspxhosting" />
<h4>8.2.14.3. ASPX Hosting Using the Hosted CLR</h4>
<p>Virtuoso CLR hosting allows ASPX pages to be executed through the
Virtuoso HTTP server inside the hosted CLR Virtual machine. To enable this
support an additional library (virt_http.dll) needs to be registered with
the Global Assembly cache. Having achieve this and copying ASPX project files
under the Virtuoso HTTP server's root allows direct execution of the ASPX
page. See the sample ASPX pages in the Virtuoso distribution.</p>
<p>If the ASPX project files reside in a WebDAV directory they are copied
into a temporary file system directory under a special temporary directory
(configurable by the TempASPXDir INI parameter in [HTTPServer] section of
<a href="databaseadmsrv.html#VIRTINI">virtuoso ini</a> file) before executed. In order
to be execute correctly from WebDAV the ASPX files should have Execute WebDAV
permission set them. The execution of ASPX is also controlled by the
EnableDavVSP INI parameter in the [HTTPServer] as with any active content
within WebDAV.</p>
<div class="tip">
<div class="tiptitle">See Also:</div>
<p>
<a href="rthwritaspxapps.html">Deploying ASP.Net Web Applications</a>
</p>
<p>
<a href="runtimehosting.html">Runtime Hosting</a>
</p>
</div>
<br />
<a name="udtaspxhostexprsdeps" />
<h4>8.2.14.4. Expressing Hosted Language Supertype/Subtype Dependencies With Virtuoso/PL User-Defined-Types</h4>
<p>It is also possible to represent the tree or in-part of Java or the CLR's
superclass/subclass hierarchy with Virtuoso user defined type mappings.</p>
<p>Consider the following sample Java code:</p>
<div>
<pre class="programlisting">
class g1 { public int g1_value; public int mtd_g1 (int x) { return g1_value; } };
class g2 extends g1 { public int g2_value; public int mtd_g2 (int x) { return g2_value; } };
class g3 extends g2 { public int g3_value; };
class g2_sib extends g1 { public int g2_sib_value; };
class uses_types {
public static g3 mtd (int x) { return new g3 (); }
}
</pre>
</div>
<p>One can create SQL user defined types for
<span class="computeroutput">g1</span>, <span class="computeroutput">g2</span>
and <span class="computeroutput">g3</span> to represent the
<span class="computeroutput">g1</span>/<span class="computeroutput">g2</span>/
<span class="computeroutput">g3</span> Java class hierarchy if calling
<span class="computeroutput">mtd_g1</span> and
<span class="computeroutput">mtd_g2</span> is needed:</p>
<div>
<pre class="programlisting">
create type sql_g1 language java external name 'g1' as (
g1_value int)
method mtd_g1 (x integer) returns integer;
create type sql_g2 under sql_g1 language java external name 'g2' as (
g2_value int)
method mtd_g2 (x integer) returns integer;
create type sql_g3 under sql_g2 language java external name 'g3' as (
g3_value int)
method mtd_g3 (x integer) returns integer;
create type uses_types language java external name 'uses_types'
static method mtd (x integer) returns sql_g3;
</pre>
</div>
<p>provided with the above, one can call
<span class="computeroutput">uses_types.g3 ()</span>method and
call <span class="computeroutput">mtd_g1 ()</span> on the returned instance in
Virtuoso/PL as follows:</p>
<div>
<pre class="programlisting">
select uses_types::mtd (12).mtd_g1 (10);
</pre>
</div>
<p>Sometimes it is not desirable or necessary to mirror the full
supertype/subtype hierarchy from Java to Virtuoso/PL.</p>
<p>For the above example only <span class="computeroutput">sql_g2</span>
and <span class="computeroutput">sql_g3</span> can be defined if the goal was
to call <span class="computeroutput">mtd_g2()</span> instead of
<span class="computeroutput">mtd_g1()</span>.</p>
<p>When creating instances of the Virtuoso/PL user defined types to
represent the data returned by the hosted code, Virtuoso tries to find the
closest common ancestor of the hosted instance's class and the ones defined
in Virtuoso as user defined types.</p>
<p>For example if in the above example a Java function returns an instance of
<span class="computeroutput">g3</span> and there is a
<span class="computeroutput">sql_g3</span> defined inside virtuoso the
<span class="computeroutput">g3</span> Java instance will be wrapped into an
<span class="computeroutput">sql_g3</span> Virtuoso/PL instance. Note that
that will not depend on the presence or absence of
<span class="computeroutput">sql_g1</span> and
<span class="computeroutput">sql_g2</span> definitions - i.e. Virtuoso will favor
the exact match.</p>
<p>If, however <span class="computeroutput">sql_g3</span> is not defined, but
<span class="computeroutput">sql_g2</span> and <span class="computeroutput">sql_g1</span>
are, then the <span class="computeroutput">g3</span> instance will be wrapped
up in an <span class="computeroutput">sql_g2</span> instance when returned.</p>
<p>Similarly, if an instance of the <span class="computeroutput">g2_sib</span>
is to be returned in Virtuoso/PL and <span class="computeroutput">sql_g1</span>
to <span class="computeroutput">sql_g3</span> are defined, Virtuoso will wrap the
<span class="computeroutput">g2_sib</span> Java instance into an
<span class="computeroutput">sql_g1</span> SQL instance.</p>
<br />
<br />
<a name="udtrepressentsoapstruct" />
<h3>8.2.15. Using User Defined Types to Represent SOAP Structures</h3>
<p>The Virtuoso SOAP server is capable of using user defined types (both
native and hosted) to represent structures in SOAP requests/responses.
Normally a SOAP exposed procedure would have references to defined schema
types (__SOAP_TYPE for it's return type and for it's argument types). When
such a schema type represents a structure (see the SOAP RPC encoding) the
Virtuoso SOAP server will map the structure to an array of name/value pairs
for it's members (the type of value returned by the soap_box_structure VSE).
The user defined types however are better suited for representing such data.
In order for a user defined type to be usable in SOAP, it must have a default
constructor (no arguments). For native types that is always true (since they
have the implicit constructor setting up the member's values to the respective
DEFAULT values from the user defined type declaration). The Virtuoso SOAP
implementation supports two ways of specifying how a SOAP value XML fragment
should be materialized as a user defined type instance, as follows.</p>
<a name="udtsoapuseschemafrag" />
<h4>8.2.15.1. Using Schema Fragments</h4>
<p>The <a href="fn_soap_dt_define.html">SOAP_DT_DEFINE()</a>
function is used to map a particular schema fragment describing a composite
schema type for SOAP usage. This now takes an additional optional argument to
establish a link to a user defined type name:</p>
<div>
<pre class="programlisting">
create type SO_S_30
as (
"varString" nvarchar,
"varInt" integer,
"varFloat" real,
"processingResult" nvarchar,
"vmVersion" nvarchar)
constructor method SO_S_30 (),
method process_data () returns nvarchar;
soap_dt_define ('',
'<complexType name="SOAPStruct"
xmlns:enc="http://schemas.xmlsoap.org/soap/encoding/"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="services.wsdl"
xmlns:tns="services.wsdl">
<all>
<element name="varString" type="string" nillable="true"/>
<element name="varInt" type="int" nillable="true"/>
<element name="varFloat" type="float" nillable="true"/>
<element name="processingResult" type="string" nillable="true"/>
<element name="vmVersion" type="string" nillable="true"/>
</all>
</complexType>', 'SO_S_30');
</pre>
</div>
<p>The CREATE TYPE statement defines the user defined type SO_S_30 as
having 5 data members, a no-parameters constructor and a processing method.
The <span class="computeroutput">soap_dt_define()</span> call maps the SO_S_30 to a schema
type SOAPStruct which also has 5 data members and makes that schema type
available to SOAP. Now we create a SOAP exposed stored procedure:</p>
<div>
<pre class="programlisting">
create procedure echoSOAPStructSch (
in sst SO_S_30 __soap_type 'services.wsdl:SOAPStruct')
returns SO_S_30 __soap_type 'services.wsdl:SOAPStruct'
{
declare processingResult nvarchar;
processingResult := sst.process_data ();
return sst;
};
</pre>
</div>
<p>When processing the SOAP request for calling that stored procedure,
the SOAP server will call the default constructor for SO_S_30 (require to
create the empty instance) and will fill up the values from the incoming XML
fragment for the sst parameter to the members of the newly created SO_S_30
instance. Then it will pass that instance as a value for the sst parameter
of the echoSOAPStructSch function. As a result echoSOAPStructSch will correctly
execute the member function process_data. Then it will return the (possibly)
modified SO_S_30 instance to the SOAP server. The SOAP server will make the
XML fragment for the return value based on the SOAPStruct schema fragment using
the values from the SO_S_30 members.</p>
<p>This approach allows easy migration for the existing SOAP services
using structures. To upgrade a SOAP service procedure to use user defined
types one should define the types and add the additional argument to
<span class="computeroutput">SOAP_DT_DEFINE()</span>.</p>
<p>For developing new SOAP services, however, it is redundant to create the
schema fragment in addition to creating the user defined type to hold the
SOAP structure.</p>
<p>For this reason, Virtuoso offers a second approach in using user
defined types in SOAP.</p>
<br />
<a name="udtsoapudt" />
<h4>8.2.15.2. Using the User Defined Type Definition</h4>
<p>Consider the altered definition of SO_S_30 as follows:</p>
<div>
<pre class="programlisting">
create type SO_S_30
as (
varString nvarchar __soap_type 'string' __soap_name 'varString',
"varInt" integer __soap_type 'int',
"varFloat" real __soap_type 'float',
"processingResult" nvarchar __soap_type 'string',
"vmVersion" nvarchar)
__soap_type 'services.wsdl:SOAPStruct'
constructor method SO_S_30 (),
method process_data () returns nvarchar;
</pre>
</div>
<p>and the procedure echoSOAPStructSch as:</p>
<div>
<pre class="programlisting">
create procedure echoSOAPStructSch (in sst SO_S_30) returns SO_S_30
{
declare processingResult nvarchar;
processingResult := sst.process_data ();
return sst;
};
</pre>
</div>
<p>Now all we have to do is expose the echoSOAPStructSch in a SOAP service.
The SOAP server will take into account the fact that the sst type and the
return type are user defined types and automatically make the WSDL
description (including the schema fragments) and will correctly process
the incoming XML.</p>
<p>The SOAP names and data type names inside the user defined type definition
are optional and default to the SQL member's name for names and employ
a straight mapping of the PL types to the SOAP types for data types.</p>
<br />
<br />
<a name="udtcnsmsoap" />
<h3>8.2.16. Consuming Third-Party SOAP Services via User Defined Types</h3>
<p>Virtuoso provides function for acting as a SOAP client called
<a href="">SOAP_CLIENT()</a>.
Embedding SOAP Web Service methods in Virtuoso/PL procedures using this function,
however, is not suitable in most cases, especially if the services contained a
large number of parameters of different type, the procedure could become very
complex, the encoding may also vary. Thus direct SOAP_CLIENT() invocation in
some cases in non-trivial and may lead to errors that are hard to debug.</p>
<p>To aid development of Web based applications written in PL that use
the SOAP protocol, Virtuoso introduces two new approaches for consuming
a Web service:</p>
<ul>
<li>generate SOAP proxy wrappers encapsulated in a PL Module</li>
<li>generate a User Defined Type (UDT) for SOAP proxy encapsulation.</li>
</ul>
<p>Please note that both methods of making a SOAP proxy-wrapper requires
a WSDL description. If some SOAP service does not have a corresponding
WSDL, neither of these methods can be used.</p>
<p>The first approach can be achieved using the SOAP_WSDL_IMPORT() function.
This will make a PL module utilizing the SOAP_CLIENT() function that will import the
complex types and pass appropriate parameters. But it has one significant
problem in that it will return the result as a parsed XML entity. The result should
then be processed in the application code, which requires prior knowledge of
the return parameters.</p>
<p>The second approach consists of creating a UDT encapsulation of
the SOAP wrappers using the
<a href="fn_wsdl_import_udt.html">WSDL_IMPORT_UDT().</a>
</p>
<div class="tip">
<div class="tiptitle">See Also</div>
<p>The <a href="fn_soap_wsdl_import.html">SOAP_WSDL_IMPORT()</a> function.</p>
<p>The <a href="fn_wsdl_import_udt.html">WSDL_IMPORT_UDT()</a> function.</p>
</div>
<p>The import function <span class="computeroutput">WSDL_IMPORT_UDT()</span> has two
phases:</p>
<ul>
<li>retrieve and expand the WSDL file (expansion will be done if import is specified)</li>
<li>compile the result and make SQL script with UDT definition</li>
</ul>
<p>The following points will hold true for this method of SOAP encapsulation:</p>
<ul>
<li>Any XML Schema types, required for calling the target SOAP service will be imported in database.</li>
<li>A UDT will be created for each service defined within the WSDL description. </li>
<li>The UDT will have members: url, request, response, and debug.
<ul>
<li>the 'url' member designate the endpoint for SOAP invocation</li>
<li>debug is a flag to manage wrappers to return wire dumps if needed</li>
<li>request and response members will contain wire dumps if 'debug' is equal to 'true' (integer 1).</li>
</ul>
</li>
<li>for each method defined in the WSDL description there will be UDT's method.</li>
<li>each method of UDT will contain:
<ul>
<li>a number IN/OUT/INOUT parameters (depending on the target method)</li>
<li>no return value, SOAP does not define explicitly return values.</li>
<li>call to the SOAP_CLIENT () function with appropriate arguments</li>
<li>XPATH over the result and transform with SOAP validation functions to
ensure value of OUT and INOUT parameters. </li>
</ul>
</li>
</ul>
<div class="tip">
<div class="tiptitle">See Also</div>
<p>The Virtuoso Administration Interface provides a web based
interface for importing WSDL definitions and creating UDTs and procedures.
This can be found in the <a href="htmlconductorbar.html#admiui.wsdl">Visual Server Administration
Interface</a> Chapter.</p>
</div>
<br />
<a name="udtsecurity" />
<h3>8.2.17. UDT Security</h3>
<p>Security of UDTs is maintained through normal SQL GRANT and REVOKE
statements via a simple extension. You can define the level of access to both native
and externally hosted UDTs.</p>
<p>Grants for persistent user defined types are persisted into the SYS_GRANTS
table. Grants on temporary user defined types are in-memory only and are lost
(together with the temporary user defined type definition) when the server is
restarted.</p>
<p>There are two GRANT/REVOKE types for UDTs as follows:</p>
<ul>
<li>
<strong>EXECUTE</strong> - all methods and members of a class are accessible to the grantee.</li>
<li>
<strong>UNDER</strong> - the grantee can create subclasses of the class.</li>
</ul>
<div>
<pre class="programlisting">
GRANT/REVOKE EXECUTE on <user_defined_type>
GRANT/REVOKE UNDER on <user_defined_type>
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>SQL modules, user defined types and SQL stored procedures are exposed
to GRANT/REVOKE in the same namespace, therefore care must be taken avoid
inadvertently granting to multiple objects at the same time.</p>
</div>
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="sqlrefDATATYPES.html" title="Datatypes">Previous</a>
<br />Datatypes</td>
<td align="center" width="34%">
<a href="sqlreference.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="sqlrefxmldatatype.html" title="XML Column Type">Next</a>
<br />XML Column Type</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
>
905
