Name

vd_remote_proc_wrapper — Creating a PL wrapper for remote procedure execution

Synopsis

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 generation

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

Example24.438.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.