Re: Issue with prepared statement

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.