SQL query generator for Perl with stored procedures support

Question

Current code base I'm working on is full of ad-hoc conditional string concatenations producing less than clear SQL queries. I want to make them maintainable, but since using DBIx::Class is too complex to move to for now (giant legacy base), I'm looking to at least make them more robust by using some sort of SQL generator, which would only create the SQL by object-orientation or any other clean technique (no DB handling necessary).

One general constraint on that generator is the ability to use stored procedures in a sane way, since my application is mostly based on those. For example, I need to SELECT * FROM StoredProcedure(Parameter) WHERE .... I've looked into Fey::SQL, SQL::Abstract and some others, but haven't seen any support apart from "inline SQL" for this kind of statement. Neither have I seen any support for EXECUTE ..., not even in DBIx::Class, which I frankly can't really believe, probably I've been looking in wrong places.

I actually liked Fey::SQL's approach, until I found out it required some sort of scheme:

 $select->select( $user->columns( 'user_id', 'username' ) )
     ->from( $user, $group )
     ->where( $group->group_id, 'IN', 1, 2, 3 )
     ->and  ( $func, 'LIKE', 'smith%' );

What would you recommend?

Answer 1

You can try SQL::Abstract. It's nice for simple SQL statements

Answer 2

I think the main problem is I do not understand what function(?) does. Is the ? there a placeholder to be passed to a stored procedure called function? I also do not have access to a database where I can test my understanding of this. However, does the following really not work?

my $sql = SQL::Abstract->new;

my ($st, @values) = $sql->select(
    \'function(?)',
    '*',
    { group_id => { 'IN' => [ 1 .. 3] } },
);

my $sth = $dbh->prepare($st);
$sth->execute('arg1', @values);

In my case, $st contains:

SELECT * FROM function(?) WHERE ( group_id IN ( ?, ?, ? ) )

If that does not work, then how about:

my ($st, @values) = build_select_for_function(
    function => [ qw(arg1) ],
    '*',
    { group_id => { 'IN' => [ 1 .. 3] } },
);

print $st, "\n";


sub build_select_for_function {
    my ($func, $args, @sql_abstract_args) = @_;
    my $func_str = sprintf '%s(%s)', $func, join(',', @$args);

    my $sql = SQL::Abstract->new;
    $sql->select(
        \$func_str,
        @sql_abstract_args,
    );
}

Output:

SELECT * FROM function(arg1) WHERE ( group_id IN ( ?, ?, ? ) )