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?

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]

In reply to The name for another SQL text with bound values module by roman

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.