Re: Issue with prepared statement

On Jun 5, 2:57 pm, [email protected] (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 <[email protected]>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