Varying numbers of DBI placeholders

MrCromeDome has asked for the wisdom of the Perl Monks concerning the following question:

I have a query that I wrote some time ago looking something like this:

$script = 'SELECT foo FROM bar WHERE item1 = ' . $item1;
if(defined($item2)) { $script .= ' and item2 = ' . $item2; }
if(defined($item3)) { $script .= ' and item3 = ' . $item3; }
[download]

Having some time lately to go revisit this (and being smart enough these days to want to do better), I would like to something more like this:

$script = 'SELECT foo FROM bar WHERE item1 = ?';
if(defined($item2)) { $script .= ' and item2 = ?';
if(defined($item3)) { $script .= ' and item2 = ?';
[download]

so that I could do something like this:

$sql->execute($item1,$item2,$item3);
[download]

Does DBI support this kind of behavior? Can I pass additional items to execute() even if there are no placeholders that correspond with them? How will DBI react if those items are undefined? SuperSearch yielded nothing, and I couldn't find what I was looking for in the DBI reference. While the DBI docs that you can't pass an undef value to a SELECT statement via a placeholder and expect to get the results you are looking for (that just strikes me as poor programming and a bad idea anyhow), that's not exactly the same as what I'm trying to accomplish.

Any insight is truly appreciated.

Thanks in advance,
MrCromeDome

Comment on Varying numbers of DBI placeholders Select or Download Code

Replies are listed 'Best First'.
Re: Varying numbers of DBI placeholders by dws (Chancellor) on Apr 18, 2002 at 18:26 UTC
so that I could do something like this: `$sql->execute($item1,$item2,$item3);` You have to pass exactly as many arguments to `execute()` as you have placeholder. Fortunately, `$sql->execute(@items);` will do the trick, assuming you've populated `@items` with the right number of values.	[reply] [d/l] [select]
Re: Re: Varying numbers of DBI placeholders by MrCromeDome (Deacon) on Apr 18, 2002 at 18:43 UTC
You're a saint, dws, and in more than the typical PerlMonks fashion ;) Thanks for pointing out that I was making this harder than I needed to. ++ to you. Again, thanks! MrCromeDome	[reply]
Re: Varying numbers of DBI placeholders by ehdonhon (Curate) on Apr 18, 2002 at 22:18 UTC
As has already been pointed out, you can not do that. But in the code you provided, you would not want to. Consider the case when $item2 is undef and $item3 is defined, you'd be selecting from foo where item3 = ''. Not what you want to do. `my (@where, @params ); push ( @where, 'item1 = ?' ); push ( @params, $item1 ); if ( defined( $item2 ) ) { push( @where, 'item2 = ?' ); push( @params, $item2 ); } if ( defined( $item3 ) ) { push( @where, 'item3 = ?' ); push( @params, $item3 ); } my $script = sprintf("SELECT foo FROM bar WHERE %s", join ( ' AND ', @WHERE ) ); ... $sql->execute( @params );` [download] This code is a bit more verbose than necessary. It could be cleaned up by using a configuration hash and looping through each of the items you are searching on rather than doing them linearly, but I think its good enough to demonstrate one way of addressing the issue.	[reply] [d/l]