Skip to content

Instantly share code, notes, and snippets.

@MattOates
Created August 28, 2014 15:39
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save MattOates/32379f4c37edbb1c054f to your computer and use it in GitHub Desktop.
Save MattOates/32379f4c37edbb1c054f to your computer and use it in GitHub Desktop.
SQL Named Parameter Replacement
#!/usr/bin/env perl
use warnings;
use strict;
use Data::Dumper;
sub sql {
my ($sql,%data) = @_;
my @parameters;
foreach my $placeholder ($sql =~ /:([a-zA-Z_]+)/g) {
if (ref $data{$placeholder} eq 'ARRAY') {
push @parameters, map {/\d+/ ? int($_) : "'$_'"} @{$data{$placeholder}};
my $expanded_placeholder = join ",", ('?') x scalar @{$data{$placeholder}};
$sql =~ s/:$placeholder/$expanded_placeholder/;
}
else {
push @parameters, $data{$placeholder};
$sql =~ s/:$placeholder/\?/;
}
}
return ($sql,\@parameters);
}
print Dumper sql("SELECT * FROM genome JOIN genome AS other_genomes USING (taxon_id) WHERE genome.genome = :genome AND other_genomes.genome != :genome AND genome.taxonomy LIKE :taxon AND other_genomes.taxonomy LIKE :taxon",genome=>'hs',taxon=>'%Homo%');
print Dumper sql("SELECT * FROM genome JOIN genome AS other_genomes USING (taxon_id) WHERE genome.genome IN (:genome) AND other_genomes.genome NOT IN (:genome) AND genome.taxonomy LIKE :taxon AND other_genomes.taxonomy LIKE :taxon",genome=>['hs','at'],taxon=>'%Homo%');
print Dumper sql("SELECT * FROM protein WHERE protein IN (:protein)",protein=>[256,'123']);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment