• Can JDBC RowSetDataSource use an MS SQL Identity Column for Inserts?

    By Jack Wilson 1 decade ago

    I've been playing with the XPagesJDBC demo JDBC_RowSetDataSource example using a MS Sql Server 2005 backend. I've got a simple table (MonthLookup) with just two columns:

    CREATE TABLE [MonthLookup] (
      [month_id] INT IDENTITY(1,1) NOT NULL,
      [month_value] VARCHAR(40) NOT NULL,
      CONSTRAINT [PK_MonthLookup] PRIMARY KEY ([month_id])
    )

    I can edit and delete an existing table with data from the XPage with no issues. The problem is inserts. I would have thought as long as the month_id column was empty then when the jdbcData1.acceptChanges() is called that any null values would be filled in with the proper identity values. But what I get instead is the following exception.

    Is there a trick to make the identity column be filled-in just like it is with a standard insert when the month_id column is null or omitted?

    INSERT INTO MonthLookup (month_value) 
    VALUES ('January')

    Exception I get:

    Unexpected runtime error

    The runtime has encountered an unexpected error.
     

    Error source

    Page Name:/MonthData.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'
     

    JavaScript code


     

       1: jdbcData1.acceptChanges()
    

     

    Stack Trace


     

    javax.faces.FacesException: Error while executing JavaScript action expression
        com.sun.faces.lifecycle.ApplyRequestValuesPhase.execute(ApplyRequestValuesPhase.java:106)
        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:183)
        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.processDecodes(UIViewRoot.java:344)
        com.ibm.xsp.component.UIViewRootEx._processDecodes(UIViewRootEx.java:1438)
        com.ibm.xsp.component.UIViewRootEx.processDecodes(UIViewRootEx.java:1399)
        com.sun.faces.lifecycle.ApplyRequestValuesPhase.execute(ApplyRequestValuesPhase.java:98)
        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:183)
        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.processDecodes(UIViewRoot.java:344)
        com.ibm.xsp.component.UIViewRootEx._processDecodes(UIViewRootEx.java:1438)
        com.ibm.xsp.component.UIViewRootEx.processDecodes(UIViewRootEx.java:1399)
        com.sun.faces.lifecycle.ApplyRequestValuesPhase.execute(ApplyRequestValuesPhase.java:98)
        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)
    java.lang.NullPointerException
        com.sun.rowset.internal.CachedRowSetWriter.writeData(CachedRowSetWriter.java:411)
        com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:875)
        com.sun.rowset.CachedRowSetImpl.acceptChanges(CachedRowSetImpl.java:934)
        com.ibm.xsp.extlib.jdbc.model.JdbcRowSetAccessor.acceptChanges(JdbcRowSetAccessor.java:425)
        sun.reflect.GeneratedMethodAccessor178.invoke(Unknown Source)
        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:183)
        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.processDecodes(UIViewRoot.java:344)
        com.ibm.xsp.component.UIViewRootEx._processDecodes(UIViewRootEx.java:1438)
        com.ibm.xsp.component.UIViewRootEx.processDecodes(UIViewRootEx.java:1399)
        com.sun.faces.lifecycle.ApplyRequestValuesPhase.execute(ApplyRequestValuesPhase.java:98)
        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)
    

     Thanks...jack

    • Will need to check code in regards to that

      By Andrejus Chaliapinas 1 decade ago

      Sounds like a bug while trying to omit Identity column in your table during acceptChanges operation after Insert. I'll take a look during weekend on that. Is that IDENTITY column in MS SQL has Autoincrement or Autogenerate feature?

      • it is using autoincrement <eom>

        By Jack Wilson 1 decade ago
        • Could you attach your sample code/NSF?

          By Andrejus Chaliapinas 1 decade ago

          Hi,

          Could you provide your sample .xsp file/NSF which tries to utilize an insert/acceptChanges into your MonthLookup table?

          • Attached MonthData.xsp

            By Jack Wilson 1 decade ago

            This is really just a copy of the JDBC_RowSetDatasource.xsp from the XPagesJDBC demo nsf, with the data source switched to using an MS SQL backend. Refer to my first post in this thread for the actual MS SQL Table definition.

            Thanks...jack

            • Can't download your attachment

              By Andrejus Chaliapinas 1 decade ago

              Hi Jack,

              I cannot download your attachment, but I did quick test over my local MS SQL Express instance with your table DDL provided and only month_value field used and could see the issue. Will try my best to investigate if it's MS SQL specific or more general case.

              • MonthData.xsp (zip file)

                By Jack Wilson 1 decade ago

                Andrejus,

                Sorry about that. I've reattached it as a zip file.

                Thanks for looking into this.

                ...jack

                • I'm working with another team on this

                  By Andrejus Chaliapinas 1 decade ago

                  Hi Jack,

                  Currently I'm working with another team on this issue. It's a core related. Hope to have some update soon.

                  • Great! Thanks for the update <eom>

                    By Jack Wilson 1 decade ago

                    Thanks...jack

                    • Another update on this issue

                      By Andrejus Chaliapinas 1 decade ago

                      Hi Jack,

                      I'm still working on your issue. While going down it was found that some changes were made between JDK 5.0 and JDK 6.0 code in terms how it deals with primary keys, so I'm awaiting a response from another team on this. Will have some additional information next week.

                      Now, while you are waiting for that - I've implemented additional @JdbcInsert SSJS method prototype (was shown during my Lotusphere 2012 AD107 presentation), which now will allow you to insert a record into MonthLookup table without specifying an ID value and get generated ID back. Of course it's not the solution of initial issue, but maybe could help you in the meantime to realise some Domino-RDBMS based workflows.

                       

                      Here is a code snippet for DB2 (for MS SQL should be the same) related table 'Dept' where I have an ID column which is autogenerated:

                          try {
                        
                         var v = {
                          name: "R&D Department"
                         }
                         var idCol = ["id"]
                         var id = @JdbcInsert("db2demo","Dept",v,idCol)
                         @InfoMessage("Record inserted, id generated: "+id)
                         
                        } catch(e) {
                         @ErrorMessage(e);
                        }
                       }]]>

                       

                      • Is your Lotusphere AD 107 online?

                        By Jack Wilson 1 decade ago

                        Andrejus,

                        Thanks for the update. Is your Lotusphere 2012 AD 107 presentation on line anywhere? I just did a quick google but did not find anything...

                        ...jack

                    • You need to request a fix through IBM support for this

                      By Andrejus Chaliapinas 1 decade ago

                      Hi Jack,

                       

                      It took quite long time of work with several teams to identify the exact cause of that problem. As a result an APAR IV16493 was developed and should allow IBM customers to request specific iFix for their OS platform. Please let me know if you'll be able to go that support route.

                       

                      As a summary - it's not an Extension Library issue, but rather JRE SR9 FP1 we have inside 8.5.3