freddo411 has asked for the wisdom of the Perl Monks concerning the following question:

Esteemed Monks,

I've run into a problem reading data with DBI.

In short, I'm trying to match a field in the database (varchar2) containing a string that ends in a space. 

 
  $lastname = "Smith ";
  $qs = "select * from table where lastname = ? "
  $sth = $dbh->prepare($qs) or die "db_error";
  $sth->execute($lastname)
        or die "db error " . DBI->errstr;
  @data = $sth->fetchrow_array;
  print "@data";

[download]
When I use sql placeholders in the DBI query string and I pass in a string variable to the execute method that contains the exact match of the database field date for substitution by the dbi placeholder (including the trailing space) I do not get the matching row returned. I am certain the data is in the DB.

When I perform the identical SQL (without placeholders) with identical field data in my sql gui I get a match returned.

My running theory is that placeholders in DBI strip off trailing whitespace. Is this true? If not, does anyone have an alternate explaination?

I am running an Oracle DB

I have working example code if anyone is inclined to try this out ... 


#!/usr/local/bin/perl
###########################################

my $lastname = "AVCO Financial Services India ";

print qq/Trying:  "$lastname"\n/;

use DBI;
use Data::Dumper;
use strict;
my $name        =       "*****";
my $pw          =       "*****";
my $dbname      =       "gonzodb";
my $dbh = DBI->connect( $dbname, $name, $pw,'Oracle')
            or die DBI->errstr ;
my ( $qs, $sth);

   $qs = "select * from CTemp where lastname like ? ";
   $sth = $dbh->prepare($qs) or die "db_error";
   $sth->execute($lastname)
        or die "db error " . DBI->errstr;
   my @data = $sth->fetchrow_array;
   print "like result:  @data \n";

########

   $qs = "select * from CTemp where lastname = ? ";
   $sth = $dbh->prepare($qs) or die "db_error";
   $sth->execute($lastname)
        or die "db error " . DBI->errstr;
   my @data = $sth->fetchrow_array;
   print "equal result: @data \n";

# Clean up
$sth->finish;
$dbh->commit;
$dbh->disconnect;

[download]

-------------------------------------
Nothing is too wonderful to be true
-- Michael Faraday

Replies are listed 'Best First'.
Re: Problem with DBI placeholders
by etcshadow (Priest) on Jan 28, 2004 at 04:02 UTC
    Yes, this is absolutely the case with DBD::Oracle. It caused us some horrible pains in the ass when we retrofitted unbound SQL.

    By the way, the first hit on google can give you some good info here: http://dbforums.com/arch/96/2002/4/341286 

    ------------
:Wq
Not an editor command: Wq

    [download]
[reply]
[d/l]
Re: Problem with DBI placeholders
by graff (Chancellor) on Jan 28, 2004 at 02:26 UTC
    I tried to replicate your observation using oracle 8.1.3 with perl 5.5.3 and perl 5.8.0 on a solaris 8 system. The 5.5.3 (with DBI.pm version 1.15 and DBD.pm revision 10.8) worked as hoped for: a table field that ends with a space can be retrieved using a statement with a "?" placeholder, and passing a string with final space as a parameter to the statement's "execute" method.

    The 5.8.0 version (DBI version 1.4, DBD revision 11.8) behaved like your case -- same table, same data, same methods. I didn't get a chance to step into the "execute" call with the debugger. Nor was I able to test a more recent perl version.

    I was able to confirm that the placehold worked when the parameter value was "Smith_" and the query condition was "where lastname like ?" -- so it's almost certainly a matter of losing trailing white-space from the parameter value within the execute call.

[reply]
Re: Problem with DBI placeholders
by Roger (Parson) on Jan 28, 2004 at 02:25 UTC
    I have tested a few trailing-spaces scenarios on several database, and I get different behaviour from different databases (MySQL, Sybase, Oracle). My conclusion is that the behaviour of trailing-spaces depends on the SQL parser of the underlying database (or database driver DBD::?). So you might want to code around it somehow.

[reply]
Re: Problem with DBI placeholders
by PodMaster (Abbot) on Jan 28, 2004 at 02:15 UTC
    Is this true? If not, does anyone have an alternate explaination?
    What's the server log say (it should record queries)? Did you try turning on trace (DBI method)?

    MJD says "you can't just make shit up and expect the computer to know what you mean, retardo!"
    I run a Win32 PPM repository for perl 5.6.x and 5.8.x -- I take requests (README).
    ** The third rule of perl club is a statement of fact: pod is sexy.

[reply]
Re: Problem with DBI placeholders
by dragonchild (Archbishop) on Jan 28, 2004 at 13:15 UTC
    Whitespace is handled differently in versions of the same database. I recently sent Tim Bunce a patch for a test in DBD::Oracle regarding whitespace handling in Oracle9i (which differs from Oracle8, which differs from Oracle7, etc.)

    My recommendation is to not use trailing and/or preceding whitespace in your data, for several reasons. This isn't even the most important one. More important is that if you have a trailing space in one row and not in the other, you can't find equality in your SQL.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

[reply]
      More important is that if you have a trailing space in one row and not in the other, you can't find equality in your SQL
      I would qualify that statement and say that it is true for certain database systems (and Oracle in particular). For Sybase trailing whitespace is not significant: either the column is of type char(), in which case it is fixed width and is always padded with whitespace, or it is of type varchar(), in which case trailing whitespace is ignored (although inseting a zero-length string will be converted to a single whitespace).

      Michael

[reply]
Re: Problem with DBI placeholders
by CountZero (Bishop) on Jan 28, 2004 at 11:13 UTC
    General question/comment: Is there a way to somehow "escape" the trailing space in order to protect it against it being stripped? I see some difficulties ahead on this road with the automatic quoting of the placeholder value; which might "quote" the "escape character".

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
Re: Problem with DBI placeholders
by freddo411 (Chaplain) on Jan 28, 2004 at 21:46 UTC
    Thanks to everyone for their helpful replies on this annoying bug.

    I have decided to scrub my data, and to scrub any incoming data to have no trailing/leading spaces.

    Cheers

    -------------------------------------
    Nothing is too wonderful to be true
    -- Michael Faraday

[reply]
      Yeah, that's how we ended up dealing with it, as well. Another thing is that if you consistently bind all of your literals both going into the database as well as in your queries... you'll never have this problem (because the literals had their trailing whitespace stripped going in, so it's fine if it gets stripped when you query for it, as well).

      And of course, always binding all of your literals (or at least very nearly all of them) is pretty much always the right course. (Excepting of course for some moderately rare conditions which I describe in great detail here.) 

      ------------
:Wq
Not an editor command: Wq

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom