Refreshing Connection Pool via MBeans

You can easily refresh the connection pool via Enterprise Manager. If you look at MBean Browser (for more information please look at my earlier blog on JMX http://chintanblog.blogspot.com/2007/12/it-is-all-about-jmx-i-worked-on-jmx.html, it is very comprehensive blog) you can search for your Connection Pool it provides management interface to refresh the connection pool as well. I believe EM internally must be using MBean interface.

Anyways, here is programmatic way of accessing MBean related to Connection pool and doing refresh using MBean client API.

To get Mbean Name, you can look at MBean Browser as displayed here. You can also see it supports testConnection, refreshConnection, etc.. methods.

You can see Mbean name and method name in the Mbean browser, you invoke here if you want also.


Here is the code to execute this method using Java API:

Here MBean name and method name I have got it from System MBean Browser. This code works great and does invoke the refreshConnectionPool method.


Testing the connection pool refresh

To replicate the scenario where connection pool get broken and remain broken without calling my refresh method was quite tricky to figure out. I tried multiple different scenarios and here is the one which worked great and as expected.

Here is the use case and steps I came up with:

1) Start database and create connection pool and data source
2) Test the connection pool using Mbean browser "testConnection" method
Expected Result: it should work without any isuse.
3) Stop database and Start the database
4) Test the connection pool using Mbean browser "testConnection" method
Expected Result: it should not work, and should show error (at least for a while) because of stale connection
5) Stop database and start the database
6) Run refresh Mbean Client routine
7) Test the connection pool using Mbean browser "testConnection" method
Expected Result: it should work without any issue.

Execution of the usecase:

Step 1) Start database and create connection pool and data source:
I created connection pool called TestPool which points to Test Schema

Step 2) Test the connection pool using Mbean browser "testConnection" method:
As show below it was successful.

Step 3) Stop database and Start the database

Step 4) Test the connection pool using Mbean browser "testConnection" method
As we already know, testConnection method failed with four different error message each time we invoke test connection method. After these four messages, connection was successful.

Exception occurred testing connection. Exception: java.sql.SQLException: No more data to read from socket.
Exception occurred testing connection. Exception: java.sql.SQLException: OALL8 is in inconsistent state.
Exception occurred testing connection. Exception: java.sql.SQLException: Io exception: Software caused connection abort: socket write error.
Exception occurred testing connection. Exception: java.sql.SQLException: Closed Connection.

---

---

---

Step 5) Stop database and start the database

Step 6) Run refresh Mbean Client routine

Step 7) Test the connection pool using Mbean browser "testConnection" method

This time it just works fine...


Code for MBean Client can be downloaded from here.

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.

File Size from File Adapter

Well, reading/writing file name and file directory are very well documented in File Adapter guide and blogs. Just to recap, in order to read/write file name and directory you are supposed create a header variable of element InboundFileHeaderType (it will be different if it is outbound header variable). The WSDL file is created when you create FileRead or FileWrite operation.

After you create Variable, we just need to specify that variable as part of Header variable in Adapter.

That's all required, and during file polling you will get the fileName and directoryName already populated.


Wait a minute, but if you look at Variable_FileHeader at runtime in Assign Activity, you can see 5 elements are populated rather than 2. E.g.

It means, this header variable is populating 5 elements, where wsdl file is exposing only two! How to get remainder three, or let;s say SIZE which is more interesting to all of us.


Two ways:

1) Hack the wsdl file and put size as one of the element as shown below. (Note fileAdapterInboundHeader.wsdl file is created with ReadOnly attribute so you have to change the attribute prior to modification).

After doing this size attribute is populated and you can assign to any other variable if you like.

2) This is non-intrusive way. As we know that Header Variable (XML Element) already has size, it is just matter of extracting it, so why not to convert Header Variable in String and do string search? e.g. I did following assign activity to get the size and assign it to some string variable called Variable_File_Size. You can see, that I am converting xml element to string and then doing search for string between <size> </size>.

Things didn't end here, as somebody asked me that how to know how many elements/attributes are really supported. I couldn't find the documentation anywhere, so back to the JAD, and I could find the file name called "oracle/tip/adapter/file/FileAgent.java" which had following lines of code:

Based on code, it looks like variables are just getting inserted on the fly, as it is not JAXB but just being treated as DOM element. Ofcourse, it showed me a way what I need to do if I need to insert my custom header in File Adapter.


Here is the entire code used for sample.

Business Rules WebDav Repository

I was used to configure WebDav on Oracle Database for Oracle Business Rules, it was pretty hard to configure and very unstable. Recently found out that I can configure WebDav on Oracle Apache.

Usually to install WebDav on vanilla Apache server, we need to install couple of dll file and load those module during Apache startup, but for Oracle Apache all configuration are done out-of-the-box, only thing we need to specify is location and type of repository.


Configuration file:

%soasuite%/Apache/oradav/conf/moddav.conf


To enable default WebDav repository:

<Location /dav_public>
DAV on
</Location>

Once you specify on, http://host:port/dav_public (which located under %soasuite%/Apache/Apache/htdocs/dav_public is ready for use as webdav repository.


To create new WebDav repository:

1) Add following entry in %soasuite%/Apache/oradav/conf/moddav.conf file

<Location /my_webdav_repository>
DAV on
</Location>

2) Create directory called %soasuite%/Apache/Apache/htdocs/my_webdav

3) Restart the Apache server and that's all required for configuring custom repository.


To create password for WebDav repository:

1) Create authentication file with different user, I created

%soasuite%/Apache/Apache/bin/htdigest -c %soasuite%/Apache/oradav/conf/webdav.access webdav-authentication oc4jadmin
%soasuite%/Apache/Apache/bin/htdigest %soasuite%/Apache/oradav/conf/webdav.access webdav-authentication ruleauthor

2) Change %soasuite%/Apache/oradav/conf/moddav.conf to provide the authentication mode:

<Location /my_webdav_repository>
DAV on
AuthType Digest
AuthName "webdav-authentication"
AuthDigestFile %soasuite%/Apache/oradav/conf/webdav.access
Require valid-user
</Location>

3) Restart the server and now my_webdav_repository will be accessible only after username and password authentication.

Flex Field Mapping Migrator

Nothing special, just putting together some BPEL client API to create useful utility for BPEL Human Workflow migration.

Flex field mappings are very useful for creating custom views/queues and reports. Flex-Field mapping in worklist are persisted in Workflist Schema with MD5 encoded guid. It is not advisable to promote them from one server to another server via database scripts. BPEL workflow client API supports extracting, creating and deleting such mappings for worklist application.

Here is the utility which I created by using worklist client API, which I have released it as JAR file, and I wrote wrapper BAT file to execute the command.

As mentioned earlier I support three COMMANDNAME export, import and clean. Export will generate workflowmappings.xml file in the current directory and import will take workflowmappings.xml file and import all the payload mappings to the target server. My import program is not very intelligent, it cleans all the mapping on target server and creates them according to workflowmappings.xml file, it is not smart update.

It can be downloaded from here.