Thursday Aug 29, 2013

ADF Desktop Integration Client Logging

In general, when we encounter errors, we often tend to look at logs to get detailed information. Users of ADF Desktop Integration can also enable logging in both client-side and server-side. In this blog we will have a look on how to enable client-side logging.

Let us assume an ADF application is created with Employees table using simple HR schema and ADFdi workbook is created and configured to use EmployeesView1 as ADF Table.

simple table-new.png

How to enable logging for all workbooks opened with installed client:

For Design time client, copy 'adfdi-excel-addin.dll.config' from "<JDEV_HOME>\jdeveloper\adfdi\bin\excel\samples to the directory mentioned under About ADF Desktop Integration dialog in Oracle ADF tab -> Properties tab -> Configuration property.

For Runtime client, copy 'adfdi-excel-addin.dll.config' from "<JDEV_HOME>\jdeveloper\adfdi\bin\excel\samples to the directory mentioned under About ADF Desktop Integration dialog in MyWorkbook tab -> Properties tab -> Configuration property and rename it as 'adfdi-excel-addin-runtime.dll.config'.

We will continue with the assumption that we have installed Design time client and hence will use adfdi-excel-addin.dll.config file.

In adfdi-excel-addin.dll.config file, we have an <add> tag inside <listeners> tag where we can set type, name, location and trace options of log file.
<add type="System.Diagnostics.DelimitedListTraceListener" name="adfdi-common-text-listener" initializeData="C:\temp\adfdi-common-excel.txt" delimiter="|" traceOutputOptions="ThreadId, ProcessId, DateTime"/>

type - determines type of logs. Logs can be obtained in 3 different formats:

  • System.Diagnostics.DelimitedListTraceListener - Default format. Logs will be obtained with the specified delimiter.
  • System.Diagnostics.XmlWriterTraceListener - Logs will be obtained in xml format.
  • System.Diagnostics.TextWriterTraceListener - Logs will be obtained in Text format.

name - name of the trace listener. Can be set to any value

initializeData - determines the path where the log file has to be generated. Valid path has to be mentioned.

delimiter - specified character is used as a delimiter when type is set to 'System.Diagnostics.DelimitedListTraceListener'. It is not required to be set for other types.

traceOutputOptions - determines the optional content of trace output. If no extra details are required, it can be set to "None"

Different levels of logging can be set by chaning the switchValue property inside <source> tag. By default it is set to Information and logging can be turned off by setting switchValue = Off

