Re: a problem in about CHAR column in the select statement

That is the problem with using CHAR instead of VARCHAR2. Oracle pads the field out to the length specified, in this case 128 characters. To fix it (just a guess, but try):

>pstmt = conn.prepareStatement("select id from temp where col LIKE ?");

You will need to add % after the string you are looking for. In other words in a straight select statement, it would look like this:

select id from temp where col LIKE 'aa%'

A better solution MAY be to convert the column to VARCHAR2(128), but you will then need to trim out all of the trailing blanks.

xinhuang00_at_my-deja.com wrote in message <8k5964$1lm$1_at_nnrp1.deja.com>...
>Hi everyone, I really need a help here:
>I try to select a record from a table in Oracle by a CHAR column, but
>the record can not match because of the tailing blanks in the column:
>
>>Class.forName( "oracle.jdbc.driver.OracleDriver" );
>>System.out.println ("registered driver");
>>Connection conn =
>> DriverManager.getConnection

 ("jdbc:oracle:thin:@localhost:1521:orcl","scott", "tiger");
>>conn.setAutoCommit (true);
>>Statement stmt = conn.createStatement ();
>>ResultSet rset;
>>rset = stmt.executeQuery("create table temp (id int, col char(128))");
>>PreparedStatement pstmt;
>>pstmt = conn.prepareStatement("insert into temp (id, col) values
 (?,?)");
>>pstmt.setInt(1,1);
>>pstmt.setString(2,"aa");
>>rset = pstmt.executeQuery();
>>pstmt = conn.prepareStatement("select id from temp where col = ?");
>>pstmt.setString( 1, "aa" );
>>rset = pstmt.executeQuery ();
>>while (rset.next ()) {
>> System.out.println ("result = " + rset.getObject(1));
>>}
>
>However Oracle can not get back any record.
>But if I change the "aa" to "aa" and following 126 white spaces (add up
>to 128) and then oracle can match the record.
>
>So, does anyone know how to solve this problem. I really don't want to
>add so many white spaces in every of my record.
>
>Thank You Very Much !!!
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Fri Jul 07 2000 - 00:00:00 CDT