Dear monks,

reinventing the wheel, I wrote another module trying to keep together arbitrary chunks of SQL text with bound parameter values. Or - in other words - trying to bind params in statement before the statement is passed to database. (I know there are other modules doing similar thing in different manner, as are SQL::Interp and SQL::String). I want to upload my module to CPAN and looking for an appropriate name:

According to synopsis below What is the most apt replacement of original - my company's proprietary - name Akar::DBI::Statement?

• SQL::Prebound?
• SQL::Arbitrary?
• SQL::Chunk?
• SQL::Piece?
• something in DBIx:: namespace?
• something completely different?

I used DBIx::AbstractStatement 2 years ago, but the concept changed and I don't like it anymore.

Please tell me also, if there is a more appropriate place to ask such questions.

Thanks

Extract from Synopsis

use Akar::DBI::Statement qw(sql sql_in sql_param sql_param_inout);

my $start_date = '2007-06-01';
my $statement = "SELECT id, filetype, filesource_name"
    . "\nFROM manggis.files "
    . "\nWHERE filetype IN ". sql_in( qw(durian durianloader_in durian
+loader_out) )
    . "\n  AND state IN ". sql_in('f', 'F')
    . "\n  AND created > to_date(". sql_param($start_date ). ", 'YYYY-
+MM-DD')";

my ( $text, $values_ref ) = $statement->text_and_values;
[download]

Yields:

# text:
    'SELECT id, filetype, filesource_name
    FROM manggis.files
    WHERE filetype IN (?, ?, ?)
        AND state IN (?, ?)
        AND created > to_date(?, \'YYYY-MM-DD\')'
# values_ref is 
    [ 'durian', 'durianloader_in', 'durianloader_out', 'f', 'F', '2007
+-06-01' ]
[download]

The statement may be further interpolated with predictable results

my $cnt_statement = "SELECT count(*) FROM ($statement)";
[download]

with Interpolation the code is cleaner

use Interpolation
    'sqlp' => sub { sql_param(@_) },
    'E'    => 'eval';

$statement = <<"END_SELECT";
    SELECT id, filetype, filesource_name
    FROM manggis.files 
    WHERE filetype IN $E{ sql_in( qw(durian durianloader_in durianload
+er_out) ) }
        AND state IN  $E{ sql_in('f', 'F') }
        AND created > to_date( $sqlp{ $start_date } , 'YYYY-MM-DD');
END_SELECT
[download]

You can bind inout parameters as well:

my $retval; 
my $statement = <<"END_PSQL";
    BEGIN
        manggis.create_new_file(
            'filetype' => $E{ sql_param($filetype) },
            'file_id'  => $E{ sql_param_inout(\$retval, 40) },
        );
    END;
END_PSQL
[download]

The statement may be passed to dbh in different ways

$dbh->selectrow_array($statement->text_attr_values);
$statement->prepare_and_bind_on($dbh);
[download]

Or even (when you modify your dbh class) you don't need to distinguish between text and statement at all

my @row = $dbh->selectrow_array($statement_or_text);
my $sth = $dbh->prepare($statement_or_text);
[download]

You can even bind the params "tradionally".

my $statement2 = sql('SELECT * FROM manggis.files WHERE state = :state
+');
$statement2->bind_param(':state', 'F');
[download]