Bug #8285

insert into auto_increment field with '' fails but works with null

Details

Submitted	2006-07-25 15:03 UTC
From	acerbiter at yahoo dot com
Status	Wont fix
Package	DB
PHP Version	5.1.2
OS	Windows XP
Roadmaps	(Not assigned)

Comments

[2006-07-25 15:03 UTC] acerbiter at yahoo dot com

Description:
------------
Apache/2.0.55 (Win32) PHP/5.1.2

MYSQLI Client API version 4.1.7
MYSQLI_SOCKET /tmp/mysql.sock
======================================================

using $dbtype = 'mysqli' the test script fails if i replace 'null' with '' in the insert statement giving me "unknown error"

everything works fine using $dbtype = 'mysql' under PHP Version 4.3.4 with MYSQL Client API version 3.23.56 running on Apache/1.3.33 (Unix) mod_ssl/2.8.22 OpenSSL/0.9.7d VDB/1.1.1

I do know whether this is an intention or limitation of mysqli but I'd assume it could be easily fixed within the pear db package especially because you can find a lot of mysql queries using '' instead of null, and I believe that this behaviour would improve the intended abstraction of the DB package

Test script:
---------------
$dsn = $dbtype ."://". $dbuser .":". $dbpass ."@". $dbhost ."/". $dbname;

$conn =& DB::connect ($dsn);

$sql = "CREATE TABLE test_table (
id int(5) auto_increment NOT NULL,
name varchar(25) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=MyISAM COMMENT='Membership Information'";

$sql = "INSERT INTO test_table VALUES(null, 'Tomas'),
(null, 'Alex'),
(null, 'Peter'),
(null, 'Marry')";

if (DB::isError ($result)){
die ("INSERT failed: " . $result->getMessage () . "<br />query: ".$sql);}

[2006-09-12 15:22 UTC] myloforreal at hotmail dot com

I think you'll find the auto_increment part giving you problems. It is mysql specific, and DB tries to stay database agnostic