Laravel 5.1 DB::statement with named binding returning "SQLSTATE[HY093]: Invalid parameter number"

Viewed 215

I have the following code:

DB::statement(
    "DO $$
    BEGIN
        DELETE FROM sku_inativacao WHERE active = true;
        
        INSERT INTO sku_inativacao (id_sku, id_motivo, created_at, updated_at) (
            SELECT 
                id_sku, 
                CASE
                    WHEN data_u_venda is null AND estoque_atual = 0 THEN 1
                    WHEN data_u_venda < current_timestamp - interval '2 years' THEN 2
                END id_motivo,
                current_timestamp AS created_at,
                current_timestamp AS updated_at
            FROM 
                sku
            WHERE
                data_criacao::timestamp < current_timestamp - interval ':diasCriacao days'
                and fase_vida < 3
            AND
                (data_u_venda is null AND 
                estoque_atual = 0)
            OR
                (data_u_venda < current_timestamp - interval '2 years')
        ) ON CONFLICT (id_sku) DO nothing;
    END $$;",
    ['diasCriacao' => $this->diasCriacao ?? 90]
);

As far as I can see in the documentation this is correct, however with named biding it returns:

SQLSTATE[HY093]: Invalid parameter number: :diasCriacao

And using normal bindings it returns:

SQLSTATE[08P01]: <>: 7 ERROR:bind message supplies 1 parameters, but prepared statement "pdo_stmt_00000004" requires 0

What can I do to fix this? I am using Laravel 5.1, sadly.

1 Answers

This was solved by not using DB::statement. When used with DB::update it worked with the provided parameters, it would seem like it is a bug in Laravel 5.1

Related