On May 24, 2012, at 5:06 PM, Adam Heath wrote: > On 05/24/2012 03:22 AM, Jacopo Cappellato wrote: >> >> On May 24, 2012, at 9:04 AM, Jacques Le Roux wrote: >> >>> From: "Jacopo Cappellato" <jacopo.cappell...@hotwaxmedia.com> >>>> On May 23, 2012, at 9:25 PM, Adam Heath wrote: >>>> >>>>> On 05/23/2012 01:13 PM, Jacopo Cappellato wrote: >>>>>> Using count() was simply a trick to be sure to get a record back even if >>>>>> the entity is empty... but if we use an entity that we are sure is >>>>>> populated we don't have to use it, of course. Of course if we use >>>>>> count(*) we have to use it in an entity with a very low number of >>>>>> rows... but I too would prefer to avoid it. >>>>> >>>>> select 1; >>>> >>>> Thank you Adam, this is the "trick" I was looking for when I mentioned >>>> option #3 in my first email. >>>> I will test the system with it and then commit. >>>> >>>> Jacopo >>> >>> Which entity will you use Jacopo? >> >> When I first saw Adam's comment I thought he was suggesting to simply execute >> >> "select 1" >> >> in order to run a "query" with no table to return a "record" with one field >> containing 1. >> But I maybe misunderstood the suggestion and at least on Derby this doesn't >> seem to work; but I still need to fully test. >> In my opinion adding a new entity for this would be an overkill; one >> solution could be the following: >> >> select count(key_name) from entity_key_store where 1 = 2 > > Ah, grr, I'm so used to postgres, sorry for the trick not working. > > In oracle, postgres, and mysql, the following works. It returns 0 > rows, but tests the connection, and sql parsing in the server. Would > this be good enough? > > select 1 from entity_key_store where key_name = '' limit 1; > > Using '1 = 2' still causes a SEQ_SCAN on the table(explain in postgres > says this). key_name has an index on it, so that part is faster. > Since key_name is the primary key, you might not need the limit, but > it couldn't hurt(except for a slightly slower parsing of the sql).
Yeah, I like it, thanks man! Jacques: it is probably not a bad idea to add an attribute to make it configurable from the datasource xml setup but at the moment I would not spend too much time on this; if you want I can create a Jira ticket with low priority as a reminder for the future. But I would actually avoid it also because the new JDBC API has a isValid (or similar) method that should replace this trick at some point in the future. Thanks again, Jacopo
