FAQ #15 - How to synchronize ADF Entity Objects with database changes in JDeveloper

Introduction

For ADF applications, as the database schema evolves, there will be a need to synchronize the corresponding Entity and View objects involved, to reflect the changes in the schema. This is a pretty straightforward task in JDeveloper; there are a few things to watch out however.

Main Theme

The task of synchronizing an Entity Object to its respective database table is done by simply right-clicking on the Entity Object in the Application Navigator and selecting Synchronize with Database...

Selecting to synchronize, will bring up the Synchronize with Database dialog where the actions that require synchronization, if any, will be displayed. For the HR schema, the screen below indicates that there are no synchronization actions for the Employee Entity Object since no changes were done to the EMPLOYEES table.

The consequences of the Entity and View Object synchronization differ for the following cases:

• when new fields are added to the database table
• when existing fields are removed from the database table
• when existing fields of the database table are modified

New fields are added to the database table

When new fields are added to the database table, the synchronization feature will determine the new fields and upon synchronization it will add them to the Entity Object. The screen below shows the effect of synchronizing the Employee Entity Object when a new field - MIDDLE_NAME, the employee's middle name - is added to the EMPLOYEES database table of the HR schema.

To reflect this change in the Employee Entity, you can click the Synchronize All (or Synchronize for selected changes) button. JDeveloper proceeds with the synchronization and a new attribute - MiddleName - is added to the Entity Object. This can be verified by reviewing the Attributes page of the Employee Entity Object.

In all cases, synchronization applies only to the Entity Object. The newly added attribute must be added manually to all View Objects that refer the synchronized Entity. This can be done by opening each View Object and in the Attributes page selecting Add Attribute from Entity....

This will bring up the Attributes dialog from where we can shuttle the new attribute from the Available to the Selected list. This is effect will add the new attribute to the View Object as well.

Existing fields are removed from the database table

When a field is removed from the database table, the synchronization feature will not actually remove the corresponding attribute from the Entity Object but instead will make the attribute transient. The screen below shows what happens when we synchronize the Employee Entity Object after we remove the MIDDLE_NAME field from the EMPLOYEES table.

After the synchronization process we can verify that indeed the MiddleName attribute has been made transient and remove it manually ourselves. The attribute indeed is not mapped to the removed table column. Instead it displays a "Synchronize:Fix_SQL" string in the column.

If the attribute is used somewhere else in the project, attempting to delete it will display a "Usages were found" warning message.

Clicking on the View Usages button will determine where the attribute is referenced and display these usages in the Log window. In this case it is used in the Employees View Object.

We need to ensure that we remove the attribute ourselves from everywhere else that is used in the project.


Existing fields of the database table are modified


The synchronization feature will also determine and synchronize the Entity Object for any changes to the precision of the database table fields. For example, after changing the size of the EMPLOYEE table LAST_NAME column from 25 characters to 30 and synchronizing we will see the following:

Reviewing the LastName attribute in the Employee Entity Attributes page reflects this change. The change is also propagated to all View Objects that reference the LastName attribute as it shown below:

The synchronization feature will also detect changes to a database table's column data type. In this case however, although the changes to column's data type will be reflected to the Database Column Type of the Entity Object, the change will not be reflected to the Entity Object's attribute type and you will have to make this change by yourself. To demonstrate this case we will add a VARCHAR2 column called PHONE_EXTENSION to the EMPLOYEE table and then change its data type to a NUMBER. In this case the synchronization feature determines the change in data type as it is shown below.

Reviewing the synchronization results however, we can see that although the change is reflected in the Database Column Type of the Entity Object's PhoneExtension attribute, it is not applied to the Entity's attribute type, which retains the old data type (String).

In this case we will need to make the change to the appropriate data type for the attribute ourselves. This also applies to all View Objects where the specific attribute is referenced.


Conclusion

