www.openlinksw.com
docs.openlinksw.com

Book Home

Contents
Preface

Virtuoso Functions Guide

Administration
Aggregate Functions
Array Manipulation
BPEL APIs
Backup
Compression
Cursor
Date & Time Manipulation
Debug
Dictionary Manipulation
Encoding & Decoding
File Manipulation
Free Text
Hashing / Cryptographic
LDAP
Locale
Mail
Miscellaneous
Number
Remote SQL Data Source
att_local_name
quote_dotted
rclose
rexecute
rmoreresults
rnext
rstmtexec
sql_columns
sql_config_data_sour...
sql_data_sources
sql_driver_connect
sql_get_installed_dr...
sql_get_private_prof...
sql_gettypeinfo
sql_primary_keys
sql_procedures
sql_remove_dsn_from_...
sql_special_columns
sql_statistics
sql_tables
sql_transact
sql_write_file_dsn
sql_write_private_pr...
vd_remote_data_sourc...
vd_remote_proc_wrapp...
vd_remote_table
vd_statistics
vdd_disconnect_data_...
vdd_measure_rpc_time
Replication
SOAP
SQL
String
Transaction
Type Mapping
UDDI
User Defined Types & The CLR
Virtuoso Java PL API
Virtuoso Server Extension Interface (VSEI)
Web Server & Internet
XML
XPATH & XQUERY

Functions Index

vd_remote_proc_wrapper

Creating a PL wrapper for remote procedure execution
varchar vd_remote_proc_wrapper (in dsn varchar, in remote_name varchar, in local_name varchar, in metadata any, out status_code varchar, out error_message varchar, in make_resultset integer, in description varchar);
Description

This is to create a PL stored procedure to execute a Remote Stored Procedures. It returns results as a SQL result set as well as an array(vector) depending of the 'make_resultset' flag.

Parameters
dsn – the remote datasource name
remote_name – name of the remote procedure
local_name – name of the PL wrapper to be created locally
metadata – A vector of vectors containing a parameters information each of vectors need to have following structure:
  • type of parameter - 'IN'/'OUT'/'INOUT'
  • name of parameter - the name of parameter
  • data type - SQL datatype of parameter as string (for example 'VARCHAR', 'INT' etc.)
  • SOAP type - a XSD/custom datatype for SOAP/WSDL actions (if not needed, must be empty string ie. '')
status_code – returns SQL status code of the PL wrapper genearation
error_message – returns the error message if status code is different than 00000
make_resultset – A flag 0/1 default 0. If is true (1) the wrapper will be generated to return result set. Otherwise will be generated to return an array.
description – The text of a comment to added to the wrapper. It then will be shown as a method description if the PL wrapper is published as a SOAP method.
Examples
Creating a PL wrapper which returns resultset

The following example will create a PL wrapper to invoke the MS SqlServer Stored Procedure 'Northwind.dbo.CustOrderHist'

-- the wrapper creation (fragment of Virtuoso/PL)   
declare state, msg varchar;
vd_remote_proc_wrapper ('Northwind.dbo.CustOrderHist', 'MS.SQL.CustOrderHist', 
'sql_lite', vector (vector ('IN', '_CustomerID', 'VARCHAR', '')), state, msg , 1, 'This is a test');
if (state <> '00000')
  signal (state, msg);


-- PL wrapper source that will be created   
create procedure "MS"."SQL"."CustOrderHist" (IN "_CustomerID" VARCHAR) returns any array array
{ 
--PL Wrapper for remote procedure
--##This is a test
--"DSN:sql_lite PROCEDURE:Northwind.dbo.CustOrderHist"
declare dta, mdta any; 
declare params any; 
params := vector ("_CustomerID"); 
set_user_id ('dba');
rexecute ('sql_lite', '{call "Northwind"."dbo"."CustOrderHist" (?)}', NULL, NULL, params, 0, mdta, dta); 
exec_result_names(mdta[0]);
declare i, l integer;
i := 0; l := length (dta);
 while(i<l) {
   exec_result(dta[i]);
   i:=i+1;
 }
}

-- result from execution
SQL> "MS"."SQL"."CustOrderHist" ('ALFKI');
ProductName                               Total
VARCHAR NOT NULL                          INTEGER
_______________________________________________________________________________

Aniseed Syrup                             6
Chartreuse verte                          21
Escargots de Bourgogne                    40
Flotemysost                               20
Grandma's Boysenberry Spread              16
Lakkalikri                                15
Original Frankfurter grne Soe             2
Raclette Courdavault                      15
Rssle Sauerkraut                          17
Spegesild                                 2
Vegie-spread                              20

11 Rows. -- 18 msec.

 
	  
See Also

rexecute vd_remote_data_source