17.1.11. Exposing Remote Third Party SQL Stored Procedures as SOAP Services

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)
as
  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);
         i:=0;
         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.