DBI bind error

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

Monks, I am getting an odd error when attempting to bind on an in statement in mysql.

The table looks like:
value: 1, id: 1
value: 2, id: 2

This would only print 1:

        my $id_string = '1,2';
        my $value_lookup = $dbh->prepare("
            SELECT
             value
            WHERE
             id IN (?)");

        #EXECUTE SQL QUERY
        $value_lookup->bind_param(1, $id_string);
        $value_lookup->execute() or debug ($DBI::errstr);

        while(my($value) = $value_lookup->fetchrow_array()) {
            print "$value\n";
        }
[download]

This would print 1,2:

        my $value_lookup = $dbh->prepare("
            SELECT
             value
            WHERE
             id IN (1,2)");

        #EXECUTE SQL QUERY
        $value_lookup->execute() or debug ($DBI::errstr);

        while(my($value) = $value_lookup->fetchrow_array()) {
            print "$value\n";
        }
[download]

What would be the proper way to bind a variable on an in string?

Comment on DBI bind error Select or Download Code

Replies are listed 'Best First'.
Re: DBI bind error by JavaFan (Canon) on Jan 18, 2009 at 02:26 UTC
Binding isn't the problem. Placeholders hold a single value. So if you try to put "1,2" there, the result is: `SELECT value WHERE id IN ('1,2')` [download] which is not what you want. Try this: `SELECT value WHERE id IN (?, ?)` [download] and use two values. Oh, and you probably want to supply a table to select from as well.	[reply] [d/l] [select]
Re^2: DBI bind error by Nar (Novice) on Jan 18, 2009 at 02:36 UTC
I see what your saying. THat just brings up a host of new problems though. The input on id is variable and can be changed. Is there a good fix to get around this, or should i just split off the commas and count values then add the ? as a string?	[reply]
Re^3: DBI bind error by ikegami (Patriarch) on Jan 18, 2009 at 02:46 UTC
`my @ids = ( 1, 2 ); my $value_lookup_sth = $dbh->prepare(" SELECT value WHERE id IN (" . join(',', ('?') x @ids ) . ") "); $value_lookup_sth->execute(@ids) or debug($DBI::errstr);` [download] or `sub plist { '(' . join( ',', ('?') x $_[0] ) . ')' } my @ids = ( 1, 2 ); my $value_lookup_sth = $dbh->prepare(" SELECT value WHERE id IN ".plist(0+@ids)." "); $value_lookup_sth->execute(@ids) or debug($DBI::errstr);` [download] or if you're particularly silly `{ package My::plist; use Tie::Array; our @ISA = 'Tie::Array'; sub TIEARRAY { bless [], shift } sub FETCH { '(' . join( ',', ('?') x $_[1] ) . ')' } } tie my @plist, 'My::plist'; my @ids = ( 1, 2 ); my $value_lookup_sth = $dbh->prepare(" SELECT value WHERE id IN $plist[@ids] "); $value_lookup_sth->execute(@ids) or debug($DBI::errstr);` [download]	[reply] [d/l] [select]