For multiple services in our OSB landscape we needed some sort of name-value pair lookup mechanism. What we didn’t want is excessive IF-THEN-ELSE statements in our OSB services, so in the end we decided to create:
- a function (local type Oracle Service Bus proxy service)
- use a simple database table from where we could externally retrieve the data
- access the database through the fn-bea:execute-sql() function.
So first things first:
Creating the table with SQL where we use (parent proxy) SERVICE and NAME as input to retrieve the required VALUE:
CREATE TABLE NVPTABLE ( "SERVICE" VARCHAR2(256 BYTE), "NAME" VARCHAR2(256 BYTE), "VALUE" VARCHAR2(256 BYTE) ) REM INSERTING into NVPTABLE Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','010','Rotterdam'); Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','020','Amsterdam'); Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','030','Utrecht'); Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','040','Eindhoven'); Insert into NVPTABLE (SERVICE,NAME,VALUE) values ('myParentService','073','Den Bosch');
In Weblogic we create a datasource, use jdbc/nvp_ds as JNDI name and target it to the correct OSB managed servers.
Then in our OEPE (or Notepad++) we create the following XML schema and use it in a WSDL as a nice datamodel for our soon-to-be function. As you can see the 2 elements for request and response are simple and logically match the database columns.
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:us01="http://rubix.nl/schemas/cdm/utility01" elementFormDefault="qualified" targetNamespace="http://rubix.nl/schemas/cdm/utility01"> <xs:element name="GetNameValuePairRequest"> <xs:complexType> <xs:sequence> <xs:element name="serviceName" type="xs:string"/> <xs:element name="inputName" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="GetNameValuePairResponse"> <xs:complexType> <xs:sequence> <xs:element name="resultValue" type="xs:string"/> </xs:sequence> </xs:complexType> </xs:element> </xs:schema>
Then create a local proxy service which uses the SOAP/WSDL. The message flow of our function (local proxy) can be simple and just use 1 XQuery transformation to do all the magic stuff:
And this is how the magic really looks like under the hood:
(:: pragma bea:global-element-parameter parameter="$getNameValuePairRequest1" element="ns0:GetNameValuePairRequest" location="../interfaces/util01.GetNameValuePair.xsd" ::) (:: pragma bea:global-element-return element="ns0:GetNameValuePairResponse" location="../interfaces/util01.GetNameValuePair.xsd" ::) declare namespace ns0 = "http://rubix.nl/schemas/cdm/utility01"; declare namespace xf = "http://tempuri.org/Local.GetNameValue/transformation/resultXQ/"; declare namespace funcRBX = "http://www.rubix.nl/local"; declare function funcRBX:runSql($inputA as xs:string, $inputB as xs:string) as xs:string { let $sqlstring := concat('SELECT value FROM NVPTABLE where SERVICE=''',$inputA,''' and NAME=''',$inputB,'''') let $result := fn-bea:execute-sql('jdbc/nvp_ds','result',$sqlstring) return $result }; declare function xf:resultXQ($getNameValuePairRequest1 as element(ns0:GetNameValuePairRequest)) as element(ns0:GetNameValuePairResponse) { <ns0:GetNameValuePairResponse> <ns0:resultValue>{ funcRBX:runSql($getNameValuePairRequest1/ns0:serviceName/text(),$getNameValuePairRequest1/ns0:inputName/text())}</ns0:resultValue> </ns0:GetNameValuePairResponse> }; declare variable $getNameValuePairRequest1 as element(ns0:GetNameValuePairRequest) external; xf:resultXQ($getNameValuePairRequest1)
The resultXQ function which does the XQuery transformation uses the custom funcRBX:runSql function (don’t forget to declare the namespace). This runSql function performs the following steps.
- First we concatenate the SQL-statement we want to use to run against the database. We do this to prevent some weird typecasting problems which can occur with the execute-sql function.
- Second we run the function and return the result. Using the let and return command, we prevent the default behaviour of the execute-sql function to return:
<result><VALUE>Den Bosch</VALUE><result>
Voila, you are finished and the final result will look like this:
Hope it helps.
References:
- how to properly use execute-sql() @ Java Mon Amour