The ADF Entity synchronization feature in JDeveloper comes pretty handy when updating your Entity Objects to reflect changes to their corresponding tables in the database schema. Be aware however, that depending on whether columns are added, deleted or modified, the synchronization feature will behave slightly different.

Until the next time, keep on JDeveloping!

FAQ #14 - How to test an Application Module for activation-safe in ADF 11g

Introduction

For ADF applications, Activation refers to the process of re-instating an Application Module (AM) from a stored snapshot. A recommended test practice for ADF applications is to ensure that AMs are tested for activation-safe. This FAQ will show you how to do so.

Main Theme

The intricacies of Application Module state management are quite complex and if we were to describe it in a brief paragraph and as simple as possible we would say:

"For each request, as AMs are allocated from a pre-defined pool, they become associated with specific sessions, acquire a transactional state and become referenced by specific Data Controls (DC). The AMs along with their acquired state are returned back to the pool as soon as the request is serviced. When all AMs in the pool are used, i.e. all of them have a state and are associated with specific sessions and DCs, it becomes necessary to store the state of one such AM to the database (Passivation) and then assign it to some other session. Activation is the reverse process of restoring the state of a previously passivated AM. Once it is activated, the AM has its transactional state restored and becomes re-referenced by its original session and DC."

Testing an AM for activation-safe ensures that the transactional state of the AM being activated is intact up to the moment when it was actually passivated. In order to do such a test, we need to force the AM to go through a passivation/activation cycle. We can accomplish this by disabling AM pooling. This is easily done by right-clicking on the application module in the Application Navigator and selecting Configurations... to bring up the Manage Configurations dialog.

Select the Local configuration and click Edit... to bring up the Edit Business Components Configuration dialog. Click on the Pooling and Scalability tab and deselect the Enable Application Module Pooling option.

Disabling Application Module pooling has the effect of setting the jbo.ampool.doampooling configuration property to false as it is shown below.

Because there is no AM pool in use, this in effect turns on the passivation/activation cycle forcing a Passivation to occur at the end of each request as the AM is de-referenced from the session and the DC. Activation takes place on the next request when the AM along with its transactional state is reconstructed from the passivation store - usually a database - and is re-referenced to the session and the DC in order to service the request. Once you setup debug logging in the console, the activation/passivation cycle becomes obvious by examining the log messages in the Log console. Observe what is happening as you do uncommitted changes to your view objects. Passivation XML records are generated for each request describing the data changes - the data before and after the changes. These records are written to the PS_TXN table of the passivation store.

The passivation store defaults to the database used by the AM database but it can be specified to a different database by setting the property jbo.server.internal_connection in AM configuration as it is shown below.

The passivation store can also be a file in the system file by setting the AM configuration property jbo.passivationstore as it is shown below. The file system location can be specified by setting the property jbo.tmpdir.

Testing your AMs for activation-safe is a must if you are using:

• transient variables
• bind variables
• private member variables in AMs, VOs or EOs
• custom data in the Session hashtable 

For a blog entry that examines the effects of the AM passivation/activation cycle on transient variables take a look at Andrejus Baranovski's Demystifying ADF BC Passivation and Activation.


Conclusion

Testing your AMs for activation-safe is a recommended practice especially for the cases mentioned above.

Until the next time, keep on JDeveloping!


References

Fusion Developer's Guide for Oracle Application Development Framework 11g Release 1 (11.1.1) B31974-03 [Section 39.10 Testing to Ensure Your Application Module is Activation-Safe]
http://andrejusb.blogspot.com/2010/01/demystifying-adf-bc-passivation-and.html

FAQ #13 - How to avoid common pitfalls related to expert mode queries in ADF 11g

Introduction

In FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g we have seen one of the pitfalls related to expert mode queries known as query wrapping and the unexpected result it may produce when dynamic criteria are added to the query at runtime. In this FAQ we will see some additional pitfalls related to expert mode queries and how to watch out for them.

Main Theme

Pitfall #1 - Unexpected query results due to query wrapping

