Yii2: ms sql insert error 07002: COUNT field incorrect or syntax error

[alligator77]

yii\db\ExpressionBuilder incorrect merging general params with \yii\db\Expression class params.

I use ms sql, dsn in config:

'dsn' => 'sqlsrv:Server=localhost;Database=testdb',

DDL for table

CREATE TABLE [dbo].[dev_functions] (
[function_id] int NOT NULL IDENTITY(1,1) ,
[shema_id] int NULL ,
[version_id] int NULL ,
[source] varbinary(MAX) NULL ,
[arguments] nvarchar(255) COLLATE Cyrillic_General_CI_AS NULL ,
[use_in_acalcs] tinyint NULL ,
[name] nvarchar(255) COLLATE Cyrillic_General_CI_AS NULL ,
[deleted] tinyint NULL ,
[tags] nvarchar(500) COLLATE Cyrillic_General_CI_AS NULL ,
[description] nvarchar(255) COLLATE Cyrillic_General_CI_AS NULL ,
[builder_name] nvarchar(150) COLLATE Cyrillic_General_CI_AS NULL ,
CONSTRAINT [PK__dev_func__FC85AD04D84d9655C] PRIMARY KEY ([function_id])
)

Model:

class DevFunctions extends \yii\db\ActiveRecord
{

    public static function tableName()
    {
        return 'dev_functions';
    }

    public function rules()
    {
        return [
            [['shema_id', 'version_id', 'use_in_acalcs', 'deleted'], 'integer'],
            [['source', 'builder_name'], 'string'],
            [['arguments', 'name', 'description'], 'string', 'max' => 255],
            [['tags'], 'string', 'max' => 500],
        ];
    }
}

Create new object expression:

$func = new DevFunctions();
$func->shema_id = 1; 
$func->version_id = 1;
$func->name = 'MinOf ';
$func->arguments = 'a, b';
$func->source = 'If a < b Then
    Return a
    Else
    Return b
    End If
';

if (!$func->save())
    throw new Exception();

Try to insert new record, but throw exception:

Exception 'yii\db\Exception' with message 'SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server] 
COUNT field incorrect or syntax error
The SQL being executed was: INSERT INTO [dev_functions] ([shema_id], [version_id], [name], [arguments], [source]) VALUES ('
    If a < b Then
    Return a
    Else
    Return b
    End If
', 29, 'MinOf', 'a, b', CONVERT(VARBINARY, '
    If a < b Then
    Return a
    Else
    Return b
    End If
'))'

It is quite logical. Yii2 sets incorrect value in field [shema_id].
I got to the place, where there is a non-correct merging of parameter arrays. See bellow.

When [source] field in table is [varbinary] type, then yii\db\mssql\QueryBuilder::normalizeTableRowData(...) convert value of [source] field to yii\db\Expression object as:

yii\db\Expression Object
(
    [expression] => CONVERT(VARBINARY, :qp0)
    [params] => Array
        (
            [:qp0] =>
    If a < b Then
    Return a
    Else
    Return b
    End If

        )

)

after then, class yii\db\ExpressionBuilder merging general params with params of yii\db\Expression:

class ExpressionBuilder implements ExpressionBuilderInterface
{
    use ExpressionBuilderTrait;


    /**
     * {@inheritdoc}
     * @param Expression|ExpressionInterface $expression the expression to be built
     */
    public function build(ExpressionInterface $expression, array &$params = [])
    {
    print_r($params); //i set this debug
        print_r($expression->params); //i set this debug
        $params = array_merge($params, $expression->params);
    print_r($params); //i set this debug
        return $expression->__toString();
    }
}

Result:

General Params:
Array
(
    [:qp0] => 30
    [:qp1] => 29
    [:qp2] => MinOf
    [:qp3] => a, b
)
Expression Params:
Array
(
    [:qp0] =>
    If a < b Then
    MinOf = a
    Else
    MinOf = b
    End If

)
Merged Params:
Array
(
    [:qp0] =>
    If a < b Then
    MinOf = a
    Else
    MinOf = b
    End If

    [:qp1] => 29
    [:qp2] => MinOf
    [:qp3] => a, b
)

Its a bug.

| Q | A
| ---------------- | ---
| Yii version | 2.0.15.1
| PHP version | 5.6
| Operating system | Win 10 x64

[samdark]

Would you like to fix it with a pull request?

[alligator77]

I try tomorrow.. I think :)

[alligator77]

Please, create You pull request, I dont know where is true branch of 2.0.15.1

$exParams = []; //here is bug
$phName = $this->bindParam($value, $exParams );
$columns[$name] = new Expression("CONVERT(VARBINARY, $phName)", $exParams );

Small changes, need to replace private func normalizeTableRowData in yii\db\mssql\QueryBuilder:

private function normalizeTableRowData($table, $columns, &$params)
    {
        if (($tableSchema = $this->db->getSchema()->getTableSchema($table)) !== null) {
            $columnSchemas = $tableSchema->columns;
            foreach ($columns as $name => $value) {
                // @see https://github.com/yiisoft/yii2/issues/12599
                if (isset($columnSchemas[$name]) && $columnSchemas[$name]->type === Schema::TYPE_BINARY && $columnSchemas[$name]->dbType === 'varbinary' && is_string($value)) {
                    $phName = $this->bindParam($value, $params);
                    $columns[$name] = new Expression("CONVERT(VARBINARY, $phName)", $params);
                }
            }
        }

        return $columns;
    }

Tnx

[samdark]

It is master.