in reply to Re: DBI/DBD::mysql bugs?
in thread DBI/DBD::mysql bugs?

I used your and Fletch's suggestions and here's what I came up with.

1. Setting a tracelevel of 2 on the problem statement handle pretty much confirmed that DBI is having a quoting problem. The single char strings are not getting quoted(ironically, the 5-8 digit int is :) Well I think we all pretty much knew that was part of the problem based on the error message.
2. I used hardcoded values in the prepare statement and it worked fine. As soon as I used the placeholders though, everything goes bad. NONE of the string values are being treated as such. 3. I tried explicitly binding the parameters but to no avail. Still the chars are not quoted.

I did get it to work using the 'do' method, but that is less efficient since the statement is executed inside a loop. Now the statement has to be prepared each time through the loop(which is better than not working at all I suppose :). Still though, for efficiency's sake I'd rather use the 'prepare-execute' method, so I'd still like to figure this out.

Anyway, here's what the trace on the statement handle output to STDOUT: 

    DBI::st=HASH(0x87b0d80) trace level set to 2 in DBI 1.13-nothread
    Note: perl is running without the recommended perl -w option
SANFRNCSCO****
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x83dd82c)~0x87b0d8
+0 1 undef)
    <- bind_param= 1 at bb_ps_stats.pm line 49.
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x83dd82c)~0x87b0d8
+0 2 R)
    <- bind_param= 1 at bb_ps_stats.pm line 50.
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x83dd82c)~0x87b0d8
+0 3 R)
    <- bind_param= 1 at bb_ps_stats.pm line 51.
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x83dd82c)~0x87b0d8
+0 4 A)
    <- bind_param= 1 at bb_ps_stats.pm line 52.
    -> bind_param for DBD::mysql::st (DBI::st=HASH(0x83dd82c)~0x87b0d8
+0 5 '10457')
    <- bind_param= 1 at bb_ps_stats.pm line 53.
    -> execute for DBD::mysql::st (DBI::st=HASH(0x83dd82c)~0x87b0d80)
    -> dbd_st_execute for 083dd634
      Binding parameters: UPDATE players SET t_id=NULL, bats=R, throws
+=R, status=A WHERE id='10457'
Unknown column 'R' in 'field list' error 5 recorded: Unknown column 'R
+' in 'field list'
    <- dbd_st_execute -2 rows
    !! ERROR: 5 'Unknown column 'R' in 'field list''
    <- execute= undef at bb_ps_stats.pm line 54.
    -> $DBI::errstr (&) FETCH from lasth=DBI::st=HASH(0x87b0d80)
No status UPD: Unknown column 'R' in 'field list'
    -> DESTROY for DBD::mysql::st (DBI::st=HASH(0x87b0d80)~INNER)
    <- DESTROY= undef at unknown location!

[download]
Doesn't reveal anything new to me but who knows what you'll see.

Thanks again.

Amel - f.k.a. - kel

Replies are listed 'Best First'.
Re: Re: Re: DBI/DBD::mysql bugs?
by perlplexer (Hermit) on Apr 10, 2002 at 01:25 UTC
    Can you upgrade to a newer version of DBI ?
    The latest one is 1.21, I believe.
    The following code works on my Win2K PC (cygwin, PostgreSQL, DBI 1.201, ActivePerl 5.6.1) 
    use DBD::ODBC;
use DBI;

my $p = {};
my $t_id = '20'; 

$p->{P_BATS}     = 'R';
$p->{P_THROWS}   = 'R';
$p->{P_STATUS}   = 'A';
$p->{P_PLAYERID} = 1;  


my $dbh = DBI->connect('DBI:ODBC:test', 'foo', 'bar', {RaiseError => 1
+});
my $sth = $dbh->prepare(qq/UPDATE players SET t_id=?, bats=?, throws=?
+, status=? WHERE id=?/);
$sth->execute($t_id, $p->{P_BATS}, $p->{P_THROWS}, $p->{P_STATUS}, $p-
+>{P_PLAYERID});

$sth = $dbh->prepare(qq/SELECT * FROM players/);
$sth->execute();
while (my @row = $sth->fetchrow_array()){
    print join(',', @row),"\n";
}

$dbh->disconnect();

    [download]
    Table 'players' is defined as follows 
    test=# \d players
               Table "players"
 Attribute |         Type          | Modifier
-----------+-----------------------+----------
 id        | integer               |
 t_id      | character varying(20) |
 bats      | character varying(20) |
 throws    | character varying(20) |
 status    | character varying(20) |

    [download]

    --perlplexer
[reply]
[d/l]
[select]
      We found what we think is the problem.

      At another point I was checking to see if the value is equal to 0 regardless of whether its a string or not(checking all hash elements).

      What seems like is happening is that when the comparison is made the variable is flagged as an integer. Then when DBI gets the variable it still sees the variable as an integer so doesn't quote it. 

      if ($p->{P_THROWS} == 0) {    # flag as int for numeric comparison
    # blah
}

# blah
# blah
# blah

# now when DBI prepares this statement with the bind variable
# $p->{P_THROWS} is seen as an int so not quoted.
$sth = $dbh->prepare("UPDATE players SET throws=? WHERE id=?");

      [download]
      So it could be some obscure bug with DBI that doesn't recognize the variable as a string. But we upgraded to the recent version of DBI and the problem still occured. So either we're wrong or the bug hasn't been spotted/fixed.

      We did manage a work around that doesn't involve testing for 0 so the variable is never flagged as an integer.

      Thanks for your help. ;)

      Amel - f.k.a. - kel

[reply]
[d/l]

In Section Seekers of Perl Wisdom