On Jun 5, 6:52 pm, listm...@triad.rr.com (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