• XpagesJDBC.nsf Library insert Update exception on jdbcData1.acceptChanges()

    By cyrus mobaraki 6 years ago

    Hello,

    I get an error when trying to insert or Update a table in postgres db using the JDBC_RowSetDatasource.xsp (from the XpagesJDBC.nsf example), see the error output below. The insert and update does the insert and update but at the same time I get an exception,  I have even added the following two lines to the DBUtil.getConnection(), with or without the following two lines the exception will be raises after the save button clicked.

    Can you tell me plase if that is a defect or I have done somthing wrong.

    publicConnection getConnection() throwsSQLException {
           if(connection==null) {
    connection = JdbcUtil.getConnection(FacesContext.getCurrentInstance(),connectionName);
                           // CY 30.10.2011 enable commit
                           connection.setReadOnly(false);
                           connection.setAutoCommit(true);
                   }
                   returnconnection;
           }

    com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)

    javax.sql.rowset.spi.SyncProviderException: Cannot commit when autoCommit is enabled.






    The runtime has encountered an unexpected error.

     

    Page Name:/CY_JDBC_RowSetDatasource.xsp
    Control Id: button1
    Property: onclick

    Exception

    Error while executing JavaScript action expression
    Script interpreter error, line=1, col=11: Error calling method 'acceptChanges()' on java class 'com.ibm.xsp.extlib.jdbc.model.JdbcRowSetAccessor'
    Cannot commit when autoCommit is enabled.

    JavaScript code

       1: jdbcData1.acceptChanges()

    ► ▼Stack Trace

    javax.faces.FacesException: Error while executing JavaScript action expression
       com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:102)
       com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210)
       com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:96)
       com.ibm.xsp.controller.FacesControllerImpl.execute(FacesControllerImpl.java:250)
       com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:223)
       com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:200)
       com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160)
       com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:137)
       com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1267)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:847)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1251)
       com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:598)
       com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:421)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:341)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:297)
       com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)
    com.ibm.xsp.exception.EvaluationExceptionEx: Error while executing JavaScript action expression
       com.ibm.xsp.binding.javascript.JavaScriptMethodBinding.invoke(JavaScriptMethodBinding.java:126)
       com.ibm.xsp.application.ActionListenerImpl.processAction(ActionListenerImpl.java:60)
       javax.faces.component.UICommand.broadcast(UICommand.java:324)
       com.ibm.xsp.component.UIEventHandler.broadcast(UIEventHandler.java:366)
       com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
       com.ibm.xsp.extlib.component.layout.UIVarPublisherBase.broadcast(UIVarPublisherBase.java:181)
       com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
       com.ibm.xsp.component.UIViewRootEx.broadcast(UIViewRootEx.java:1535)
       javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:307)
       javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:428)
       com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:94)
       com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210)
       com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:96)
       com.ibm.xsp.controller.FacesControllerImpl.execute(FacesControllerImpl.java:250)
       com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:223)
       com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:200)
       com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160)
       com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:137)
       com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1267)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:847)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1251)
       com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:598)
       com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:421)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:341)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:297)
       com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)
    com.ibm.jscript.InterpretException: Script interpreter error, line=1, col=11: Error calling method 'acceptChanges()' on java class 'com.ibm.xsp.extlib.jdbc.model.JdbcRowSetAccessor'
       com.ibm.jscript.types.JavaAccessObject.call(JavaAccessObject.java:334)
       com.ibm.jscript.types.FBSObject.call(FBSObject.java:161)
       com.ibm.jscript.ASTTree.ASTCall.interpret(ASTCall.java:175)
       com.ibm.jscript.ASTTree.ASTProgram.interpret(ASTProgram.java:119)
       com.ibm.jscript.ASTTree.ASTProgram.interpretEx(ASTProgram.java:139)
       com.ibm.jscript.JSExpression._interpretExpression(JSExpression.java:435)
       com.ibm.jscript.JSExpression.access$1(JSExpression.java:424)
       com.ibm.jscript.JSExpression$2.run(JSExpression.java:414)
       java.security.AccessController.doPrivileged(AccessController.java:284)
       com.ibm.jscript.JSExpression.interpretExpression(JSExpression.java:410)
       com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:251)
       com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:234)
       com.ibm.xsp.javascript.JavaScriptInterpreter.interpret(JavaScriptInterpreter.java:221)
       com.ibm.xsp.binding.javascript.JavaScriptMethodBinding.invoke(JavaScriptMethodBinding.java:111)
       com.ibm.xsp.application.ActionListenerImpl.processAction(ActionListenerImpl.java:60)
       javax.faces.component.UICommand.broadcast(UICommand.java:324)
       com.ibm.xsp.component.UIEventHandler.broadcast(UIEventHandler.java:366)
       com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
       com.ibm.xsp.extlib.component.layout.UIVarPublisherBase.broadcast(UIVarPublisherBase.java:181)
       com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
       com.ibm.xsp.component.UIViewRootEx.broadcast(UIViewRootEx.java:1535)
       javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:307)
       javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:428)
       com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:94)
       com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210)
       com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:96)
       com.ibm.xsp.controller.FacesControllerImpl.execute(FacesControllerImpl.java:250)
       com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:223)
       com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:200)
       com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160)
       com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:137)
       com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1267)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:847)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1251)
       com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:598)
       com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:421)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:341)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:297)
       com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)
    javax.sql.rowset.spi.SyncProviderException: Cannot commit when autoCommit is enabled.
       com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:902)
       com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:934)
       com.ibm.xsp.extlib.jdbc.model.JdbcRowSetAccessor.acceptChanges(JdbcRowSetAccessor.java:421)
       sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
       sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
       java.lang.reflect.Method.invoke(Method.java:611)
       com.ibm.jscript.types.JavaAccessObject.call(JavaAccessObject.java:321)
       com.ibm.jscript.types.FBSObject.call(FBSObject.java:161)
       com.ibm.jscript.ASTTree.ASTCall.interpret(ASTCall.java:175)
       com.ibm.jscript.ASTTree.ASTProgram.interpret(ASTProgram.java:119)
       com.ibm.jscript.ASTTree.ASTProgram.interpretEx(ASTProgram.java:139)
       com.ibm.jscript.JSExpression._interpretExpression(JSExpression.java:435)
       com.ibm.jscript.JSExpression.access$1(JSExpression.java:424)
       com.ibm.jscript.JSExpression$2.run(JSExpression.java:414)
       java.security.AccessController.doPrivileged(AccessController.java:284)
       com.ibm.jscript.JSExpression.interpretExpression(JSExpression.java:410)
       com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:251)
       com.ibm.jscript.JSExpression.evaluateValue(JSExpression.java:234)
       com.ibm.xsp.javascript.JavaScriptInterpreter.interpret(JavaScriptInterpreter.java:221)
       com.ibm.xsp.binding.javascript.JavaScriptMethodBinding.invoke(JavaScriptMethodBinding.java:111)
       com.ibm.xsp.application.ActionListenerImpl.processAction(ActionListenerImpl.java:60)
       javax.faces.component.UICommand.broadcast(UICommand.java:324)
       com.ibm.xsp.component.UIEventHandler.broadcast(UIEventHandler.java:366)
       com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
       com.ibm.xsp.extlib.component.layout.UIVarPublisherBase.broadcast(UIVarPublisherBase.java:181)
       com.ibm.xsp.component.UIDataPanelBase.broadcast(UIDataPanelBase.java:400)
       com.ibm.xsp.component.UIViewRootEx.broadcast(UIViewRootEx.java:1535)
       javax.faces.component.UIViewRoot.broadcastEvents(UIViewRoot.java:307)
       javax.faces.component.UIViewRoot.processApplication(UIViewRoot.java:428)
       com.sun.faces.lifecycle.InvokeApplicationPhase.execute(InvokeApplicationPhase.java:94)
       com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:210)
       com.sun.faces.lifecycle.LifecycleImpl.execute(LifecycleImpl.java:96)
       com.ibm.xsp.controller.FacesControllerImpl.execute(FacesControllerImpl.java:250)
       com.ibm.xsp.webapp.FacesServlet.serviceView(FacesServlet.java:223)
       com.ibm.xsp.webapp.FacesServletEx.serviceView(FacesServletEx.java:200)
       com.ibm.xsp.webapp.FacesServlet.service(FacesServlet.java:160)
       com.ibm.xsp.webapp.FacesServletEx.service(FacesServletEx.java:137)
       com.ibm.xsp.webapp.DesignerFacesServlet.service(DesignerFacesServlet.java:103)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.invokeServlet(ComponentModule.java:576)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.invokeServlet(NSFComponentModule.java:1267)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$AdapterInvoker.invokeServlet(ComponentModule.java:847)
       com.ibm.designer.runtime.domino.adapter.ComponentModule$ServletInvoker.doService(ComponentModule.java:796)
       com.ibm.designer.runtime.domino.adapter.ComponentModule.doService(ComponentModule.java:565)
       com.ibm.domino.xsp.module.nsf.NSFComponentModule.doService(NSFComponentModule.java:1251)
       com.ibm.domino.xsp.module.nsf.NSFService.doServiceInternal(NSFService.java:598)
       com.ibm.domino.xsp.module.nsf.NSFService.doService(NSFService.java:421)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.doService(LCDEnvironment.java:341)
       com.ibm.designer.runtime.domino.adapter.LCDEnvironment.service(LCDEnvironment.java:297)
       com.ibm.domino.xsp.bridge.http.engine.XspCmdManager.service(XspCmdManager.java:272)

    • Seems to be sue to your autoCommit flag

      By Philippe Riand 6 years ago

      I'd like to understand how your getConnection() method is being used, and why you have to set the autoCommit property explicitely. Can you please elaborate?

      I bet the Jdbc CachedRowSet do not support the autoCommit mode.

      • respond to your question getConnection() method

        By cyrus mobaraki 6 years ago

        Hi Phil,

        Sorry for the delay to answer to your question, I wasn't in the office.

        I am using your code example (XpagesJDBC.nsf) with the new configuration to work with the postgress db.

         

        The insert and update technique in the JDBC_AtFunctions.xsp works with no problem as you because it doesn’t use the same code (just to point out this part is working with the same db configuration)

        The issue comes when I use the JDBC_RowSetDatasource.xsp

        Since I had the exception and the error message was saying I have to enable the Auto commit then I thought if I add the following two lines of  code then it will solve my issue but  I get the same exception with or without my changes.

        The update and insert in the db is work but I get the exception after wards.

        public Connection getConnection() throws SQLException {

                    if(connection==null) {

                          connection = JdbcUtil.getConnection(FacesContext.getCurrentInstance(),connectionName);

                          // CY 30.10.2011 enable commit

        //                connection.setReadOnly(false);

        //                connection.setAutoCommit(true);

                    }

                    return connection;

              }

         

        The postgres drive used in my code

        postgresql-9.0-801.jdbc4.jar

         

        I have changed the following configuration :

         

        Old derby1.jdbc

         

              org.apache.derby.jdbc.EmbeddedDriver

              jdbc:derby:${rcp.data}\derby\XPagesJDBC;create=true

              phil

              phil

         

        New postgres.jdbc

              org.postgresql.Driver

              jdbc:postgresql://test.wsl.ch:5432/intranetdb_test

              postgres

              test

         

        from the JDBC_RowSetDatasource.xsp

        <xp:this.data>

              <xe:jdbcRowSet connectionName="postgres" var="jdbcData1"

                    sqlTable="ressystem.users" showDeleted="true">

              xe:jdbcRowSet>

        xp:this.data>

        • newer version of postgres DB and jdbc didnot help either

          By cyrus mobaraki 6 years ago

          Hi Phil,

          I have installed the new postgres database and also used newer version of jdbc but unfortuniatly  I am having the same proble,

          Old DB

          posgres version 8.3.6

          postgresql-9.0-801.jdbc4.jar

          New DB

          posgres version 9.1.1

          postgresql-9.1-901.jdbc4.jar

           

          The runtime has encountered an unexpected error.

          Error source

          Page Name:/CY_JDBC_RowSetDatasource.xsp
          Control Id: button1
          Property: onclick




          Exception

          Error while executing JavaScript action expression
          Script interpreter error, line=1, col=11:
          Error calling method 'acceptChanges()' on java class 'com.ibm.xsp.extlib.jdbc.model.JdbcRowSetAccessor'
          Cannot commit when autoCommit is enabled.


          JavaScript code

             1: jdbcData1.acceptChanges()
          

          Stack Trace

           

          • See if this could help

            By Andrejus Chaliapinas 6 years ago

            Try to set explicitly autoCommit to false for your connection configuration and then try your code.

            http://www.daniweb.com/software-development/java/threads/203710

            • This solution works aoutside of lotus notes environment but not within

              By cyrus mobaraki 6 years ago

              Hi Andrejus,

              Thanks for your response and help; I have added this line of code already to the XpagesJDBC.nsf code which I am using but it doesn’t help with the exception.

              What I did, I took the code from your example in this website  http://www.daniweb.com/software-development/java/threads/203710

              and tested outside of lotus notes environment (my local Eclipse ) without any change to the code, I get the same exception.

              I added this line of code  con.setAutoCommit(false);  after my getConnection then it works.  

              Class.forName("org.postgresql.Driver");

              con=DriverManager.getConnection("jdbc:postgresql://test.wsl.ch:5432/intranetdb_test", "postgres", "test");

              // without this line will give exception

              con.setAutoCommit(false);

               

              What do you think I need to do in Lotus notes designer to make it work?

               

              Thanks

              Cyrus

              • Currently you could set autoCommit only for xe:jdbcConnectionManager

                By Andrejus Chaliapinas 6 years ago

                For xe:jdbcRowSet it's not currently possible - I'll try to address that in a next build

                • thanks,on which date would you release

                  By cyrus mobaraki 6 years ago

                  Thanks for your respons, just to have an idea when the next realse is planed.

            • Another way

              By Janey Cringean 6 years ago

              I've just hit the same problem. I fixed it by adding a ConnectionManager to the XPage. I then defined by rowset on a panel within the XPage (not the page itself). The ConnectionManager uses the connection I was using on the rowset previously (defined in the connection's .jdbc file). The rowset now uses the ConnectionManager. It's a tiny change in the way I was doing things but has fixed the issue. I haven't had to refer to autocommit anywhere.

              • thank for your respond (can you give a example)

                By cyrus mobaraki 6 years ago

                Hello,

                Many thanks for your response, I tried and couldn't achive what you have done. Can you give me an example or look at my example from  (t XpagesJDBC.JDBC_RowSetDatasource.xsp), then I can follow your solution please.

                 

                Just to let you know that Andrejus Chaliapinas has fixed this issue with the following  APAR(s) : IV16493, IV16885, but I haven’t got the fix yet and I would like to try your solution also.


                                     sqlTable="ressystem.users" showDeleted="true">
                        

                    

                    
                    
                        
                            
                        

                        
                        

                        
                        
                      
                        
                        
                                             value="#{jdbcData1}" indexVar="idx">
                                
                                                             xp:key="headerPager" id="pager1">
                                    

                                

                                
                                    
                                                                     id="viewColumnHeader1" value="" sortable="true">
                                        

                                    

                 

                Thanks

                Cyrus