Return-Path: 
 <user-java-return-12266-apmail-ibatis-user-java-archive=ibatis.apache.org@ibatis.apache.org>
Delivered-To: apmail-ibatis-user-java-archive@www.apache.org
Received: (qmail 40860 invoked from network); 9 Aug 2007 08:48:28 -0000
Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2)
  by minotaur.apache.org with SMTP; 9 Aug 2007 08:48:28 -0000
Received: (qmail 19170 invoked by uid 500); 9 Aug 2007 08:48:25 -0000
Delivered-To: apmail-ibatis-user-java-archive@ibatis.apache.org
Received: (qmail 19150 invoked by uid 500); 9 Aug 2007 08:48:25 -0000
Mailing-List: contact user-java-help@ibatis.apache.org; run by ezmlm
Precedence: bulk
List-Help: <mailto:user-java-help@ibatis.apache.org>
List-Unsubscribe: <mailto:user-java-unsubscribe@ibatis.apache.org>
List-Post: <mailto:user-java@ibatis.apache.org>
List-Id: <user-java.ibatis.apache.org>
Reply-To: user-java@ibatis.apache.org
Delivered-To: mailing list user-java@ibatis.apache.org
Received: (qmail 19139 invoked by uid 99); 9 Aug 2007 08:48:25 -0000
Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 09 Aug 2007 01:48:25 -0700
X-ASF-Spam-Status: No, hits=1.5 required=10.0
	tests=HELO_DYNAMIC_DHCP,SPF_PASS
X-Spam-Check-By: apache.org
Received-SPF: pass (nike.apache.org: local policy)
Received: from [195.130.132.35] (HELO nocme2bl3.telenet-ops.be)
 (195.130.132.35)
    by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 09 Aug 2007 08:48:18 +0000
Received: from hoboe3bl6.telenet-ops.be (hoboe3bl6.telenet-ops.be
 [195.130.136.39])
	by nocme2bl3.telenet-ops.be (Postfix) with ESMTP id 358F4334002
	for <user-java@ibatis.apache.org>; Thu,  9 Aug 2007 10:47:59 +0200 (CEST)
Received: from nocme1bl6.telenet-ops.be (localhost.localdomain [127.0.0.1])
	by hoboe3bl6.telenet-ops.be (Postfix) with ESMTP id 11B393EC132
	for <user-java@ibatis.apache.org>; Thu,  9 Aug 2007 10:47:59 +0200 (CEST)
From: "davy" <davypulinckx@telenet.be>
To: user-java@ibatis.apache.org
Importance: Normal
Sensitivity: Normal
Message-ID: <W4917623093269671186649279@nocme1bl6.telenet-ops.be>
X-Originating-IP: [193.191.216.52]
X-Forwarded-For: [(null)]
Date: Thu, 09 Aug 2007 08:47:59 +0000
Subject: Re: Small problem with date.
MIME-Version: 1.0
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: 8bit
X-Virus-Checked: Checked by ClamAV on apache.org

I have done what you have mailto me, but we use db2 and there is the problem I get every time a error message from DB2 when I change something in the xml file
Is there really now way to make it like this or something else, that I don’t have to change the xml file.


Example.setOrderByClause("substr(number,13,8)");
Example.createCriteria().andnumberBetween(fDate, uDate);


Every idea is welcome.
Davy




-----Original Message-----
From: Christopher Lamey [mailto:clamey@localmatters.com] 
Sent: woensdag 8 augustus 2007 22:07
To: user-java@ibatis.apache.org
Subject: Re: Small problem with date.

Ah, the joy of legacy systems.

If you only have one SQL call that needs this, then I would write a new
SELECT element in your sqlmap and call it through the DAO.

Something like this in the sqlmap xml, where you reuse the abator result
map:

<select id="search_by_date" parameterClass="map"
        resultClass=" abatorgenerated_BeheerResult">
    select * 
    from beheer
    where (substr(number,13,8)
    between #startNum:BIGINT# and #stopNum:BIGINT#
</select>

Then add the method signature to the DAO interface:

public List<Beheer> selectByDateNumber(long start, long stop) throws
SQLException;


And then implement it in the DAOImpl class:

public List<Beheer> selectByDateNumber(long start, long stop) throws
SQLException {
    Map<String, Long> params = new HashMap<String, String>();

    params.put("startNum", new Long(start));

    params.put("stopNum", new Long(stop));
    return sqlMapClient.queryForList("BEHEER.search_by_date", params);
}

I'm not sure what type you're using for the start and stop parameters, so
you should change them as needed.

Cheers,
Chris

On 8/8/07 1:41 PM, "Davy" <davypulinckx@telenet.be> wrote:

> Hooow, then I have a big problem, is there now other way to get 8 numbers
> (date) out of that specific number?
> Is this really not possible in Ibatis.
> Adding an extra column in the table is a problem.
> What would be the best way outside adding and extra column to the database
> to solve this problem?
> Any suggestion would be nice.
> 
> Davy
> 
> 
> 
> -----Original Message-----
> From: Richard Yee [mailto:ryee@cruzio.com]
> Sent: woensdag 8 augustus 2007 16:14
> To: user-java@ibatis.apache.org
> Subject: Re: Small problem with date.
> 
> Davy,
> I suggest adding a date column to your table. It will make things a lot
> easier in the long run. You can run a program on your existing data to
> populate the new field. the search performance will be better too.
> 
> -Richard
> 
> davy wrote:
>> I have the next problem, I have a query which must filters a certain
> period out a number. for example:
>> this is the number 54789635479E200708081545 this means
>> 2007 year 08 month 08 day 15 hour 45 minutes.
>> Now, i have a query (substr(number,13,8) between ? and ?
>> 
>> This means that the user can give a certain from date and until date
> (year,month,day) sow the query will search on this date in these number
>> 
>> I have developed this :
>> 
>> Example.setOrderByClause("substr(number,13,8)");
>> Example.createCriteria().andnumberBetween(fDate, uDate);
>> 
>> fDate and uDate = are both strings, these are the 2 dates that the user
> has given to search for.
>> for example 
>> fDate = 20050101 from
>> uDate = 20063112 until
>> 
>> When i use the query, al my numbers are displayd, butt the query have not
> worked properly.
>> Is there someone that has a proposal how it must be done.
>> 
>> 
>> 
>> 
>> 
>>   
> 
>