Bug in ADF 11g R2- Transient and Bind Valiable of Timestamp type

This is a bug in ADF 11g R2, and sometimes so much annoying for developer.

Bug Is-

• Suppose you have created business components for Employees table of HR Schema, there is HireDate in Employees table , see it in EntityObject-

• Now go to source and see, it is of type oracle.jbo.domain.Timestamp (default java representation of Timestamp database data type )

• Now go to Employees ViewObject and create a transient variable for DOB(Date Of Birth)  and select it as Timestamp

•  Now after this if developer tries to get or set value of this transient variable, it shows a exception (java.sql.Timestamp can not be cast to oracle.jbo.domain.Timestamp)

•  To solve this error go to Employees ViewObject and select transient attribute and go to source, se it is of type java.sql.Timestamp

 

• Change it to oracle.jbo.domain.Timestamp  and then run it
• So this is a bug when developer creates new Transient or Query bind variables of Timestamp type, ADF automatically assign it java.sql.Timestamp

Always check it when using Jdveloper 11gR2

Cheers... happy debugging

Implementing custom search form in ADF programmatically (Without using af:query)

Sometimes we need to implement custom search instead of using adf query component.
To implement custom search form , i have used a quite beautiful way to handle search and reset functionality for a View Object.
In this post i am taking Employees Table (Default HR Schema) to search and reset af:table.

• First create a Fusion Web Application and create model (EO,VO and AM) using Employees table

• Now suppose we have to search on 4 fields (EmployeeId, FirstName, Email, Salary) , so for this i have created a new read only ViewObject from dual

•  This VO from dual is created to be used as Search form on page

•  Now to make search effective on Employees VO , i have created 4 bind variable for corresponding fields in Employees ViewObject

•  Now i have created a ViewCriteria using these 4 bind variables to re-query ViewObject  data

•  This ViewCriteria will be executed each time when value of bind variable is changed, now this is the time to set value of bind variables, so i have dragged dual vo as a form on page, and added 2 button for Search and Reset

•  To View Search Result , Employees Table is there on page

•  Now to get value from Form Field to bean , i have created binding for all 4 fields in bean

•  Now i have created ActionListener for Search button- in this code i have get value from page using component bindings and passed in Employees ViewObject's bind variable in order to execute viewCriteria.



    public void searchButton(ActionEvent actionEvent) {
        searchAMImpl am = (searchAMImpl)resolvElDC("searchAMDataControl");
        ViewObject empVo = am.getEmployees1();
        empVo.setNamedWhereClauseParam("EmpIdBind", empIdPgBind.getValue());
        empVo.setNamedWhereClauseParam("FirstNmBind", firstNmPgBind.getValue());
        empVo.setNamedWhereClauseParam("EmailBind", emailPgBind.getValue());
        empVo.setNamedWhereClauseParam("SalaryBind", salaryPgBind.getValue());
        empVo.executeQuery();
    }



• Once value is set in bind variable , view criteria is executed and Search result will be shown in resultant table- Run your application and see

• To reset values in table and search form, see the managed bean code of Reset button



    public void resetButton(ActionEvent actionEvent) {
        searchAMImpl am = (searchAMImpl)resolvElDC("searchAMDataControl");
        ViewObject empVo = am.getEmployees1();
        ViewObject attrVo=am.getattr1();
        empVo.setNamedWhereClauseParam("EmpIdBind", null);
        empVo.setNamedWhereClauseParam("FirstNmBind", null);
        empVo.setNamedWhereClauseParam("EmailBind", null);
        empVo.setNamedWhereClauseParam("SalaryBind", null);
        empVo.executeQuery();
        attrVo.executeQuery();
      
    }



• Click on reset button and page value are set to default

• This is how we can implement custom search using ADF components, you can apply validations, auto suggest feature etc while using this custom search

          Cheers :-)  Download Sample App

JBO-27022-Failed to load value at index 'n' , java.sql.SQLException: Invalid column index in Oracle ADF

'Invalid column index' is the error occurs due to SQL Exception in ADF Faces.
and main reason behind this is mismatch between ViewObject's query and Attributes.
 suppose i have an application with default HR Schema and Employees Table.

• See the query and attribute list of Employees ViewObject , when there is no mismatch between attributes and query

 

and query is -

 

• Now i have removed a attribute from ViewObject query, then mismatch between VO Query and attribute list tends towards sql exception "Invalid Column Index"

 Query is- (First_Name is not there)

•  Now when you try to run application, this error occurs, "Failed to load value at index n - java.sql.SQLException Invalid column index"- see error log 



