Re: Issue with prepared statement

[Lethal Possum]

On Jun 6, 8:46 pm, listm...@triad.rr.com (listmail) wrote:
 Lethal Possum wrote:
  On Jun 5, 6:52 pm, listm...@triad.rr.com (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

Re: Issue with prepared statement

[Lethal Possum]

On Jun 5, 6:52 pm, listm...@triad.rr.com (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

Re: Issue with prepared statement

[Lethal Possum]

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.comwrote:

  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

Re: Issue with prepared statement

[Lethal Possum]

On Jun 5, 6:52 pm, listm...@triad.rr.com (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

Re: Issue with prepared statement

[listmail]

Lethal Possum wrote:

On Jun 5, 6:52 pm, listm...@triad.rr.com (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.

RE: Issue with prepared statement

[Martin Gainty]

2 bound variables require 2 bound values

my $sth= $dbh-prepare(INSERT INTO users VALUES (?, ?));
my $random_chars = join 'a','b';  //supply AT LEAST 2 values
my $rows = $sth-execute($i, $random_chars);

Martin Gainty 
__ 
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
 
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger 
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung 
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem 
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. 
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung 
fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le 
destinataire prévu, nous te demandons avec bonté que pour satisfaire informez 
l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est 
interdite. Ce message sert à l'information seulement et n'aura pas n'importe 
quel effet légalement obligatoire. Étant donné que les email peuvent facilement 
être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité 
pour le contenu fourni.




 To: dbi-users@perl.org
 From: lethal.pos...@gmail.com
 Subject: Re: Issue with prepared statement
 Date: Sat, 5 Jun 2010 11:32:55 -0700
 
 On Jun 5, 6:52 pm, listm...@triad.rr.com (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
 
  
_
The New Busy is not the too busy. Combine all your e-mail accounts with Hotmail.
http://www.windowslive.com/campaign/thenewbusy?tile=multiaccountocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4

Re: Issue with prepared statement

[Ashish Mukherjee]

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.comwrote:

 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

Re: Issue with prepared statement

[listmail]

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 '%'||?||'%'};