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