Right, but shouldn't
ora_ph_type => 5 which Michael used too, should behave same as
ora_ph_type => 96 and not strip the trailing spaces, at leaset that is what
is mentioned in Changes.
5=> STRING, doesn't strip trailing spaces, embedded \0 ends string
96=> CHAR, doesn't strip trailing spaces, embedded \0 okay
Ilya Sterin
-----Original Message-----
From: Michael A. Chase
To: Sterin, Ilya; 'Fox, Michael '; [EMAIL PROTECTED]
Sent: 05/01/2001 8:38 AM
Subject: Re: Oracle behaviour when inserting strings containing only blanks
What's needed is a documentation patch. Attribute ora_ph_type already
handles this situation. If I recall, it can be either an attribute in
bind_param() or a database handle attribute.
I think what Michael needs would be handled by either of these, but I
haven't tried them myself:
$dbh->{ora_ph_type} = 96;
$sth->bind_param( 1, $val, { ora_ph_type => 96 } );
The possible values are listed in DBD-Oracle-1.06/Changes.
--
Mac :})
** I normally forward private database questions to the DBI mail lists.
**
Give a hobbit a fish and he'll eat fish for a day.
Give a hobbit a ring and he'll eat fish for an age.
----- Original Message -----
From: "Sterin, Ilya" <[EMAIL PROTECTED]>
To: "'Fox, Michael '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Tuesday, May 01, 2001 6:40 AM
Subject: RE: Oracle behaviour when inserting strings containing only
blanks
> I wonder if because of it binding as as string the driver attempts to
trim
> the trailing spaces, though leaving it as '' instead of ' ', when you
bind
> as a char, char does not trim trailing spaces, though leaving the ' '
as
it
> is.
> Any more ideas guys, I can go in and modify and possibly submit a
patch.
> Unless there is a good reason for this.
>
> Ilya Sterin
>
> -----Original Message-----
> From: Fox, Michael
> To: '[EMAIL PROTECTED]'
> Sent: 05/01/2001 12:27 AM
> Subject: Oracle behaviour when inserting strings containing only
blanks
>
> I see this has come up before, but looking at the archives it does not
> seem
> to have been properly resolved.
>
> Inserting a string containing only blanks into a NOT NULL column
gives
> an
> error, as in the example (note: the third insert is actually trying to
> insert a single space character):
>
> #!/u02/devel/bin/perl -w
> #
> use DBI;
>
> my $db_user='****';
> my $db_name='dwht01';
> my $db_type='Oracle';
>
> my $dbh=DBI->connect("dbi:$db_type:$db_name", $db_user, $db_user,
> {AutoCommit => 0, PrintError => 0})
> || die "Cannot connect to $db_name: $DBI::errstr";
>
> # create table with a null col
> $dbh->do('DROP TABLE xyzzy')
> || warn "cannot drop table $DBI::errstr\n";
> $dbh->do('CREATE TABLE xyzzy(a varchar(10) null, b varchar2(10) not
> null)')
> || die "cannot create table $DBI::errstr\n";
>
> my $sql="INSERT INTO xyzzy(a,b) VALUES(?,?)";
> my $sth=$dbh->prepare($sql)
> || die "cannot prepare $sql $DBI::errstr\n";
>
> # normal insert
> $sth->execute('1','a')
> || warn "cannot insert ['1','a'] $DBI::errstr\n";
> # null insert
> $sth->execute('2',undef)
> || warn "cannot insert ['2',undef] $DBI::errstr\n";
> # blank insert
> $sth->execute('3',' ')
> || warn "cannot insert ['3',' '] $DBI::errstr\n";
>
> $sth->finish;
>
> $sql="INSERT INTO xyzzy(a,b) VALUES(5,' ')";
> $sth=$dbh->prepare($sql)
> || die "cannot prepare $sql $DBI::errstr\n";
> $sth->execute
> || warn "cannot insert ['5',' '] $DBI::errstr\n";
> $sth->finish;
>
> $sql='SELECT * FROM xyzzy';
> $sth=$dbh->prepare($sql)
> || die "cannot prepare $sql $DBI::errstr\n";
> $sth->execute;
> $sth->dump_results;
> $sth->finish;
>
> $dbh->commit;
> $dbh->do('DROP TABLE xyzzy')
> || die "cannot drop table $DBI::errstr\n";
> $dbh->disconnect;
>
> which gives
>
> cannot drop table ORA-00942: table or view does not exist (DBD ERROR:
> OCIStmtExecute)
> cannot insert ['2',undef] ORA-01400: cannot insert NULL into
> ("FOXM"."XYZZY"."B") (DBD ERROR: OCIStmtExecute)
> cannot insert ['3',' '] ORA-01400: cannot insert NULL into
> ("FOXM"."XYZZY"."B") (DBD ERROR: OCIStmtExecute)
> '1', 'a'
> '5', ' '
> 2 rows
>
> ie the 3rd insert unexpectedly fails
>
> The platform is alpha OSF1, DBI 1.15 and DBD-Oracle 1.06, Oracle 8.0.4
>
> Finally reading the Changes file, I found the database handle
attribute
> ora_ph_type, which sets the default binding (plus a request for
testing
> it!); I tried setting it to STRING with
>
> {AutoCommit => 0, PrintError => 0, ora_ph_type => 5})
>
> with no luck. I then tried CHAR using
>
> {AutoCommit => 0, PrintError => 0, ora_ph_type => 96})
>
> and it inserted correctly, producing:
>
> cannot drop table ORA-00942: table or view does not exist (DBD ERROR:
> OCIStmtExecute)
> cannot insert ['2',undef] ORA-01400: cannot insert NULL into
> ("FOXM"."XYZZY"."B") (DBD ERROR: OCIStmtExecute)
> '1', 'a'
> '3', ' '
> '5', ' '
> 3 rows
>
> From the documentation, I thing the STRING type should have worked.
> Hopefully using CHAR will not create other side effects, although it
has
> correctly inserted a single space into a VARCHAR2(10) column in this
> example.
>
> Michael Fox
> Australia Post
>
>
> CAUTION
>
> This e-mail and any files transmitted with it are privileged and
> confidential information intended for the use of the addressee. The
> confidentiality and/or privilege in this e-mail is not waived, lost or
> destroyed if it has been transmitted to you in error. If you have
> received this e-mail in error you must (a) not disseminate, copy or
take
> any action in reliance on it; (b) please notify Australia Post
> immediately by return e-mail to the sender; and (c) please delete the
> original e-mail.
>
