Friday, 3 March 2017

JDBC from the Oracle Service Bus

There are different ways to do database calls from the Oracle Service Bus. In this blog post I look at several methods.

The methods looked at;
1 using an external webservice (without OSB)
2 using an external webservice proxied by the OSB
3 using the fn-bea:execute-sql function from the OSB
4 using the JCA DbAdapter from the OSB provided by Oracle as part of Oracle SOA Suite
Oracle Java Certifications, Oracle Java materials, Oracle Java Tutorials


Setup


Database call

In order to test the database call, I've created a function which returns systimestamp (the current time). This function is called with the previously described methods. A PL/SQL function was chosen as database call because the execute-sql function requires a select-statement to be performed. A function allows usage in SQL statements.

The PL/SQL code used was the following;

CREATE OR REPLACE 
PACKAGE TEST_PACKAGE AS 
  FUNCTION GET_DATE RETURN TIMESTAMP;
END TEST_PACKAGE;

CREATE OR REPLACE
PACKAGE BODY TEST_PACKAGE AS
  FUNCTION GET_DATE RETURN TIMESTAMP AS
  BEGIN
  RETURN SYSTIMESTAMP;
  END;
END TEST_PACKAGE;

Oracle Service Bus configuration


Because the OSB configuration is relatively straightforward, I will not explain it in detail here. In all methods, only one XQuery transformation was required. I could have chosen for XSLT transformations but the available examples for the usage of fn-bea:execute-sql where in XQuery so I decided to use that.

JDBC methods


External webservice

Oracle Java Certifications, Oracle Java materials, Oracle Java Tutorials

The Java code used was the following;
package getdatejava;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

@WebService
public class SimpleJDBCService {

    @WebMethod
    public String GetDate() throws Exception {
        String query = "select TEST_PACKAGE.GET_DATE from dual";
        Context context = new InitialContext();
        DataSource dataSource =
            (javax.sql.DataSource)context.lookup("jdbc/testuser");
        Connection con = dataSource.getConnection();
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        Timestamp result = null;
        while (rs.next()) {
            result = rs.getTimestamp(1);
        }
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'hh:mm:ss");
        String res = dateFormat.format(result.getTime());
        //con.close();
        return res;
    }
}

If I didn't close the connection explicitly (con.close() in the above sample) I got a lot of the following exception;

weblogic.common.resourcepool.ResourceLimitException: No resources currently available in pool testuserDS to allocate to applications, please increase the size of the pool and retry.

Because I'm manually programming out the Java JDBC code, the code is specific to a query/call. The Java code requires changes if the query changes. Also for every new query, a new service needs to be created.

fn-bea:execute-sql


Oracle Java Certifications, Oracle Java materials, Oracle Java Tutorials

In order to use the XPath function and generate the resultmessage, I used the following XQuery code.

(:: pragma bea:global-element-return element="ns0:processResponse" location="../xsd/GetDate.xsd" ::)

declare namespace ns0 = "http://xmlns.oracle.com/HelloWorld/GetDate/GetDate";
declare namespace xf = "http://tempuri.org/TestProject1/xquery/query_XQ/";

declare function xf:query_XQ()
as element(ns0:processResponse) {
    <ns0:processResponse>
    <ns0:result>
    {fn-bea:dateTime-to-string-with-format("dd MMM yyyy hh:mm a G", (
    fn-bea:execute-sql(
    'jdbc/testuser', 
    xs:QName('ns0:result'), 
    'SELECT test_package.get_date from dual')/GET_DATE/text()))
    }
    </ns0:result>
    </ns0:processResponse>
};

xf:query_XQ()

As can be seen, a datasource is used directly in the call to execute-sql. Since I'm using a select statement, there are several limitations. Every action has to be wrapped in an SQL statement. More complex logic can (as workaround) be wrapped by an SQL function which in an autonomous transaction performs actions. Such a construction is not a nice way of doing things like this. Hardcoding the datasource and the query in an XQuery transformation might not be desirable for maintainability. It is however relatively fast. In the methods tested, this is about just as fast as directly using a Webservice which does the JDBC.

DbAdapter


Oracle Java Certifications, Oracle Java materials, Oracle Java Tutorials

In order to call the DbAdapter from the OSB, JDeveloper was used to generate the required files; JCA file and schema's. In the OSB, an assign (for the call to the adapter) and a transformation for the response message was required.

The DbAdapter supports transactions and provides several options for handling them. Also it allows various Db actions to be performed such as inserts and procedure calls. PL/SQL objects are also supported. It is even possible to use a custom SQL statement which can be changed at runtime in the Enterprise Manager.

Performance measures


For  completeness I've also added performance measures of a BPEL process using the DbAdapter. The performance test was executed in SOAP UI with the settings as specified below.

Oracle Java Certifications, Oracle Java materials, Oracle Java Tutorials

The below list summarizes the results. Fastest is on top, slowest at the bottom.

- JAX-WS web-service with JDBC (method 1)
- OSB with XQuery (method 3)
- OSB with DbAdapter (method 4)
- JAX-WS web-service with JDBC proxied by OSB (method 2)
- BPEL no audit logging
- BPEL audit logging production