Be aware that queries are wrapped when dynamic WHERE and ORDER BY clauses are applied at runtime and of the unexpected results this may have on the execution of your query. We talked extensively about this in FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g.

Pitfall # 2 - Query failure due to dynamic criteria referring to columns not in the original query

Due to query wrapping, dynamic criteria applied at runtime must refer only to columns in the SELECT list of the original query. This can be best demonstrated with the following example.

Let's create a Fusion Web Application (ADF) and a read-only view based on the EMPLOYEES table of the HR schema. We will base the view on the following query.

The view will select the employee id and the employee first and last name. Now let's add an additional attribute called HireDate mapped to the HIRE_DATE column of the EMPLOYEES table, which we will use to further refine the employees for a given hire date range and is not part of the query select list.

We will add dynamic criteria based on bind variables. So first we will create the bind variables inHireDateFrom and inHireDateTo

and then the view criteria based on the bind variables above.

Finally we will create an application module and add the view to its data model. After enabling debug information in the console and testing the application module using the Oracle Business Components Browser, we can observe that a JBO-27122 oracle.jbo.SQLStmtException is thrown when we apply the view criteria at runtime.

The query that was executed is also shown in the Log window:

As you can see, the query is wrapped. The WHERE clause column - the HIRE_DATE - is not referring to any of the SELECT list columns of the original inlined query, which is what actually is causing the exception. To solve this issue, we will need to either disable query wrapping, as explained in FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g, or to ensure that the column used by the dynamically added WHERE clause - the HIRE_DATE -  is added to the SELECT columns of original query.

Pitfall #3 - Row inconsistency errors due to SQL calculations that change entity attributes

This applies when switching an entity-based view object to expert mode and changing an entity-mapped column in the SELECT list expression to include an SQL calculate expression.

This can be demonstrated with the following example. Create an entity object based on the DEPARTMENTS table of the HR schema, a view object based on this entity and finally add the view object to the application module data model. Now, edit the view object and in the Edit Query dialog let's change it to expert mode by selecting Expert from the SQL Mode.

Notice that the query is now editable in the Query Statement edit box. Let's assume now that we have a requirement to display only the first word of the department name and we decide to introduce an SQL-calculate expression for the DEPARTMENT_NAME column as shown below.

When testing the view with the Oracle Business Components Browser at a first glance we will see that the query works fine, returning the first word of the department name just as expected. The problem appears as soon as we attempt to modify a row - a row that the department name has been altered by the SQL-calculate expression we introduced. Try modifying for example department 210 - IT Support. You will get an oracle.jbo.RowInconsistentException: JBO-25014: Another user has changed the row with primary key oracle.jbo.Key[210 ] exception.

This exception is thrown because the department name value in the entity cache - the first word of the department - and the department name value retrieved when locking the row for update - the whole department name - differ. They are indeed inconsistent.

The solution to this pitfall is not to actually alter the entity-mapped column but rather to introduce a new SQL-calculated attribute to fulfill the specific requirement.

Pitfall #4 - Changes to SQL expressions are not reflected in the SQL statement

Basically be aware that the changes you do to the SQL expression in the attribute definition are not carried over to the SQL statement. This is not done by accident - you will need to update the SQL statement yourself to reflect these changes in the attribute.

Pitfall #5 - Limited attribute mapping

Each time you modify the SQL statement SELECT list in expert mode JDeveloper will attempt to remap the view attributes to the query SELECT columns. You will need to ensure that the attribute mappings are correct. To ensure that this is the case, for expert mode queries JDeveloper enables the Attribute Mappings page in the Edit Query dialog.

All of the above and more are very well documented in section 5.9 - Working with View Objects in Expert Mode of the Fusion Developer's Guide for Oracle Application Development Framework 11g Release 1 (11.1.1) B31974-03 documentation.


Conclusion

Using expert mode queries gives you full control of the SQL query definition in ADF BC. This comes with a cost. So, when using expert query mode, observe and navigate away from the pitfalls associated with it.

