Extending the WBI JDBC Adapter Migration sample

Here are some examples of the ways in which the sample can be extended:

Modify the sample to use a message set generated from the DBINPUT.dbm file. You therefore avoid the need to populate using Adapter XML Schema Definitions (XSD), which might contain properties that you do not need
Modify the sample to use the XMLNSC domain, because it is faster than MRM for XML messages.
Extend the sample to transform the message to a simpler target message.
Extend the sample to support invoices for multiple items.

Supporting invoices for multiple items

This scenario presents the following challenges:

Retrofitting a new database table to the existing XSDs and ESQL
Creating a SELECT statement for a part of the message that has multiple sub-elements

Updating the database tables

The following SQL is for DB2 Universal Database. if you are running Oracle, you may need to modify it accordingly (and remember to set @ as the statement terminator):

CREATE TABLE "ADAPTER_ITEM" (
		"PKEY" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE),
		"NAME" VARCHAR(20) NOT NULL,
		"INVOICE_KEY" VARCHAR(10) NOT NULL,
		"PRICE" FLOAT(53) DEFAULT 0
	)
	DATA CAPTURE NONE@

ALTER TABLE "ADAPTER_ITEM" ADD CONSTRAINT "ADAPTER_ITEM_PK" PRIMARY KEY
	("PKEY")@

The SQL creates a new table, ADAPTER_ITEM, with four columns: an auto-generated primary key, the name of the item, a reference to the invoice it belongs to, and the price of the item. The new table uses the INVOICE_KEY as a foreign key to the ADAPTER_ITEM table.

Updating the message model

Switch to the Data perspective.
Drag the ADAPTER_ITEM table from the Data Source Explorer into the AdapterMigration Data Design project in the Data Project Explorer. This will create a new database model file.
Switch back to the Application Development perspective
Import the new definition into the MigratedAdapter Input Messages project. Right-click the project, select New>Message Definition File from>Database Definition File and select the new dbm file in the workspace.
Open the new .mxsd file, and rename the ADAPTER_ITEM element to ADAPTERITEM to match the naming convention used by the adapters
Open ADAPTERINVOICE.mxsd.
Change the name from Item to Items, and change the type to (New Complex Type), accepting the defaults.
Navigate to the new {Local Complex Type} in the hierarchy. Right-click {Local Complex Type}, select Add Local Attribute, and set name to size and type to int.
Right-click {Local Complex Type} again and select Add Element Reference, choosing the new ADAPTERITEM dialog. Set Max Occurs to -1.
Save the message

Updating the flow ESQL

You need to update only the part of the flow that creates ADAPTERINVOICE messages. Double-click the DatabaseInput node in the MigratedAdapter flow to open the ESQL, navigate to the Source view, and scroll down to the portion that creates the ADAPTERINVOICE message:

SET Root.MRM = THE(
SELECT 
	DispatchedEvent.Usr.OBJECT_VERB AS verb,
	INVOICE.PKEY        AS Inv:PKEY,
	INVOICE.ITEM        AS Inv:"Item",
	INVOICE.QUANTITY    AS Inv:QUANTITY,
	INVOICE.CUSTOMER    AS Inv:CUSTOMER,
		'Retrieve'         AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.verb,
		CUSTOMER.PKEY      AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:PKEY,
		CUSTOMER.FIRSTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:FIRSTNAME,
		CUSTOMER.LASTNAME  AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:LASTNAME,
		CUSTOMER.ADDRESS   AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADDRESS,
			'Retrieve'         AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.verb,
			ADDRESS.PKEY       AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:PKEY,
			ADDRESS.FIRSTLINE  AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:FIRSTLINE,
			ADDRESS.SECONDLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:SECONDLINE,
			ADDRESS.CODE       AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CODE,
			ADDRESS.CITY       AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CITY,
			ADDRESS.COUNTRY    AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:COUNTRY,
		CUSTOMER.CCODE    AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:CCODE ,
	'JDBCConnector_'
	 ||CAST(DispatchedEvent.Usr.EVENT_ID AS CHARACTER)
	 ||'ibm_events'
	 ||CAST(DispatchedEvent.Usr.EVENT_TIME AS CHARACTER FORMAT 'yyyyMMddhhmmss.SSS') AS Inv:ObjectEventId
	FROM Database.ADAPTER_INVOICE  AS INVOICE, 
		 Database.ADAPTER_CUSTOMER AS CUSTOMER, 
		 Database.ADAPTER_ADDRESS  AS ADDRESS
	WHERE INVOICE.PKEY  = DispatchedEvent.Usr.OBJECT_KEY AND 
		  CUSTOMER.PKEY = INVOICE.CUSTOMER AND 
		  ADDRESS.PKEY  = CUSTOMER.ADDRESS);