Once after setting switchValue save the config file. Open the adfdi workbook and click on Refresh Config button in Logging section of Oracle ADF ribbbon tab.

  • With switchValue = Information - Information, warning and error messages will be logged in the log file.
    Eg: In excel workbook, add an ADF Input Text into cell J5. Select the same cell (J5) and insert ADF Button Component. A warning will be displayed.
    In C:\temp\adfdi-commom-excel.txt log file, logs similar to below will be seen:
    "adfdi-common"|Warning|3|"A component already exists at origin J5."||7284||"1"|"2013-08-23T10:28:15.2851735Z"||
  • With switchValue = Warning - Warning and error messages will be logged into log file.
    Eg: Edit ADF Table properties by double-clicking any cell in table range, and open Edit Columns by clicking on button on RHS of Columns property. Select FirstName column and set the value of DynaminColumn property to True. Close all dialogs by giving OK and run the workbook.
    In C:\temp\adfdi-commom-excel.txt log file, logs similar to below will be seen:
    "adfdi-common"|Warning|3|"ADFDI-07520: Unable to evaluate the expression '#{bindings.EmployeesView1.hints.FirstName.label}' in 'Sheet1.TAB429819356.FirstName.HeaderLabel'. Detail: "||6352||"1"|"2013-08-23T10:40:16.0642489Z"||
  • With switchValue = Error - only error/exception messages will be logged into log file.
    Eg: Run the workbook. Click on Edit Options in MyWorkbook tab and enter an invalid webapproot (say add 1 at end). Click on OK and click Yes in Web App Root confirmation dialog. ConnectionFailedException will be seen.

    In C:\temp\adfdi-commom-excel.txt log file, logs similar to below will be seen:
    "adfdi-common"|Error|2|"ADFDI-05530: unable to initialize worksheet: Sheet1
    ADFDI-00134: An attempt to connect to the web application has failed.
    ADFDI-00501: An unexpected status: 404 (NotFound) was returned from the server while requesting the URL:

    ConnectionFailedException: ADFDI-00134: An attempt to connect to the web application has failed.
    Source: adfdi-datamanager
    at maybeHadSession)
    at bc, String contentType)
    at isFirstLoad)
    at initializeUI)
    UnexpectedHttpStatusException: ADFDI-00501: An unexpected status: 404 (NotFound) was returned from the server while requesting the URL:
    Source: adfdi-datamanager
  • With switchValue = Verbose - detailed information about the workbook events including warning, information and errors will be seen with delimiter in log file.
    Eg: Run the workbook and perform Download by clicking on Download ribbon command. Enter an invalid value for FirstName(say 'aaaaaaaaaaaaaaaaaaaaaaaaaaaa') and perform Upload by clicking on Upload ribbon command. Upload will fail as FirstName has invalid precision/scale.
    In C:\temp\adfdi-commom-excel.txt log file, all details in About dialog will be logged similar to below :
    "adfdi-common"|Verbose|5|"oacw.datamanager.BindingContainer.AppendExceptionToList: Exception received from server: localized message: JBO-27023: Failed to validate all rows in a transaction.; display message: null; java class name: oracle.jbo.TxnValException; cause: null
    product code: JBO; error code: 27023; severity: Error; row key: null; attribute id: null;
    Detail: localized message: JBO-27024: Failed to validate a row with key oracle.jbo.Key[100 ] in AppModule.EmployeesView1; display message: null; java class name: oracle.jbo.RowValException; cause: null
    product code: JBO; error code: 27024; severity: Error; row key: 00010000000AACED0005770400000064; attribute id: null;
    Detail: localized message: JBO-27010: Attribute set with value aaaaaaaaaaaaaaaaaaaaaaaaaaaa for FirstName in AppModule.EmployeesView1 has invalid precision/scale; display message: Attribute set with value aaaaaaaaaaaaaaaaaaaaaaaaaaaa for FirstName in AppModule.EmployeesView1 has invalid precision/scale; java class name: oracle.jbo.PrecisionScaleValidationException; cause: null
    product code: JBO; error code: 27010; severity: Error; row key: null; attribute id: FirstName; Details: null"||12780||"1"|"2013-08-23T13:10:38.8164715Z"||

How to enable logging for a particular session:

This type of logging can be used only for DT/TST mode and cannot be used for RT mode. This logging is of major help when we want detailed log messages for a specific usecase/error.

On opening ADFdi enabled excel workbook, under Oracle ADF ribbon tab, there will be Logging section with buttons for Console, Set Output Level, Add Log Output File and Refresh Config


Clicking on Console button, opens up Logging Console non-modal dialog. By default the logging level is set to Off and hence no logs will be seen.

logging console-latest.png

Logging levels can be set either by clicking Set Output Level in Logging section of Oracle ADF tab or by clicking Set Level in Logging Console dialog.

Logging levels-latest.png

Logs can also be saved in a file by specifying file name and location in 'Add New Temporary Logging Output File' dialog by clicking on 'Add Log Output File' button in Logging section of Oracle ADF tab.

log output file format -latest.png

The format of log can also be changed by selecting Text/XML from Output Type as seen in above image.

Through these ways we can enable client-side logging for ADF Desktop Integration to trace down the error /exception we get during development / testing / debugging the workbook.

Wednesday Jul 18, 2012

How to access published ADFdi enabled excel workbook from ADF Library jar

We generally tend to create multiple applications and re-use them in one main application by adding them as ADF Library jars.Applications with ADF Desktop Integration support added to ViewController can also be re-used.

This blog is about how to access workbooks in ADF Library jar from a different application(which consumes ADF Library Jar).

Assumption : Let's assume that a simple ADF application(TestApp) is created with Dept and Emp as entities and added as Master Form Detail Table in jspx.Also an ADFdi enabled excel workbook is created to simulate DeptView and EmpView as Master Form Detail Table.Publish the workbook(PBook1.xlsx) and store it in ViewController/public_html/excel/ folder and check for the working of published workbook by re-deploying the application.

