Re: how to allow variable number of argumets in mysql insert quer y

On Dec 18, 2007 1:06 PM, Selke, Gisbert W. <[email protected]> wrote:
> > -----Ursprüngliche Nachricht-----
> > Von: fungazid [mailto:[email protected]]
> > Gesendet: Dienstag, 18. Dezember 2007 16:57
> > I'm using DBD::mysql, where Insert query looks like:
> >
> > ______________________________________________________________
> > my $str= "?,?,?,,,";
> > $q=$dbh->prepare("INSERT INTO $table VALUES($str)")
> > or die "Couldn't prepare statement: " . $dbh->errstr;
> >
> > $q->execute($args1[0],$args1[1],$args1[2],…)
> > or die "Couldn't execute statement: " . $q->errstr;
> > ________________________________________________________________
> >
> > THE PROBLEM:
> > If I want to change the number of arguments sent to
> > execute(), I must change
> > the source code.
> > I would like to be able to change it on run-time with something like:
> _____________________________________________________________
> > $q->execute(f(@args1))
> > or die "Couldn't execute statement: " . $q->errstr;
> > _____________________________________________________________
> >
> > where f() formats the input to the function, and @args1 is
> > allowed to have a
> > variable size.
> > This may add invaluable flexibility to my program, but how to
> > do it ????.
> The number of arguments in the execute() call must match the number of
> placeholders (question marks) in the prepare().
> So, either you leave the prepare() as it is (assuming you can predetermine
> the maximum number of placeholders you will ever need). You then collect the
> known arguments into @args1 and add as many undefs at the end as you need in
> order to match the number of placeholders.
> Or, you drop the whole prepare() step and you build up a complete SQL
> statement (like
> "insert into $table values(" . join(',', @args1) . ')'
> and do() that. Depending on the content of your @args1, you may need to
> quote the arguments.
> Of course, the latter approach might open some serious security problems
> with SQL injection, depending on the nature of your application, so you'd
> better make quite sure that your @args1 contains nothing poisonous.

You could also use this idiom:

$dbh->do($sql, \%attrs, @args)

For example,

$dbh->do("select foo from bar where bar in(" . join(',', map { '?' }
@args) . ")",
   {}, @args);

-- 
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe:    http://lists.mysql.com/[email protected]