How to run multiple SQL queries for each call to a query using Zend_Db_Adapter?

Question

This is the Zend_Application_Resource that I wrote to automatically update the schema when making changes to the deployment.

 <?php /** * Makes sure the current schema version matches what we're expecting for this * particular version of the application. * * The version of the database is compared against the value configured in * application.ini. SQL scripts corresponding to versions between the database * version and the current local SQL scripts are run against the database to * get things up to date. * * @copyright 2011 Case Western Reserve University, College of Arts and Sciences * @author Billy O'Neal III ( [e-mail] ) */ class Cas_Application_Resource_Schema extends Zend_Application_Resource_ResourceAbstract { /** * Creates an array of filepaths corresponding to the scripts that need * to run. * * @param int $from * @param int $to * @return array */ private function GetScriptsToRun($from, $to) { $application = APPLICATION_PATH . '/configs/sql/'; $result = array(); for($cur = $from + 1; $cur <= $to; $cur++) { $result[] = "{$application}{$cur}.sql"; } return $result; } /** * Returns the version the application is locally configured to expect. * * @return int */ private function GetLocalVersion() { $options = $this->getOptions(); $version = (int)$options['version']; return $version; } /** * Returns the version the database thinks it is. * * @return int */ private function GetDbVersion() { $adapter = Zend_Db_Table::getDefaultAdapter(); $metadataTable = new Cas_Model_Table_Metadata; $verQuery = $metadataTable->select()->from($metadataTable, array('Value')); $verQuery->where("{$adapter->quoteIdentifier('Key')} = ?", 'Version'); $dbVersion = $adapter->fetchOne($verQuery); return (int)$dbVersion; } /** * Runs the specified filepath file contents as a SQL script. * * @param string $scriptPath */ private function RunSqlScript($scriptPath) { $adapter = Zend_Db_Table::getDefaultAdapter(); $contents = file_get_contents($scriptPath); $adapter->query($contents); } /** * Updates the version number in the database to match the version * specified in the local configuration file. */ private function UpdateVersion() { $metadataTable = new Cas_Model_Table_Metadata; $metadataTable->delete(array("{$metadataTable->getAdapter()->quoteIdentifier('Key')} = ?" => 'Version')); $metadataTable->insert(array('Version' => $this->GetLocalVersion())); } /** * Performs the actual schema checks. */ public function init() { //We depend on the database being connected. $this->getBootstrap()->bootstrap('db'); $local = $this->GetLocalVersion(); $remote = $this->GetDbVersion(); if ($local < $remote) { throw new Exception('Database version is newer than local version.'); } else if ($remote < $local) { $scripts = self::GetScriptsToRun($remote, $local); foreach($scripts as $script) { $this->RunSqlScript($script); } $this->UpdateVersion(); } } } 

This is because the scripts (for example, configs / sql / 1.sql, configs / sql / 2.sql, etc.) contain several SQL statements, separated by s, with the message:

Zend_Db_Statement_Mysqli_Exception: Mysqli preparation error: you have an error in the SQL syntax; check the manual corresponding to your MySQL server version for the correct syntax used next to ';'

Answer 1

Zend_Db_Adapter does not support multi_query() .

You have a choice for workarounds:

• A call to $adapter->getConnection() , which will return an instance of the underlying mysqli resource. You can call the multi_query() method of this resource.

• Divide the contents of the file into an array of individual SQL statements, and for each statement, call $adapter->query() . Be careful with edge cases.

• Use shell_exec() to invoke a subprocess for the mysql command-line tool to process the SQL script.