Add the nested items and set the size attribute to the number of retrieved items.

Because the Invoice->Item link is a one-to-many mapping, you cannot retrieve the elements in the SELECT statement that was used to retrieve customer and address data.
To make the SELECT statement easier to create, and to avoid unnecessary errors, use the code generator to create the basic SELECT. Switch to the Database Event Design pane.
Select ADAPTER_ITEM as the Application Table name. The Primary Key and Output Message Element fields will be populated for you.
Click Generate Query and switch back to the source pane.
Change the new namespace declaration at the top of the module source code to something more readable than "ns"; for example, "Ite".
Scroll down to the section entitled BuildMessage auto-generated code. Copy the SELECT clause and delete the whole SET line.
Replace INVOICE.ITEM in the above code with your newly copied SELECT clause. Add opening and closing parentheses around the SELECT clause and change DispatchedEvent.Usr.OBJECT_KEY to INVOICE.PKEY. Change the AS clause from Inv:"Item" to Inv:ITEMS.Ite:ADAPTERITEM[]. The new clause should look like this:
```
	
(SELECT ADAPTER_ITEM.PKEY,
		ADAPTER_ITEM.NAME,
		ADAPTER_ITEM.INVOICE_KEY,
		ADAPTER_ITEM.PRICE
	FROM Database.ADAPTER_ITEM
	WHERE ADAPTER_ITEM.INVOICE_KEY = INVOICE.PKEY) AS Inv:ITEMS.Ite:ADAPTERITEM[],
```

Now populate the new attribute with the number of ADAPTERITEM rows retrieved. Add the following after the SELECT:

CREATE FIRSTCHILD OF Root.MRM.Inv:ITEMS TYPE NameValue NAME 'size' VALUE CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[]);

Normally, you could write:

SET Root.MRM.Inv:ITEMS.size = CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[])

But, because the message is an ordered sequence, you need to ensure the attribute is the first child.
Here is the final ESQL:

SET Root.MRM = THE(
SELECT 
	DispatchedEvent.Usr.OBJECT_VERB AS verb,
	INVOICE.PKEY        AS Inv:PKEY,
		(SELECT ADAPTER_ITEM.PKEY,
				ADAPTER_ITEM.NAME,
				ADAPTER_ITEM.INVOICE_KEY,
				ADAPTER_ITEM.PRICE
		FROM Database.ADAPTER_ITEM
		WHERE ADAPTER_ITEM.INVOICE_KEY = INVOICE.PKEY) AS Inv:ITEMS.Ite:ADAPTERITEM[],
	INVOICE.QUANTITY    AS Inv:QUANTITY,
	INVOICE.CUSTOMER    AS Inv:CUSTOMER,
		'Retrieve'         AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.verb,
		CUSTOMER.PKEY      AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:PKEY,
		CUSTOMER.FIRSTNAME AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:FIRSTNAME,
		CUSTOMER.LASTNAME  AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:LASTNAME,
		CUSTOMER.ADDRESS   AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADDRESS,
			'Retrieve'         AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.verb,
			ADDRESS.PKEY       AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:PKEY,
			ADDRESS.FIRSTLINE  AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:FIRSTLINE,
			ADDRESS.SECONDLINE AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:SECONDLINE,
			ADDRESS.CODE       AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CODE,
			ADDRESS.CITY       AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:CITY,
			ADDRESS.COUNTRY    AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:ADAPTERADDRESS.Add:ADAPTERADDRESS.Add:COUNTRY,
		CUSTOMER.CCODE    AS Inv:ADAPTERCUSTOMER.Cus:ADAPTERCUSTOMER.Cus:CCODE ,
	'JDBCConnector_'
	 ||CAST(DispatchedEvent.Usr.EVENT_ID AS CHARACTER)
	 ||'ibm_events'
	 ||CAST(DispatchedEvent.Usr.EVENT_TIME AS CHARACTER FORMAT 'yyyyMMddhhmmss.SSS') AS Inv:ObjectEventId
	FROM Database.ADAPTER_INVOICE  AS INVOICE, 
		 Database.ADAPTER_CUSTOMER AS CUSTOMER, 
		 Database.ADAPTER_ADDRESS  AS ADDRESS
	WHERE INVOICE.PKEY  = DispatchedEvent.Usr.OBJECT_KEY AND 
		  CUSTOMER.PKEY = INVOICE.CUSTOMER AND 
		  ADDRESS.PKEY  = CUSTOMER.ADDRESS);
