Home » Database » MDB2 » Bug #7680

[2006-05-20 16:27 UTC] reg at dav-muz dot net

Description:
------------
MySQL < 5.0.7 (4.1 in my case) do not support LIMIT in PREPARE statement, but MDB2 could support this for compatibility reason. But it is not so...

I have test it with MDB2_PORTABILITY_ALL and MDB2_PORTABILITY_NONE.

See http://dev.mysql.com/doc/refman/4.1/en/select.html (and use "prepare " in the search function of your browser). I think there is more exceptions.

Test script:
---------------
$prepared = $mdb2->prepare('SELECT * FROM `news` LIMIT ?,?', array('integer', 'integer'));
$result = $prepared->execute(array(0, 30));

Expected result:
----------------
Success.

Actual result:
--------------
_doQuery: Could not execute statement[Native code: 1064] [Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?,?' at line 1]

[2006-05-20 16:56 UTC] reg at dav-muz dot net

...huff

In my application (a framework and a CMS) is possible that I use all the databases supported by MDB2 and this problem could create a switch control for every differences of databases in many functions.

It is possible, for resolve quicly the problem, to add a parameter to prepare function to force the substitution of placeholders via PHP?

An example:
function &prepare($query, $types = null, $result_types = null, $lobs = array(), $forceSubstitution = false)

I could do for you if this is a useful feature (I will do in any case but it is more clean and useful in MDB2 package!). I could write a private function for MDB2_Driver_Common. :)

[2006-05-20 19:17 UTC] reg at dav-muz dot net

THX!

It is just emulated for default and, in line 416 of MDB2/mysql.php, it is set to true if the version is major or pair of 4.1.

IMHO, I think that is this the problem, MySQL is no more compatible with "prepared_statements" than previous: it is only partially compatible.

"sub_selects" is compatible in MySQL >= 4.0 (if I am not wrong, is in the same page).

The code:
if (is_array($server_info)
&& ($server_info['major'] > 4
|| $server_info['major'] == 4
)
) {
$this->supported['sub_selects'] = true;
if ($server_info['major'] > 5
|| $server_info['major'] == 5
) {
$this->supported['prepared_statements'] = true;
}
}

[2006-05-20 19:44 UTC] reg at dav-muz dot net

It run only with prepared_statements = false.

Sorry for my english, I am not sure of understand right:

<cut>
For prepared statements, you can use placeholders (supported as of MySQL version 5.0.7). The following statements will return one row from the tbl table:

SET @a=1;
PREPARE STMT FROM 'SELECT * FROM tbl LIMIT ?';
EXECUTE STMT USING @a;
</cut>
and
<cut>
however LIMIT does not support placeholders
(it also does not support expressions for that matter).
</cut>

I use often LIMIT feature with placeholders because the framework do a large flexibility to final user and this is the principal feature. If MySQL 4.1 do not support fully placeholders (only LIMIT is out, uff..) I, and who use LIMIT, need to use this selection (the if proposed) in MDB2 or directly in the code.

If this selection is in MDB2 there is no problem for all.
If this selection is in the code: the code do something thas is not in the right place..

Other solution that come in my mind is to put the selection in _doQuery() to save performance and stability.

If you wont I could write the code for do that, say me where.. ;)

[2006-05-20 20:06 UTC] reg at dav-muz dot net

Thanks!

Now I am blocked because I have to publish this release of framework and this is the only last critical bug: but half of my hostings and clients have MySQL 4.1.x ... :(

Patching all PEAR repository is not a good idea, some are not mine and I can't... (some have previous version and could have problems..)
Can you commit my workaround to solve quicly the problem, before have your well written solution?

Thank you really a lot, if you can!

[2006-05-20 20:10 UTC] reg at dav-muz dot net

> Can you commit my workaround to solve quicly the problem,
> before have your well written solution?

Excuse me: for only not compatible versions like mine.

[2006-05-20 20:11 UTC] reg at dav-muz dot net

Ok, THX!

[2006-05-22 09:47 UTC] reg at dav-muz dot net

Now it run with:
supported['prepared_statements'] = 'emulated'

And also with:
options['emulate_prepared'] = true;