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 {