In one of my older blogs “Dump VO query and it’s parameter with their values”, I described a technique on how to print or dump out a query and its parameters to the log when the query gets executed. It turned out that the method provided in the old blog did not print out all VO query parameters. This update shows what’s missing and how to get the missing VO query parameters printed out too.

Sample Application

We use a simple sample application (application module only) to show the problem. The sample application can be downloaded from GitHub by following the instructions at the end of the blog.

The data model for the sample is pretty simple. It just used the HR DB and the employee, department, region, and county tables. 

From the old blog, we start by using the existing method to dump the query parameter ‘dumpQueryAndParameters()’ and create a new base class implementing ViewObjectImpl class

public class BaseViewObjectImpl extends ViewObjectImpl 

Using this base class we overwrite the executeQueryForCollection(…) method of the ViewObjectImpl class. In the executeQueryForCollection(…) we call the dumpQueryAndParameters() to print out the query and its parameter.

@Override
protected void executeQueryForCollection(Object object, Object[] object2, int i) {
    dumpQueryAndParameters(object2);
    super.executeQueryForCollection(object, object2, i);
}

Let’s look at the output when you use the ApplicationModuleTester and execute the EmployseeView1 query by double-clicking the VO

You get this message in the log:

+++query+++ SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees

So it’s just the query without any parameter. We can use QBE (Query by Example) in the ApplicationModuleTester by clicking the binoculars in the toolbar and enter e.g. ‘K%’ to the LastName and ‘>3000’ to the salary field

Clicking on ‘Find’, we get all employees with the last name starting with ‘K’ and earnings greater than 3000. The log will print

+++query+++ SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE ( ( (Employees.LAST_NAME LIKE :vc_temp_1 ) AND (Employees.SALARY > :vc_temp_2 ) ) )
+++Variables:
  +++ Name: vc_temp_1 Value: K%
  +++ Name: vc_temp_2 Value: 3000

We see the query and the automatically added two bind variables for the LAST_NAME and SALARY.

Fine, now you ask: What’s missing?

Missing Parameters

The missing parameters come to play if you use a ViewLink ‘EmpDepFKLink1’ like if you look at employees of a department

+++query+++ SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE Employees.DEPARTMENT_ID = :Bind_DepartmentId
+++Variables:

We see a query parameter ‘:Bind_DepartmentId’ in the query, but nothing is printed in the log for this parameter.

When you add the QBE for LastName and Salary to the employees like before you see this log message

+++query+++ SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE ( ( (Employees.LAST_NAME LIKE :vc_temp_1 ) AND (Employees.SALARY > :vc_temp_2 ) ) ) AND Employees.DEPARTMENT_ID = :Bind_DepartmentId
+++Variables:
  +++ Name: vc_temp_1 Value: K%
  +++ Name: vc_temp_2 Value: 3000

Still nothing about the parameter ‘:Bind_DepartmentId’ is shown in the log. The parameter is the one defined by the ViewLink between the two tables

Whenever we follow such a ViewLink the query part defined in the ViewLink is added to the query to join the tables. 

New Method

The name and the value of these parameters are not picked up by the original dumpQueryAndParameters() method. These parameters are passed to the method

protected void executeQueryForCollection(Object object, Object[] object2, int i)

in the array of Objects passed as the second parameter.

The new dumpQueryAndParameters(…) method looks into this array of Objects. Actually, the array holds NVPs (Named Value Pair)

// checking view link related parameters
if (params != null && params.length > 0) {
    for (int jj = 0; jj < params.length; jj++) {
        Object[] nvp = (Object[]) params[jj];
        // avoid duplicate printout of hte variable. Don't print if already print via variable manager
        if (lEnsureVariableManager.lookupVariable((String) nvp[0]) == null)
            System.out.println("  --- Name: " + nvp[0] + " Value: " + (nvp[1] != null ? nvp[1] : "null"));
    }
}

Each element is in turn an array of two elements, first the name and second the value of the named parameter. Getting the needed data, we take the name and use the variable manager of the VO to check if the given NVP is indeed a bind parameter. If yes we print the name and the value for the log. The full method can be found in the sample you can download from GitHub.

Final Run

Doing the same test as in the ‘Sample Application’ section we now get

---query--- SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE Employees.DEPARTMENT_ID = :Bind_DepartmentId
---Variables:
  --- Name: Bind_DepartmentId Value: 10

Clicking the view link EmpDepFKLink1. We see the bind parameter for the DEPARTMENT_ID printer with the query.

For the second test (employees with the last name starting with ‘K’ and salary > 300) we get

---query--- SELECT Employees.EMPLOYEE_ID,         Employees.FIRST_NAME,         Employees.LAST_NAME,         Employees.EMAIL,         Employees.PHONE_NUMBER,         Employees.HIRE_DATE,         Employees.JOB_ID,         Employees.SALARY,         Employees.COMMISSION_PCT,         Employees.MANAGER_ID,         Employees.DEPARTMENT_ID FROM EMPLOYEES Employees WHERE ( ( (Employees.LAST_NAME LIKE :vc_temp_1 ) AND (Employees.SALARY > :vc_temp_2 ) ) ) AND Employees.DEPARTMENT_ID = :Bind_DepartmentId
---Variables:
  --- Name: vc_temp_1 Value: K%
  --- Name: vc_temp_2 Value: 3000
  --- Name: Bind_DepartmentId Value: 80

Again, in addition to the LAST_NAME and SALARY parameters, we see the bind parameter for the view link.

Sample Download

You can download the complete sample from GitHub BlogDumpQueryParameter2. The sample application was built using JDeveloper 12.2.1.4 but the new dumpQueryAndParameters_new(…)The project uses the HR DB schema and only provides a model project!