Virtuoso can expose any of its available PL resources to the SOAP world. This includes data from remote attached tables and procedures. To do this, one needs to write a wrapper procedure in Virtuoso/PL.

Example 17.10. Exposing a MS SQL Server procedure to SOAP using Virtuoso

Here we have a sample MS SQL Server procedure and an accompanying Virtuoso wrapper function. The MS SQL Server function returns a result set based on a simple join query with a filter input. The Virtuoso procedure calls the remote procedure, iterates through the result set returned and produces XML output. First the MS SQL Server procedure:

create procedure ms_remote
        @mask varchar(15)
  select c.CustomerID, c.CompanyName, o.OrderDate,
      o.ShippedDate,ol.ProductID, ol.Quantity, ol.Discount
    from Northwind..Customers c
      inner join Northwind..Orders o on c.CustomerID = o.CustomerID
      inner join Northwind.."Order Details" ol on o.OrderID = ol.OrderID
    where c.CustomerID like @mask

Then the Virtuoso wrapper function:

create procedure WS.SOAP.ms_remote_call (
  in dsn varchar, in uid varchar, in pwd varchar, in mask varchar)
  declare m, r, ses any;
  vd_remote_data_source (dsn, '', uid, pwd);
  rexecute (dsn, 'ms_remote ?', null, null, vector (mask), 1000, m, r);
  ses := string_output ();
  http ('<?xml version="1.0" ?>\n<remote>\n', ses);
  if (isarray(m) and isarray (r))
      declare i, l, j, k integer;
      declare md, rs any;
      md := m[0];
      i := 0; l := length (md); k := length (r); j := 0;
      while (j < k)
         http ('<record ', ses);
         while (i < l)
             dbg_obj_print (md[i][0],r[j][i]);
             http (sprintf (' %s="%s"', trim(md[i][0]), trim(cast (r[j][i] as varchar))), ses);
             i := i + 1;
         http (' />\n', ses);
         j := j + 1;
  http ('</remote>', ses);
  return string_output_string (ses);

Now, as before, we grant execute rights to the SOAP user:

grant execute on WS.SOAP.ms_remote_call to SOAP;

The remote procedure ms_remote() can now be accessed via SOAP.

[Tip] See Also:

The Virtual Database chapter for information regarding use of remote datasources and their tables.