Yii2 How to Execute two Statement using PDO? or other way

Question

I want to CREATE DATABASE and in same database want to import data. But what i tried since getting no luck.

Action

public function actionRestore($id = null)
{
    $list = $this->getFileList();
    $file = $list[$id];
    if(isset($file))
    {
        $transaction = Yii::$app->db->beginTransaction();
        try{

            $sql = 'DROP DATABASE IF EXISTS '.$this->getDbName().';CREATE DATABASE '.$this->getDbName();
            $sqlFile = $this->path . basename($file);
            Yii::$app->db->pdo->prepare($sql,$this->execSqlFile($sqlFile));
            if(Yii::$app->db->pdo->exec())
            {
                $transaction->commit();
                Yii::$app->session->setFlash('success', 'Backup Restored Successfully');
                return $this->redirect(['index']);
            }
            $transaction->rollback();
        } 
        catch(\Exception $e) {
            $transaction->rollBack();
            Yii::$app->session->setFlash('error', "Backup not Restored. <br>".$e->getMessage());
            return $this->redirect(['index']);

        }   
    }
}

I am not sure about execSqlFile() method :

public function execSqlFile($sqlFile)
{
    $flag = false; 
    if (file_exists($sqlFile))
    {
        $sqlArray = file_get_contents($sqlFile);

        $cmd = Yii::$app->db->createCommand($sqlArray);
        try {
            $cmd->execute();
            $flag = true;
        }
        catch(Exception $e)
        {
            $flag = false;
            throw new \yii\db\Exception($e->getMessage());

        }

    }
    return $flag;

}

1) getDbName() gets database name.
1) getFileList() gets file to be executed in execSqlFile().

I am not getting any error or message of success or failure.
I want to combine both into one preparedStatement, but don't know what i am missing here.

see this [answer](http://stackoverflow.com/a/6549440/3294262) about creating (and maybe dropping) database — fusion3k, Feb 17 '16 at 13:01
[```prepare()``` in Yii2](http://www.yiiframework.com/doc-2.0/yii-db-command.html#prepare%28%29-detail) only takes one argument. Your execution relies on the second argument. — WeSee, Feb 17 '16 at 13:08
1. No point in using transactions with ddl commands, they implicitly commit any transaction and they cannot be rolled back. 2. There is no point using prepared statements this way either. There are no parameters. — Shadow, Feb 17 '16 at 13:12
See my answer here: https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — Sam Dark, Feb 17 '16 at 13:12
@SamDark. Unable to get you can you provide an answer if possible. I am trying different approach whole day. — Insane Skull, Feb 17 '16 at 13:18

score 1 · Accepted Answer · answered Feb 18 '16 at 06:53

I found the solution i need to use shell_exec:

public function execSqlFile($sqlFile)
{

    if (file_exists($sqlFile))
    {   

        $database=array();
        $db=Yii::$app->db;
        $database=explode(";",$db->dsn);
        $dbname=explode("=",$database['1']);
        $output = shell_exec('mysql -u '.$db->username.' -p'.$db->password.' '. $dbname['1'] .'< '.$sqlFile);
    }
    return $output;

}

score 0 · Answer 2 · answered Feb 17 '16 at 13:17

Don't use PDO object directly. You loose abstractions. I would just execute two commands like this:

public function actionRestore($id = null)
{
    if($id !== null && $this->restoreDatabase($id)) {
        Yii::$app->session->setFlash('success', 'Backup Restored Successfully');
    } else {
        Yii::$app->session->setFlash('error', "Backup not Restored. <br>" . $e->getMessage());
    }

    return $this->redirect(['index']);
}

private function restoreDatabase($id)
{
    $list = $this->getFileList();
    $file = $list[$id];
    if (isset($file)) {
        $transaction = Yii::$app->db->beginTransaction();
        try {
            Yii::$app->db->createCommand('DROP DATABASE IF EXISTS ' . $this->getDbName() . '; CREATE DATABASE ' . $this->getDbName())->execute();

            $sqlFile = $this->path . basename($file);
            $this->execSqlFile($sqlFile);

            $transaction->commit();

            return true;
        } catch (\Exception $e) {
            $transaction->rollBack();
            Yii::error($e->getMessage()); //Probably throw exception?

            return false;
        }
    }
}

private function execSqlFile($sqlFile)
{
    if (!file_exists($sqlFile)) {
        return false;
    }

    $sql = file_get_contents($sqlFile);
    $command = Yii::$app->db->createCommand($sql);
    try {
        $command->execute();

        return true;
    } catch (Exception $e) {
        throw new \yii\db\Exception($e->getMessage());
    }
}

But when i try this, i lost authentication during execution because of no database. — Insane Skull, Feb 17 '16 at 13:19
if i drop database it will break down operation, says tables not found. — Insane Skull, Feb 17 '16 at 18:47
@InsaneSkull It's strange. Commands executed successively and even in transaction. Try write test for delete-create database in first executed command and create table and insert in second executed command. I think that is passed. On that sting you get error? — Onedev_Link, Feb 17 '16 at 21:36

Yii2 How to Execute two Statement using PDO? or other way

2 Answers2