• Info
• Initial State
• View Source
• Run

BPEL

sqlexec Inventory Service

Using SQL binding and SQLX to generate requests process

Example

The demo is a service which is invoked on time basis in order to update an inventory. This demo using the Northwind demo Database as an inventory.

The client generates a list of items that are under 10 units using SQLX. This XML document is sent to the inventory process and will make new process instance.

create procedure DB..update_inventory ()
{
  declare req any;
  whenever not found goto nf;
  -- the following statement generates an XML document using SQLX
  --
  -- the result would like :
  -- <Items xmlns="http://temp.org"><Product><ProductID>5</ProductID><Quantity>10</Quantity></Product>...</Items>
  --
  select xmlelement(Items,
  		xmlattributes ('http://temp.org' as xmlns),
		xmlagg (
		  xmlelement (item,
		    xmlelement (ProductID, ProductID),
		    xmlelement (Quantity, UnitsInStock + 10)
		    ))) into req
      from Demo..Products where UnitsInStock < 10 and Discontinued = 0;

  -- call the process with generated XML document as a input parameter
  soap_client (	url=>'http://host:port/InventorySvc',
      		operation=>'initiate',
		parameters=>vector ('par0', req),
		soap_action=>'initiate',
		style=>1
      );

  nf:
  return;
}
	  

The process will loop over all items in input and for each will ask for quote. Also the quote service will return a list of wholesalers and their prices. Then the process will choose the best price and will make an order using specific partner URL (this is simulated by appending a URL parameter to the service URL). When an order confirmation is received, the process will update the inventory.

At the end of process it will sent an e-mail to the pre-configured operator address.

The following code is a pseudo-code which describes the process flow:

{
  declare i, l int;
  declare j, k, oid int;
  declare in, out, ord any;

  in = receive ();

  l = length (in);
  for (i = 0; i < l; i++)
    {
      declare q, best any;
      q = getQuote (in[i]);
      k = length (q);
      for (j := 0; j < k; j++)
        {
          if (q.price < best.price)
            best = q;
        }
      oid = newOrder (best.url, in[i].item, in[i].quantity, best.price);
      update Demo.demo.Products set UnitsOnStock = UnitsOnStock + in[i].quantity where
           ProductID = in[i].ProductID;
    }
   sendMail ('Inventory have been updated');
}
	  

To run the example follow the steps bellow:

• Load the initial setup file: 'store.sql'
• Login into the BPEL UI via http://host:port/BPELGUI.
• From the 'Home' tab page go to section 'Tasks' and click 'Upload Process' link. Another way is to choose 'Processes' tab and go to the 'Process Upload' sub-tab.
• In the "Load Deployment Descriptor URI" field type 'http://host:port/BPELDemo/sqlexec/bpel.xml'.
• Choose the 'Import Process' button.
• Choose the 'Compile process' button.
• If compilation succeeds, will go to the 'Processes' page where the process will be in the given processes list.
• Run via ISQL the DB..update_inventory () procedure.
• Also the update_inventory procedure can be used as a scheduled task (see 'Virtuoso scheduler' section in the documentation for details).

View the source	Action
1. store.sql	Set the initial state
2. bpel.xml
3. inventory.bpel
4. inventory.wsdl
5. store.vsp	Run
6. wsa.xsd