On Thu, 28 Nov 2002 11:29:04 +0000, Tony Bowden wrote:
>> I don't know how MySQL handles placeholders, so this is just a guess. It
>> may simply be interpolating the string 'nine' into the query string, in the
>> same way it interpolated the number 9, that is, without qotes:
>> INSERT INTO user (username, password)
>> VALUES ('bar', nine)
>
>Goot catch. This is almost certainly what's happening.
I hadn't looked at this thread until now, but now that I do, I do
recognize the pattern. I've had the same kind of problems with Access.
What I feel that must be happening, is that the character type for a
placeholder is determined on how the data looks, the very first time it
is invoked. If at that time, it's called with "nine", it is determined
as being a string, so for the rest of the lifetime of this statement
handle, parameters will be quoted. But if it looks like "9", it is
considered as a number, and it won't be quoted. Not even if you invoke
it with a string. (My bet is that if in the latter case you provide the
string "null", it will be treated as NULL by the database.)
What can fix this, is having an explicit bind on the variable type,
after the pepare phase, but before the first time you execute this
statement. It certainly did help in Access. My code (adapted) looked
like this:
use DBI ':sql_types'; # to import the constants
$sth->bind_param(2, undef, SQL_VARCHAR);
(untested)
The 2 is the index of the placeholder. The undef is the actual value,
but that exact value doesn't matter much. You override it later, anyway.
(undef produces a NULL, which normally is safe)
p.s. Actually the problem I had with Access was the reverse: if the
first execute call was with undef, the placeholder got treated as a
string. The field itself was a nullable integer. If later, it got called
with a number, Access barfed because it got quoted, and Access doesn't
treat 9 and '9' as the same -- unlike MySQL, for example.
So my fix was:
$sth->bind_param(1, undef, SQL_INTEGER);
(which is a paste from my original code, so this is tested :-)
HTH,
Bart.
