Is there a way to mix @variables with placeholders in Perl using DBI and MySQL/MyISAM?

user_639716 0
Viewed 75

I have a query like this:

SELECT
    foo.bar
FROM
    foo
WHERE
    foo.bang = 0
    AND (
        CASE
            WHEN ? = 2 THEN foo.baz IS NOT NULL
            WHEN ? = 1 THEN foo.baz IS NULL
            ELSE ? NOT IN (1, 2)
        END
    )
    AND (
        (? = 0)
        OR
        (foo.bang = ?)
    )

where the placeholders (?) are used as inputs for filter parameters:

my $query = $aboveQuery;
my ( $results ) = $dbh->DBI::db::selectall_arrayref(
    $query,
    { Slice => {} },
    $bazFilter,
    $bazFilter,
    $bazFilter,
    $bangFilter,
    $bangFilter,
);

This works, but it's not very reader-friendly.
MySQL supports @ variables, which would increase readability:

SET @bazFilter = ?;  -- passed in via selectall_arrayref or execute;
SET @bangFilter = ?;

SELECT
    foo.bar
FROM
    foo
WHERE
    foo.bang = 0
    AND (
        CASE
            WHEN @bazFilter = 2 THEN foo.baz IS NOT NULL
            WHEN @bazFilter = 1 THEN foo.baz IS NULL
            ELSE @bazFilter NOT IN (1, 2)
        END
    )
    AND (
        (@bangFilter = 0)
        OR
        (foo.bang = @bangFilter)
    );

I'd like to do something like this instead:

my $query = $aboveAtVariablizedQuery;
my ( $results ) = $dbh->DBI::db::selectall_arrayref(
    $query,
    { Slice => {} },
    $bazFilter,
    $bangFilter,
);

but MyISAM apparently won't do multiple statements in a single query.

My google-fu is failing me.
Is there a good way to mix-and-match the @variables with the placeholders?

dbi myisam perl mysql
1 Answers

Do each statement separately, using execute not one of the fetch methods.

@ variables are local to the connection, so there is no problem with contamination between threads.

If the goal is to have a single query with no repeated substitutions, then consider:

SELECT  foo.bar
    FROM  ( SELECT baz = ?, bang = ? ) AS init
    JOIN  foo
    WHERE   foo.bang = 0
        AND (
            CASE
                WHEN init.baz = 2 THEN foo.baz IS NOT NULL
                WHEN init.baz = 1 THEN foo.baz IS NULL
                ELSE init.baz NOT IN (1, 2)
            END
        )
        AND (
            (init.bang = 0)
            OR
            (foo.bang = init.bang)
        );
user_17668310
Related