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.9. CREATE PROCEDURE Syntax - External hosted procedures

Virtuoso provides a syntax shortcut for calling static method from hosted user defined types without first defining a Virtuoso external hosted user defined type:

CREATE (PROCEDURE|FUNCTION) <local_name> ([<arg_def1>, ...])
	[returns <sql_datatype>] LANGUAGE [JAVA|CLR] EXTERNAL NAME '<external_static_proc_name_literal>'

<arg_def> := [IN|OUT|INOUT] param_name <data_type_spec>

<external_static_proc_name_literal> = <external_type_name_literal>.<static_proc_name>

This compiles into an functional equivalent of :

create procedure <local_name) ([<arg_def1>, ....])
{
  declare ret any;

  exec ('
    create type <local_name>
      temporary self as ref 
        static method m1 ([<arg_def1>, ....]) 
           returns <datatype> EXTERNAL NAME ''<static_proc_name>''
   ');

  ret := <local_name>::m1 (....);

  exec ('drop type <local_name>');

  return ret;
  }
  

For more details see CREATE TYPE and Runtime hosting chapters.

CREATE PROCEDURE for a Java method:

Here is an example for CREATE PROCEDURE and the hosted Java VM:

create procedure get_property (in x varchar) returns varchar language java external name 'java.lang.System.getProperty';

Here's how that procedure is called:

SQL> select get_property ('java.vm.name');
callret
VARCHAR
_______________________________________________________________________________

Java HotSpot(TM) Client VM
CREATE PROCEDURE for a CLR method:

Here is an example for CREATE PROCEDURE and the hosted CLR:

create procedure curr_thr_id () returns integer language CLR external name 'mscorlib/System.AppDomain.GetCurrentThreadId';

Here's how that procedure is called:

SQL> select curr_thr_id();
callret
INTEGER
_______________________________________________________________________________

2156