Home » Database » MDB2 » Bug #9158

[2006-10-25 07:42 UTC] jonathan dot martens at gmx dot net

Description:
------------
I have trouble execuing a prepared query with on parameter when I try to bind a value of 0 to the placeholder.

MDB 2.2.2
MDB_Driver_mysql 1.2.2
PHP 5.0.3
PEAR 1.4.11

Test script:
---------------
<?php

/**
* DROP TABLE IF EXISTS `checklist`;
* CREATE TABLE `checklist` (
* `id` bigint(20) NOT NULL auto_increment,
* `sortorder` bigint(20) unsigned NOT NULL default '1',
* `category` varchar(10) NOT NULL default '',
* `group` varchar(100) NOT NULL default '',
* `monthly` tinyint(1) NOT NULL default '0',
* `weekly` tinyint(1) NOT NULL default '0',
* `id_modality` bigint(20) NOT NULL default '0',
* `energy` bigint(20) NOT NULL default '0',
* `direction` varchar(10) NOT NULL default '',
* `doserate` varchar(100) NOT NULL default '',
* `description` varchar(50) NOT NULL default '',
* `lastmodified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
* PRIMARY KEY (`id`)
* ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
*
* INSERT INTO `checklist` VALUES (1,1,'schuster','symmetrie',1,0,1,6,'GT','','Symmetrie X6 (GT)','2006-10-23 10:58:42'),(2,2,'schuster','symmetrie',1,0,1,6,'AB','','Symmetrie X6 (AB)','2006-10-23 10:58:42'),(3,3,'schuster','symmetrie',1,0,1,10,'GT','','Symmetrie X10 (GT)','2006-10-23 10:58:42'),(4,4,'schuster','symmetrie',1,0,1,10,'AB','','Symmetrie X10 (AB)','2006-10-23 10:58:42'),(5,5,'schuster','symmetrie',1,0,2,6,'GT','','Symmetrie E6 (GT)','2006-10-23 10:58:42'),(6,6,'schuster','symmetrie',1,0,2,6,'AB','','Symmetrie E6 (AB)','2006-10-23 10:58:42'),(7,7,'schuster','symmetrie',1,0,2,9,'GT','','Symmetrie E9 (GT)','2006-10-23 10:58:42'),(8,8,'schuster','symmetrie',1,0,2,9,'AB','','Symmetrie E9 (AB)','2006-10-23 10:58:42'),(9,9,'schuster','symmetrie',1,0,2,12,'GT','','Symmetrie E12 (GT)','2006-10-23 10:58:42'),(10,10,'schuster','symmetrie',1,0,2,12,'AB','','Symmetrie E12 (AB)','2006-10-23 10:58:42'),(11,11,'schuster','symmetrie',1,0,2,15,'GT','','Symmetrie E15 (GT)','2006-10-23 10:58:42'),(12,12,'schuster','symmetrie',1,0,2,15,'AB','','Symmetrie E15 (AB)','2006-10-23 10:58:42'),(13,13,'schuster','symmetrie',1,0,2,18,'GT','','Symmetrie E18 (GT)','2006-10-23 10:58:42'),(14,14,'schuster','symmetrie',1,0,2,18,'AB','','Symmetrie E18 (AB)','2006-10-23 10:58:42'),(15,15,'schuster','symmetrie',1,0,2,21,'GT','','Symmetrie E21 (GT)','2006-10-23 10:58:42'),(16,16,'schuster','symmetrie',1,0,2,21,'AB','','Symmetrie E21 (AB)','2006-10-23 10:58:42'),(17,17,'output','energie',1,0,1,6,'','','Energiebepaling X6','2006-10-23 10:59:23'),(18,18,'output','energie',1,0,1,10,'','','Energiebepaling X10','2006-10-23 10:59:23'),(19,19,'schuster','energie',1,0,2,6,'','','Energiebepaling E6','2006-10-23 10:59:23'),(20,20,'schuster','energie',1,0,2,9,'','','Energiebepaling E9','2006-10-23 10:59:23'),(21,21,'schuster','energie',1,0,2,12,'','','Energiebepaling E12','2006-10-23 10:59:23'),(22,22,'schuster','energie',1,0,2,15,'','','Energiebepaling E15','2006-10-23 10:59:23'),(23,23,'schuster','energie',1,0,2,18,'','','Energiebepaling E18','2006-10-23 10:59:23'),(24,24,'schuster','energie',1,0,2,21,'','','Energiebepaling E21','2006-10-23 10:59:23'),(25,25,'output','output',1,1,1,6,'','300','Outputmeting X6 (HDR)','2006-10-23 10:59:10'),(26,26,'output','output',1,1,1,6,'','50','Outputmeting X6 (LDR)','2006-10-23 10:59:10'),(27,27,'output','output',1,1,1,10,'','300','Outputmeting X10 (HDR)','2006-10-23 10:59:10'),(28,28,'output','output',1,1,1,10,'','50','Outputmeting X10 (LDR)','2006-10-23 10:59:10'),(29,29,'output','output',1,1,2,6,'','','Outputmeting E6','2006-10-23 10:59:10'),(30,30,'output','output',1,1,2,9,'','','Outputmeting E9','2006-10-23 10:59:10'),(31,31,'output','output',1,1,2,12,'','','Outputmeting E12','2006-10-23 10:59:10'),(32,32,'output','output',1,1,2,15,'','','Outputmeting E15','2006-10-23 10:59:10'),(33,33,'output','output',1,1,2,18,'','','Outputmeting E18','2006-10-23 10:59:10'),(34,34,'output','output',1,1,2,21,'','','Outputmeting E21','2006-10-23 10:59:10'),(35,35,'films','films',1,1,1,6,'','','Filmmetingen','2006-10-23 10:58:58');
*/

