Not working bind values in CreateCommand

[free6k]

Hello! I have problem in binding params...
I use:

Yii::$app->db->createCommand('SET TIME ZONE :timezone', [':timezone' => $timezone])->execute();

or

Yii::$app->db->createCommand('SET TIME ZONE :timezone', [':timezone' => [$timezone, PDO::PARAM_STR]])->execute();

And i get error:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1"
LINE 1: SET TIME ZONE $1
^
The SQL being executed was: SET TIME ZONE 'Europe/Moscow'
Error Info: Array
(
[0] => 42601
[1] => 7
[2] => ERROR: syntax error at or near "$1"
LINE 1: SET TIME ZONE $1
^
)


Tell me what to do? Thanks for all!

Yii 2.0 - stable
php 5.4
PSQL 9.3

[qiangxue]

Try using raw PDO to see if this works. If not, it means PDO doesn't support this syntax.

[cebe]

what is the value of $timezone in your code?

[free6k]

My code

    public static function processingTimezone()
    {
        $timezone = (Yii::$app->user->timezone) ? Yii::$app->user->timezone : Yii::$app->params['languageParams']['defaultTimezone'];

        if ($timezone) {
            date_default_timezone_set(StringTool::escapeString($timezone));
            Yii::$app->db->createCommand("SET TIME ZONE '" . StringTool::escapeString($timezone) . "'")->execute();

        }
    }

$timezone is string value with timezone name 'Europe/Moscow' default

[mdmunir]

IMO its bug (or not suported) of PDO

Yii::$app->db->open();
$pdo = Yii::$app->db->pdo;
$timezone = 'Asia/Jakarta';
$statement = $pdo->prepare('SET TIME ZONE :timezone');
$statement->execute([':timezone'=>$timezone]);

get the same error

PDOException
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1"
LINE 1: SET TIME ZONE $1

[qiangxue]

Thanks @mdmunir.

[brocard]

The continuing problem when trying to pass a parameter as a string for example

Yii::$app->db->createCommand("TRUNCATE TABLE :tableName", [':tableName'=> $tableName]);

Any solution???

[samdark]

You can't bind table names or column names. These should be properly escaped and then concatenated w/ SQL string.