Until the next time, keep on JDeveloping!


References

Fusion Developer's Guide for Oracle Application Development Framework 11g Release 1 (11.1.1) B31974-03 [Section 5.9 Working with View Objects in Expert Mode]


Code

http://jdeveloperfaq.googlecode.com/files/JDeveloperFAQNo13.rar

FAQ #12 - How to disable query "wrapping" for expert mode queries in ADF 11g

Introduction

In ADF, when using read-only views, query "wrapping" refers to nesting of the original query into an inline view. In some cases, query "wrapping" could produce unexpected results and should be disabled. This FAQ shows you how.

Main Theme

When creating read-only view objects where the query is entered manually by the developer, ADF wraps the original query during its execution at runtime and then applies additional view criteria if needed. This is necessary because the original query could be complex enough - for example combining multiple queries using a UNION operator - and simply applying the view criteria directly on the query it will apply it only on the last UNIONed statement. Let's take a closer look and illustrate with an example.

Start JDeveloper and create a sample Fusion Web Application (ADF). Right-click on the Model project and select New.... From the New Gallery dialog select View Object from the ADF Business Components category under Business Tier.

In the Initialize Business Components Project dialog create a new connection to the HR schema, select it and click OK.

In the Create View Object - Step 1 of 9 page, enter the name of the View Object, select Read-only access through SQL for the data source and click Next.

In the Create View Object - Step 2 of 9 page, enter the query directly as shown below.

Click Next for all other pages and in the last page of the Create View Object wizard click Finish to create the View Object.

Now create an Application Module and add the View Object created above into the data model.

Finally, add debug logging to the console - refer to FAQ #2 – How to enable debug information generation for an ADF BC project, right-click the application module and run it. In the Oracle Business Component Browser, double-click the Employees view object in the tree to execute its query. In the Log window observe that the query that was executed is identical to the one we entered when we created the view object. No query wrapping.

The framework will introduce query wrapping once a dynamic WHERE clause is applied to the query at runtime. To simulate this, we will refine the query results by creating view criteria based on a bind variable. In the Query page of the view object add a bind variable as shown below.

Then add the View Criteria based on the bind variable we just created as shown below.

Now run again the Oracle Business Component Browser by right-clicking on the application module and selecting Run. Double-click on the Employees view to execute it and then apply the view criteria by clicking on the Specify View Criteria icon.

Shuttle the view criteria to the Selected list and click Find in the Business Component View Criteria dialog.

In the Bind Variables dialog specify a department identifier and click OK to apply the view criteria.

Observe the Log window as the view executes the query with the view criteria applied. The original query is wrapped.

This is OK until a new requirement is introduced for the Employees view to produce the total salary amount for the department. Piece of cake you say. So we edit the query and we add a sum on the salary.

We save the query and we run the Oracle Business Components Browser once more. We apply the view criteria for the specific department as before only to find out that the department salary produced is not specific to the department but the grand total of all salaries. This becomes obvious once you take a look at the query in the Log window. The original query is wrapped and the view criteria applied on the inlined view.

Resolving this issue will require us to disable query wrapping. This is pretty straightforward. The framework supplies a function called setNestedSelectForFullSql() which accepts a true/false boolean to enable/disable query wrapping respectively. So, we generate a java implementation class for the view object and call setNestedSelectForFullSql() with false in the constructor.

Now we can re-run the Oracle Business Components Browser and verify the expected results. Re-applying the same view criteria, this time the result is as expected.

Observe that no query wrapping occurs this time around.

Conclusion

Query wrapping is introduced by the ADF framework for expert mode queries in read-only views when a dynamic WHERE clause is applied at runtime. In some cases, when this produces unexpected results, we can call setNestedSelectForFullSql() with false as an argument to disable it.

Until the next time, keep on JDeveloping!

Code
http://jdeveloperfaq.googlecode.com/files/JDeveloperFAQNo12.rar