CREATE FIRSTCHILD OF Root.MRM.Inv:ITEMS TYPE NameValue NAME 'size' VALUE CARDINALITY(Root.MRM.Inv:ITEMS.Ite:ADAPTERITEM[]);

Test the new functionality

Test the new functionality by adding a couple of items to the ITEM table, and adding or modifying an invoice that uses these items:

--<ScriptOptions statementTerminator="@"/>
INSERT INTO ADAPTER_ITEM (NAME,INVOICE_KEY,PRICE) 
  VALUES ('Trousers', 'inv10', 2.17)@
  
INSERT INTO ADAPTER_ITEM (NAME,INVOICE_KEY,PRICE)
  VALUES ('Jumpers', 'inv10', 12.00)@

INSERT INTO ADAPTER_INVOICE
  VALUES ('inv10', '', 10, 'cust1')@

Insert the above SQL in to a new SQL script in the AdapterMigration Data Design project in the Data Project Explorer. Then right-click the project inside the Data Project Explorer view and choose "Run SQL". This will cause the flow to be executed and the following message to appear on the ADAPTERMIGRATION.INVOICE queue:

<?xml version="1.0"?>
<Q1:ADAPTERINVOICE xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERINVOICE" xmlns:Q4="http://dbinput/amartens" xmlns:Q3="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERADDRESS" xmlns:Q2="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/ADAPTERCUSTOMER" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" verb="Create">
 <Q1:PKEY>inv10</Q1:PKEY>
 <Q1:ITEMS size="2">
  <Q4:ADAPTERITEM>
   <PKEY>9</PKEY>
   <NAME>Trousers</NAME>
   <INVOICE_KEY>inv10</INVOICE_KEY>
   <PRICE>2.17E+0</PRICE>
  </Q4:ADAPTERITEM>
  <Q4:ADAPTERITEM>
   <PKEY>10</PKEY>
   <NAME>Jumpers</NAME>
   <INVOICE_KEY>inv10</INVOICE_KEY>
   <PRICE>1.2E+1</PRICE>
  </Q4:ADAPTERITEM>
 </Q1:ITEMS>
 <Q1:QUANTITY>10</Q1:QUANTITY>
 <Q1:CUSTOMER>cust1</Q1:CUSTOMER>
 <Q1:ADAPTERCUSTOMER>
  <Q2:ADAPTERCUSTOMER verb="Retrieve">
   <Q2:PKEY>cust1</Q2:PKEY>
   <Q2:FIRSTNAME>Barney</Q2:FIRSTNAME>
   <Q2:LASTNAME>Rubble</Q2:LASTNAME>
   <Q2:ADDRESS>add1</Q2:ADDRESS>
   <Q2:ADAPTERADDRESS>
    <Q3:ADAPTERADDRESS verb="Retrieve">
     <Q3:PKEY>add1</Q3:PKEY>
     <Q3:FIRSTLINE>MP211</Q3:FIRSTLINE>
     <Q3:SECONDLINE>IBM Hursley Park</Q3:SECONDLINE>
     <Q3:CODE>SO21 2JN</Q3:CODE>
     <Q3:CITY>Winchester</Q3:CITY>
     <Q3:COUNTRY>GB</Q3:COUNTRY>
    </Q3:ADAPTERADDRESS>
   </Q2:ADAPTERADDRESS>
   <Q2:CCODE>Dev</Q2:CCODE>
  </Q2:ADAPTERCUSTOMER>
 </Q1:ADAPTERCUSTOMER>
 <Q1:ObjectEventId>JDBCConnector_160ibm_events20101108020022.046</Q1:ObjectEventId>
</Q1:ADAPTERINVOICE>

Back to sample home