| Re: mysqli bind_param reports error with COLLATE [message #185985] |
Wed, 21 May 2014 20:28  |
Lew Pitcher
Messages: 60
Registered: April 2013
Karma: 0
|
Member |
|
|
On Wednesday 21 May 2014 15:50, in comp.lang.php, "Gregor Kofler"
<usenet(at)gregorkofler(dot)com> wrote:
> PHP 5.5.9 on Ubuntu 14.04 64bit. The database table/column in question
> is on utf8/utf8_general_ci.
>
> The following code
>
> $stmt = $db->prepare('
> SELECT f.filesID FROM files f WHERE f.File = ? COLLATE utf8_bin
> ');
> $file = 'foo';
> $stmt->bind_param('s', $file);
>
> results in
>
> Warning: mysqli_stmt::bind_param(): Number of variables doesn't match
> number of parameters in prepared statement in ...
>
> When removing the collation
>
> $stmt = $db->prepare('
> SELECT f.filesID FROM files f WHERE f.File = ?
> ');
> $file = 'foo';
> $stmt->bind_param('s', $file);
>
> the binding works as expected.
>
> Any ideas?
It appears that prepare() doesn't like your COLLATE clause.
I note that you do not check the results from prepare(). I'm not an OO
programmer, and I may have misread or misunderstood the mysqli
documentation, but
http://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.html
says that prepare() returns TRUE or FALSE, and shows examples of how to test
it in OO code.
My guess is that your version of the mysqli interface, or mysql, finds some
problem with the statement you are trying to prepare (when you include the
COLLATE clause), and builds it's results such that there are *no*
parameters to be bound to the prepared statement (either, no statement at
all, or a dummy statement, with FALSE returned). This, in turn, causes
bind_param() to complain that the the number of parameters given (1) does
not match the number of parameters expected (0).
You might want to discuss this problem of COLLATE with the
comp.databases.mysql newsgroup; it sounds like it is right up their alley.
As for your code, try putting some error-checking around the prepare() call,
and see if it returns an error when you include COLLATE in the statement.
HTH
--
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request
|
|
|
|
| Re: mysqli bind_param reports error with COLLATE [message #185994 is a reply to message #185985] |
Mon, 26 May 2014 23:12  |
Gregor Kofler
Messages: 69
Registered: September 2010
Karma: 0
|
Member |
|
|
Am 2014-05-21 22:28, Lew Pitcher meinte:
> On Wednesday 21 May 2014 15:50, in comp.lang.php, "Gregor Kofler"
> <usenet(at)gregorkofler(dot)com> wrote:
>
>> PHP 5.5.9 on Ubuntu 14.04 64bit. The database table/column in question
>> is on utf8/utf8_general_ci.
>>
>> The following code
>>
>> $stmt = $db->prepare('
>> SELECT f.filesID FROM files f WHERE f.File = ? COLLATE utf8_bin
>> ');
>> $file = 'foo';
>> $stmt->bind_param('s', $file);
>>
>> results in
>>
>> Warning: mysqli_stmt::bind_param(): Number of variables doesn't match
>> number of parameters in prepared statement in ...
>>
>> When removing the collation
>>
>> $stmt = $db->prepare('
>> SELECT f.filesID FROM files f WHERE f.File = ?
>> ');
>> $file = 'foo';
>> $stmt->bind_param('s', $file);
>>
>> the binding works as expected.
>>
>> Any ideas?
>
> It appears that prepare() doesn't like your COLLATE clause.
Thanks for the response - I cancelled my posting, since I found a
solution (or workaround) just immediately after posting. Therefore I'm
gonna leave the FQ.
> I note that you do not check the results from prepare(). I'm not an OO
> programmer, and I may have misread or misunderstood the mysqli
> documentation, but
> http://dev.mysql.com/doc/apis-php/en/apis-php-mysqli-stmt.prepare.html
> says that prepare() returns TRUE or FALSE, and shows examples of how to test
> it in OO code.
>
> My guess is that your version of the mysqli interface, or mysql, finds some
> problem with the statement you are trying to prepare (when you include the
> COLLATE clause), and builds it's results such that there are *no*
> parameters to be bound to the prepared statement (either, no statement at
> all, or a dummy statement, with FALSE returned). This, in turn, causes
> bind_param() to complain that the the number of parameters given (1) does
> not match the number of parameters expected (0).
Well, I *know* ther error (see above for the error message). However,
the actual error message is (IMO) misleading.
> You might want to discuss this problem of COLLATE with the
> comp.databases.mysql newsgroup; it sounds like it is right up their alley.
They might complain that this is supposedly a problem with the database
driver.
> As for your code, try putting some error-checking around the prepare() call,
> and see if it returns an error when you include COLLATE in the statement.
Anyway, the solution:
I replaced
SELECT f.filesID FROM files f WHERE f.File = ?
with
SELECT f.filesID FROM files f WHERE f.File COLLATE utf8_bin = ?
Gregor
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]