RE: [JBoss-user] select count(*) from ... during CMP bean creation
> It occurs to me that if you expect the insert to normally > succeed, you could start by trying the insert, if it fails > you can investigate further with the count(*) query to > see if the problem is the existing row. This would be > one access for success, 2 for failure instead of 1+ > for failure and 2 for success (Even if the key is missing, > the insert might fail for other reasons.. maybe a unique > index). I support this idea. Some time before I've used the same technique for the small datawarehouse, it worked perfectly. > Another possibility (presumably for the future) would be > to have JAWS customizable with a plugin/database > vendor and recognize their error messages. That would be great. I've tried to customize the JAWS jdbc command object, but left that idea because of complexities: The reason is the inheritence of all JDBC. Usually one need to change only JDBCCommand in order to customize it to the database, but then you're not able to tell all other commands to use new one. I suggest that we define the abstract class that can takes JDBCCommand as constructor parameter and then invokes all methods on the passed JDBCCommand. All concrete commands should be inherited from that abstract class. It seems to me that in this case we will be able to customize JAWS for different databases easily. Regards, Roman ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] select count(*) from ... during CMP bean creation
Hi, On 2001.05.14 11:47:44 -0400 danch (Dan Christopherson) wrote: > [EMAIL PROTECTED] wrote: > > > Hi all, > > > > Is there any possibility to switch off the additional lookup when > creating > > the CMP entity bean? > > > > As far as I know, JAWS first invokes the "SELECT COUNT(*) FROM > MYBEANTABLE > > WHERE MYBEANPK = ?", checks if the column contains 0, and only then > issues > > the "INSERT INTO MYBEANTABLE ". > > > > Abovementioned select is quite expensive operation if the database is > quite > > large (even if I create all indexes there). Hovever, I can create > unique > > index on the PK column, and JBoss will receive the SQLException during > > insert... So, anyway I hope get the CreateException on the client side, > > which is quite acceptable for me. > > > I'd really like make autocreation of the primary key constraint default > on again - you can do this manually by adding > true to the jaws.xml section for > that entity or to standardjaws.xml (please reference the jaws.dtd from > CVS first, I'm not sure on the spelling of that option. > > Without a primary key constraint (or index of some sort), performance > will be bad with large tables. > > Given a primary key constraint (or a unique index) the performance of > the existence check should be much better, correct? > > > > > > So the question is: is there any possibility to switch the existence > lookup? > > > The problem here is that we need to throw DuplicateKeyException, not > just CreateException. Given that there's no SQLException sub-type that > we can count on, and there's no database independent way to determine if > the SQLException is from a key violation, I think we're stuck with > something similiar to what we have. I can be persuaded otherwise, > however. > > -danch It occurs to me that if you expect the insert to normally succeed, you could start by trying the insert, if it fails you can investigate further with the count(*) query to see if the problem is the existing row. This would be one access for success, 2 for failure instead of 1+ for failure and 2 for success (Even if the key is missing, the insert might fail for other reasons.. maybe a unique index). Another possibility (presumably for the future) would be to have JAWS customizable with a plugin/database vendor and recognize their error messages. david jencks > > > ___ > JBoss-user mailing list > [EMAIL PROTECTED] > http://lists.sourceforge.net/lists/listinfo/jboss-user > ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user
Re: [JBoss-user] select count(*) from ... during CMP bean creation
[EMAIL PROTECTED] wrote: > Hi all, > > Is there any possibility to switch off the additional lookup when creating > the CMP entity bean? > > As far as I know, JAWS first invokes the "SELECT COUNT(*) FROM MYBEANTABLE > WHERE MYBEANPK = ?", checks if the column contains 0, and only then issues > the "INSERT INTO MYBEANTABLE ". > > Abovementioned select is quite expensive operation if the database is quite > large (even if I create all indexes there). Hovever, I can create unique > index on the PK column, and JBoss will receive the SQLException during > insert... So, anyway I hope get the CreateException on the client side, > which is quite acceptable for me. I'd really like make autocreation of the primary key constraint default on again - you can do this manually by adding true to the jaws.xml section for that entity or to standardjaws.xml (please reference the jaws.dtd from CVS first, I'm not sure on the spelling of that option. Without a primary key constraint (or index of some sort), performance will be bad with large tables. Given a primary key constraint (or a unique index) the performance of the existence check should be much better, correct? > > So the question is: is there any possibility to switch the existence lookup? The problem here is that we need to throw DuplicateKeyException, not just CreateException. Given that there's no SQLException sub-type that we can count on, and there's no database independent way to determine if the SQLException is from a key violation, I think we're stuck with something similiar to what we have. I can be persuaded otherwise, however. -danch ___ JBoss-user mailing list [EMAIL PROTECTED] http://lists.sourceforge.net/lists/listinfo/jboss-user