Re: Issue with prepared statement

On Jun 6, 8:46 pm, [email protected] (listmail) wrote:
> 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.

I certainly don't mind telling you my mistake but it was not DBI or
SQL related. Let me add a little context to the code I posted
previously:

my $query = 'INSERT IGNORE INTO foo (group, value) (SELECT ?, value
FROM bar WHERE value LIKE ?);';
my $sth = $dbh->prepare($query);

sub function($$) {
   my $value = shift();
   my $group = shift();
   if ($value !~ m/(\w*)/) {
      my $pattern = '%' . $value . '%';
      $sth->execute($group, $pattern) or die($sth->errstr);
   }
}

I don't want to run the query if the value starts and ends by
parenthesis but I forgot the I needed to escape them in my regular
expression or else they are considered grouping operators :(

Thanks again,

Tom