Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Looking for a simpler DBI solution.

by Zecho (Hermit)
on Nov 01, 2001 at 23:19 UTC ( #122638=perlquestion: print w/replies, xml ) Need Help??

Zecho has asked for the wisdom of the Perl Monks concerning the following question:

Ok, DBI & MySQL are kickin my butt right now, I've read what tutorials and documentation I could find (and those pointed out to me in the CB) and am really looking for a simple solution for this. Someone please tell me there's a cleaner/easier way to simply add a row to the table.

What I've hacked together so far:

## table customers #|Company #|FirstName #|LastName #|PhoneNumber #|Bill2Street #|Bill2City #|Bill2State #|Bill2Zip #|Ship2Street #|Ship2City #|Ship2State #|Ship2Zip ###### my $dbh = DBI->connect('DBI:mysql:advance') or die "Couldn't connect t +o database: " . DBI->errstr; sub add_customer{ my ($company, $firstname, $lastname, $phonenumber, $bill2street, $bill +2city, $bill2state, $bill2zip, $ship2street, $ship2city, $ship2state, + $ship2zip) = @_; my $sth = $dbh->do('INSERT INTO customers VALUES (?,?,?,?,?,?,?,?,?,?, +?,?)'); die "Couldn't add customer" unless defined $sth; $sth->execute($company, $firstname, $lastname, $phonenumber, $bill2str +eet, $bill2city, $bill2state, $bill2zip, $ship2street, $ship2city, $s +hip2state, $ship2zip); }

Replies are listed 'Best First'.
(Ovid) Re: Looking for a simpler DBI solution.
by Ovid (Cardinal) on Nov 01, 2001 at 23:42 UTC

    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.


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

      Especially if you're in a real homogenous environment, with potentially other applications writing to the same database, it can pay to move your validation step into the database. Create constraints and checks that verify the integrity and correctness of the data at insert-time, and be sure that when you do your inserts and updates you're checking for errors and propogating them out.

      When designing major database-driven applications, it pays to move certain aspects of your functionality into the database itself.

      Plus, like I alluded to above, if you have more than one application writing to the same database, these constraints ensure a common set of validation across all applications.

      Just some thoughts; they may not be applicable to this particular case, though.

      Another advantage of this way might be if you often use default values for some of the columns in the table - this version won't fall over if you only pass it the arguments relevant to the non-default values - i.e. you can give it as many or as few args as you want, so long as you have good default values defined in the db.

      I must say, I wish I could have voted for Ovid's node more than once - it helped me a lot.

      George Sherston
Re: Looking for a simpler DBI solution.
by kwoff (Friar) on Nov 01, 2001 at 23:32 UTC
    $dbh->do() does the execute() for you already. That's the point of using do() :). You could do this:
    $dbh->do('INSERT INTO ... (?,....?)', undef, @_);
    and skip the execute().
      Also, if you're starting out doing web application development, read "MySQL and Perl for the Web". That site contains a slick that does some input validation based on the MySQL table "metadata". (read the book for explanation how to use it :) Actually he (Paul DuBois) has a really excellent book called "MySQL" (I used it to figure out how to do MySQL stuff from the Apache C API, which made me feel special, hehe).
        Kwoff, if you find some spare time I would appreciate if you could write a review for the Book Reviews section.

Re: Looking for a simpler DBI solution.
by lo_tech (Scribe) on Nov 01, 2001 at 23:27 UTC

    Well, you havent told me what the problem is, but I'll try to help with what I can.

    sub add_customer{ my ($company,$fname,$lname,$phn,$b2stre,$b2city,$b2stat,$b2zip,$s2st +re,$s2city,$s2stat,$s2zip) = @_; my ($handl) = DBI->connect('DBI:mysql:advanc'); or die "Couldn't con +nect to database: " . DBI->errstr; my $sql = "INSERT INTO customers (Company,FirstName,LastName,PhoneNumber,Bill2Street,Bill2City,Bill2S +tate,Bill2Zip,Ship2Street,Ship2City,Ship2State,Ship2Zip) VALUES (?,?,?,?,?,?,?,?,?,?,?,?) "; my $sth = $handl->prepare($sql) or die "Couldn't prep statement"; $sth->execute($company,$fname,$lname,$phn,$b2stre,$b2city,$b2stat,$b +2zip,$s2stre,$s2city,$s2stat,$s2zip) or die "Couldn't add customer"; }


    • named the insert field names. Dont know if this matters to mysql, but Informix is picky that way
    • added "or die" to $sth to catch those before the execute()
    • shortened the varnames. not a nit, im just a lazy typist
Re: Looking for a simpler DBI solution.
by Anonymous Monk on Nov 02, 2001 at 20:50 UTC

    I am building a small web based application and have abstracted my database calls (updates, queries and deletes) into a single sub that I then call with up to 2 parameters. The first parameter is the query itself. The second is any data to be inserted. The sub will return the results (if any). This has really helped me to simplify the database calls within my application.

    sub db_do { my (%attr) = ( RaiseError => '1'); my $dbh = DBI->connect('DBI:mysql:DATABASE:localhost', 'userna +me', 'password', \%attr); my ($dbq) = shift; my @args = @_; my $sth = $dbh->prepare("$dbq"); $sth->execute(@args); my $result; my @results; if ($dbq =~ /select/i) { my @row; while (@row = $sth->fetchrow_array) { push @results, [ @row ]; } $result = \@results; } $sth->finish; $dbh->disconnect; return $result; } Note that this doesn't handle errors as it should yet, but is function +al. An example of how I use it would be: my $dbq = qq{ INSERT INTO pagers (ptype,pnum,ppin,enum) VALUES (?,?,?, +?) }; my $res = db_do("$dbq", "$ptype", "$pnum", "$ppin", "$enum"); or for a simple select: my $dbq = qq{ SELECT email from email where email = ? }; my $res = db_do("$dbq", "$email");

    Without seeing your data, it's difficult to say, but your problem might be that your data needs to be quoted properly to accomplish the insert. The $sth = $dbh->prepare($dbq) statment above will accomplish this for you.

    Hope this helps!

    Jerald Jackson

    Edited 2001-11-02 by Ovid

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://122638]
Approved by root
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (4)
As of 2021-05-15 07:53 GMT
Find Nodes?
    Voting Booth?
    Perl 7 will be out ...

    Results (150 votes). Check out past polls.