pdo_commit_bug

<? # PDO::commit() bug(?) -- cottton @i-stats.net
$user = 'set_uname';
$pw = 'set_pw';
$db = 'set_ db';

# 1. connect and use db
#
try{
    $dbh = new PDO('mysql:host=localhost',$user,$pw);
}catch(PDOException $e){
    echo $e->getMessage();
}
$sql = "USE ".str_replace("'",'`',$dbh->quote($db));
$stmt = $dbh->prepare($sql);
if(!$stmt->execute()){
    $err_info = $stmt->errorInfo();
    exit(
        '(SQLSTATE: '.$err_info[0].') '.((empty($err_info[2]))?error_get_last_str():$err_info[2]).
        '<br>Query: '.$sql
    );
}


# 2. create tbl if not exists
#
$sql = "
    CREATE TABLE IF NOT EXISTS `testtable`
    (
        `id` INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
        `name` VARCHAR(16) NOT NULL
    );
";
$stmt = $dbh->prepare($sql);
if(!$stmt->execute()){
    $err_info = $stmt->errorInfo();
    exit(
        '(SQLSTATE: '.$err_info[0].') '.((empty($err_info[2]))?error_get_last_str():$err_info[2]).
        '<br>Query: '.$sql
    );
}


# 3. multi insert via beginTransaction() and commit() / rollBack()
#
$dbh->beginTransaction();
$sql = "
    INSERT INTO testtable SET name = :name1;
    INSERT INTO testtable SET name = :name2;
    INSERT INTO testtable SET name = :name3;
";
$para = array(
    'name1' => 'hans',
    'name2' => 'klaus',
    'name3' => 'olaf'
);
$stmt = $dbh->prepare($sql);
if(!$stmt->execute($para)){
    $dbh->rollBack();
    $err_info = $stmt->errorInfo();
    exit(
        '(SQLSTATE: '.$err_info[0].') '.((empty($err_info[2]))?error_get_last_str():$err_info[2]).
        '<br>Query: '.$sql
    );
}else{
    if(1){
        echo '<h1>bug(?) "version":</h1>';
        /*DEBUG*/ test($stmt,'$stmt before $dbh->commit()');
        /*DEBUG*/ test($dbh->commit(),'fire $dbh->commit()');
        /*DEBUG*/ test($stmt,'$stmt after $dbh->commit()');
    }else{
        echo '<h1>working "version":</h1>';
        /*DEBUG*/ test($stmt,'$stmt before $dbh->commit()');
        /*DEBUG*/ test($stmt = 'string','$stmt = \'string\'');
        /*DEBUG*/ test($dbh->commit(),'fire $dbh->commit()');
        /*DEBUG*/ test($stmt,'$stmt after $dbh->commit()');
    }
}


## 4. read tbl
#
$sql = "SELECT * FROM `testtable`;";
$stmt = $dbh->prepare($sql);
if(!$stmt->execute()){
    $err_info = $stmt->errorInfo();
    exit(
        '(SQLSTATE: '.$err_info[0].') '.((empty($err_info[2]))?error_get_last_str():$err_info[2]).
        '<br>Query: '.$sql
    );
}else{
    while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
        $result[] = $row;
    }
    /*DEBUG*/ test($result,'SELECT * FROM `testtable`;');
}



function test($val,$title='') # just a dubug func ... life easier ...
{
    $br = '<br />';
    echo '<pre># <b>DEBUG</b>: <u>'.$title.'</u>'.$br;
    echo '<i>type:</i> '.gettype($val).$br.'<i>val:</i> ';
    if(is_bool($val)){
        echo ($val === true)?'true':'false';
    }elseif(is_null($val)){
        echo 'NULL';
    }elseif(!is_numeric($val)and empty($val)){
        echo 'empty';
    }elseif(is_resource($val)){
        echo get_resource_type($val);
    }else{
        print_r($val);
    }
    echo $br.'#/DEBUG</pre>';
}
?>