Bug on PDO? Table Disappears After INSERT Command, Like Debug?

Question

Bug on PDO? Table Disappears After INSERT Command, Like Debug?

Navigation

#1 by (1 votes)

3

Introduction

I'm creating a library with some "magic methods" on the PDO , already I did this with MySQLi and it worked beautifully. The intention is to use it in a Framework that I am building , finally it comes from soap.

Problem

Mysteriously my test table is being deleted shortly after (or during, I'm not sure) a INSERT .

Code

File:teste.php

<?php$con=NewConnectionPDO;echo'<pre>';var_dump($con->getTables());echo'</pre>';$r=$con->insert('tab_teste',Array('id'=>1,'name'=>'FirstRecord','col3'=>'test'))->execute();$log.=($r?'Success':'Fail').PHP_EOL;echo'<pre>';echo$con->lastSQL().PHP_EOL;echo'</pre>';echo'<pre>';var_dump($con->getTables());echo'</pre>';?>

Class:ConnectionPDO

<?phpclassConnectionPDOextendsPDO{function__construct($dsn,$username=NULL,$password=NULL,$options=NULL){parent::__construct($dsn,$username,$password,$options);$this->LoadDriverMethods();}privatefunctionLoadDriverMethods(){$driver=__DIR__.DIRECTORY_SEPARATOR.'drivers'.DIRECTORY_SEPARATOR.'sqldriver.'.strtolower($this->getAttribute(PDO::ATTR_DRIVER_NAME)).'.php';if(!is_file($driver))thrownewException('Nãofoipossívelcarregarosmétodosdodriver',1);require_once$driver;$this->driver=newSQLDriver();}publicfunctioninsert($table,$data){$this->lastSQL=$this->driver->insert($table,$data);$stmt=$this->prepare($this->lastSQL);$this->driver->setParams($this->stmt);$this->log.=$this->driver->flushLog();return$this->stmt;}}?>

Class:SQLDriver

<?phpclassSQLDriverimplementsDriverInterface{publicfunctioninsert($table,$data){$this->clearParams();$sql="INSERT INTO '{$table}' ";

       $colunms = Array();

       $values  = Array();

       foreach ($data as $col => $value) {
          $colunms[] = "'{$col}'";
          $values[]  = '?';

          $this->addParam($col, $value);
       }

       $sql .= '(' . implode(', ', $colunms) . ') VALUES (' . implode(', ', $values) . ');';

       $this->log .= $sql . PHP_EOL;

       return $sql;
    }

    public function addParam($key, $value){
       //$this->params[':'.$key] = $value;
       $this->params[] = $value;
    }

    public function setParams(PDOStatement $stmt){
       $params = $this->getParams();
       $this->log .=  'Setando Parâmetros: '.PHP_EOL;
       if (is_array($params) && !empty($params)){
          foreach ($params as $param => $value){
             $stmt->bindValue($param+1, $this->prepareParam($value), $this->getParamType($value));
             $this->log .=  $param+1 . ' => ' . $this->prepareParam($value) . PHP_EOL;
          }
       }
       $this->log .= PHP_EOL.'-----------------------------'.PHP_EOL.PHP_EOL;
    }

 }
 ?>

I just put the code around the error. The complete code can be seen in the links:

Unless someone downloads the code and keeps testing and searching I do not think you'll find the problem so easily, so the main question here is, How to debug the PDO? The secondary is > what could be causing this? The third, why?

php mysql pdo

asked by anonymous 23.09.2015 / 22:52

1 answer

Notifications API PagSeguro Slide in jquery with div, containing image and content

score 1 · Accepted Answer

Part of the problem is the example of the question it does not simulate the table symptom disappear, as the github makes it clear.

TL; DR

The method insert() , the% select also prepares the last sql statement but then return to the previous prepared query ie the combination of the test% DROP TABLE runs twice.

I built a smaller test, just made a change to highlight the problem, changed the visibility $stmt in ConnectionPDO private to public only for testing, so you can see what the last query executed by queryString and make the comparison.

<?php 
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
$stmt = NULL;
function __autoload($class){
   if (class_exists($class)) return true;
   $dir = __DIR__.DIRECTORY_SEPARATOR;
   $ext = '.php';
   if (file_exists($dir.$class.$ext)) require_once $dir.$class.$ext;
   else exit('Couldn\'t open class '.$class.'!');
}
$settings = Array(
   'driver'    => 'mysql',
   'host'      => 'localhost',
   'port'      => '3306',
   'schema'    => 'pdoweb2',
   'username'  => 'root',
   'password'  => 'root'
);
$dns = $settings['driver'] . ':host=' . $settings['host'] . 
                             ';port=' . $settings['port'] . 
                             ';dbname=' . $settings['schema'];
$con = new ConnectionPDO($dns, $settings['username'], $settings['password']);



$r = $con->drop('tab_teste');

    $fields = Array('id' => Array('type' => 'int', 'size' => '4', 'comment' => 'first key', 'auto' => true),
                    'name' => Array('type' => 'varchar', 'size' => '60', 'comment' => 'test name'),
                    'col3' => Array('type' => 'varchar', 'size' => '60', 'default' => NULL,'comment' => 'test name')
            );

    $sql =  'CREATE TABLE 'tab_teste' (' . PHP_EOL .
            '   id INT(4) NOT NULL AUTO_INCREMENT PRIMARY KEY,' . PHP_EOL .
            '   name VARCHAR(60),' . PHP_EOL .
            '   col3 VARCHAR(60) DEFAULT NULL' . PHP_EOL .
            ');';

    $r = $con->query($sql);

    echo 'getTables()<pre>';        
    print_r($con->getTables());
    echo '</pre>';

    $r = $con->insert('tab_teste',Array('id'=>1,'name' => 'First Record', 'col3' => 'test '))->execute();

    echo 'LastSQL: '. $con->lastSQL() .'<br>';
    echo 'Ultima consulta preparada: '. $con->stmt->queryString .'<br><br>';


    $r = $con->insert('tab_teste',Array('id'=>2,'name' => 'Second Record', 'col3' => 'test '))->execute();
    echo 'LastSQL: '. $con->lastSQL() .'<br>';
    echo 'Ultima consulta preparada: '. $con->stmt->queryString .'<br><br>';


    echo 'getTables()<pre>';        
    print_r($con->getTables());
    echo '</pre>';

The output was:

getTables()

Array
(
    [0] => tab_teste
)

LastSQL: INSERT INTO 'tab_teste' ('id', 'name', 'col3') VALUES (?, ?, ?);
Ultima consulta preparada: DROP TABLE IF EXISTS 'tab_teste';

LastSQL: INSERT INTO 'tab_teste' ('id', 'name', 'col3') VALUES (?, ?, ?);
Ultima consulta preparada: DROP TABLE IF EXISTS 'tab_teste';

getTables()

Array
(
)

The first statement executed is a drop() no problem, the next is a insert() here is where the problem occurs, the insert is prepared and played in a local variable $stmt and a few lines later is returned the % s of% that has the drop processed previously.

public function insert($table, $data) {
   $this->lastSQL = $this->driver->insert($table, $data);
   $stmt = $this->prepare($this->lastSQL); //<--- variável local
   $this->driver->setParams($this->stmt);
   $this->log .= $this->driver->flushLog();
   return $this->stmt; //<--- retorno da consulta anterior, o drop no caso
}

public function drop($table){
   $this->lastSQL = $this->driver->drop($table);
   $this->stmt = $this->prepare($this->lastSQL);
   $this->log .= $this->driver->flushLog();
   return $this->stmt;
}

To fix this do the assignment in member in place of the local variable, apply the mudanção% in $this->stmt and insert() .

Change:

$stmt = $this->prepare($this->lastSQL);

To:

$this->stmt = $this->prepare($this->lastSQL);