I have two answers, one roughly sticks to your framework and the other changes the structure of the code but is how I would divide the problem.

The first version changes your execsql code to take a list of bind params too.

my $sql = 'select comp_name from company where comp_name = ?';

my $query = execsql($sql, $hash{comp_id});

sub execsql {
    my ($sql, @bind_params) = @_;

    my $sth = $dbh->prepare($sql) || error('prep_sql', $0);
    $sth->execute(@bind_params)   || error('sql', $0);
    $dbh->commit;
    return $sth;
}

[download]

The advantages are that it now handles escaping special SQL characters in your variables and putting the right delimiters around strings. The disadvantages are that you are doing more copying but you can get around that if you want by passing references.

What I would do if I were coding this from scratch and had free reign is to separate the DB access from the main application logic so that all SQL queries are hidden in modules (or at least subroutines) in case I ever needed to port to a different DB and the syntax needs to change. So:

my $dbh = get_db_handle();
my $company_name = get_company_name(company_id => $comp_id,
                                    dbh        => $dbh,
                                );
$dbh->disconnect;


sub get_db_handle {
    # Do whatever you need to get the DB handle and set
    # your favorite options
    my $dbh = DBI->connect(...);
    error('DB connect') unless defined $dbh;
}

sub get_company_name {
    my %args = @_;
    my $dbh = $args{dbh};

    my $statement = 'select comp_name from company where comp_name = ?
+';

    my $sth = $dbh->prepare($sql) || error('prep_sql', $0);
    $sth->execute(@bind_params)   || error('sql', $0);
    my ($company_name) = $sth->fetchrow_array;
    $sth->finish;

    return $company_name;
}

[download]

You could certainly use the execsql code in each access subroutine. I prefer not to commit the DB handle inside the accessor functions so that I can call multiple accessors with the same handle and commit all of them as one transaction. In this case it does not matter since I am simply doing a select.

Hope that helped.

-ben

In reply to Re: DBI Placeholders by knobunc
in thread DBI Placeholders by qball

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.