# Using ad-hoc queries with the DB Adapter

A BPEL White Paper
Written by Olivier Le Diouris, Oracle Corporation
Setpember, 2005

- Introduction
- The regular path
- The trick and tips
- Errors to avoid
- Resources

## Introduction | Top

When a Database operation - select, update or insert, PLSQL function or procedure call - is to be performed from a BPEL process, the way to go is to use the DB Adapter that comes with the BPEL Process Manager and Designer.

This DB Adapter uses Toplink for the Object-Relational mapping, and exposes the generated objects using WSIF - like other Adapters do.

Toplink is providing a thick abstraction layer, hiding whenever it is possible the complexity of the SQL or PLSQL statements to perform in the database.

However, it is sometime necessary to open the hood and implement a SQL statement.
Like for example:

     SELECT SCOTT.EMP.EMPNO,
            SCOTT.EMP.ENAME,
            SCOTT.EMP.JOB,
            SCOTT.EMP.MGR,
            SCOTT.EMP.HIREDATE,
            SCOTT.EMP.SAL,
            SCOTT.EMP.COMM,
            SCOTT.EMP.DEPTNO,
            SCOTT.DEPT.DNAME
     FROM SCOTT.EMP,
          SCOTT.DEPT
     WHERE SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO AND
           SCOTT.EMP.HIREDATE BETWEEN TO_DATE(UPPER('&dateFrom'), 'DD-MON-YYYY') AND
                                      DECODE(NVL(LENGTH(LTRIM('&dateTo')), 0), 0, SYSDATE,
                                             TO_DATE(UPPER('&dateTo'), 'DD-MON-YYYY'))

where dateFrom and dateTo are provided by the user. Generating an equivalent Toplink expression is quite a challenge. The BETWEEN, NVL, LENGTH, LTRIM, SYSDATE, UPPER, TO_DATE operators and functions are Oracle extensions to SQL, not handled by Toplink.
It is not unusual to have such pre-defined queries, which can be way more complex than in our example, and reusing them instead of defining or trying to define their Toplink equivalent would probably save quite some time.
This is the path we want to follow in this document, so that we take advantage of the wizards as much as possible, to generate the appropriate classes and the associated XML schemas that are going to match what the SQL statement would return.

## The regular path | Top

There is a sample coming with Oracle BPEL that explains how to customize the toplink_mapping.xml file to reach the goal we're targeting.
This example is located in [BPEL_HOME]\integration\orabpel\samples\tutorials\122.DBAdapter\PureSQLSelect
It is mostly about replacing some element(s) of the mapping file with a <sql-string> element, actually containing the SQL Query to execute.
This example is clear and well-done, we are not going to go through it again. But we are going to show how to implement its recommandations with minimum effort. Let's be lazy.

## The trick and tips | Top

It's all about minimizing the customization to toplink_mapping.xml, and the number of manual steps we will have to go through, so we minimize the risk of introducing errors.
In this example, we will only talk about a query (a Select statement) in the database.
When you use the DB Adapter Wizard, the following visible operations are performed:

- The table(s) and/or view(s) to work on are identified
- The appropriate columns of each of the identities selected above are selected
- Potential user parameters are created

Under the hood, the following actions (at least) are taken:

- Java classes corresponding to the data to retrieve are created
- Toplink mapping file (toplink_mapping.xml) is generated
  This file contains the mapping from SQL to Java, as well as the structure of the parameters and the potential Toplink expression - corresponding to some extend to the where clause of the SQL select statement.
- The XML Schema matching the returned structure is created.
  Mandatory for WSIF, extensively used by BPEL.

You certainly do not want to go through all those steps by hand...

The trick we used is the following one:

