Path: csiph.com!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From: markspace <-@.>
Newsgroups: comp.lang.java.programmer
Subject: Re: JDBC, PreparedStatement and named parameters
Date: Fri, 20 Jul 2012 16:08:32 -0700
Organization: A noiseless patient Spider
Lines: 68
Message-ID: <jucodj$cd3$1@dont-email.me>
References: <slrnk0i9rb.u9l.avl@gamma.logic.tuwien.ac.at> <jubmj6$ip8$1@dont-email.me> <slrnk0is4j.u9l.avl@gamma.logic.tuwien.ac.at> <jubsm4$o36$1@dont-email.me> <slrnk0j1lf.u9l.avl@gamma.logic.tuwien.ac.at> <juc1g8$olu$1@dont-email.me> <slrnk0jhun.u9l.avl@gamma.logic.tuwien.ac.at> <juchjt$vsa$1@dont-email.me> <slrnk0jn8n.u9l.avl@gamma.logic.tuwien.ac.at>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Injection-Date: Fri, 20 Jul 2012 23:08:36 +0000 (UTC)
Injection-Info: mx04.eternal-september.org; posting-host="61282af8d6595e8d991edb5ac03d6e00"; logging-data="12707"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1885h4+MtFs5/Dm3FlPWY++KYgRwg9ZxeE="
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:14.0) Gecko/20120713 Thunderbird/14.0
In-Reply-To: <slrnk0jn8n.u9l.avl@gamma.logic.tuwien.ac.at>
Cancel-Lock: sha1:v4BelD5IdAoLx8mbYUUDoXnCUoA=
Xref: csiph.com comp.lang.java.programmer:16168

On 7/20/2012 3:35 PM, Andreas Leitgeb wrote:
>
> Here's a slight simplification of my problem at hand:
> (I'm *NOT* asking for a solution nor even for help to this


Yeah but it's an interesting problem. ;)


>   problem - Unlike the Jdbc-question which I originally posted,
>   this one's not even related to Java.)
>
> I have a somewhat convoluted sql-problem at hand.  Two tables are joined,
> just not by a single join-column, but by overlapping intervals.
>
> Table1:   from1, to1
> Table2:   from2, to2
> Params:   from3, to3


I think I did something similar once, although there was no join 
involved.  Intersection isn't really hard, but if you have a lot of 
intervals to search it might be useful to look hard for an efficient 
solution.

Not posting "solutions" because you asked that none be posted....


>
> I have to identify the intersections of the table's intervals
> within the bounds of a third interval given as parameter.
> I figured it would take me quite a couple of tries to find which
> variant of conjunctive inequalities would actually work best,


I'm going to have to look those words up.


> and the params from3 and to3 would not only change their relative
> position often between consecutive trials, but will likely turn
> out to be used multiple times, each, in the query.
>
> With named parameters I could concentrate on the ordering of all
> the single "fromX < toY"-terms.
>
> As it is in jdbc, I'm instead facing a lot of "fromX < ?" and
> "toY < ?" terms as well as a hard time trying to memorize which
> of all the "?" was really meant to mean which of the params.


In a pinch, string substitution might work.

long start = ...
long end = ...
String sql = "Select * from SomeTable where from >= ::start:: & to <= 
::end::";
sql = sql.replaceAll( "::start::", Long.toString( start ) );
sql = sql.replaceAll( "::end::", Long.toString( end ) );

Cheesy, but it's "clear" what is being done.


Hmm, final thoughts... you have two tables joined on an *interval*? 
That doesn't seem right.  Time, or length, is continuous.  Normally you 
wouldn't expect the *exact* same values to appear in two places.  Are 
you sure this spec is correct?