I am using oracle. We want the equivalent of an IDENTITY or autonumber column, so we created views on top of our tables, and use a sequence to generate the primary key when an insert attempts to insert null. This works fine in SQL.
Most JDBC drivers also have some functionality that allows auto generated keys to be returned. Here's the error we get when we try it from JBoss.
Exception in thread "main" javax.ejb.CreateException: Primary key for created instance is null.
Wouldn't it be wise for the container to attempt the insert with key return enabled, and then see if it gets a primary key for the object. If a proper primary key is returned, then update the bean, and continue. If the needed keys aren't returned (DB doesn't support it, schema doesn't match EJBs, etc....), then rollback the transaction and throw the exception just like before.
This would allow for much more efficient insert statements, as we wouldn't
have to do one call to get the key, and a second call to perform the insert.
Oh, and of course throw the exception immediately if there is no transaction, as there would be no way to recover in that case.