3
\$\begingroup\$

How can this method that inserts a hashref into a database table be improved? It assumes that the columns of the database table are named identically to the fields in the hashref. The return value is the id field created by the insert.

sub insert(){
    my ($table, $data) = @_;
    my ($fields, $placeholders, $values) = ("", "", []);
    while (my ($field, $value) = each %$data){
        $fields.=", " if ($fields);
        $fields.=$field;
        $placeholders.=", " if ($placeholders);
        $placeholders.="?";
        push(@$values, $value);
    }
    $dbh->prepare("INSERT INTO $table ($fields) VALUES ($placeholders)")->execute(@$values);
    return $dbh->last_insert_id(undef, undef, undef, undef);
}

It assumes that $dbh is a correctly initialized global database handle.

It is used like:

&insert("animals", {
    "type"=>"cat",
    "name"=>"Patches",
    "date-of-birth"=>"November 1, 2015",
    "date-of-death"=>undef
});

Which will insert a record into the animals table:

id | type | name    | date-of-birth    | date-of-death
=====================================================
 1 | cat  | Patches | November 1, 2015 | null
\$\endgroup\$
4
\$\begingroup\$

This might be a good candidate to use SQL::Abstract in, as per DBIx::Class.

sub insert {
    # insert takes ( $table, \%fieldmap )
    my $sql = SQL::Abstract->new();
    my ( $query, @binds ) = $sql->insert( @_ );
    $dbh->prepare($query)->execute(@binds);
    return $dbh->last_insert_id(undef, undef, undef, undef);
}

This gives you a few easy features that may come in handy in the future if you change your database, like:

\$\endgroup\$
  • \$\begingroup\$ Nice find of an already written library that pretty much does exactly what I was trying to do. The security features of guarding against malicious key names is pretty sweet as well. \$\endgroup\$ – Stephen Ostermiller Feb 16 '16 at 13:09
  • \$\begingroup\$ Its pretty handy if most of what you're doing involves single table queries. But if you're wanting Join queries, it may be better to reach for a full DBIx::Class config. \$\endgroup\$ – Kent Fredric Feb 17 '16 at 1:28
1
\$\begingroup\$

Creating an insert() method is a good idea when working with DBI so you can reduce redundant code and standardize (or omit) your error handling.

use arrays

If you use arrays to store your fields and placeholders you can save yourself some trouble. Your declaration can become:

my(@sql_fields,@sql_values);

and the loop is simplified to:

while (my ($field, $value) = each %$data) {
    push(@sql_fields,$field);
    push(@sql_values,$value);
}

and now we can join those back together:

my $sql_fields = join(",", @sql_fields);

and since we have the same number of placeholders as fields we can create that without an extra array:

my $sql_placeholders = join(",", map {"?"} @sql_fields);

which leads to a few tweaks on your prepare:

$dbh->prepare("INSERT INTO $table ($sql_fields) VALUES (@sql_placeholders)")->execute(@sql_values);

error checking

I hope you also take some time to check for potential errors. Maybe the disk is full. Maybe a field gets renamed. Providing useful diagnostics for those sorts of things would be a good idea. Some errors could be handled without hitting the database, like avoiding invalid characters in field names.

\$\endgroup\$
  • \$\begingroup\$ Mapping the fields to question marks is quite clever. \$\endgroup\$ – Stephen Ostermiller Feb 15 '16 at 20:40
  • \$\begingroup\$ As for error handling, DBI seems to throw appropriate errors (for example when this tries to insert an unknown column name). \$\endgroup\$ – Stephen Ostermiller Feb 15 '16 at 20:42
  • 1
    \$\begingroup\$ DBI's messages don't include the SQL statement or values that caused the error. \$\endgroup\$ – chicks Feb 15 '16 at 21:07
1
\$\begingroup\$

You can replace your while loop with more straightforward equivalent,

$fields  = join(", ", keys %$data);
@$values = values %$data;
$placeholders = join(", ", ("?") x @$values);
\$\endgroup\$
  • \$\begingroup\$ Does that guarantee the ordering of the keys and values will be the same? \$\endgroup\$ – Stephen Ostermiller Feb 16 '16 at 9:50
  • \$\begingroup\$ @StephenOstermiller yes, you can rely on same order of keys and values. So long as a given hash is unmodified you may rely on keys, values and each to repeatedly return the same order as each other. \$\endgroup\$ – mpapec Feb 16 '16 at 12:25
  • \$\begingroup\$ Just make sure your code doesn't rely on the same ordering across runs, because the order is random per hash and per-perl-instance. \$\endgroup\$ – Kent Fredric Feb 17 '16 at 1:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.