Peter, I understand, but silly or not silly, if it's the standard, I'd
rather have it followed than to fullfill someones request based on their
current need. I totally agree and you have a good argument, but the
standard is the standard, and we'll leave not following it up to the vendors
like M$ and Oracle.
Ilya
-----Original Message-----
From: Peter J. Holzer
To: '[EMAIL PROTECTED] '
Sent: 2/28/02 3:11 PM
Subject: Re: Whitespace being truncated with Oracle
On 2002-02-28 13:38:23 -0800, Michael Peppler wrote:
> Jeff Hunter writes:
> > I agree, it should be fixed.
> >
> > Peter J. Holzer wrote:
> >
> > >I don't think the current behaviour[1] is correct. In perl,
strings can
> > >have trailing spaces: "test" and "test " compare as not equal.
> > >In Oracle varchar2 can store strailing spaces: If I store 'test '
in a
> > >varchar2 column, I get back 'test ' and not 'test' or 'test
'.
>
> Are you *sure* that you get 'test ' back???
dialog:~ 22:56 103% sqlplus hjp@dv
SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 28 22:56:38 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
With the Partitioning option
JServer Release 8.1.7.2.0 - Production
SQL> drop table foo;
Table dropped.
SQL> create table foo (t varchar2(40));
Table created.
SQL> insert into foo(t) values('test ');
1 row created.
SQL> select t || 'x' from foo;
T||'X'
-----------------------------------------
test x
SQL>
dialog:~ 23:00 106% ./listtable.pl dv hjp <censored> foo | od -c
0000000 T \n t e s t \n
0000012
dialog:~ 23:00 107% cat listtable.pl
#!/usr/bin/perl -w
use strict;
use DBI;
my @driver_names = DBI->available_drivers;
my $dbh = DBI->connect("dbi:Oracle:${ARGV[0]}", "${ARGV[1]}",
"${ARGV[2]}");
my $sth = $dbh->prepare("select * from " . $ARGV[3]);
my $rv = $sth->execute;
print join('|', (@{$sth->{NAME}})), "\n";
while (my @ary = $sth->fetchrow_array) {
print join('|', @ary), "\n";
}
> I'm not be an Oracle specialist, but I know that trailing spaces in
> varchar() columns are normally removed on insert. This is definitely
> the case for Sybase - irrespective of the client that is used to
> access the data.
At least sqlplus doesn't remove spaces on insert, and a select returns
them from both sqlplus and perl.
> I also seem to recall that this behaviour is a SQL
> standard.
Quite possible, as mysql seems to behave the same. It's still silly.
hp
--
_ | Peter J. Holzer | My definition of a stupid question is
|_|_) | Sysadmin WSR / LUGA | "a question that if you're embarassed to
| | | [EMAIL PROTECTED] | ask it, you stay stupid."
__/ | http://www.hjp.at/ | -- Tim Helck on dbi-users, 2001-07-30
