# 22. Virtuoso Functions Guide - vd_remote_proc_wrapper

Chapter Contents | Prev | Next
Keyword Search:
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