DB Adapter Tricks

1. Dealing with Special Characters in Table/Column name

Recently saw the issue on forum that Database does allow creating table or column with special characters inside the name e.g. $, #, etc.. Based on W3C standard (http://www.w3.org/TR/xmlschema-1/#cElement_Declarations) not all special characters are allowed in XSD element/attribute names. $ is certainly not allowed. Oracle Database adapter creates one-to-one mapping between database column/table name and element/attribute name.

Sample Table: I created table named "mytable" with following script:

create table mytable (
id int,
company varchar2(100),
my$comments varchar2(100),
processed varchar2(100),
processed_time date
);

a) Custom SQL : No toplink mapping is used, in this case conversion from $ value to _ is done automatically. Means, when DB adapter creates XSD file it creates attribute with my_comemnts. That's cool, no work.

b) Insert/Select operation: That uses Toplink mapping which is basically <<SERVICENAME>>_table.xsd file and <<SERVICENAME>>_toplink_mappings.xml file. We can change _table.xsd file and convert my$comments to my_comments as shown below:

Only these changes are required in order to make things work. The same thing can achieved for table name and other types of special characters.

---

2. Dealing with Dynamic Queries - Query By Example

For simple use case, you can always use custom query with multiple parameters e.g. "select * from TABLENAME where COL1 = ?1 and COL2 = ?2". It works perfectly fine and input parameters shows up in XSD and also in wsdl contract.

Sometimes, some richer query is required, usually the dynamic where clause. Here is the example:

sometimes, you need query, e.g. select * from tablename where col1 = ?1
sometimes, you need query, e.g. select * from tablename where col1 = ?1 and col2 = ?2

Here you can see the where clause is getting build dynamically. It is supported by Oracle DB adapter and it is called Query By Example. It was very well supported in 10.1.2. but somehow it got deleted by some Jdev developer, the good thing is that it is still supported at runtime and you can make small changes to SELECT operation to convert it into Query By Example.

What it does: It takes lets say object as input (e.g. Employee), and returns collection of Employee as output. Here the trick is, where clause is created based on Employee attribute you provide. So if pass null Employee object it would "select * from Employee" querey. If you provide Employee input with Company name "Oracle", it will return all employee matching company name oracle...

How to implement it:

I created DynamicQueries BPEL process and DBSelect partnerlink which does simple SELECT operation (not custom sql), and no parameters on table name Test. Now, we need to modify two files:

DBSelect_table.xsd: Create an Element called Test.

DBSelect.wsdl: Specify keyword called IsQueryByExample="true" in your JCA operation as shown below:

That's all it needs. You need to create input variable of Element "Test" and assign it to Partnerlink Input variable and deploy the process.

---

3. Inserting multiple records in Database in single transaction

Well, I know that is supported out of the box and nothing you need to do special to achieve it. If you create database adapter in your BPEL process with INSERT operation it is exposed as collection of object as Input. You can use XSLT to assign that Collection and all records will be inserted in one atomic transaction.


Working example for all three can be found here.