- We created a view in the database, returning the exact same structure as the SQL Statement we wish to execute, and ran the DB Adapter wizard on it.
- The SQL Statement itself could be used to create the view (with no user parameters, or with hard-coded ones), with an additional where clause like 1 = 2, to actually minimize the response time (some views with GROUP BYs, HAVINGs, etc, can take some time to execute specially if the user parameters are not correct).
  We used for this example a statement like this one:

       CREATE VIEW FOR_TOPLINK AS
       SELECT SCOTT.EMP.EMPNO,
              SCOTT.EMP.ENAME,
              SCOTT.EMP.JOB,
              SCOTT.EMP.MGR,
              SCOTT.EMP.HIREDATE,
              SCOTT.EMP.SAL,
              SCOTT.EMP.COMM,
              SCOTT.EMP.DEPTNO,
              SCOTT.DEPT.DNAME
       FROM SCOTT.EMP,
            SCOTT.DEPT
       WHERE SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO AND
             SCOTT.EMP.HIREDATE BETWEEN TO_DATE(UPPER('01-jan-1970'), 'DD-MON-YYYY') AND
                                        DECODE(NVL(LENGTH(LTRIM('31-dec-2010')), 0), 0, SYSDATE,
                                               TO_DATE(UPPER('31-dec-2010'), 'DD-MON-YYYY')) AND
             1 = 2;

- Then we modified the mapping file - we will see that the modification to make is now a minor one.
- And finally, we dropped the view.

### Walk through

Let's precisely describe the steps we went through, one by one.

#### Create the view in SQL*Plus

We use the statement mentioned above:
The view is created, and contains nothing.

#### Create the Partner Link in JDeveloper

Now, in JDev, where you have created a new BPEL Process, you add a Partner Link.
Choose to invoke the Adapter Wizard
We will obviously use the Database Adpater
Give the service a name. Remember that this name will be used to create the name of the WSDL file for this service. It has to be unique in this project at least.
You now need to refer to the connection where the recently created view can be found.
In our case, as we said, it will be a Select Statement
In the chosen connection, pick up the view we have just created.
Select the view in this pane
A primary key must be defined. It is very important to refer to a column that uniquely identifies the rows. Irrelevant data might be returned otherwise.
No relation is to be defined in our sample.
Review your work...
Here, you have the possibility to define parameters, define the two ones we already talked about, but do not create any expression, where they could potentially have been involved.
Review, and finish.
The Partner Link is created.

#### Customization...

After walking through the steps described above, JDeveloper has created several things:
Notice the following objects:

- The AdHocQueryService.wsdl file
- The Toplink mapping
- The ForToplink.java class

We no want to edit the toplink_mapping.xml file to modify it, manually.
Between <distinct-state> and <query-indirection>, insert the following element:

                         <distinct-state>0</distinct-state>
                         <sql-string>
     SELECT SCOTT.EMP.EMPNO,
            SCOTT.EMP.ENAME,
            SCOTT.EMP.JOB,
            SCOTT.EMP.MGR,
            SCOTT.EMP.HIREDATE,
            SCOTT.EMP.SAL,
            SCOTT.EMP.COMM,
            SCOTT.EMP.DEPTNO,
            SCOTT.DEPT.DNAME
     FROM SCOTT.EMP,
          SCOTT.DEPT
     WHERE SCOTT.DEPT.DEPTNO = SCOTT.EMP.DEPTNO AND
           SCOTT.EMP.HIREDATE BETWEEN TO_DATE(UPPER(#fromDate), 'DD-MON-YYYY') AND
                                      DECODE(NVL(LENGTH(LTRIM(#toDate)), 0), 0, SYSDATE,
                                             TO_DATE(UPPER(#toDate), 'DD-MON-YYYY'))
                         </sql-string>
                         <query-indirection>
                            <in-memory-query-indirection>
                               <policy>0</policy>

Notice the way to refer to a parameter, no quote, and a '#' in front.

#### Drop the view

The view is now useless, we can drop it.

#### Ready to go

Let's imagine we have created an activity to invoke the Partner Link, and created the two variables it needs in input for the BPEL Process, this would be what we have:
And after invoking the Partner Link, the output variable would be populated with the expected data:

## Errors to avoid | Top

#### Primary keys

This one actually does not stricly belongs to this test case, it is a general Toplink concern. The primary key has to be chosen in such a way that it identifies one and only one record returned by the query. If not, if the same key value is the same for n records, then n instances of the first record having this value for the key will be returned.

#### Null values for parameters

In our example, based on the where clause of the query, we can see that if the second parameter is not entered, the value for the date will be SYSDATE (current date).
To populate the invoke input variables, instead of an assign, we used a transformation to pass into toDate a blank value instead of no value, in order to avoid a NullPointerException from the BPEL Process Manager.

## Resources | Top

- The samples used in this document