Re: Issue with prepared statement

Lethal Possum wrote:
> Hello,
>
> I am trying to create a prepared statement for the following query in
> MySQL:
>
> INSERT IGNORE INTO foo (group, value) (SELECT 101, value FROM bar
> WHERE value LIKE '%something%');
>
> So far my Perl code looks something like this:
>
> my $group = 101;
> my $pattern = '%something%';
> my $query = 'INSERT IGNORE INTO foo (group, value) (SELECT ?, value
> FROM bar WHERE value LIKE ?);';
> my $sth = $dbh->prepare($query);
> $sth->execute($id, $pattern) or die($sth->errstr);
>
> It does not insert anything into the table foo even though there are
> values in table bar that match the pattern. So my question is: what am
> I doing wrong. I have other, simpler prepared queries in my program
> that work just fine but this one does not do anything and does not
> trigger any error as far as I can see.
>
> Are ? parameters allowed in a nested SELECT?
> Are they allowed not in the WHERE clause?
> Are they allowed with the LIKE operator?
>
> Thanks in advance for your help.
>
> Tom
>
>   
I haven't ever tried binding column names so I am not sure about that 
piece.  I don't have access to a db at the moment but for the LIKE 
portion maybe try something such as this output indicates.  I believe it 
is what I've done in the past.  It implies excluding % in $pattern and 
moving it to the select statement.

perl -e "print q{INSERT IGNORE INTO foo (group, value) (SELECT ?, value 
FROM bar WHERE value LIKE '%'||?||'%'};"