-
1. Re: delay-database-insert-until and autogenerated PKs
sebastien_petrucci Feb 5, 2003 4:28 AM (in response to jimjonah)I agree ... I'm not really pleased with the fact that we need to use unknown pk in order to get auto-generated keys... It implies some problems, especially for CMR (how to reference an EJB pk if it's not declared ?).
Concerning the "delay-database-insert-until", I think it's more used to make it possible to have non-null foreign keys when you do CMR (great feature) and to provide bulk inserts.
Regards,
Sebastien. -
2. Re: delay-database-insert-until and autogenerated PKs
jimjonah Feb 5, 2003 8:08 PM (in response to jimjonah)Thanks.
After reading lots of messages about people struggling with the same thing, one of them struck a cord. While generally we use databases as the persistence engine, that may not always be the case, hence the lack of built-in support for autogenerating keys, claimed on poster.
The book EJB Design Patterns has a couple of solutions for generating unique keys so I'm going down that path. i.e. don't use the autogenerate feature and instead use a Block Sequence design pattern.
Jim -
3. Re: delay-database-insert-until and autogenerated PKs
sebastien_petrucci Mar 4, 2003 4:36 PM (in response to jimjonah)Hi,
After some testing, I just discovered that we can use the auto-generated key feature without having to use an unknown PK.
I'm using JBoss 3.2 RC2.
I can post an example if someone is interested.
Regards,
Sebastien. -
4. Re: delay-database-insert-until and autogenerated PKs
ronr Mar 10, 2003 8:51 PM (in response to jimjonah)please do.
-
5. Re: delay-database-insert-until and autogenerated PKs
sebastien_petrucci Mar 11, 2003 12:17 PM (in response to jimjonah)Ok :-)
Here is an example using MySQL and it's JDBC 3.0 driver.
The CMP 2.0 Entity bean named 'Foo' has 2 fields. 1 field called 'pk' of type Integer (I use MySQL, which can only auto-increment on numbers ...) and 1 field 'name' of type String.
We want 'pk' to be the PK of the bean generated by the DB, and 'name' to be a normal field.
First, you create everything exactly like 'pk' was a normal field. Then, in the jbosscmp-jdbc descriptor :
<ejb-name>Foo</ejb-name>
<table-name>foo</table-name>
<cmp-field>
<field-name>pk</field-name>
<column-name>foo_pk</column-name>
<auto-increment/>
</cmp-field>
<cmp-field>
<field-name>name</field-name>
<column-name>foo_name</column-name>
</cmp-field>
<entity-command name="mysql-get-generated-keys"/>
That's it. Nothing more to do.
The entity-command declaration could also be done in the section, but then it would apply to all the EJBs you declare.
Last advice : MAKE SURE THAT YOUR ENTITY BEANS THAT DO NOT NEED AUTO-INCREMENTED KEYS USE <entity-command name="default"/>, OTHERWISE THE PK WILL NOT BE INSERTED AT BEAN CREATION !!
Good luck,
Sebastien. -
6. Re: delay-database-insert-until and autogenerated PKs
dklehmann Mar 28, 2003 9:08 AM (in response to jimjonah)Hi guys,
I have followed Sebastien's example, but am still having a problem.
It seems that JBoss (I'm using version 3.2.0RC3) recognizes the <auto-increment> tag within my primary-key cmp field in jbosscmp-jdbc.xml. However, I unsuccessfully tried a number of entity commands, including a custom one, to retrieve the generated key from my SQL 2000 database. The error I keep getting is:
Failure while creating an OrderEntity bean: Entity with primary key [.0.] already exists
I don't have a primary key with the value '0', but I'm also not sure my entity command is correct:
<entity-command name="pk-sql">
SELECT (IDENT_CURRENT('tOrder') + 1)
</entity-command>
I haven't been able to understand how entity commands are used during the create method. Are they used BEFORE the actual insert, or AFTER(the above SQL command will return the next pk before the insert). Futhermore, I am not sure which entity commands are used in what way?
Has someone gotten this to work? Any help is appreciated.
Thanks,
Daniel -
7. Re: delay-database-insert-until and autogenerated PKs
jtbell Mar 28, 2003 4:02 PM (in response to jimjonah)The problem here is that if you are using an "enterprise" database that can be accessed by non-java programs how do you enforce the key generation unless the database does it? There really must be a way to use a database generated primary key.
-
8. Re: delay-database-insert-until and autogenerated PKs
sebastien_petrucci Apr 2, 2003 11:20 AM (in response to jimjonah)I quickly looked at the source code of JDBCPkSqlCreateCommand and it seems that :
- first the provided SQL (pk-sql) is executed to retrieve the next bean's PK
- a check is made to ensure that this PK is not already used
- the first CMP field of the bean marked as 'unknown-pk' is filled with the PK value (probably source of your problem)
- the insert is executed
If you look at JDBCGetGeneratedKeysCreateCommand or even JDBCMySQLCreateCommand, you will see that no check is done to ensure that a field is marked as unknown-pk. That's why it works immediatly by just using auto-increment with a normal PK definition.
You're very unlucky :-/
Anyway, I guess you could (quite easily) derive a specific command implementation from existing ones. Basically, you need to make an insert query (check wether the PK fields should be part of the query or not) and immediatly after execute 'select @@IDENTITY' to get the last generated key (the PK must be of IDENTITY type)... or something like that.
Another solution would be to buy a JDBC 3.0 compliant driver for M$ SQL SERVER and port your app to JDK 1.4 (if not already the case).
Good Luck !
Sebastien. -
9. Re: delay-database-insert-until and autogenerated PKs
dklehmann Apr 3, 2003 8:46 AM (in response to jimjonah)Sebastien,
Thanks a lot for the answer and the suggestions.
Acutally, I forgot to mention the following in my first post:
1. I am using a JDBC 4.0 driver for MS SQL
2. I am not using unknown-pk, but instead just the suggested approach of using only the 'auto-increment' tag in jbosscmp-jdbc.xml and the custom SQL command (I also left my getters and setters for my PK fields in the bean).
So, from your description it seems like it should be working, right? Could it be that in this approach JBoss attempts to insert the ENTIRE row including the PK? That would definitely not work since the PK field is declared as auto-increment... (By the way, I already unsuccessfully tried the unknown-pk approach, but it just seems that it should not be this complicated and that your suggested approach should do).
I will take a look at the source code in the indicated places, but am still curious what your thoughts are. Thanks a lot for the help.
Daniel -
10. Re: delay-database-insert-until and autogenerated PKs
sysuser1 Apr 15, 2003 11:34 PM (in response to jimjonah)Hi Sebastien,
Where can I get information/docs about <auto-increment/> and <entity-command>. I searched around and could not find anu documentation regarding this - even the JBossCMP2.0 docs. Can you point me where this is documented>
Thanks,
Trev -
11. Re: delay-database-insert-until and autogenerated PKs
sysuser1 Apr 15, 2003 11:36 PM (in response to jimjonah)Hi Sebastien,
Where can I get information/docs about <auto-increment/> and <entity-command>. I searched around and could not find anu documentation regarding this - even the JBossCMP2.0 docs. Can you point me where this is documented>
Thanks,
Trev -
12. Re: delay-database-insert-until and autogenerated PKs
sebastien_petrucci Apr 23, 2003 4:14 AM (in response to jimjonah)Hi,
Unfortunately, the paid docs do not seem to reflect all the changes of JBoss 3.2.x series yet.
I guess it will be fixed soon.
kr,
Sebastien. -
13. Re: delay-database-insert-until and autogenerated PKs
sebastien_petrucci Apr 23, 2003 4:31 AM (in response to jimjonah)Daniel,
What driver do u exactly use ?
I already noticed that when I use the MySQL-specific create command, the PK is not part of the insert query. I guess it's the same with the other create commands.
If you continue having problems with that, I think you could :
- use XDoclet (use CVS version) and let this nice tool generate all the XML descriptors for u. The CVS version supports all these auto-increment/create commands tags
- Use the JDBCGetGeneratedKeysCreateCommand (JDK 1.4 needed) in conjonction with a JDBC 3.0 compliant driver like :
http://www.j-netdirect.com/JSQLConnect/JSQLFeatures.html
or
http://www.idssoftware.com/jdbcdrv.html
kr,
Sebastien. -
14. Re: delay-database-insert-until and autogenerated PKs
pup99 Apr 24, 2003 1:24 PM (in response to jimjonah)Sebastien,
Was wondering if you could put up a copy of a small entity bean using auto-increment. I have been trying to get this to work for some time. Though I can get the insert to work correctly I do get an error after the insert seems to happen. So it does show up in the database, but I get an error back here is the first part
10:54:35,828 ERROR [LogInterceptor] TransactionRolledbackException, causedBy:
java.lang.IllegalArgumentException
at sun.reflect.UnsafeObjectFieldAccessorImpl.set(UnsafeObjectFieldAccess
orImpl.java:63)
at java.lang.reflect.Field.set(Field.java:519)
at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.setP
rimaryKeyValue(JDBCAbstractCMPFieldBridge.java:229)
Anyway, I could really use an example that works, so that I could understand where I am going wrong. I am sure I am not the only one who would find it usefull.
Thanks a lot for the help so far :)
Lyle