Now,let's proceed to the steps to be done before deploying ViewController as ADF Library jar.

Open web.xml in ViewController->Web Content->WEB-INF of TestApp, in Filters tab, check for adfBindings and adfdiExcelDownload filters. If not present add by referring this ADFdi guide.

Create a new deployment profile for ViewController by right-click ViewController and Deploy->New Deployment profile. Select ADF Library Jar File as Profile Type and give a profile name like TestAppLibJar

Create a new custom application(ClientApp) with ADF ViewController as project.

Create a FileSystemConnection to import the deployed library jar of TestApp as below:
In Resource Pallete, open New File System Connection, give Connection Name as TestAppConn and for DirectoryPath browse and select the deploy folder inside ViewController of TestApp application.


Select ViewController of ClientApp, expand the TestAppConn(File System Connection) created, select TestAppLibJar, right-click and Add to Project. In Confirmation dialog, click on Add Library,


In ViewController add ADF Desktop Integration support by right-click ViewController->Project Properties->Features, click on + sign and move ADF Desktop Integration from Available to Selected.


Add ADF Library Web Application Support into ViewController of ClientApp by right-click ViewController->Project Properties->ADF View, select Enable ADF Library WebApp Support checkbox.


Open web.xml and check for the correct ordering of ADFLibraryFilter and adfdiExcelDownload filter. Make sure adfdiExcelDownload filter is above ADFLibraryFilter.


Add Initialization Parameter for ADFLibraryFilter with Name 'include-extension-list' and Value 'png,jpg,jpeg,gif,js,css,htm,html,xlsx' as shown in above pic.

Now we will create a jspx page and add Go link with Text 'Download Excel' and Destination '/excel/PBook1.xlsx'


Run the jspx page and click on Download Excel link. Click on Open, and give yes in Login dialog. There we go, here is our published workbook.


Friday Mar 02, 2012

Refreshing One Column based on the value of Another Column in ADFdi Table

When using ADF Desktop Integration, quite frequently, we get into a situation where we would like to refresh one column based on the value of another column. In ADF Faces, we can achieve this by setting the autoSubmit property and partialTriggers property for the corresponding columns.

However, in ADFdi, we do not have such option. Though we can achieve this by using LOVs and Dependent LOVs. But, in some scenarios we would like to achieve this when using an Input Text Component.

In this article, we will simulate this Auto Refresh functionality in a ADFdi Table.

Note : Since we would be using VBA code to achieve this, we can use this only on the Macro Enabled Excel Workbooks.

Let us assume that we have a View Object based on the Emp table. We could take an example of having a transient attribute in the VO, that gives the sum of Salary and Commission attributes.


In the above example, we've added a new transient attribute (SalPlusComm) to the EmpVO, that would give the sum of Sal and Comm attributes. Since we need this attribute to get refreshed when either Sal or Comm attribute changes, we set the Sal and Comm attributes as Dependencies. Also, we set the AutoSubmit property (under UI Hints tab) for the Sal and Comm attributes.


Now, we are done with the model layer. We can now, create a jspx page and then Drag and Drop EmpView as ADF Table. After this, we create an Excel Workbook (macro enabled), enable ADF Desktop Integration for it, set the required Workbook Properties, and then add a Table based on the EmpView.


As there are no straight forward way in ADFdi to trigger a request to server when a value of a cell is changed, we will now add a DoubleClickActionSet for the Sal and Comm columns. This DoubleClickActionSet will have the Table.RowUpSync and Table.RowDownSync actions.


Above example image shows the DoubleClickActionSet for Sal column. In the same manner, we need to add the DoubleClickActionSet for the Comm column as well.

Now, we have the workbook, that would fetch the SalPlusComm attribute (after recalculation in the model), when we change the Sal / Comm attribute and then double click on that column. To do this automatically when the user tabs out / presses enter key on the cells, we'll write a bit of VBA Code on the Worksheet where we've this table (Go to Developer Tab and Click on Visual Basic).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 14 Or Target.Column = 15 Then
    Application.CommandBars("Cell").Controls("Invoke Action...").Execute
  End If
End Sub
Excel would trigger Worksheet_Change event when a cell in the worksheet is modified. So, we would code our logic in that event. The above code assumes that Sal column is present in the N (14th) column on the worksheet and Comm column is present in O (15th) column. So, we would execute our logic only when the contents in these two columns change.

