On Jun 5, 2:57 pm, ashish.mukher...@gmail.com (Ashish Mukherjee) wrote: > Hello, > > Generally, there is no gain to using bind parameters if you don't know the > columns to be projected in advance in SELECT and your LIKE regex is also > dynamic. It can't prepare a query execution plan and cache it for later use, > since the basic constructs of your query keep changing. I am not even sure > you can use a placeholder for a column to be SELECT'ed. > > - Ashish > > On Sat, Jun 5, 2010 at 2:33 AM, Lethal Possum <lethal.pos...@gmail.com>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
Hi Ashish, I don't actually need to change the selected column, that will be a constant like 101 in my example. That's just the value I want to insert in the group column of the foo table. By the way I tried once to include the constant in the prepared statement like this but it didn't work either: my $query = "INSERT IGNORE INTO foo (group, value) (SELECT $group, value FROM bar WHERE value LIKE ?);"; Thanks for your help, Tom