Handling of trailing spaces when reading CHAR fields

Thread view:

Benjamin Lorenz - 25 Aug 2003 21:33 GMT

Hi *,

Several jdbc drivers seem to handle trailing spaces on
CHAR fields differently.

Suppose you have a CHAR(20) field in a table, and you put
a string with length < 20 into this field, using
a prepared statement, and the setString() method.

Now, when doing a select, again with a prepared statement,
and reading the string with getString(), one database (SOLID) gives
me "thestring", with no trailing spaces, the other (Daffodil)
returns "thestring ", with a number of spaces appended.

Which one is right?

Moreover, can I define how to fill the remaining space in
the CHAR field (with spaces or NULLs)?

Thanks,
Benjamin

Reply to this Message

Joseph Weinstein - 25 Aug 2003 21:59 GMT

> Hi *,
>
[quoted text clipped - 14 lines]
> Moreover, can I define how to fill the remaining space in
> the CHAR field (with spaces or NULLs)?

This is a vaguery of fixed-length character columns. You should use the
more intuitive varchar column, if the DBMS offers it.
However, assuming you define a fixed-length character column, it is
you who should determine the full contents, in order to avoid ambiguity.
You should therefore pre-pad the value with blanks, or any other character
you want, and the DBMS will accept.
However, if the DBMS chooses to automatically pad-out any unspecified
characters, nothing is 'correct', it is just a DBMS-specific option in handling
an ambiguity between your column definition and your submitted data. It would
only be a bug if you inserted a value like "the string " and you got
back "the string".

Joe Weinstein at BEA

> Thanks,
> Benjamin

Reply to this Message

Ed Yu - 26 Aug 2003 02:16 GMT

The behavior of CHAR data type is fixed width with trailing spaces for ANSI
compliance databases. I belive that there is not a general way to change
this behavior. If you need variable length character field, use varchar.

> Hi *,
>
[quoted text clipped - 17 lines]
> Thanks,
> Benjamin

Reply to this Message

Real Gagnon - 26 Aug 2003 12:33 GMT

> Moreover, can I define how to fill the remaining space in
> the CHAR field (with spaces or NULLs)?

Some JDBC drivers give you choice about how to handle CHAR() column.

For example, with Oracle it's

oracle.jdbc.driver.OracleConnection c =
(oracle.jdbc.driver.OracleConnection)connection;

c.setDefaultFixedString(true);

Bye.

Signature

Real Gagnon from Quebec, Canada
* Looking for Java or PB snippets ? Visit Real's How-to
* http://www.rgagnon.com/howto.html

Reply to this Message

X_AWemner_X - 26 Aug 2003 14:22 GMT

> Several jdbc drivers seem to handle trailing spaces on
> CHAR fields differently.
[quoted text clipped - 7 lines]
> me "thestring", with no trailing spaces, the other (Daffodil)
> returns "thestring ", with a number of spaces appended.

It's just an db implementor's choice. But, I would first assume CHAR columns
be padded with trailing spaces when retrieving data from the server. Its
VARCHAR to give non-padded strings. This is what Ive used to see in a
databases I use.

I never use CHAR columns just because a padding might give a side effects.
You should use varchar types if possible and life is easier.

Reply to this Message

Benjamin Lorenz - 30 Aug 2003 08:46 GMT

> I never use CHAR columns just because a padding might give a side effects.
> You should use varchar types if possible and life is easier.

So, are there any situations in which you really _want_ to use
CHAR columns for a Java-based application?

I learned that performance might be a reason (database fragmentation...).

What are your experiences?

Thanks, again,
Benjamin

Reply to this Message

Handling of trailing spaces when reading CHAR fields

Free Magazines