Dashes Causing SQL Trouble in DBI

Question

I have a SQL query with a WHERE clause that typically has values including a dash as stored in the database as a CHAR(10). When I explicitly call it like in the following:

$sth = $dbh->prepare("SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = 'A-50C'");

It works and properly returns my 1 row; however if I do the following:

my $code = 'A-50C';
$sth = $dbh->prepare("SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = ?");
$sth->execute($code);

or I do:

my $code = 'A-50C';
$sth = $dbh->prepare("SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = ?");
$sth->bind_param(1, $code);
$sth->execute();

The query completes, but I get no results. I suspect it has to do with the dash being interpretted incorrectly, but I can't link it to a Perl issue as I have printed my $code variable using print "My Content: $code\n"; so I can confirm its not being strangely converted. I also tried including a third value for bind_param and if I specify something like ORA_VARCHAR2, SQL_VARCHAR (tried all possibilities) I still get no results. If I change it to the long form i.e. { TYPE => SQL_VARCHAR } it gives me an error of

DBI::st=HASH<0x232a210>->bind_param(...): attribute parameter 'SQL_VARCHAR' is not a hash ref

Lastly, I tried single and double quotes in different ways as well as back ticks to escape the values, but nothing got me the 1 row, only 0. Any ideas? Haven't found anything in documentation or searching. This is oracle for reference.

Code with error checking:

my $dbh = DBI->connect($dsn, $user, $pw, {PrintError => 0, RaiseError => 0})
  or die "$DBI::errstr\n";

# my $dbh = DBI->connect(); # connect

my $code = 'A-50C';
print "My Content: $code\n";
$sth = $dbh->prepare( "SELECT COUNT(*) FROM MyTable WHERE CODE = ?" )
  or die "Can't prepare SQL statement: $DBI::errstr\n";
$sth->bind_param(1, $code);
$sth->execute() or die "Can't execute SQL statement: $DBI::errstr\n";

my $outfile = 'output.txt';
open OUTFILE, '>', $outfile or die "Unable to open $outfile: $!";

while(my @re = $sth->fetchrow_array) {
    print OUTFILE @re,"\n";
}

warn "Data fetching terminated early by error: $DBI::errstr\n"
  if $DBI::err;

close OUTFILE;

$sth->finish();
$dbh->disconnect();

I ran a trace and got back:

-> bind_param for DBD::Oracle::st (DBI::st=HASH(0x22fbcc0)~0x3bcf48 2 'A-50C' HASH(0x22fbac8)) thr#3b66c8
dbd_bind_ph(1): bind :p2 <== 'A-50C' (type 0 (DEFAULT (varchar)), attribs: HASH(0x22fbac8))
dbd_rebind_ph_char() (1): bind :p2 <== 'A-50C' (size 5/16/0, ptype 4(VARCHAR), otype 1 )
dbd_rebind_ph_char() (2): bind :p2 <== ''A-50' (size 5/16, otype 1(VARCHAR), indp 0, at_exec 1)
    bind :p2 as ftype 1 (VARCHAR)
dbd_rebind_ph(): bind :p2 <== 'A-50C' (in, not-utf8, csid 178->0->178, ftype 1 (VARCHAR), csform 0(0)->0(0), maxlen 16, maxdata_size 0)

Answer 1

Your problem is likely a result of comparing CHAR and VARCHAR data together.

The CHAR data type is notorious (and should be avoided), because it stores data in fixed-length format. It should never be used for holding varying-length data. In your case, data stored in the ACC_TYPE column will always take up 10 characters of storage. When you store a value whose length is less than the size of the column, like A-50C, the database will implicitly pad the string up to 10 characters, so the actual value stored becomes A-50C_____ (where _ represents a whitespace).

Your first query works because when you use a hard-code literal, Oracle will automatically right-pad the value for you (A-50C -> A-50C_____). However, in your second query where you use bind variables, you're comparing a VARCHAR against a CHAR and no auto-padding will happen.

As a quick fix to the problem, you could add right-padding to the query:

SELECT STATUS_CODE FROM MyTable WHERE ACC_TYPE = rpad(?, 10)

A long-term solution would be to avoid using the CHAR data type in your table definitions and switch to VARCHAR2 instead.