require_once 'MDB2.php';

$mdb = new MDB2();

$dsn = array(
'phptype' => DB_TYPE,
'hostspec' => DB_HOST,
'database' => DB_DATABASE,
'username' => DB_USERNAME,
'password' => DB_PASSWORD
);

$options = array(
'debug' => 2,
'portability' => MDB2_PORTABILITY_ALL,
);

$conn =$mdb->connect($dsn, $options);

$conn->loadModule('Extended');

$monthly = 0;

$sql = "SELECT id, sortorder, id_modality, energy, direction, description, checklist.group FROM checklist WHERE checklist.group='symmetrie' AND monthly = ? ORDER BY sortorder ASC";
$sth = $conn->prepare($sql, array('integer'), array('integer', 'integer', 'integer', 'integer', 'text', 'text'));
$result = $sth->execute($monthly);
if (PEAR::isError($result)) {
die("<pre><b>error: </b><br>". print_r($result, true)."</pre>");
}
?>

Expected result:
----------------
Based on the data in the table I expect an empty record set, however it comes with the error described below. The same query ran in a mysql client using the same username results properly in an empty set.

Actual result:
--------------
error: MDB2_Error Object
(
[error_message_prefix] =>
[mode] => 1
[level] => 1024
[code] => -4
[message] => MDB2 Error: not found
[userinfo] => _execute: [Error message: Unable to bind to missing placeholder: 0]
[Last query: PREPARE MDB2_Statement_mysql_6a396a1ea034b8e3182cae827f494f01 FROM 'SELECT id, sortorder, id_modality, energy, direction, description, checklist.group FROM checklist WHERE checklist.group=\'symmetrie\' AND monthly = ? ORDER BY sortorder ASC']
[Native code: 0]

[backtrace] => Array
(
[0] => Array
(
[file] => C:\Program Files\php\php-5.0.3\PEAR\MDB2.php
[line] => 961
[function] => PEAR_Error
[class] => MDB2_Error
[type] => ->
[args] => Array
(
[0] => MDB2 Error: not found
[1] => -4
[2] => 1
[3] => 1024
[4] => _execute: [Error message: Unable to bind to missing placeholder: 0]
[Last query: PREPARE MDB2_Statement_mysql_6a396a1ea034b8e3182cae827f494f01 FROM 'SELECT id, sortorder, id_modality, energy, direction, description, checklist.group FROM checklist WHERE checklist.group=\'symmetrie\' AND monthly = ? ORDER BY sortorder ASC']
[Native code: 0]

)

)

[1] => Array
(
[file] => C:\Program Files\php\php-5.0.3\PEAR\PEAR.php
[line] => 563
[function] => MDB2_Error
[class] => MDB2_Error
[type] => ->
[args] => Array
(
[0] => -4
[1] => 1
[2] => 1024
[3] => _execute: [Error message: Unable to bind to missing placeholder: 0]
[Last query: PREPARE MDB2_Statement_mysql_6a396a1ea034b8e3182cae827f494f01 FROM 'SELECT id, sortorder, id_modality, energy, direction, description, checklist.group FROM checklist WHERE checklist.group=\'symmetrie\' AND monthly = ? ORDER BY sortorder ASC']
[Native code: 0]

)

)

[2] => Array
(
[file] => C:\Program Files\php\php-5.0.3\PEAR\MDB2.php
[line] => 1420
[function] => raiseError
[class] => MDB2_Driver_mysql
[type] => ->
[args] => Array
(
[0] =>
[1] => -4
[2] =>
[3] =>
[4] => _execute: [Error message: Unable to bind to missing placeholder: 0]
[Last query: PREPARE MDB2_Statement_mysql_6a396a1ea034b8e3182cae827f494f01 FROM 'SELECT id, sortorder, id_modality, energy, direction, description, checklist.group FROM checklist WHERE checklist.group=\'symmetrie\' AND monthly = ? ORDER BY sortorder ASC']
[Native code: 0]

[5] => MDB2_Error
[6] => 1
)

)

[3] => Array
(
[file] => C:\Program Files\php\php-5.0.3\PEAR\MDB2\Driver\mysql.php
[line] => 1334
[function] => raiseError
[class] => MDB2_Driver_mysql
[type] => ->
[args] => Array
(
[0] => -4
[1] =>
[2] =>
[3] => Unable to bind to missing placeholder: 0
[4] => _execute
)

)

[4] => Array
(
[file] => C:\Program Files\php\php-5.0.3\PEAR\MDB2.php
[line] => 4001
[function] => _execute
[class] => MDB2_Statement_mysql
[type] => ->
[args] => Array
(
[0] => 1
[1] =>
)

)

[5] => Array
(
[file] => D:\SourceCode\qc2web\test_mdb2.php
[line] => 39
[function] => execute
[class] => MDB2_Statement_mysql
[type] => ->
[args] => Array
(
[0] => 0
)

)

)

[callback] =>
)

[2006-10-25 07:43 UTC] jonathan dot martens at gmx dot net

I forgot to mention that it works flawlessly with $monthly being other than 0, e.g. 1.