Beefy Boxes and Bandwidth Generously Provided by pair Networks
We don't bite newbies here... much
 
PerlMonks  

comment on

( #3333=superdoc: print w/replies, xml ) Need Help??

I think you'll find that, when working with complicated databases, you'll often wind up with large statements like that. One (bad) way to simplify:

sub add_customer{ my $sth = $dbh->prepare('INSERT INTO customers VALUES (?,?,?,?,?,? +,?,?,?,?,?,?)'); die "Couldn't add customer" unless defined $sth; $sth->execute( @_ ); }

One thing you could do to simplify your code is to pass all fields as hashrefs with keys being the field names and values being data. Then you could do this (untested):

sub add_customer { # $data_hash is a hashref representing the fields # in the customers table that will be filled. my ( $data_hash ) = @_; my ( $fields, $values, $place_holders ) = format_insert_data( $dat +a_hash ); my $sth = $dbh->prepare( "INSERT into customers ( $fields ) VALUES + ( $place_holders )" ); $sth->execute( @$values ); } sub format_insert_data { # Takes a hash ref with keys being field names and # values being the values to insert into the database and # returns a list of fields, values, and placeholders suitable for +an SQL INSERT statement: # INSERT into table ( $fields ) VALUES ( $place_holders ) my ( $hash_ref ) = @_; if ( ref $hash_ref ne 'HASH' ) { croak "Argument to format_insert_data must be a hash reference +"; } my @fields = keys %$hash_ref; my $fields = join ',', @fields; my @values = @$hash_ref{ @fields }; my $place_holders = '?,' x scalar @fields; chop $place_holders; # remove trailing comma return ( $fields, \@values, $place_holders ); }

The problem with that, of course, is that it doesn't allow for data validation. However, the way to handle validation depends greatly upon your database. However, then all of your add_foo methods can be a wrapper around a generic add subroutine with the argument being the table and the data hash.

Cheers,
Ovid

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.


In reply to (Ovid) Re: Looking for a simpler DBI solution. by Ovid
in thread Looking for a simpler DBI solution. by Zecho

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, 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, 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.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others lurking in the Monastery: (4)
    As of 2021-05-10 08:39 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?
      Perl 7 will be out ...





      Results (105 votes). Check out past polls.

      Notices?