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=multiaccount&ocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_4
