The $DBH->do(...) method automatically combines the prepare and execute into a single operation. It's quick and dirty and handy for quick tests and oddball operations that you do only once in your program (such as table creation). For actual data manipulations, I generally prefer to use prepare/execute, but since this was a quick test to verify your installation, I didn't worry about it.
So to answer your question: You can do it either way, there aren't requirements as such. BUT the prepare/execute format for data manipulation offers some advantages that easily outweigh the slightly larger coding effort: You can get a lot of safety by using placeholders, you can get better performance if your database compiles statements for reuse. Consider this:
print "Please enter the last name:\n"; my $user_data = <>; chomp $user_data; $DBH->do("select * from people where last_name='$user_data'");
So if someone enters a last name like:
Jones'; drop table people; --
Then your application won't work very well any more. So if you need to use data provided by a user (via input, a web application or whatever), you need to be careful that your data doesn't cause a serious problem. DBI covers you by offering placeholders: Rather than you having to worry about the quoting rules for the database code, you just use a ? and let DBI do the quoting for you:
print "Please enter the last name:\n"; my $user_data = <>; chomp $user_data; my $STH = $DBH->prepare("select * from people where last_name=?"); $STH->execute($user_data);
Now if the user enters that last name, you'll simply get no data (unless you happen to have such a strangely-named person in your database).
Now on to the performance aspects: when you run an SQL statement, the database can spend a significant amount of time determining out what it's going to do, which indexes it needs to reference and/or update. Some databases will precompute all that information for an SQL statement, and then use that same plan to execute multiple SQL statements. That can save a *lot* of time in some cases. For example, what if your application was reading last names from a file instead of prompting from the user:
print "Please enter the file name:\n"; my $user_data = <>; open my $FH, '<', $user_data; while (my $last_name = <$FH>) { chomp $last_name; my $STH = $DBH->prepare("select * from people where last_name=?"); $STH->execute($last_name); }
If you have a thousand names in your file, then it will figure out how to do that statement a thousand times, and then do it a thousand times. But by moving one statement, you can get a performance boost:
print "Please enter the file name:\n"; my $user_data = <>; open my $FH, '<', $user_data; my $STH = $DBH->prepare("select * from people where last_name=?"); while (my $last_name = <$FH>) { chomp $last_name; $STH->execute($last_name); }
Now the database need only figure out how to do the statement once, and then just do it for a thousand times. So you get safety and a possible performance boost. Note: not all databases will give you a performance boost, but you still get the safety. But if you use placeholders, then if you switch to a database that does precompilation of statements, you'll get a free boost.
Since you don't create a thousand tables at once, and the database couldn't benefit from precompilation in that case anyway, using the do method is just fine.
Update: Fixed broken code tag.
...roboticus
When your only tool is a hammer, all problems look like your thumb.
In reply to Re^3: SQLite select wont' return records
by roboticus
in thread SQLite select won't return SELECTed records
by bulrush
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |