Beefy Boxes and Bandwidth Generously Provided by pair Networks
No such thing as a small change
 
PerlMonks  

Re^2: DBD::mysql Unusual Behavior

by tadman (Prior)
on Aug 23, 2002 at 10:16 UTC ( #192297=note: print w/replies, xml ) Need Help??


in reply to Re: DBD::mysql Unusual Behavior
in thread DBD::mysql Unusual Behavior

This is going to sound crazy, I know, but I have a feeling DBI is actually paying some kind of attention to the internal type of the presented variables.

During diagnosis, I tried to abstract the variable away into an array, which would make a copy and theoretically de-taint the orginal scalar. Then, a few things worked, surprisingly:
my $foo = ''.$key_value; my $foo = "$key_value"; my $foo = eval($dbh->quote($key_value)); # Desperation
Also, priming the query with some stupid data did the trick too:
$sth->execute(1,"A","B","C");
Now it just occured to me that the data in $key_value leading up to the call that failed was strictly numeric, except when either a) the code was literally converted to a scalar, or b) enquoted, to force stringification.

Now, the question is, what does this mean?

My busted test case, which I'm curious to see reproduced, is:
my $sth = $dbh->prepare(" INSERT INTO web_sessions_aux (wsa_id, wsa_type, wsa_key, wsa_value) VALUES (?,?,?,?)", {}); my $rv = $sth->execute("2012","CRS","crs_search_index",1) && $sth->execute("2012","CRS","crs_search_criteria","S") ; $sth->finish();
Note the numeric in the fourth column (CHAR). Why would this matter?

Update: Solved!
It would seem to be a problem in DBD::mysql which checks the "type" of the scalar being passed to the execute call. From dbdimp.c:
if (!ph->type) { ph->type = SvNIOK(ph->value) ? SQL_INTEGER : SQL_VARCHAR; }
You can see here that if the placeholder type is not set, it uses the Perl internal representation as a hint. Unfortunately, once assigned, this is never checked again. To clear up the problem, I just assigned straight to SQL_INTEGER and everything is A-OK.

Replies are listed 'Best First'.
Re: Re^2: DBD::mysql Unusual Behavior
by bart (Canon) on Aug 23, 2002 at 20:44 UTC
    This is going to sound crazy, I know, but I have a feeling DBI is actually paying some kind of attention to the internal type of the presented variables.
    I pretty much got the same gut feeling. It's not doing it intentionally, though...

    I gather that Perl's scalars have a pretty large internal representation, and perl turns one representation into another when needed if it didn't exist yet, on the fly. You say the one case that fails is numerical, while the other ones are strings. So only this one needs to be converted to a string.

    My guess is that, when Perl does this conversion, something goes astray, and modifies a bit (or a few more) in a location that doesn't belong to this scalar's structure. It could well be that this always happens, but that most of the time, we don't notice. After all, you say you're using 5.8.0, and not too many many people are using that yet. Well, my guess is that in your particular case, this stray modified bit is in this DBI statement handle, thereby corrupting it, and this is what throws this spurious error.

    All just speculation, really. :-)

    ps. What's the value of Re xor 2, anyway? ;-)

      Could be re() ** 2 but that would be silly.

      Re:4

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://192297]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (6)
As of 2019-11-21 22:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    Strict and warnings: which comes first?



    Results (106 votes). Check out past polls.

    Notices?