How to check if db execute sql fails?

Question

I use console command in Yii2 framework and want to import db schema file via execute() method:

public function actionReset()
{
    $schemasPath = Yii::getAlias('@common') . DIRECTORY_SEPARATOR . 'data' . DIRECTORY_SEPARATOR;
    $schemaFile = $schemasPath . 'schema.sql';
    try {
        $command = Yii::$app->db->createCommand(file_get_contents($schemaFile));
        $command->execute();

        Console::output('Schema imported.');
        return self::EXIT_CODE_NORMAL;
    } catch (\Exception $e) {
        Console::error($e->getMessage());
    }
    return self::EXIT_CODE_ERROR;
}

Documentation says:

This method should only be used for executing non-query SQL statement, such as INSERT, DELETE, UPDATE SQLs. No result set will be returned.

Run this as:

$ ./yii db/reset

produce no error/exceptions.

Run this as:

$ mysql -u{user} -p -hlocalhost -D{base} < schema.sql

produce:

ERROR 1064 (42000) at line 241: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'JSON DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci' at line 4

Because MariaDB 10.1 don't support JSON data type. So, how to check if all queries/commands from $schemaFile was success? Trying find some Yii::$app->db->getLastError() - no result. Hm, some ideas?

Use http://www.yiiframework.com/doc-2.0/yii-db-transaction.html.. and catch errors — Double H, Sep 12 '17 at 14:06
@scaisEdge, I did not change/set exception management - it's framework defaults. — TomaszKane, Sep 13 '17 at 09:01

score 0 · Accepted Answer · answered Sep 18 '17 at 13:09

I found a solution here: https://stackoverflow.com/a/23258691/1829368, it's:

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

    do { } while ($command->pdoStatement->nextRowset());

    Console::output('Schema imported.');
    return self::EXIT_CODE_NORMAL;
} catch (\Exception $e) {
    Console::error($e->getMessage());
}

A do-while loop with nextRowset() trigger \PDOException when first db query fails.

How to check if db execute sql fails?

1 Answers1