-
1. Re: Does Jboss support IDENTITY field in MSSQL SERVER / ORAC
lekkim Dec 17, 2003 1:24 PM (in response to marcoyeung)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 Dec 23, 2003 7:10 AM (in response to 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 Dec 23, 2003 1:27 PM (in response to marcoyeung)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 Dec 23, 2003 9:59 PM (in response to 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 Dec 23, 2003 10:13 PM (in response to 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 Dec 29, 2003 2:28 PM (in response to marcoyeung)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 Dec 29, 2003 2:32 PM (in response to marcoyeung)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 Jan 2, 2004 3:31 AM (in response to marcoyeung)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 Jan 2, 2004 5:35 PM (in response to marcoyeung)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