How do I run multiple SQL queries per call to "query" using Zend_Db_Adapter?

Question

This is a Zend_Application_Resource that I wrote to update the schema automatically when changes are made in a deploy.

<?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's 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 (bro4@case.edu)
 */
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's 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 fails because the scripts (e.g. configs/sql/1.sql, configs/sql/2.sql, etc.) in question contain multiple SQL statements, separated by ;s, with a message something like:

Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ';'

score 4 · Accepted Answer · edited May 23 '17 at 10:24

4

Zend_Db_Adapter does not support multi_query().

You have choices for workarounds:

Call $adapter->getConnection(), which will return an instance of the underlying mysqli resource. You can call the multi_query() method of this resource.
Split the contents of the file into an array of individual SQL statements, and for each statement, call $adapter->query(). Be careful about edge cases.
Use shell_exec() to invoke a subprocess for the mysql command-line tool to process the SQL script.

edited May 23 '17 at 10:24

Community

1
1

answered Sep 12 '11 at 23:29

Bill Karwin

538,548
86
673
828

:sigh: ... that's frustrating. +1. – Billy ONeal Sep 12 '11 at 23:32
Well, that causes another problem -> http://stackoverflow.com/questions/7395326/how-do-i-ensure-i-caught-all-errors-from-mysqlimulti-query .. I can't use `shell_exec` because the mysql binary isn't available on the PHP server, and I'm not getting into SQL parsing myself. – Billy ONeal Sep 12 '11 at 23:47

How do I run multiple SQL queries per call to "query" using Zend_Db_Adapter?

1 Answers1