ADF Entity Track Change History Attributes and MySQL Database

Hello all.

Oracle ADF can be used with other databases vendors rather than Oracle, however some out-of-the-box functionalities may not work properly and you'll find yourself looking for workarounds to make them work. Today, we are going to see how we can use the Track Change History properties (CreatedBy, CreatedOn, ModifiedBy, ModifiedOn) of a Business Components Entity when using MySQL.

What you need

JDeveloper 12c

MySQL 5.6.4 or higher

Configure MySQL for ADF

Before we start, you need to know the following:

• In MySQL 5.5 and higher, InnoDB is the default storage engine. The benefits of this are: ACID Transactions, Referential Integrity, and Crash Recovery.
• Previous versions of MySQL do NOT store fractional seconds (milliseconds) into columns of any temporal data type. More info on this, here.
• From version 5.6.4, MySQL stores fractional seconds (milliseconds) into columns of any temporal data type. More info in this, here.
• You can define how many milliseconds to store from 0 to 6.

To start, execute the following script in MySQL database:

CREATE  TABLE `Player` (
  `idPlayer` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(150) NOT NULL ,
  `age` INT NOT NULL ,
  `created_by` VARCHAR(50) NULL ,
  `created_on` DATETIME(3) NULL ,
  `modified_by` VARCHAR(50) NULL ,
  `modified_on` DATETIME(3) NULL ,
  PRIMARY KEY (`idPlayer`) )
;

You can see that we are creating a table with an autoincrement primary key. We have seen before, a workaround about having autoincrement columns in MySQL to work with ADF, but we are not using it in this post. 

Also, notice that we are defining that the DATETIME columns will have 3 milliseconds. Once you have executed the script, go to File->New->Business Components From Tables and follow the wizard to create an Entity of the table Player, a default ViewObject and an Application Module. Remember to configure the SQL Platform as SQL92 and Data Type Map as Java:

JDeveloper will map the DATETIME columns as Timestamp fields in the Entity, which is fine since we want to store milliseconds:

Configure each history attribute as one of: Created On, Created By, Modified On, Modified By and notice that when you configure an attribute as a Track Change History Attribute, JDeveloper will select default values for other properties:

Once all the Track Change History Attributes are set, you can run the Application Module in order to test the functionality. However, you may find the following error when trying to commit to the database:

Current time is not available from SQL statement "select sysdate from dual".: Unknown column 'sysdate' in 'field list'...

This is happening because the Application Module tries to get the current date from the database using Oracle's statement: "select sysdate from dual", which is not valid in MySQL. In order to change that, go to your Application Module and edit the LOCAL configuration:

A pop up opens, go to the Properties section and look for a property called: jbo.sql92.DbTimeQuery

We need to change the value of that property to something that works on MySQL. There are many DATE related functions in MySQL, here we are going to use NOW(). So, change the value of the jbo.sql92.DbTimeQuery to:

select NOW() from dual

Click OK button and save your changes.

CAUTION: At the time of this writing, JDeveloper is using mysql-connector-java-commercial-5.1.22-bin.jar as the default MySQL library. However, when I tried the above configuration using that library, it didn't work because no milliseconds information were being stored into the database. The solution is to update the MySQL driver. You can find the official MySQL connector drivers at: http://dev.mysql.com/downloads/connector/j/

If you don't update the MySQL driver, you may experience the following error when trying to modify a row (this is the so called Phantom Error):

The following picture was taken when using the default MySQL library, notice that there is no milliseconds information saved into the database:

And here is the picture after using the latest MySQL connector, notice the milliseconds information:

When you have downloaded the new version of the driver, you have to remove the default MySQL library from your project and add the new jar. Go to your project Properties:

Run the Application Module again, create or modify rows and this time it should work.

That's it, with the above configuration you are now ready to use the Track Change History functionality when using MySQL database.


see ya!


References

MySQL 5.6 Reference Manual :: 12.7. Date and Time Functions. MySQL [online].
Available on Internet: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html
[accessed on February 09 2014].

MySQL 5.6 Reference Manual :: 11.3.6. Fractional Seconds in Time Values. MySQL [online].
Available on Internet: http://dev.mysql.com/doc/refman/5.6/en/fractional-seconds.html
[accessed on February 09 2013].

MySQL 5.6 Reference Manual :: 14.2.1.1. InnoDB as the Default MySQL Storage Engine. MySQL [online].
Available on Internet: https://dev.mysql.com/doc/refman/5.6/en/innodb-default-se.html
[accessed on February 09 2013].