title: Using Doctrine DBAL to create views and stored procedures
body: <p>We are using Doctrine in a Symfony2 application. In order to help with some database interactions, we have defined some views and stored procedures. These are stored in a specific directory of our project source and then put under version control so everyone in the team can have access to them. Each view or stored procedure has its own .sql file containing the query that will create the view / stored procedure, including a "drop if already exists" line.</p>
  
  <p>We have written a console command to go through the directory that holds the .sql files and then issues the query to the database via a PDO object created from the Doctrine dbal connection.</p>
  
  <p>Examination of the MySQL query log reveals that each "CREATE VIEW" and "CREATE PROCEDURE" command has been executed, and the PDO connection is not giving any errors. But, when we examine the database after the command have run, not all the expected items exist. Here is the strange bit... at the moment, we have commands to create 3 views and 1 stored procedure. When we run the command for the first time, 2 of the views are created. Then, we run it again, and the third view is created. The stored procedure is never created, no matter how many times we run the command.</p>
  
  <p>So, can anyone offer any help as to why this could be happening. The database user has the necessary privileges and the query log suggests everything is fine.</p>
  
  <p>Here is the execute method of the console command;</p>
  
  <pre><code>/**
  * execute command
  *
  * @param InputInterface  $input
  * @param OutputInterface $output
  */
  protected function execute(InputInterface $input, OutputInterface $output)
  {
      $path = $input-&gt;getArgument('path');
  
      $this-&gt;setAbsolutePath($path);
  
      if (!$this-&gt;doesPathExist()) {
          throw new \InvalidArgumentException($this-&gt;absolutePath.' does not exist, or is not a directory');
      }
  
      $files = $this-&gt;getFiles();
  
      foreach ($files as $file) {
          $bits = explode('.', $file);
          if (count($bits)&gt;1) {
              $extension = end($bits);
              if (strtolower($extension) === 'sql' ) {
  
                  if ($this-&gt;runScript($file)) {
                      $output-&gt;writeln($file.' ran successfully!');
                  } else {
                      $output-&gt;writeln($file. ' errored!');
                  }
              }
          }
      }
  
      $output-&gt;writeln('Done');
  }
  </code></pre>
  
  <p>which calls this method 'runScript'...</p>
  
  <pre><code>/**
  * run script
  * executes the sql commands in the provided file
  * returns true if the script executed, false otherwise
  *
  * @param string $filename
  *
  * @return boolean
  */
  private function runScript($filename)
  {
      $em = $this-&gt;getContainer()-&gt;get('doctrine')-&gt;getManager();
  
      $connection = $em-&gt;getConnection();
      $host = $connection-&gt;getHost();
      $username = $connection-&gt;getUsername();
      $password = $connection-&gt;getPassword();
      $database = $connection-&gt;getDatabase();
      $pdoConn = new \PDO('mysql:host=' . $host . ';dbname=' . $database, $username, $password);
  
      $result = false;
  
      if ($pdoConn) {
          $result = false;
          $script = file_get_contents($this-&gt;absolutePath.'/'.$filename);
          $stmt = $pdoConn-&gt;prepare($script);
          try {
              $result = $stmt-&gt;execute();
          } catch (\PDOException $e) {
              $result = false;
          }
          unset($stmt);
      }
      unset($pdoConn);
  
      return $result;
  }
  </code></pre>
  
  <p>AS you can see, we've been experimenting with creating a completely new PDO database connection to issue the commands. When the command is run, it lists the four .sql files and says they all run successfully.</p>
  
  <p>If we execute the .sql files directory using MySQL on the command line, using the same user that Symfony uses to connect to the database, all the views and procedures are created correctly, so we're sure there are no errors in the .sql files.</p>
  
  <p>We're stumped!</p>