A note on bind variables:
I'm running Oracle 10gR2 and select stateens with bind variables of type
CHAR do not behave as expected. For example, one of my DB's has the
following table in it:
SQL> desc webuserparm;
Name Null? Type
------------------ -------- -------------
USERCD NOT NULL CHAR(15)
USERNAME NOT NULL VARCHAR2(25)
USERPWD NOT NULL CHAR(15)
GRPID NOT NULL NUMBER(5)
ACTIVEFLAG NOT NULL CHAR(1)
USERTYPE CHAR(1)
CLTID NUMBER(8)
EMAILID CHAR(200)
BUSINESSPHONE VARCHAR2(10)
BUSINESSPHONEEXTN CHAR(4)
BUSINESSTITLE VARCHAR2(50)
DPSIMTGPROVIDERID VARCHAR2(15)
DPSIMTGCOMPANYID VARCHAR2(15)
DPSIMTGUSERID VARCHAR2(15)
DPSIMTGUSERPWD VARCHAR2(15)
I run the following SQL against this table several thousand time a day
so I have an incentive to use bind variables:
$script =
"select count (*) from webuserparm where usercd = ? and cltid = ?";
If I run it
$sth = $dbh->prepare($script);
$usercd = "MYUSER";
$cltid = 10101;
$sth->execute($usercd,$cltid);
I get a "0" even tho MYUSER and 10101 are in the same row.
If I add just before the execute a function padding the string with spaces:
$usercd = pack("A15",$usercd);
I get a "1" as expected.
Apparently Oracle compares a space padded copy of a CHAR to the
submitted value.
Perl version 5.8.2 and 5.8.6
DBI version 1.51
OracleDBD version 1.17
--
Stephen Carville <[EMAIL PROTECTED]>
Unix and Network Admin
Nationwide Totalflood
6033 W. Century Blvd
Los Angeles, CA 90045
310-342-3602
