Subject Re: [IBO] Re: ib_query ignores my data!
Author Jason Wharton

I use Oracle at work and in the case of a user wishing to have trailing
spaces then they must use a CHAR field - or a LOB of some sort. The user
entry is *always* padded with blanks to the full length of the field width.

The same actually happens with InterBase. InterBase will not trim anything

To have the minimum space used for a field, then a VARCHAR2 field is used.
This has trailing blanks (which I presume is spaces !) removed. It does
cause problems when extending a VARCHAR2 field, but that is another matter
and is reasonably well handled within Oracle anyway.

With InterBase a varchar is exactly the same as a char except that it allows
you to specify the length of significant characters. Again, no trimming is
done for you.

Thus if I store "hello world" in a char(15) field I get 4 blanks added to
the end, but if I store it in a VARCHAR2(15) field, I get exactly 11
characters stored.

Same as in InterBase. The varchar looks like a char but it has a smallint
telling that only the first 11 bytes are significant.

The data definitions are documented as is their behavior, so any database
being designed should use the correct data type for the field. If comms
flags (or whatever) are needed to be stored, then CHAR is the field type,
otherwise VARCHAR.
My opinion, trim VARCHAR and leave/pad CHAR field.

My opinion is exactly the same as yours. I want to keep trimming varchar
columns by default but discontinue doing any trimming by default to char
columns. The definition of the char type suggests that trimming is not an
issue because it is fixed width.

The only difference between Oracle and IB(?) is that on Oracle, the padding
carried out by the RDBMS and not by the application - so whatever the user
enters in a field is sent over the network and the RDBMS pads or trims as
appropriate. (Just to complicate matters).

I think I would like that. I have to pad char columns when using prepared
statements on inserts/updates. Let's try not to get into pad char issues
though. We could really make a mess of that one.

Thanks for the feedback.

Jason Wharton
CPS - Mesa AZ