JavaScript Editor Js editor     Website development 



Main Page

Previous Page
Next Page

Using Prepared Statements

The last topic I'll discuss with respect to Perl is the prepared statement. Prepared statements offer many benefits. First, they are often a faster method of running queries, particularly in applications where the same query is run multiple times, using different values for each. The second benefit is that you do not need to worry about escaping problematic characters like apostrophes. They'll automatically be handled by the process. A minor plus as well is that they separate the query from the data used in the query, which can make for more readable code.

To start, you prepare a query as you already have been, but you use question marks to act as placeholders where the actual data will later go:

my $sth = $dbh->prepare("INSERT INTO tablename (col1, col2) VALUES (?, ?)");

The question mark represents the data that will be added to the query. Note that no quotation marks go around a placeholder, even if it may represent string data (which otherwise has to be quoted in queries). Once you've prepared the query, you can then associate data with that placeholder when you execute the query:

$sth->execute("some value", 235);

This has the effect of replacing the first placeholder in the query with the text some value. The second placeholder will be replaced with 235. The result will be the execution of the query

INSERT INTO tablename (col1, col2) VALUES ('some value', 235)

If you want to run the query again using new values, just repeat the execute() line. You only need to prepare the statement once.

To demonstrate this, I'll write a script that lets a user enter multiple expense categories. This will mostly be an adaptation of the early script that let a user enter a single expense category.

To use prepared statements:

1.
Create a new Perl script (Script 8.8).

#!/usr/bin/perl -w
use strict;
use DBI;

Script 8.8. Prepared statements provide a faster and more secure way to run similar queries multiple times.

1     #!/usr/bin/perl -w
2
3     # Script 8.8 - add_categories.pl
4     # This script adds categories to the expense_categories table.
5     # It relies upon user input.
6
7     # Use what needs to be used.
8     use strict;
9     use DBI;
10
11    # Print a message.
12    print "Use this program to add expense categories.\n\n";
13
14    # Connect to the database.
15    my $dbh = DBI->connect("DBI:mysql:accounting:localhost", 'username', 'password',
      {RaiseError => 1});
16
17    # Prepare the query.
18    my $sth = $dbh->prepare("INSERT INTO expense_categories (expense_category) VALUES (?)");
19
20    if (defined($sth)) {
21
22       # Get the first category.
23       print "Enter an expense category or enter 'q' to quit: ";
24       chop (my $cat = <STDIN>);
25
26       while ($cat ne "q") {
27
28          # Execute the query.
29          $sth->execute($cat);
30          if ($sth->err()) {
31               print "The expense category could not be added.\n\n";
32              # Print MySQL error, if desired.
33              # exit() ?
34          } else {
35              print "The expense category has been added.\n\n";
36          }
37
38          # Reprompt.
39          print "Enter an expense category or enter 'q' to quit: ";
40          chop ($cat = <STDIN>);
41
42     }
43
44    }
45
46    # Disconnect.
47    $dbh->disconnect;

2.
Print an introductory message and connect to the database.

print "Use this program to add expense categories.\n\n";
my $dbh = DBI->connect("DBI:mysql:accounting:localhost",
'username', 'password', {RaiseError => 1});

This code is exactly as it is in the original version of the script.

3.
Prepare the query.

my $sth = $dbh->prepare("INSERT INTO 'expense_categories
'(expense_category) VALUES (?)");

This is the first step in the prepared query process. The query is defined, using one placeholder for the actual expense_category value.

4.
Get the category from the user.

if
(defined($sth)) {
  print "Enter an expense category 'or enter 'q' to quit: ";
  chop (my $cat = <STDIN>);

The user is prompted to enter an expense category. They are also given a way to terminate the script, when the time is right (Figure 8.33). This is more important later in the script, when a loop is used to continually take the user input.

Figure 8.33. The initial prompt with instructions for quitting.


I'm also chopping off the extraneous spaces so that they aren't added to the database (and so that my while loop condition will work).

5.
Check that a category has been entered and execute the query.

while ($cat ne "q") {
    $sth->execute($cat);

Because the user has been provided with an "out" (entering the letter q to quit), the loop has to check for that. If $cat does not equal q, then the query will be executed using the submitted value.

6.
Check for errors, then reprompt.

if ($sth->err()) {
   print "The expense category could not be added.\n\n";
} else {
   print "The expense category has been added.\n\n";
}
print "Enter an expense category or enter 'q' to quit: ";
chop ($cat = <STDIN>);

If there was an execution problem, $sth->err() will be true. Otherwise, it's safe to assume that the query worked. The user is reprompted and the next category is read in. After this, the loop will check the condition again. This whole process will be repeated until the user enters just q.

For debugging purposes, you may also want to print the MySQL error message, should one exist, and maybe terminate the script using exit().

7.
Complete the defined($sth) conditional and disconnect from the database.

}
$dbh->disconnect;

8.
Save the script as add_category.pl, change the permissions (if necessary), and run the script (Figure 8.34).

Figure 8.34. The user can enter multiple categories and quit the application when they are done.


Tips

  • Prepared statements can be used with any type of query, not just INSERTs. The greatest performance benefit comes from queries that are run multiple times with only modifications in the data used.

  • The process demonstrated here is also referred to as bound variables. Specifically, this is binding input variables. You can also bound output variables (assigning the values returned by a SELECT query to Perl variables).

  • To assign the value NULL to a placeholder, use undef (without quotes).

  • Do not use quote() with your prepared statement values or else your strings will be over-quoted.



Previous Page
Next Page


JavaScript Editor Js editor     Website development


©