Re: Issue with prepared statement

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,

I tried you way and it didn't make any difference. Actually I found
this in the DBI documentation:

"When using placeholders with the SQL LIKE qualifier, you must
remember that the placeholder substitutes for the whole string. So you
should use "... LIKE ? ..." and include any wildcard characters in the
value that you bind to the placeholder."

http://search.cpan.org/~timb/DBI-1.609/DBI.pm#Placeholders_and_Bind_Values

So I think the LIKE operator is not the root of my issue unless there
is specific to the MySQL drivers for DBI. I tried hard coding a value
instead of a ? in the SELECT clause and it didn't solve the issue
either. So that's not it. Then I tried to hard code the complete
query, so I used a prepared statement without any binded variables. It
still doesn't work. Yet I take the same hard coded query and run in
the MySQL shell and I works. So I guess nested SELECT just don't work
in prepared statement?

If anyone could confirm that, it would remove my worry that I am
simply missing something stupid here.

Thanks,

Tom