ADFdi would provide a context menu (Invoke Action...) when a DoubleClickActionSet is added to a particular column. We'll make use of that context menu and invoke it programatically.


We invoke that context menu programatically using the following line of code

Application.CommandBars("Cell").Controls("Invoke Action...").Execute

Now, we run our workbook, modify the value of Sal column for any row and tab out of that field would automatically update the value of SalPlusComm column.


Here, a simple example (Transient Attribute) is taken for the explanation. In the similar fashion, we can also have a DoubleClickActionSet to contain a method in the Impl that would perform this calculation as well.

Tip: If you are not able to view the image fully, right click on the image and choose View Image option to see it completely.

Wednesday Feb 01, 2012

Custom Upload options in ADF Desktop Integration


     In ADFdi enabled excel workbook while uploading the changes performed in ADF Table,standard upload options dialog appears like below.


    In this article,we will see how to customize these options in a dialog.


     Let us take a simple example of Employee table.


Assumption: An ADF Web Application with ADF Desktop Integration enabled workbook having Employee table as below screenshot is available.


Now let's create a new jspx page titled 'UploadOptions.jspx'.
The below code adds two checkboxes and buttons.Let's copy this into the Source view of jspx page.

<!-- Check box for AbortUploadOnFailure -->
<af:selectBooleanCheckbox text="Abort Upload On Failure" id="sbc1"
  value="#{requestScope.abortUploadOnFailure}" autoSubmit="true" selected="true"/>

<!--Check box for DownloadAfterUpload -->
<af:selectBooleanCheckbox text="Download After Upload" id="sbc2" autoSubmit="true"

<!--Command button for Continue action -->
<af:button text="Continue" id="b1" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="continue" to="#{requestScope.action}"/>

<!-- Command button for Abort action -->
<af:button text="Abort" id="b2" inlineStyle="width:100px;" partialSubmit="false">
<af:setActionListener from="abort" to="#{requestScope.action}" />

The UploadOptions.jspx page now looks like below:


Below Span elements have to be used to perform custom upload actions in ADFdi Table.

ADFdiCloseWindow - When a web page has to be closed,this span element can be set to
Continue  - to close the web page and invoke next action in actions set or
Abort       - to close the web page and terminate the action set.
ADFdiAbortUploadOnFailure - If this element is set to True, the action set stops uploading if it encounters a failure. If the element references False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload.
ADFdiDownloadAfterUpload - If this element is set to True, the action set downloads data into the ADFdi Table component after the action set uploads modified data.

Let us add the below code at the end of tag Form in source view of jspx,to include the above mentioned span elements.

<!-- Closes the Upload Options dialog and then invokes next action in the action set-->
<f:verbatim rendered="#{requestScope.action eq 'continue'}">
<span id="ADFdiCloseWindow">Continue</span>

<!-- Closes the
Upload Options dialog but aborts next actions in action set-->
<f:verbatim rendered="#{requestScope eq 'abort'}">
<span id="ADFdi

<!-- If the span element is set to
  True, the action set stops uploading if it encounters a failure.
  False, the action set attempts to upload all rows and indicates if each row succeeded or failed to upload. -->
<span id="ADFdiAbortUploadOnFailure">${requestScope.abortUploadOnFailure}</span> 

<!--If the span element is set to
True, the action set downloads data from the Fusion web application to the ADF Table after the action set uploads modified data -->
<span id="ADFdi

In ADFdi excel workbook,let us add this new jspx file as dialog before Table.Upload action in Ribbon Commands of Worksheet Properties.


Now we run the excel workbook and download data into ADF Table as shown above.Let's update a row by changing the Sal=4000 of Martin.


On clicking Upload Ribbon command like below:


the custom upload actions dialog gets opened with 'Abort Upload On Failure' check box selected by default.

We can select 'Download After Upload' check box and click on Continue button.We can see data updated and downloaded again with new value like below:


This is a simple example of how upload options can be customized. Using this, we can add more custom options to the dialog, which is not available in the standard upload options dialog. We will see such options sooner in my next blog.


Tips and Tricks from Oracle's JDeveloper & ADF QA


« June 2016