Finding the Compromise – the Adapter Framework - Applied SOA Patterns on the Oracle Platform - page 417

Dynamic Adapters implementation and DB
Transport Adapter
Now we have everything we need, we can go to the core skipping the implementation tech-
nique details explained earlier. What we do want is to execute any SQL statement on any
SQL (Oracle) database in our infrastructure and we would like to do it in a dynamic way
based on the information provided in the message container shown as follows:
1. Probably the simplest way to execute the dynamic SQL statement is to use the
execute-sql XQuery function directly in the Proxy Message flow, in a re-
quest pipeline, presented as follows. You will need two input parameters; one for
the query, and another for DB source name. Although lightweight and simple as is,
this method has some limitations—we can execute only single statement and only
SELECT . The last one is quite a considerable drawback as it would be quite diffi-
cult to execute complex transitions (although possible, depending on your SQL
skills). We saved the following code as a standalone XQuery script and will use it
further in our flow:
Declare variable $database as xs:string external;
declare variable $query as xs:string external;
declare function xf:xq_runQuery($database as
xs:string,
$query as xs:string)as element(*) {
<out>
{fn-bea:execute-sql($database, xs:QName('rows'),
$query)}
</out>
};
xf:xq_runQuery($database, $query)
2. Secondly, we can implement the Session bean with one main method, ex-
ecuteSQL , demonstrated as follows. The EJB implementation is pretty straight-
forward and EJB will be invoked using the EJB Transport, similar to what we dis-
cussed earlier. Again, it will accept a datasource and a SQL statement list. After es-
tablishing the connection, it will loop over the SQL statements in the same transac-
tion. If one transaction fails, all of them will be rolled back: