Re: Problem with fixed length fields.

From: Kris Jurka <books(at)ejurka(dot)com>
To: Dave Smith <dave(dot)smith(at)candata(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Problem with fixed length fields.
Date: 2004-10-23 17:50:42
Message-ID: Pine.BSO.4.56.0410231239390.19312@leary.csoft.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

On Sat, 23 Oct 2004, Dave Smith wrote:

> Platform:
> Postgres 7.4.5, lastest JDBC driver from CVS.
>
> Table:
> create table t(a char(2),b char(2));
> insert into t values (' ',' ');
>
> Jdbc:
>
> This query finds nothing
> PreparedStatement st = db.prepareStatement("select * from t where a=?
> and b=?");
> st.setString(1," ");
> st.setString(2," ");
> ResultSet rs = st.executeQuery();
>
> This query works
> rs = db.prepareStatement("select * from t where a=' ' and b='
> '").executeQuery();
>

This is a problem with the driver because it is typing the ' ' values as
text, not char, so the comparison doesn't work right because these two
types handle trailing spaces differently. Notice:

jurka=# select length(' '::char);
length
--------
0
(1 row)

jurka=# select length(' '::text);
length
--------
1
(1 row)

At the moment setObject(i, str, Types.CHAR) doesn't work, but we could
make that happen, but it just doesn't seem like a good workaround.
Alternatively you could use ?::char in your query or str.trim(), but
that's certainly not intuitive or user friendly. We've got to come up
with something better than this.

Oliver, any ideas?

Kris Jurka

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Tom Lane 2004-10-23 18:40:19 Re: Problem with fixed length fields.
Previous Message Dave Smith 2004-10-23 11:54:10 Problem with fixed length fields.