oracle.jbo.AttributeLoadException: JBO-27022: Failed to load value at index 11 with java object of type java.lang.String due to java.sql.SQLException.
 at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(AttributeDefImpl.java:2435)
 at oracle.jbo.server.ViewRowImpl.populate(ViewRowImpl.java:3842)
 at oracle.jbo.server.ViewDefImpl.createInstanceFromResultSet(ViewDefImpl.java:2387)
 at oracle.jbo.server.ViewObjectImpl.createRowFromResultSet(ViewObjectImpl.java:6030)
 at oracle.jbo.server.ViewObjectImpl.createInstanceFromResultSet(ViewObjectImpl.java:5861)
 at oracle.jbo.server.QueryCollection.populateRow(QueryCollection.java:3612)
 at oracle.jbo.server.QueryCollection.fetch(QueryCollection.java:3467)
 at oracle.jbo.server.QueryCollection.get(QueryCollection.java:2184)
 at oracle.jbo.server.ViewRowSetImpl.getRow(ViewRowSetImpl.java:5125)
 at oracle.jbo.server.ViewRowSetIteratorImpl.doFetch(ViewRowSetIteratorImpl.java:2936)
 at oracle.jbo.server.ViewRowSetIteratorImpl.ensureRefreshed(ViewRowSetIteratorImpl.java:2792)
 at oracle.jbo.server.ViewRowSetIteratorImpl.refresh(ViewRowSetIteratorImpl.java:3033)
 at oracle.jbo.server.ViewRowSetImpl.notifyRefresh(ViewRowSetImpl.java:2797)
 at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:1255)
 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMasters(ViewRowSetImpl.java:1413)
 at oracle.jbo.server.ViewRowSetImpl.executeQueryForMode(ViewRowSetImpl.java:1319)
 at oracle.jbo.server.ViewRowSetImpl.executeQuery(ViewRowSetImpl.java:1304)
 at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:7119)
 at oracle.adf.model.bc4j.DCJboDataControl.executeIteratorBindingIfNeeded(DCJboDataControl.java:1366)
 at oracle.adf.model.binding.DCIteratorBinding.executeQueryIfNeeded(DCIteratorBinding.java:2219)
 at oracle.jbo.uicli.binding.JUCtrlHierBinding.getRootNodeBinding(JUCtrlHierBinding.java:92)
 at oracle.adfinternal.view.faces.model.binding.FacesCtrlHierBinding$HierCurrencyRowKeySet.getRowIterator(FacesCtrlHierBinding.java:1421)
 at oracle.adfinternal.view.faces.model.binding.CurrencyRowKeySet.size(CurrencyRowKeySet.java:34)
 at org.apache.myfaces.trinidad.model.RowKeySet.getSize(RowKeySet.java:152)
 at oracle.adfinternal.view.faces.renderkit.rich.TableRendererUtils.writePojoSelectionState(TableRendererUtils.java:455)
 at oracle.adfinternal.view.faces.renderkit.rich.TableRenderer.renderDataBlockRows(TableRenderer.java:2562)
 at oracle.adfinternal.view.faces.renderkit.rich.TableRenderer.encodeAll(TableRenderer.java:685)
 at oracle.adf.view.rich.render.RichRenderer.encodeAll(RichRenderer.java:1452)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(CoreRenderer.java:511)
 at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(UIXComponentBase.java:923)
 at org.apache.myfaces.trinidad.component.UIXCollection.encodeEnd(UIXCollection.java:617)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1659)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeChild(CoreRenderer.java:624)
 at oracle.adf.view.rich.render.RichRenderer.encodeChild(RichRenderer.java:3201)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeAllChildren(CoreRenderer.java:641)
 at oracle.adf.view.rich.render.RichRenderer.encodeAllChildrenInContext(RichRenderer.java:3062)
 at oracle.adfinternal.view.faces.renderkit.rich.FormRenderer.encodeAll(FormRenderer.java:274)
 at oracle.adf.view.rich.render.RichRenderer.encodeAll(RichRenderer.java:1452)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(CoreRenderer.java:511)
 at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(UIXComponentBase.java:923)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1659)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeChild(CoreRenderer.java:624)
 at oracle.adf.view.rich.render.RichRenderer.encodeChild(RichRenderer.java:3201)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeAllChildren(CoreRenderer.java:641)
 at oracle.adf.view.rich.render.RichRenderer.encodeAllChildrenInContext(RichRenderer.java:3062)
 at oracle.adfinternal.view.faces.renderkit.rich.DocumentRenderer.encodeAll(DocumentRenderer.java:1277)
 at oracle.adf.view.rich.render.RichRenderer.encodeAll(RichRenderer.java:1452)
 at org.apache.myfaces.trinidad.render.CoreRenderer.encodeEnd(CoreRenderer.java:511)
 at org.apache.myfaces.trinidad.component.UIXComponentBase.encodeEnd(UIXComponentBase.java:923)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1659)
 at javax.faces.component.UIComponent.encodeAll(UIComponent.java:1655)
 at oracle.adfinternal.view.faces.component.AdfViewRoot.encodeAll(AdfViewRoot.java:91)
 at com.sun.faces.application.view.JspViewHandlingStrategy.doRenderView(JspViewHandlingStrategy.java:431)
 at com.sun.faces.application.view.JspViewHandlingStrategy.renderView(JspViewHandlingStrategy.java:233)
 at org.apache.myfaces.trinidadinternal.application.ViewDeclarationLanguageFactoryImpl$ChangeApplyingVDLWrapper.renderView(ViewDeclarationLanguageFactoryImpl.java:350)
 at com.sun.faces.application.view.MultiViewHandler.renderView(MultiViewHandler.java:131)
 at javax.faces.application.ViewHandlerWrapper.renderView(ViewHandlerWrapper.java:273)
 at org.apache.myfaces.trinidadinternal.application.ViewHandlerImpl.renderView(ViewHandlerImpl.java:165)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._renderResponse(LifecycleImpl.java:1027)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl._executePhase(LifecycleImpl.java:334)
 at oracle.adfinternal.view.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:232)
 at javax.faces.webapp.FacesServlet.service(FacesServlet.java:313)
 at weblogic.servlet.internal.StubSecurityHelper$ServletServiceAction.run(StubSecurityHelper.java:227)
 at weblogic.servlet.internal.StubSecurityHelper.invokeServlet(StubSecurityHelper.java:125)
 at weblogic.servlet.internal.ServletStubImpl.execute(ServletStubImpl.java:300)
 at weblogic.servlet.internal.TailFilter.doFilter(TailFilter.java:26)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.adf.model.servlet.ADFBindingFilter.doFilter(ADFBindingFilter.java:173)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.adfinternal.view.faces.webapp.rich.RegistrationFilter.doFilter(RegistrationFilter.java:122)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:468)
 at oracle.adfinternal.view.faces.activedata.AdsFilter.doFilter(AdsFilter.java:60)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl$FilterListChain.doFilter(TrinidadFilterImpl.java:468)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl._doFilterImpl(TrinidadFilterImpl.java:293)
 at org.apache.myfaces.trinidadinternal.webapp.TrinidadFilterImpl.doFilter(TrinidadFilterImpl.java:199)
 at org.apache.myfaces.trinidad.webapp.TrinidadFilter.doFilter(TrinidadFilter.java:92)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.security.jps.ee.http.JpsAbsFilter$1.run(JpsAbsFilter.java:111)
 at java.security.AccessController.doPrivileged(Native Method)
 at oracle.security.jps.util.JpsSubject.doAsPrivileged(JpsSubject.java:313)
 at oracle.security.jps.ee.util.JpsPlatformUtil.runJaasMode(JpsPlatformUtil.java:413)
 at oracle.security.jps.ee.http.JpsAbsFilter.runJaasMode(JpsAbsFilter.java:94)
 at oracle.security.jps.ee.http.JpsAbsFilter.doFilter(JpsAbsFilter.java:161)
 at oracle.security.jps.ee.http.JpsFilter.doFilter(JpsFilter.java:71)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at oracle.dms.servlet.DMSServletFilter.doFilter(DMSServletFilter.java:136)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at weblogic.servlet.internal.RequestEventsFilter.doFilter(RequestEventsFilter.java:27)
 at weblogic.servlet.internal.FilterChainImpl.doFilter(FilterChainImpl.java:56)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.wrapRun(WebAppServletContext.java:3715)
 at weblogic.servlet.internal.WebAppServletContext$ServletInvocationAction.run(WebAppServletContext.java:3681)
 at weblogic.security.acl.internal.AuthenticatedSubject.doAs(AuthenticatedSubject.java:321)
 at weblogic.security.service.SecurityManager.runAs(SecurityManager.java:120)
 at weblogic.servlet.internal.WebAppServletContext.securedExecute(WebAppServletContext.java:2277)
 at weblogic.servlet.internal.WebAppServletContext.execute(WebAppServletContext.java:2183)
 at weblogic.servlet.internal.ServletRequestImpl.run(ServletRequestImpl.java:1454)
 at weblogic.work.ExecuteThread.execute(ExecuteThread.java:209)
 at weblogic.work.ExecuteThread.run(ExecuteThread.java:178)
