This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: <dbi-users@perl.org>
From: "Harald Meier" <meh1@rts.at>
Subject: trim trailing spaces when inserting varchar2
Date: Mon, 15 Oct 2001 12:24:41 +0200
Hi All,
when I do a
$str="abc ";
$sth=$dbh->prepare("INSERT INTO test (x) values ( ? )");
$sth->bind_param(1,$str);
$sth->execute;
the string will be cutted off to "abc".
is there a way to switch off this -feature- ?
i dont want any strings to be trimmed.
I know about $str=$dbh->quote($str);
but I want something globally, not only for one string.
===
To: "Harald Meier" <meh1@rts.at>, <dbi-users@perl.org>,
From: "Michael A Chase" <mchase@ix.netcom.com>
Subject: Re: trim trailing spaces when inserting varchar2
Date: Tue, 16 Oct 2001 14:43:10 -0500
Tim,
I'm trying to remember if we discussed why the default ora_type for
strings was changed from 5 to 1 so many moons ago. It looks to me that 1 is
causing more trouble than 5 would and the problem appears on the list every
month or so. I feel that if someone really doesn't want trailing spaces in
their VARCHAR columns, they can trim them before feeding them to the
database.
Harald,
This is the current default behavior for DBD::Oracle. The following is in
the Oraperl docs (perldoc Oraperl), but also applies to DBD::Oracle. The
ora_open() and ora_bind() subroutines are similar to the prepare() and
execute() methods.
==== begin perldoc Oraperl ====
DBD: Substitution variables are now bound as type 1 (VARCHAR2)
and not type 5 (STRING) by default. This can alter the behaviour of
SQL code which compares a char field with a substitution variable.
See the String Comparison section in the Datatypes chapter of the
Oracle OCI manual for more details.
You can work around this by using DBD::Oracle's ability to specify
the Oracle type to be used on a per field basis:
$char_attrib = { ora_type => 5 }; # 5 = STRING (ala oraperl2.4)
$csr = ora_open($dbh, "select foo from bar where x=:1 and y=:2");
$csr->bind_param(1, $value_x, $char_attrib);
$csr->bind_param(2, $value_y, $char_attrib);
ora_bind($csr); # bind with no parameters since we've done bind_param()'s
==== end perldoc Oraperl ====
===
To: <dbi-users@perl.org>, "Michael A Chase"
<mchase@ix.netcom.com>
From: "Harald Meier" <meh1@rts.at>
Subject: Re: trim trailing spaces when inserting varchar2
Date: Wed, 17 Oct 2001 10:21:06 +0200
Hi Michael,
when I do a
$char_attrib = { ora_type => 5 };
$sth=$dbh->prepare("INSERT INTO test (x) values (?)");
$sth->bind_param(1,$str,$char_attrib);
$sth->execute;
I get a
DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only
for insert into a LONG column (DBD ERROR: OCIStmtExecute) at x.pl line
24.
at the execute command.
what do I do wrong???
===