Lethal Possum wrote:
On Jun 5, 6:52 pm, [email protected] (listmail) wrote:
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 '%'||?||'%'};"

Hi again,

OK this is embarrassing: I found the bug that prevented my query to be
executed at all. I am very sorry for waisting your time. If it is any
consolation, I learn quite a bit from reading the DBI doc over and
over again.

Thanks you very much for your help and enjoy the rest of your week-
end,

Tom

I wouldn't worry to much about any embarrassment :) Besides your excerpt from the docs show I that I probably didn't need to work around LIKE the way that I did. I'll be looking into my own code next week to see what was going on there as a result of this discussion. Anyway I'm curious about the bug or problem that you found if you want to expose it. If not, no worries.


Reply via email to