OSB - Rollback transactions in ForEach in case of error

Problem: In a ForEach, there is a service callout to a stored procedure which inserts/updates a row. If during that ForEach, there is an error when inserting/updating one of the rows, the whole transaction has to be rolled back.

Solution: In OSB 12c, go to the SB Console and go to the Pipeline configuration which has the ForEach activity. In Message Handling, under Transaction, select Transaction Required. This will cause for all of the inserts/updates to be rolled back if there is an error during one of them. Also, in our stored procedure, the exception is not handled and is instead re-thrown so that way OSB will catch it.

As a request, I send in 5 IDs. The Stored Procedure is programmed so that on the 5th ID, it should throw an error.

Nothing is inserted in the table after the error.

In our case, the Service Callout is in the response pipeline. Anything that is inserted in the request pipeline will be commited, and so the response pipeline has a different transaction and won't affect the transaction in the request pipeline.

OSB - fn-bea:execute-sql in XQuery Transformation

Problem: In an XQuery transformation file, an SQL query needs to be run to transform a date into the acceptable ISO date format to insert it into the database. Unlike the query-database function used in the SOA engine for XSLTs, the fn-bea:execute-sql returns the result enclosed in two XML elements. For example:

fn-bea:execute-sql('jdbc/DataSource',xs:QName('Element'),'select 123 result from DUAL')

Returns the value as such:

<Element>
    <RESULT>123</RESULT>
</Element>

This isn't really helpful when you want to just get the result itself (without surrounded elements) to use it in an XQuery transformation.

Solution:
I had to create a local function that would only return the data (in the above example, return 123). The $input is the SQL query that needs to be run.

declare function local:stringToDate($input as xs:string){
    let $value:=fn-bea:execute-sql('jdbc/DataSource',xs:QName('Element'),$input)
    return fn:data($value/RESULT)
};

In this case, the SQL query I needed to run to transform the string date (e.g. 03/16/17 9:17 PM) into the ISO date format was:

select to_char(to_date('03/16/17 9:17 PM', 'MM/DD/YY HH:MI AM'),'YYYY-MM-DD"T"HH24:MI:SS') from dual;

And then I had call it as such:

<ns2:date>{local:stringToDate(fn:concat('select to_char(to_date(''',$inDate,''',''MM/DD/YY HH:MI AM''),''YYYY-MM-DD&quot;T&quot;HH24:MI:SS'') result from dual'))}</ns2:date>

OSB - Processing a SOAP inbound attachment

Problem:
A SOAP service returns a XOP/MOTM attachment as a zip file in its response. This must be unzipped to obtain the XML document inside and then write that file to a folder.

Solution:
There are a bunch of blogs already which describe how to retrieve a XOP/MOTM attachment from a web service response, but why not document my process as well.

The Business Service must first be edited so that XOP/MOTM is enabled, which is disabled by default:

There are two options here as to how to handle the attachment. The difference is explained in this blogpost:

Include Binary Data by Reference: (Default) In an outbound response message, replace xop:Include elements with ctx:binary-content elements when setting up the $body message context variable. 

Include Binary Data by Value: In an outbound response message, replace xop:Include elements with Base64-encoded text versions of corresponding binary data when setting up the $body message context variable.

In my case I used Reference since I didn't really have to validate the XML or do anything else with it. This is how the web service would return its response:

So to get the binary-content element and pass it directly to the Java callout as a byte array, you have to select the whole binary-content element like so:

$body/typ:getDocumentsForFilePrefixResponse/*:result/*:Content/*:binary-content

The Java class which takes care of unzipping the file and returning the contents as String can be found in this blog post: An example of how to process a zipped file in OSB by Mike Muller. Putting it here as well for future reference:

To be able to write it to a file, all that's left is to call the encode method in the Java class, passing the result of the first Java callout to this method. This will transform the XML (as string) into base64 which will allow you to invoke the File adapter to write the file.

OSB - Using DVM in 12c using qualifiers

To use a DVM in OSB 12c which requires qualifiers, specify the function as such:

dvm:lookupValue(dvm-location, src-column, src-value, target-column, default-value, qualifiers[])

dvm:lookupValue('DVMQualifiers/Proxy/dvm-cities', 'CityCode', 'KN', 'CityName', 'NA', ('Country','USA'))

FYI - JMS Polling and Error Queues

FYI:

When polling a JMS queue in OSB, in order for it to go to the configured error queue, an error must be raised. Raising an error and then handling this error won't cause the message to be sent to the error queue. If the error is handled, then a fault must be raised again.

Take this simple proxy service as an example that polls from a JMS queue configured in weblogic and calls a stored procedure to insert the polled record:

EmployeePS Pipeline:

Should an unexpected error occur somewhere in this pipeline, it will be caught by the error handler. The error will be reported in the EM Message Reports but the error is raised again so that it can be sent to the error queue.

Doing a Reply with Failure will NOT send the message to the error queue. As explained by AbhishekJ here:

In case of JMS transport the JMS Queue or Topic will receive a NACK back but the message will NOT roll back and will be removed from the destination just like in case of Reply with Success. This happens because using a Reply action is considered as an exception being handled properly and a commit will be sent back to the JMS destination. To roll back the message ensure that you use Raise Error instead of a Reply action.

See also:
Transaction handling within OSB