Perl SQL Statement Creators

Sometimes it's nice to programatically create SQL statements, with quoting issues etc taken care of for you. Simple queries like INSERT and UPDATEs are easy to do in this way.

There's a plethora of related modules on CPAN already, but many of them are, in my opinion, a little over-engineered and unwieldy.

The two functions here are simple but effective for most simple SQL creation tasks. They don't try to do everything.

If I have time, I might one day make these into a nice module to release to CPAN, to make this code easy to share and re-use.

Firstly, the two functions:

create_insert()

# given a DBI object (to do quoting), the table name and a hashref of 
# field => value pairs, creates an SQL insert statement
sub create_insert {
 
    my ($dbh, $table, $data) = @_;
 
    if (!$table || ref $data ne 'HASH') {
        warn "create_insert() called wrongly";
        return;
    }
 
    my $sql = sprintf 'INSERT INTO %s (%s) VALUES (%s);',
        $table,
        join(', ', map { $dbh->quote_identifier($_) } keys   %$data),
        join(',' , map { $dbh->quote($_)            } values %$data)
    ;
 
    return $sql . "\n";
} # end of function create_insert

create_update()

The create_update() function is a little more complex, as the requirement for flexible “where” clauses to limit the effect of the update makes things a little less simple.

I hope my approach of using basic hashrefs and/or arrayrefs is simple and usable. Unfortunately, it is fairly limited, it can't do ORs yet, only AND, but it does the job for me, so far at least. Perhaps in future I'll expand the syntax slightly.

# given a table name, a "where" clause and a hashref of field => value, 
# creates an SQL update statement.  The "where" clause is a hashref too,
# of { field => value } or { field => ['!=', value ] }.
#
# examples:
# { field => 'value', field2 => 'value2' }
# produces:  WHERE field1 = 'value' AND field2 = 'value2'
# { sex => 'female', age => ['>', 18 ] }
# produces: WHERE sex = 'female' AND age > 18
sub create_update {
 
    my ($dbh, $table, $where, $data) = @_;
 
    if (!$table || ref $data ne 'HASH') {
        warn "create_update() called wrongly";
        return;
    }
 
    my $sql = "UPDATE " . $dbh->quote_identifier($table) . " SET ";
    $sql .= join ', ', 
        map { $dbh->quote_identifier($_) . ' = ' . $dbh->quote($data->{$_}) } 
        keys %$data;
 
    # now generate the where clause... this gets a little ugly
    for my $field (keys %$where) {
        # $where->{$field} will be either just a value, or an arrayref of
        # operator and value.  If it's not an array ref, make it one:
        if (!ref $where->{$field}) {
            $where->{$field} = ['=', $where->{$field} ];
        }
    }
 
    $sql .= ' WHERE ' . join ' AND ', map {
        join ' ', 
            $dbh->quote_identifier($_), 
            $where->{$_}->[0], 
            $dbh->quote($where->{$_}->[1]) 
    } keys %$where;
 
    return $sql . "\n";
 
} # end of sub _create_update

A couple of tests for the above functions to see it in action:

# some data we'll use to test both the INSERT and UPDATE:
my $data = {
    field1 => 'value 1',
    field2 => 'value "two"',
    'field three' => 'value \'three\'',
};
 
# a WHERE clause for the UPDATE:
my $where = {
    sex => 'female',
    age => ['>', 18 ],
};
 
# we need DBI, to pass $dbh to these functions so that they can use
# $dbh->quote for quoting (and, perhaps in future, also to determine
# the DBMS in use and tweak the SQL to suit)
use DBI;
my $dbh = DBI->connect('DBI:mysql::localhost', $user, $pass,
                      { RaiseError => 0, AutoCommit => 1 });
 
 
print create_insert($dbh, 'mytable', $data);
print create_update($dbh, 'mytable', $where, $data);

The above tests give the following results: (line breaks introduced for readability):

INSERT INTO mytable (`field three`, `field1`, `field2`) 
    VALUES ('value \'three\'','value 1','value \"two\"');
 
UPDATE `mytable` SET 
    `field three` = 'value \'three\'', 
    `field1` = 'value 1', 
    `field2` = 'value \"two\"' 
WHERE `age` > '18' AND `sex` = 'female';
 
perl/sqlstatementcreators.txt · Last modified: 2010/02/26 10:45 (external edit)
 
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki