3

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?

Improve this question

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Is there a typo there at the end of sprintf or is just opera mini buggy?
I dont completely understand the code. What does @$? And isnt something missing at the end of the query?
I tried to make it clearer now. I'm using an array of arrays.
Thanks! But why are you running execute * number of rows? And are @rows stuff to insert? And still... isnt something missing after join?
This piece of code is not limited to inserting one row, it can insert as many as you pass to it.
|
1

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

Improve this answer

8 Comments

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.
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.
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 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.
+1 for a nice simple function. But depending on the order of columns in your DB table makes this code very fragile.
|
0

Just pass a reference to an array of arguments. Then in insertRow, iterate over that array to get the arguments...

Improve this answer

Comments

0

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.)

Improve this answer

Comments