HostExpress Blogs Tech News & Babble

If you've ever written a stack of SQL inserts using Perl and wondered if there was a better way to do it, and that gets around these issues:

1. Doesn't need a multi-line string with quoted variables,
2. Isn't a page long handling the query with transactions, and
3. Checks that the Perl variable contains data that matches the database column's field type,

then this piece of code will help:

use strict;
use DBI qw(:sql_types);

my $dbname = "christest";

my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "",
                       { RaiseError => 1, AutoCommit => 0 });

# using the DBI statement handler's bind_param() feature, bind the
# given SQL parameters to the prepared query.
#
# To be eval'ed from sql_insert()
#
# Args:
#       - reference to statement handler (i.e. \$sth)
#       - remaining args are arrary references representing
#         value and type pairs - e.g. ["fred", SQL_VARCHAR]
#
# Returns:
#       nothing
#
sub bind_query {
    my $sth_ref = shift;
    my $sth = $$sth_ref;
    my $field_num = 0;

    foreach my $bind_pair (@_) {
        $field_num++;
        my($val, $type) = @$bind_pair;
        $sth->bind_param($field_num, $val, $type);
    }
}

# insert a row in the tabe as given in the SQL base query argument,
# with the data pairs supplied as array references in the rest of
# the arguments.  The insert is performed as a transaction with
# commit or rollback.
#
# Args:
#       - SQL base query, without the VALUEs.  e.g.:
#                   "INSERT INTO sqlbuildertest"
#       - remaining args are arrary references representing
#         value and type pairs - e.g. ["fred", SQL_VARCHAR]
#
# Uses:
#       - global $dbh database handle
#
# Returns:
#       0 on success, 1 on failure
#
sub sql_insert {
    my $base_query = shift;
    my @insert_params = @_;
    my $nparams = $#insert_params + 1;
    return 1 if ($nparams < 1);  # must be at least 1 column value

    $sql = $base_query . " VALUES (";
    $sql .= "?," x ($nparams - 1);
    $sql .= "?)";

    my $sth = $dbh->prepare($sql);

    eval {
        &bind_query(\$sth, @insert_params);

        $sth->execute();
        $dbh->commit();
    };

    if ($@) {
        warn "Database error: $DBI::errstr\n";
        $dbh->rollback();
        return 1;
    }
    $sth->finish();
    return 0;
}

and now we have a simple way to do an SQL insert, with transactions, and variable checking:

&sql_insert("INSERT INTO sqlbuildertest",
            ["barney", SQL_VARCHAR],
            [66, SQL_INTEGER]);

As with all good things in Perl land, writing it was hard, and understanding it afterwards is impossible :-)