mardi 18 mai 2021

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

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]
);

And 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]: <<Unknown error>>: 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.



via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire