Re: SQL prepared statements using MySQL In ()

"What is the useful wisdom around this?"

TMTOWTDI. Here's how I might tackle this.

Make use of the strict and warnings pragmata.
Use lexical (e.g. my) variables instead of package variables.
Present "valuesForIn" as an array.
Interpolate the "IN (...)" code directly into $query. See "perlsecret: Baby cart" if you're unfamiliar with the construct that I've used.
Fix "die->$errstr()", which should be "die $dbh->$errstr()", if you enjoy typing and want to repeatedly code that same exception message. Alternatively, and my preference, employ "the first great virtue of a programmer", namely laziness, and take advantage of DBI's RaiseError attribute.

In the code example below, I've used all of these features. The two print statements show how the variables are interpolated into the prepare() and execute() methods (note that @valuesForIn expands to a space-separated list in the double-quoted string).

$ perl -e '
    use strict;
    use warnings;

    # Somewhere earlier in your code, something like this:
    # my $dbh = DBI->connect(
    #   $data_source, $username, $auth,
    #   {RaiseError => 1, ...}
    # );

    my @valuesForIn = 1..5;

    my $query = qq{
        SELECT field1
        FROM table1
        WHERE field2 IN (@{[join ",", qw{?} x @valuesForIn]})
    };

    print "my \$sth1 = \$dbh->prepare($query);\n";
    print "\$sth1->execute(@valuesForIn);\n";
'
my $sth1 = $dbh->prepare(
        SELECT field1
        FROM table1
        WHERE field2 IN (?,?,?,?,?)
    );
$sth1->execute(1 2 3 4 5);
[download]

— Ken

Comment on Re: SQL prepared statements using MySQL In () Select or Download Code

Replies are listed 'Best First'.
Re^2: SQL prepared statements using MySQL In () by djlerman (Beadle) on Aug 11, 2023 at 16:53 UTC
Thank You. This is similar to how I solved it. `# Set up variable for the Parameters for the entire query my @sqlParameters = (); # set up variable for just the IN() statement my @valuesForIn = (1,2,3,4,5); # a value for another field my $field3 = 'abc123'; my $query = " SELECT field1 FROM table1 WHERE field2 IN (@{[join',', ('?') x @valuesForIn]}) AND field3 = ? "; push(@sqlParameters, @valuesForIn); push(@sqlParameters, $field3); my $sth = $dbh->prepare($query) or die $dbh->errstr; $sth->execute(@sqlParameters) or die $dbh->errstr;` [download] Note: I kept separate @sqlParameters variable J.I.C. the query needs to be extended with more parameters.	[reply] [d/l]

Replies are listed 'Best First'.

Re^2: SQL prepared statements using MySQL In ()
by djlerman (Beadle) on Aug 11, 2023 at 16:53 UTC

# Set up variable for the Parameters for the entire query
my @sqlParameters = ();

# set up variable for just the IN() statement
my @valuesForIn = (1,2,3,4,5);

# a value for another field
my $field3 = 'abc123';

my $query = "   SELECT field1
                FROM table1
                WHERE field2 IN (@{[join',', ('?') x @valuesForIn]}) 
                    AND field3 = ? ";

push(@sqlParameters, @valuesForIn);
push(@sqlParameters, $field3);

my $sth = $dbh->prepare($query) or die $dbh->errstr;

$sth->execute(@sqlParameters) or die $dbh->errstr;
[download]

[reply]
[d/l]