Using Perl with MySQL - Learning MySQL - page 546

Let's explore this by looking at four possibilities. Imagine you have a database called
perltest and it includes a table called testtable with two string columns; these could
have been created from the monitor with the following statements:
mysql> CREATE DATABASE perltest;
Query OK, 1 row affected (0.23 sec)
mysql> USE perltest;
Database changed
mysql> CREATE TABLE testtable (col1 CHAR(40), col2 CHAR(40));
Query OK, 0 rows affected (0.01 sec)
If the query is:
my $Query="INSERT INTO testtable (col1, col2) VALUES ('abcd1', 'abcd2')";
we'd get the message:
Operation was successful (1 rows)
reflecting the number of rows inserted.
Deleting matching rows with the query:
my $Query="DELETE FROM testtable WHERE col1='abcd1' AND col2='abcd2'";
would give us:
Operation was successful (1 rows)
reflecting the number of rows deleted.
Trying to delete data items that don't exist in the database:
my $Query="DELETE FROM testtable WHERE col1='xabcd1' AND col2='abcd2'";
would return 0E0 , so the success message is printed:
Operation was successful but no rows were affected
Finally, if the do( ) operation could not be performed for any reason (for example, an
incorrect SQL query, duplicate key, or a nonexistent table), the failure message would
be printed. If we tried to access the nonexistent table nosuchtable :
my $Query="DELETE FROM nosuchtable WHERE col1='abcd1' and col2='abcd2'";
This would result in the message:
Operation failed: Table 'perltest.nosuchtable' doesn't exist
Binding Queries and Variables
The Perl DBI module offers a convenient way to write SQL queries with placeholders
that can be replaced by arguments to the execute( ) statement. Similarly, results from
a query can be mapped to Perl variables. In this section, we describe how you can use
placeholders in your queries.