Hi! I'm doing a lot of insert queries, and I think it would be best to write a subroutine for it. Something like insertRow($table, @stuff_to_insert). But how can I make the subroutine dynamic when it comes to the @stuff_to_insert, which can be anything from 1-5 arguments?
views:
274answers:
4
+1
A:
Something like:
sub insertRow
{
my $table = shift;
my $placeholders = join(',', map { "?"; } @_);
$dbh->do("INSERT INTO $table VALUES ($placeholders)", undef, @_);
}
Edited: you need to add undef as a parameter.
Leon Timmermans suggests not using prototypes
kmkaplan
2009-02-16 10:36:01
Downvote because your protoype causes the code to malfunction in a horrible horrible way. You should not use prototypes when you don't know exactly what they do, and even they there is absolutely no reason to use the prototype you just suggested.
Leon Timmermans
2009-02-16 10:41:15
But except for the prototype its good?
2009-02-16 10:43:26
What malfunction?
kmkaplan
2009-02-16 10:47:48
If you supply an array as argument, it is evaluated in scalar context. Thus, only one value will be passed to the function: the *length of the array*. That is guaranteed to cause bugs.
Leon Timmermans
2009-02-16 10:52:22
I repeat, never use prototypes unless you really know what you're doing. And if you use $$$ prototypes, you don't know what you're doing.
Leon Timmermans
2009-02-16 10:54:46
Leon speaks the truth. Prototypes are only needed in Perl to handle certain arcane bits of deep magic. In everyday contexts, they are far more likely to do harm than good. Yet another way in which Perl Ain't C.
Dave Sherohman
2009-02-16 11:03:15
+1 the edited example does not contain prototypes anymore.
magnifico
2009-02-16 15:28:54
+1 for a nice simple function. But depending on the order of columns in your DB table makes this code very fragile.
j_random_hacker
2009-02-17 11:34:34
j_random_hacker: you can do something like: insertRow("mytable('col10', 'col2')", 'value for column 10', 'value for column 2');
kmkaplan
2009-02-17 11:50:36
@kmkaplan: I like that solution! Though personally I prefer to define insertRow() to accept a hash that maps columns to values so that it's impossible for the two lists to get out of sync.
j_random_hacker
2009-02-20 02:41:27
j_random_hacker: personally I do it as you say. But that was not what Jinxen asked.
kmkaplan
2009-02-20 07:40:23
A:
Just pass a reference to an array of arguments. Then in insertRow, iterate over that array to get the arguments...
Keltia
2009-02-16 10:49:22
+4
A:
The best solution is probably using a ORM system such as DBIx::Class. They make handling SQL much easier.
If you choose to stay on raw DBI, I would advice you to use prepared statements like this:
my $query = sprintf 'INSERT INTO %s VALUES(%s)', dbh->quote_identifier($table), join ',', ('?') x $columns;
my $sth = $dbh->prepare($query);
for my $row (@rows) {
$sth->execute(@{$row});
}
This will be a speed and robustness benefit.
You can wrap it all up in a sub, but an ORM probably offers a better solution anyway.
Leon Timmermans
2009-02-16 10:51:31
I dont completely understand the code. What does @$? And isnt something missing at the end of the query?
2009-02-16 12:36:59
Thanks! But why are you running execute * number of rows? And are @rows stuff to insert? And still... isnt something missing after join?
2009-02-16 12:54:46
This piece of code is not limited to inserting one row, it can insert as many as you pass to it.
Leon Timmermans
2009-02-16 14:03:49
A:
The parameter passing part of it is easy enough:
sub foo {
my $table = shift;
my @stuff_to_insert = @_;
# Do stuff here
}
It doesn't matter whether you pass in one parameter, or five, or fifty, they'll all make it into @stuff_to_insert.
As for running the actual queries, take Leon's advice and use prepared statements. (ORMs can be handy, but, IMO, they're overrated and are serious overkill in simple cases.)
Dave Sherohman
2009-02-16 11:13:14