ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Maves <Nathan.Ma...@Sun.COM>
Subject Re: How to deal with a char column in select statement
Date Fri, 06 Jan 2006 15:44:24 GMT
Trim would be the way to go.  I would also look into fixing your data  
in the data base.  You could run run a quick sql statement to fix it.

update myTable set id = ltrim(rtrim(id));

That should remove all leading and trailing white space from you  
column id.

I would also suggest trimming the data either before you insert it  
(in java) or as the sql level.

insert into myTable (id,...) values (trim(#id#),...)

My 2 cents

Nathan

On Jan 6, 2006, at 8:17 AM, Voorhoeve, Niels {PBG} wrote:

> use the sql rtrim function in your query for the select and the  
> comparison.
>
> From: Michael TALLET [mailto:m.tallet@delta-informatique.com]
> Sent: Friday, January 06, 2006 10:09 AM
> To: user-java@ibatis.apache.org
> Subject: How to deal with a char column in select statement
>
> Hi,
>
>
> I have to query on tables which have CHAR columns (length 5 for  
> example) as ID.
>
> Let’s say we have some lines with these ID values : XXXXX, YYYYY,  
> ZZZ  [2 white spaces at the end]. As u can see there is no  
> restriction about the length of the value : it can less than five  
> characters.
>
>
> Here is my select statement in a sql map config file :
>
> <select id=”select1” parameterClass=”string” parameterMap=”result- 
> map” >
>
>             Select id, lib1, lib2 from myTable where id = #value#
>
> </select>
>
>
> Here is the problem : if I use the value “ZZZ” as parameter value  
> then the statement finds no result. I must pass “ZZZ  “ value to  
> make the query ok
>
>  My connection (an oracle connection actually) has the “fixed 
> [Default]String” property set to true, provided by an initial  
> properties file.
>
>  The strange thing is : the query (with this “ZZZ” value) works  
> fine with a spring class like JdbcTemplate
>
>
>
> Any help is greatly appreciated
>
>
> Regards,
>
> ____________________
>
> Michael Tallet
>
> Delta Informatique
>
>
>


Mime
View raw message