9 Replies Latest reply on Jan 2, 2004 5:35 PM by lekkim

    Does Jboss support IDENTITY field in MSSQL SERVER / ORACLE??

    marcoyeung

      Does Jboss support IDENTITY field in MSSQL SERVER ??

      In SQL SERVER, I have a table which has a primary key field which use IDENTITY to autogenerate the id, does jboss support this if i use a entity bean on this table and can jboss get back this id in a smart way (not using select) after I inserted a new row in this table with that entity bean?.


      Similar case in Oracle , if I use trigger to auto-generate the primary key in my table, can JBoss know how to manipulate it, that means insert a new record in the table with a entity bean and get back the newly generated primary key??


      thank you very much

      Marco

        • 1. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
          lekkim

          Yes you can use identity columns in from MSSQL and Oracle. I use auto-incremented column from DB2 (just got it working today). The trick is to read up on "entity commands" (see page 621 in the pay-for documentation).

          Entity commands bascially allows you to specify how the entity is created incl. how the primary key is generated. I use entity commands to obtain the primary key from a sequence in DB2.

          I know there is a entity command for SQL server.

          lekkim

          • 2. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
            marcoyeung

            thank you very much, lekkim, cos I am working on a cross database projet. i will use DB2 too, could you please post the example code to set up this??


            thanks
            Marco

            • 3. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
              veemal

              Did anybody got auto-increment to work with MSSQL?
              I've used:
              <entity-command name="mssql-fetch-key" class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCSQLServerCreateCommand">
              </entity-command>


              But, I got the following error:
              [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Cannot insert the value NULL into column 'job_no', table 'opticlab.dbo.jobi'; column does not allow nulls. INSERT fails.


              • 4. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
                marcoyeung

                veemal,
                make sure your id column has set as IDENTITY and allow it to auto-increment (set identity to yes) in SQL SERVER table design view.

                this may be the solution.


                Marco

                • 5. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
                  marcoyeung

                  dear all,
                  I am currently working on Oralce ID auto-increment on
                  JBOSS 3.2.3.
                  I set the jbosscmp-jdbc.xml like this for my entity bean:

                  <cmp-field>
                  <field-name>id</field-name>
                  <column-name>ID</column-name>
                  <auto-increment/>
                  </cmp-field>
                  ....
                  <entity-command name="oracle-sequence"/>

                  But when I try to deploy the bean, i encoutered the following error in JBOSS console: java.lang.IllegalStateException: auto-increment template not found, but I am sure that in the standardjbosscmp-jdbc.xml, there is already an entity-command named oracle-sequence defined. is there anyone has idea for this? thanks.


                  ++++++++++++++ERROR++++++++++++
                  java.lang.IllegalStateException: auto-increment template not found
                  at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addField(JDBCStartCommand.java:
                  567)
                  at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.getEntityCreateTableSQL(JDBCSta
                  rtCommand.java:431)
                  at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:9
                  3)
                  at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreMana
                  ger.java:484)
                  at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:388
                  )
                  at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:15
                  2)
                  at org.jboss.ejb.EntityContainer.startService(EntityContainer.java:342)
                  at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
                  at sun.reflect.GeneratedMethodAccessor45.invoke(Unknown Source)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:9
                  76)
                  at $Proxy14.start(Unknown Source)
                  at org.jboss.system.ServiceController.start(ServiceController.java:394)
                  at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                  at $Proxy29.start(Unknown Source)
                  at org.jboss.ejb.EjbModule.startService(EjbModule.java:331)
                  at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
                  at sun.reflect.GeneratedMethodAccessor45.invoke(Unknown Source)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:9
                  76)
                  at $Proxy14.start(Unknown Source)
                  at org.jboss.system.ServiceController.start(ServiceController.java:394)
                  at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                  at $Proxy12.start(Unknown Source)
                  at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:544)
                  at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
                  at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
                  at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
                  at sun.reflect.GeneratedMethodAccessor40.invoke(Unknown Source)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                  at $Proxy6.deploy(Unknown Source)
                  at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.j
                  ava:302)
                  at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.jav
                  a:476)
                  at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(Abs
                  tractDeploymentScanner.java:201)
                  at org.jboss.deployment.scanner.AbstractDeploymentScanner.startService(AbstractDep
                  loymentScanner.java:274)
                  at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:192)
                  at sun.reflect.GeneratedMethodAccessor10.invoke(Unknown Source)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:9
                  76)
                  at $Proxy0.start(Unknown Source)
                  at org.jboss.system.ServiceController.start(ServiceController.java:394)
                  at sun.reflect.GeneratedMethodAccessor9.invoke(Unknown Source)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                  at $Proxy4.start(Unknown Source)
                  at org.jboss.deployment.SARDeployer.start(SARDeployer.java:226)
                  at org.jboss.deployment.MainDeployer.start(MainDeployer.java:832)
                  at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:642)
                  at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:605)
                  at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:589)
                  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.ja
                  va:25)
                  at java.lang.reflect.Method.invoke(Method.java:324)
                  at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatche
                  r.java:284)
                  at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:546)
                  at org.jboss.mx.util.MBeanProxyExt.invoke(MBeanProxyExt.java:177)
                  at $Proxy5.deploy(Unknown Source)
                  at org.jboss.system.server.ServerImpl.doStart(ServerImpl.java:384)
                  at org.jboss.system.server.ServerImpl.start(ServerImpl.java:291)
                  at org.jboss.Main.boot(Main.java:150)
                  at org.jboss.Main$1.run(Main.java:395)
                  at java.lang.Thread.run(Thread.java:536)

                  • 6. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
                    lekkim

                    Sorry for the late response - I have been a little away from my PC... :-)

                    I use XDoclet for the generation of my entities. For an entity with auto-incremented primary key I use the following:

                    * @ejb.bean
                    * name="Example6Record"
                    * jndi-name="ejb/cmptest/example6/remote/Example6Record"
                    * local-jndi-name="ejb/cmptest/example6/local/Example6Record"
                    * view-type="both"
                    * type="CMP"
                    * reentrant="false"
                    * cmp-version="2.x"
                    * schema="Example6Record"
                    * primkey-field="recordId"
                    * @jboss.persistence
                    * create-table="false"
                    * remove-table="false"
                    * table-name="example6_record"
                    * @jboss.table-name
                    * table-name="example6_record"
                    * @jboss.entity-command
                    * name="db2-example6_record"
                    * @ejb.permission
                    * role-name="AppUser"
                    */

                    I have a jbosscmp-jdbc-entity-commands.xml file in my XDoclet merge directory. The file contains the following (please note that the name maps to the name given in the XDoclet tags in the javadoc):
                    <entity-commands>
                    <entity-command name="db2-example6_record" class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCPkSqlCreateCommand">
                    select max(record_id)+1 from example6_record
                    </entity-command>
                    </entity-commands>

                    Not a high-performing SQL statement but you get the idea...

                    • 7. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
                      lekkim

                      Sorry forgot to add that the entity-commands are added to the bottom of the jbosscmp-jdbc.xml deployment descriptor. The entity-comand name specified in the javadoc tags are added to the entity tag:


                      <ejb-name>Example6Record</ejb-name>
                      <create-table>false</create-table>
                      <remove-table>false</remove-table>
                      <table-name>example6_record</table-name>
                      <cmp-field>
                      <field-name>recordId</field-name>
                      <column-name>record_id</column-name>
                      <auto-increment/>
                      </cmp-field>
                      <cmp-field>
                      <field-name>name</field-name>
                      <column-name>name</column-name>
                      </cmp-field>
                      <entity-command name="db2-example6_record">
                      </entity-command>

                      • 8. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
                        ebaskoro

                        Hi lekkim,

                        To tell you the truth, I don't really like the SQL query:

                        SELECT MAX(record_id)+1 FROM example6_record

                        as you show. What happen if another row is created before the query is executed? You will have a key that points to the wrong row. As a result, the data integrity is lost.

                        Am I right?

                        Regards,

                        Eki

                        • 9. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
                          lekkim

                          I couldn't agree more... :-) The example is from an demo application I have where I test the different types of CMP relationships in a "controlled" environment...

                          Normally I use sequences.

                          lekkim