Re: citext data type does not work with JDBC PreparedStatement?

On 6/08/2011 5:28 PM, Anton Moiseev wrote:
> Hi,
>
> I wanted to have case-insensitive user names in my db and found that
> citext postgresql data type
> (http://www.postgresql.org/docs/8.4/interactive/citext.html) is exactly
> what I need.
>
> So I have added to my db and it seemed to work fine when query db from
> command line interface, but when I run it from java prepared statement,
> things do not work as expected.
>
> For example, I have user name 'Leon' stored in the db and want to get
> password for him.
>
> If I execute query in sql console:
> SELECT password FROM users WHERE name = 'leon';

I hope that's not an example from your code... because storing passwords
in clear text is almost always an *INCREDIBLY* bad idea.

If at all possible, hash the password using a salted hash function, and
compare the hashes when checking passwords.

> final String query = "SELECT password FROM users WHERE name = ?";
> final PreparedStatement stmt =
> dbConnection.prepareStatement(query);
> stmt.setString(1, "leon");

> password won't be found.

What's the query sent to the backend? Enable query and parameter logging
in postgresql.conf, re-run your test, and see what the server receives.

--
Craig Ringer