Placeholders make sure that the data is quoted correctly. Remember, when inserting strings into your database, they must be surrounded by quotes, yet numbers cannot be. This strict either/or but not both situation means that you need to decide in advance how to quote. If you put quotes in your statement, but change data types later, you are going to break that part of your program in some way that isn't visible until that statement is run.

The other thing you can do is let DBI do it for you, which is what placeholders do. DBI keeps track of how each column has to be treated, and quotes accordingly. As an additional bonus, some driver implementations, such as DBD::mysql can actually save these generic statements and recycle them later. In shotgunefx's example, the execute could be called many times on exactly the same prepared statement.

Changing my scripts now. <grin>

Thanks for the complete answers everyone, and the link- you helped me learn quite a bit, and save a bit of time, too.
++ to all. :)

Efficiency. If you use placeholders, you only need to prepare() the statement once (at which point the db engine analyzes it and figures out how to most efficiently process the query) and can then execute it repeatedly, passing in different parameters each time. Without placeholders, the query has to be re-prepared each time it is executed.

Well depending on what's in your $vars, it could break your SQL if it contains ? or a quote. more on this

The other reason is that you don't have to keep preparing a statement over and over. The poster could move the prepare outside of both foreach loops and only prepare it once. Over thousands of iterations, this can make a noticable difference.

-Lee

"To be civilized is to deny one's nature."

Well, I did as you suggested, shotgunefx:  I added a print statement after the first line of the inner foreach statement and set up place holders.  The result was that I could see the employee id printed five times (once for each weekday record) for each employee.  So the inner foreach statement does seem to be getting the employee id numbers.

I tested a little further by printing out @schedule at the end of the inner loop and I ended up with data displayed that was lengthier with each looping.  It turns out that the problem was with a push statement that I didn't copy into my posting.  In an attempt to shorten my posting to make it easier to discuss, I didn't re-type my code accurately.   The last line I presented in the inner loop reads on the posting as follows:

@schedule = $sth->fetchrow_array();
[download]

In actuallity it reads in my script like this:

@_ = $sth->fetchrow_array();
push(@schedule, @_);
[download]

The problem is that the first employee has nothing scheduled because she's on vacation this week.  And since I'm appending the array for each record and not resetting it after each inner loop, each employee looks like they have nothing scheduled.

I've gone back and added @schedule = ""; to the line just above the inner loop and it now works.  Sorry I didn't post that last line correctly--as I said I was looking for brevity in the code at least.  Thanks for the troubleshooting suggestion regarding putting print statements in the inner loop.  That brought the whole thing to light.

That's Spenser, with an "s" like the detective.

Glad I could help. A couple comments though, you probably want to initilize @schedule with an empty list.


@schedule = ();


The other is you usually don't want to assign to @_, depending on the context it might cause subtle bugs. @_ has some magic associated with it. For instance, in a sub, @_ acts as aliases to the passed arguments. (Example below)

It appears assigning to @_ makes it lose it's magic so I don't think it is a problem here but it's probably a dubious habit to form.



#!/usr/bin/perl -w

use strict;

my ($foo,$bar,$baz) = (1,2,3);

print "$foo, $bar, $baz \n"; # Prints 1, 2, 3

foo($foo,$bar,$baz);

print "$foo, $bar, $baz \n"; # Prints 5, 10 , 15


sub foo {
    foreach (@_) {
        $_ *= 5;
    }
}
[download]

-Lee

"To be civilized is to deny one's nature."