Caused by: java.sql.SQLException: Invalid column index
 at oracle.jdbc.driver.OracleResultSetImpl.getString(OracleResultSetImpl.java:2824)
 at weblogic.jdbc.wrapper.ResultSet_oracle_jdbc_driver_OracleResultSetImpl.getString(Unknown Source)
 at oracle.jbo.common.StringTypeSQLNativeImpl.getDataFromResultSet(JboTypeMapEntries.java:509)
 at oracle.jbo.server.AttributeDefImpl.loadFromResultSet(AttributeDefImpl.java:2426)
 ... 97 more
## Detail 0 ## 

• So when you see this type of log , first check your viewObjects and their query.
• Always match attribute order in query of view object and in XML source, if there is mismatch in order, it may result in  great problem or data mistake

        See here is difference in order of attribute of vo and query- (Last_Name and Email)

• In this case of mismatch in order, two things may happen-
• First one- if Data type of mismatched attributes matches (as Last_Name and Email both are Var char) then value in both fields will be set in VO otherwise it will throw an exception.
• See this scenario in given snap- Only due to wrong ordering ,value of Last_Name and Email got replaced

• And if you have Impl classes for your Entity Object or View Object then also check indexing in  classes ,query and  XML source.
• Invalid Column Index may also occur due to pl/sql (database) function call, always check it and its parameter indexing