in reply to DBI-trace and $hDB-quote() interaction

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]

Replies are listed 'Best First'.
Re: (2) DBI-trace and $hDB-quote() interaction
by talexb (Chancellor) on May 23, 2002 at 13:21 UTC
    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]
Re: (2) DBI-trace and $hDB-quote() interaction
by talexb (Chancellor) on May 23, 2002 at 14:03 UTC
    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]

In Section Seekers of Perl Wisdom