Home » Database » DB » Bug #1511

[2004-05-26 23:01 UTC] codyc at lifeformsunlimited dot com

Description:
------------
PEAR DB version: 1.4.0

For background on MySQL ANSI extensions see:
http://dev.mysql.com/doc/mysql/en/Extensions_to_ANSI.html

Problem: MySQL has ANSI SQL extensions which can be supplied during SELECT querie, such as SQL_CALC_FOUND_ROWS and SQL_CACHE. In order to be portable, PHP scripts using these MySQL extensions should enclose them in comment blocks "/* .. */" so, other RDBM's wont attempt to parse and run them. E.g.

SELECT /* SQL_CALC_FOUND_ROWS */ foo,bar FROM some_table;

This query will be parseable and interpreted as intended by MySQL but will NOT fail miserably on Oracle, since Oracle's query parser will skip over the comment-blocks.

Ok, so far. In addition, MySQL has allowed a developer to specify the minimum version of MySQL which has that feature built-in. For example, the extension SQL_CALC_FOUND_ROWS is only available in MySQL >= 4.0.0, so you can specify this in the query, so even older versions of MySQL wont fail on trying to run an unsupported extension. This is accomplished using the following query:

SELECT /*! 40000 SQL_CALC_ROUND_ROWS */ foo,bar FROM some_table;

Notice the "!" and the interger "40000", this restricts this extension to versions of MySQL 4.0.0 and above.

As you can see, using this syntax combined with PEAR DB's prepare/execute facility, PEAR will see that "!" and choke on it (because the arguments to prepare() are insufficient).

Thus, the prepare() logic needs to handle the case where the binding placeholder "!" is inside of a comment "/* ... */" and not attempt to parse it.

Let me know if I can be of any further assistance.

Reproduce code:
---------------
$query = "SELECT /*! 40000 SQL_CALC_FOUND_ROWS */ postdate, body FROM ratings where ratingid = !";
$stmt = $dbh->prepare($query);
$result = $dbh->execute($stmt, array($ratingid) );
if(DB::isError($result)) {
$err = "Error [".$result->getCode()."]:";
$err .= $result->getMessage();
die( $err );
}

Expected result:
----------------
a DB_Result object

Actual result:
--------------
Error [-20]: DB Error: insufficient data supplied