How can I use placeholders for SQL variable functions with Perl DBI?

Question

I don't know if the word "variadic" is the right word, but I'm talking about things that can take a list of values, like IN() . If you have been working with DBI for a long time, you probably tried to do this:

(Note: all examples are extremely simplified for brevity)

 my $vals = join ', ', @numbers; my $sth = $dbh->prepare( "SELECT * FROM mytbl WHERE foo IN( ? )" ); $sth->execute( $vals ); # doesn't work 

DBI deputies simply do not support these types of frauds, is this the only value for everyone ? or nothing as far as I know.

This leads me to doing something like:

 my $sth = $dbh->prepare( "SELECT * FROM mytbl WHERE foo IN ( $vals )" ); 

which is not so terrible, but consider a function, as I wrote today, which should accept some arbitrary SQL with an IN clause and a list of values

 sub example { my $self = shift; my ( $sql, @args ) = @_; my $vals = join ', ', @args; $sql =~ s/XXX/$vals/; <---- # AARRRGHGH my $sth = $self->dbh->prepare( $sql ); ... } 

The result is a callable material that looks like

 my $sql = "SELECT * FROM mytbl WHERE foo IN( XXX ) AND bar = 42 ORDER BY baz"; my $result = $self->example( $sql, @quux ); 

It really offends my sense of aesthetics. Building custom SQL programmatically is a big pain, as it is; I do not want to go the way of regular expression of SQL strings if I do not need it.

Is there a better way?

Answer 1

sprintf convenient in such situations:

 my $sth = $dbh->prepare( sprintf( 'SELECT * FROM mytbl WHERE foo IN( %s )', join(',', ('?') x @numbers) ) ); 

Answer 2

Food for thought.

DBIx :: Simple offers this type of syntax using a double-label tag label:

 $db->query( 'SELECT * FROM mytbl WHERE foo IN ( ?? )', @args ); 

In addition, SQL :: Annotation is powerful, but sometimes I find that abstractions do not lead to optimal SQL.

Answer 3

Why not:

  my $sql = "SELECT * FROM mytbl WHERE foo IN(" . join(',', ('?') x@quux ) . ") AND bar = 42 ORDER BY baz"; my $sth = $dbh->prepare($sql); $sth->execute(@quux); 

Answer 4

If you don't mind hacking a clean DBI and using some modules, I would look at SQL :: Abstract for your example. SQL :: Abstract can take a Perl hash and turn it into where .

 my $sql = SQL::Abstract->new; my @numbers = (1 .. 10); my ($stmt, @bind) = $sql->where({foo => {'in', \@numbers}}); # $stmt is " WHERE ( foo IN ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) )" # @bind contains the values 1 through 10. 

Answer 5

If using placeholders and binding values ​​becomes clumsy, always DBI::quote() .

 my $sql = sprintf 'SELECT * FROM mytabl WHERE foo IN ( %s )', join( ',', map { $dbh->quote( $_ ) } @args );