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

This is a little frustrating, partly because I think I know DBI fairly well, and partly because I think I know my way around programming. Does anyone have any idea why 
DBI->trace ( 2 );
my $UserID   = $hDB->quote ( $Vars->{ userid } );
my $Password = $hDB->quote ( $Vars->{ password } );
DBI->trace ( 0 );

[download]
correctly puts values into $userID and $Password but 
DBI->trace ( 0 );
my $UserID   = $hDB->quote ( $Vars->{ userid } );
my $Password = $hDB->quote ( $Vars->{ password } );
DBI->trace ( 0 );

[download]
does not? (I get NULL values instead.)

A quick search through the Monastery hasn't turned up anything obvious. Many thanks in advance to the obvious answer. (Hopefully not really obvious.)

I'm running this from the command line using the debugger. Weird.

--t. alex

"Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny

Replies are listed 'Best First'.
Re: DBI-trace and $hDB-quote() interaction
by gmax (Abbot) on May 23, 2002 at 09:12 UTC
    The quote() method is database dependent.
    I made this quick test for a MySQL database, and I got the correct result with both trace(2) and trace(0).
    If I change 'joe' into undef, then I get NULL in both cases.
    #!/usr/bin/perl -w
use DBI;
use strict;

my $hDB = DBI->connect("DBI:mysql:test;host=localhost"
        . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
        undef, undef, {RaiseError => 1})
        or die "can't connect\n";

my $Vars = { 'userid' => 'joe', 'password' => 'secret'};

DBI->trace ( 0 );
my $UserID   = $hDB->quote ( $Vars->{ userid } );
my $Password = $hDB->quote ( $Vars->{ password } );
DBI->trace ( 0 );

print "$UserID $Password\n";

$hDB->disconnect();

__END__
'joe' 'secret'

    [download]
    Could you make a similar case script? The culprit could be something else.
    quote() will return NULL when your value is undef. I suspect that your values are either undef or becoming undefined somehow along the processing flow.

    update
    After talexb's example, I was able to reproduce the problem and indeed it seems that only by stirring the variable you can get the wanted result.
    One of my favorite idioms for quoting (when I am not using placeholders) is to map-quote the variables from an array.
    The following example gives the right result, although it only works with the interpolated "$_".
    my ($UserID, $Password) = map {$hDB->quote( "$_" )} 
        ($Vars->{userid}, $Vars->{password});

    [download]
    update (2)
    Also a simple interpolation without map tricks will do the job.
      my $UserID   = $hDB->quote ( "$Vars->{'userid'}" );
  my $Password = $hDB->quote ( "$Vars->{'password'}" );

    [download]
    It solves the problem, but doesn't satisfy my curiosity ... ;)

    update (3)
    Although the quote() method is database dependent, in this case it is not, since the responsible for this strange return is before any other method could be called.
       # from the DBI.pm module
   1126     sub quote {
   1127     my ($dbh, $str, $data_type) = @_;
   1128
   1129     return "NULL" unless defined $str;
   # more code follows .....

    [download]
    This code will return NULL if the string passed is undefined. However, the string is defined, as I could check with a simple print.
    Just brainstorming ... 
     _  _ _  _  
(_|| | |(_|><
 _|

    [download]
[reply]
[d/l]
[select]
      After I replace the 'localhost' stuff with my real userid and password, I do indeed get 
      bash-2.04$ perl -w gmaxfoo.cgi 
'joe' 'secret'

      [download]
      from your example. Thanks.

      Because time is wasting, I have to press ahead without the quote call and figure that out later. Bloody frustrating.

      --t. alex

      "Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny

      Update Because I'm like a dog with a bone when I come up against this kind of problem, I tackled it with renewed vigour and discovered that this 

      my $UserID   = $hDB->quote ( $Vars->{ userid } || "foo" );
my $Password = $hDB->quote ( $Vars->{ password } || "bar" );

      [download]
      made everything come out all right. I don't know why, but for some reason the values in the hash ref were being shy. I must be doing something wrong in my code for it to behave this way, I just haven't figured out what just yet.

      Thanks again for your help.

[reply]
[d/l]
[select]
      Here's the snippet that I am using from the command line to get NULL:NULL out the other end. 
      #!/usr/bin/perl -w

use CGI qw /:standard/;
use DBI;
use DBITT;

{
  my $Form = new CGI;
  my $Vars = $Form->Vars();

  my $hDB = DBI->connect (
    $DBITT::DatabasePath, $DBITT::UserName, $DBITT::Password ) ||
    die "DBI failed: $DBI::errstr";

  DBI->trace ( 0 );
  my $UserID   = $hDB->quote ( $Vars->{ userid } );
  my $Password = $hDB->quote ( $Vars->{ password } );
  DBI->trace ( 0 );

  print $UserID . ":" . $Password . "\n";
  $hDB->disconnect;
}

      [download]
      The command line I'm using is 
      perl -w cal.cgi userid=joe password=camel

      [download]
      The DBITT module continues userid and password for my database -- it doesn't have anything to do with the $Vars hash ref.

      Madness.

      --t. alex

      "Nyahhh (munch, munch) What's up, Doc?" --Bugs Bunny

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom