Using the SQLite DBD

In this section we will look at the methods and attributes available for a DBI object with examples specific to the DBD::SQLite2 module.

Opening and Closing the Database

As we saw before, the DBI->connect() function is used to open a database, and for a SQLite database no username or password arguments are required. The usage is always as follows:

 $dbh = DBI->connect("DBI:SQLite2:dbname=dbfile", "", ""); 

Some basic error trapping is useful in case the connection to the database fails. Because SQLite will create a new database file with the given name if one does not exist, DBI->connect() will only fail if there is an I/O error, for instance file permissions not allowing the file to be opened or created in a particular directory, or no more disk space on the device.

The errstr property contains the most recent database error. The following example could be used to exit with an error message if SQLite is unable to open the specified dbfile:

 $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")        or die("Error: " . DBI::errstr); 

To close the connection to a database, simply invoke the disconnect() method on your DBI object.

 $dbh->disconnect(); 

Executing SQL Statements

We have already seen that the do() method can be used to pass SQL to the DBI module in order to execute a command. In fact, do() is good only for non-SELECT statements and the preferred method to send SQL commands to the DBI module is using a two-step process. do() is a convenience function that effectively combines these two steps.

First the query needs to be prepared by the SQL engine using prepare, after which it can be executed using the execute method.

Listing 7.3 creates the contacts table in SQLite with error trapping at every stage. The errstr property returns the most recent error message generated by the DBD, so any cause of error that causes the script to exit prematurely will be displayed to the screen.

Listing 7.3. Creating a Table Using DBI

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr() ); my $sql = "CREATE TABLE contacts (   ".           "  id INTEGER PRIMARY KEY, ".           "  first_name TEXT,        ".           "  last_name TEXT,         ".           "  email TEXT)             "; my $sth = $dbh->prepare($sql)           or die("Cannot prepare: " . DBI::errstr() ); my $ret = $sth->execute()           or die("Cannot execute: " . DBI::errstr() ); 

The prepare() method returns a statement handle object, which in turn can invoke the execute() method to perform the query.

 $sth = $dbh->prepare($sql); ... $ret = $sth->execute(); 

Note

The prepare() method instructs SQLite to get ready to execute the statementthe database engine will tokenize the statement and work out how it will be executed when the time comesbut it does not parse the SQL statement. Any syntax errors in the SQL will only be reported when execute() is called.

Using Bind Variables

A powerful feature of DBI is that an SQL statement still needs to be prepared only once, even if it is to be executed repeatedly with different values in the statement. This is achieved using placeholders in the SQL and an array of bind values passed as an argument to execute() and gives a significant performance saving over preparing a query with static values each time it is executed.

A placeholder is indicated by the question mark symbol. For example, the following query could be prepared to create an SQL INSERT statement where the placeholders indicate that we will supply the values to be inserted at execution time. Note that no quotes are required around a placeholder even if a string value is the expected substitution.

 INSERT INTO contacts (first_name, last_name, email) VALUES (?, ?, ?) 

Listing 7.4 shows how a record is inserted using this technique.

Listing 7.4. Inserting a Record Using Bind Values

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr() ); my $sql = "INSERT INTO contacts (first_name, last_name, email) ".           "VALUES (?, ?, ?)"; my $sth = $dbh->prepare($sql)           or die("Cannot prepare: " . DBI::errstr() );  $sth->execute('Chris', 'Newman', '[e-mail]')        or die("Cannot execute: " . DBI::errstr() ); 

The execute() method takes one argument for each placeholder in the order specified in the query. We can verify that the record was inserted as expected using the sqlite tool.

 $ sqlite perldb SQLite version 2.8.15 Enter ".help" for instructions sqlite> SELECT * FROM contacts; id  first_name  last_name   email --  ----------  ----------  -------------------- 1   Chris       Newman      [e-mail] 

Let's extend this a little further to create a script that allows you to populate the contacts table by entering records from the keyboard.

The script in Listing 7.5 creates a loop that reads user input that is expected to be the three elements first_name, last_name, and email separated by commas. If the format of the input is valid, the record is inserted into the database; otherwise, the script will exit.

Listing 7.5. Inserting Records from User Input with a Single Prepared SQL Statement

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr); my $sql = "INSERT INTO contacts (first_name, last_name, email) ".           "VALUES (?, ?, ?)"; my $sth = $dbh->prepare($sql)           or die("Cannot prepare: " . DBI::errstr);  while(<>) {   chomp;   my ($first_name, $last_name, $email) = split /,/;   if (!$email) {     print "Required format: first_name, last_name, email\n";     next;   }   $sth->execute($first_name, $last_name, $email)          or die("Cannot execute: ". DBI::errstr() );   print "Inserted: $first_name, $last_name, $email\n"; } 

The key part of this script is the while loop, which reads from standard input. Input is taken from stdin a line at a time, and we use chomp to strip the trailing newline characters.

 while(<>) {   chomp; 

The expected data format is a comma-separated list of the three elements to be inserted into contacts. The split instruction breaks up the read line of input and the first three comma-separated elements are assigned to $first_name, $last_name, and $email respectively.

 my ($first_name, $last_name, $email) = split /,/; 

To validate the data record we test that a value has been assigned to $email. If there are less than three comma-separated values in the input, $email will be empty. If there are more than three elements, the fourth and subsequent elements are simply discarded.

 if (!$email) {   print "Required format: first_name, last_name, email\n";   next; } 

If everything is okay, a row is inserted by calling the execute() method on the prepared SQL statement with the assigned variables.

 $sth->execute($first_name, $last_name, $email)        or die("Cannot execute: ". DBI::errstr ); 

Note

The placeholders in an SQL statement can only represent items that do not alter the execution plan. Therefore you cannot use a placeholder to substitute a table or column name.

The bind_param() function is used to set bind parameter values individually and provides a little more functionality than supplying a list of bind values to the execute() method by allowing you to give a data type hint.

In fact when bind parameters are passed to the execute() method, the DBI effectively calls bind_param() for each value in the list. The data type is assumed to be SQL_VARCHAR.

The syntax of bind_param() is as follows:

 $sth->bind_param($p_num, $bind_value, $bind_type) 

The parameters are numbered in the order they appear in the SQL starting at 1, and the number of the parameter you want to assign is passed in the $p_num argument.

The only data type constants appropriate for SQLite are SQL_VARCHAR and SQL_INTEGER. In order to use these constants in your script you must import them with the use command:

 use DBI qw(:sql_types); 

To produce the same result we saw in Listing 7.4 using bind_param(), instead of passing the bind values to execute(), the following lines would be required:

 $sth->bind_param(1, $first_name, SQL_VARCHAR); $sth->bind_param(2, $last_name, SQL_VARCHAR); $sth->bind_param(3, $email, SQL_VARCHAR); $sth->execute() or die "Cannot execute: ". DBI::errstr; 

Bind values are safe when passed into an SQL statement. For example, the following execution of Listing 7.5 shows that a surname containing an apostrophe does not conflict with the implied quotation marks around that string value.

 $ ./listing7.5.pl Paddy,O'Brien,[e-mail] Inserted: Paddy, O'Brien, [e-mail] 

Had we performed the same INSERT command using static values in the script, some kind of delimiting would be required to avoid the query looking like this:

 INSERT INTO contacts (first_name, last_name, email) VALUES ('Paddy', 'O'Brien', '[e-mail]') 

This query would cause an error upon execution as the parser would not be able to determine that the apostrophe in O'Brien was not in fact a closing quotation mark around the string 'O'.

In Perl we can call the DBI method quote() to put a value into the right type of quotes for the underlying database (single quote for DBD::SQLite2) while adding the necessary delimiters to make them safe for use within an SQL command. Listing 7.6 performs the quote method on each of the values in an INSERT statement.

Listing 7.6. Using the quote Method to Safely Delimit String Values

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr() ); my $first_name = "Paddy"; my $last_name = "O'Brien"; my $email = "paddy\@irish.com"; my $sql = sprintf("INSERT INTO contacts (first_name, last_name, email) \n".                                 "VALUES (%s, %s, %s)",                   $dbh->quote($first_name),                   $dbh->quote($last_name),                   $dbh->quote($email)); print $sql . "\n"; 

The output from running Listing 7.6 displays the SQLite-safe query text.

 $ ./listing7.6.pl INSERT INTO contacts (first_name, last_name, email) VALUES ('Paddy', 'O''Brien', '[e-mail]') 

Remember that SQLite delimits the apostrophe character by doubling itthe occurrence of '' in a string enclosed by single quotes means that the string contains one apostrophe.

Note

Because quote() encloses the string in quotes as well as delimiting any awkward characters, it should not be used with placeholders and bind values. As we have already seen, apostrophes in bind values do not need to be delimited.

Using Commands That Change the Database

When an UPDATE or DELETE operation is performed, it is usually the intention to alter one or more database rows. The number of rows affected by the statement can be found by looking at the rows property of the statement handler.

 $sth->execute(); print $sth->rows; 

The number of affected rows is also the return value from execute, which provides a handy shortcut to this information. Because the do() method is a convenience function that prepares and executes an SQL command in one go, you can also look at the return value from do() to find the number of affected rows.

For example, after the following statement is executed, $num will contain the number of rows that has been deleted:

 $num = $dbh->do("DELETE FROM contacts WHERE first_name = 'Chris'"); 

When an INSERT is performed on a table with an INTEGER PRIMARY KEY fieldas in our previous examplesSQLite assigns the next numerical value to that field if the inserted value is NULL. The SQLite DBD module extends DBI to provide a private method for retrieving this value. It is called as follows:

 $id = $dbh->func("last_insert_rowid"). 

Note

The INTEGER PRIMARY KEY property of a database column is specific to SQLite, so last_insert_rowid is implemented by DBD::SQLite2, not the DBI module. Therefore, be aware that scripts that use this private method cannot be expected to work with other DBD modules.

Listing 7.7 performs an UPDATE followed by a INSERT on the contacts table, and displays the number of affected rows and the assigned INTEGER PRIMARY KEY field respectively.

Listing 7.7. Finding Affected Rows and the Assigned Value of a Primary Key

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr() ); my $sql = "UPDATE contacts ".           "SET first_name = upper(first_name)"; my $sth = $dbh->prepare($sql)           or die("Cannot prepare: " . DBI::errstr() ); my $num = $sth->execute or die("Cannot execute: ". DBI::errstr() ); print "Updated " . $sth->rows ." rows \n"; $sql = "INSERT INTO contacts (first_name, last_name, email) ".        "VALUES ('Bill', 'Williams', 'bill\@williams.com')"; $sth = $dbh->prepare($sql)        or die("Cannot prepare: " . DBI::errstr() );  $sth->execute() or die("Cannot execute: ". DBI::errstr() ); print "Inserted with ID " . $dbh->func("last_insert_rowid"). "\n"; 

The following output will be generated, depending on the actual number of records already in the contacts table:

 $ ./listing7.7.pl Updated 4 rows Inserted with ID 5 

Transactions

Three DBI functions are provided to begin and end SQLite transactions. They map directly to the underlying SQL commands and each is equivalent to issuing the corresponding command shown in Table 7.1.

Table 7.1. Transaction Functions in Perl DBI

begin_work()	BEGIN TRANSACTION
commit()	COMMIT TRANSACTION
rollback()	ROLLBACK TRANSACTION

Fetching Records from the Database

We have already seen how an INSERT or UPDATE command returns the number of affected rows as the result of the execute method. Now let's take a look at how the result of a SELECT statement is processed using the Perl DBI.

The fetchrow_array() method can be used on an executed statement handler to return a list of the selected column values. The first time it is called, the first row from the query is returned, and on subsequent calls the record pointer is advanced to return the next row in sequence.

Listing 7.8 shows how fetchrow_array() can be used in a loop to output every row from a table.

Listing 7.8. Using fetchrow_array() to Output the Contents of a Table

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr() ); my $sql = "SELECT first_name, last_name FROM contacts"; my $sth = $dbh->prepare($sql)           or die("Cannot prepare: " . DBI::errstr() ); $sth->execute() or die("Cannot execute: ". DBI::errstr() ); while( my ($first_name, $last_name) = $sth->fetchrow_array() ) {   print "$first_name $last_name \n"; } 

The while loop assigns two variables from the elements of the array returned by fetchrow_array() for each iteration.

 while( my ($first_name, $last_name) = $sth->fetchrow_array() ) { 

After the last row of data has been fetched, the while condition is false and the loop exits. The output generated by Listing 7.8 is as follows:

 $ ./listing7.8.pl CHRIS Newman PADDY O'Brien BILL Williams 

As an alternative to fetchrow_array(), you can use the fetchrow_arrayref() method, which returns a reference to an array containing each row's elements. A while loop to achieve the same result as Listing 7.8 using this method would look like this:

 while (my $ref = $sth->fetchrow_arrayref()) {   print "@{$ref} \n"; } 

The data rows can also be returned as a hash, using the fetchrow_hash() method. A reference to a hash is returned where the hash contains column name and value pairs.

 while (my $ref = $sth->fetchrow_hashref()) {   print "$$ref{'first_name'} $$ref{'last_name'}\n"; } 

Note

Where values in the table are NULL, they are seen in Perl as undef, regardless of which method you choose to fetch the data.

The functions that begin fetchrow_ also have corresponding functions that begin fetchall_. The difference, as you might expect, is that the entire dataset returned from the query is fetched all at once, rather than a row at a time.

Listing 7.9 shows how the result fetched by fetchall_arrayref() can be used in Perl.

Listing 7.9. Using fetchall_arrayref to Fetch an Entire Dataset

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr() ); my $sql = "SELECT * FROM contacts ORDER BY first_name"; my $sth = $dbh->prepare($sql)           or die("Cannot prepare: " . DBI::errstr() ); $sth->execute() or die("Cannot execute: ". DBI::errstr() ); my $ref = $sth->fetchall_arrayref(); foreach my $row (@{$ref}) {   print "@$row\n"; } 

The output looks like this, with the columns displayed in the order they appear in the table schema:

 $ ./listing7.9.pl 3 BILL Williams bill.com 1 CHRIS Newman [e-mail] 2 PADDY O'Brien paddy.com 

An optional argument to fetchall_arrayref() can be used to fetch only a subset of the columns returned by the query into the array, known as a slice. The argument should be a hash reference of the column numbers to be returned, where columns are numbered from zero starting with the first in the selected list. Negative numbers can be used to count from the end of the column list.

For example, the following statement would cause only the second and last columns from the query to be put into the array:

 $ref = $sth->fetchall_arrayref([1,-1]); 

An optional second argument allows the maximum number of rows that will be created in the array to be specified. A non-zero value will restrict the dataset if it is larger than the total number of rows returned by the query, including the LIMIT clause if there is one.

If either argument is not required, it can be specified as undef. The following statement does not slice the array but limits the number of rows returned to 2:

 $ref = $sth->fetchall_arrayref(undef, 2); 

The fetchall_hashref() method requires an argument that gives the name of the field to be used as the key of the returned hash. The key field argument may be the column name or number.

The following statement calls fetchall_hashref() on contacts using the email column as the key:

 $ref = $sth->fetchall_hashref('email'); 

Given an email address, we can now use this hash to find other information:

 $first_name = $ref->{'[e-mail]'}->{'first_name'}; $last_name = $ref->{'[e-mail]'}->{'last_name'}; 

The finish method that can be called on a statement handle is used to destroy the handle when it is no longer needed. It is simply called as follows:

 $sth->finish(); 

However, finish() should only be called when you are done with reading data from the statement handle and know that there is more data still to come. If any of the fetch instructions have come to the end of the dataset, there is no need to call finish().

Error Checking

The error checking we have come across so far has tested the return code of a function and taken action to display the error and exit if necessary. This might look like the following example using a traditional if condition to check for success:

 $ret = $sth->execute(); if (!$ret) {   print DBI::rrstr;   exit; } 

Or it can be done in the more concise syntax using or die.

 $ret = $sth->execute()        or die("Cannot execute: " . DBI::errstr); 

There is an even more concise way to apply error checking to all your database function calls. The Perl DBI implements automatic error checking using two built-in exceptions, PrintError and RaiseError, enabled by setting attributes with those names against the database handle.

The values of PrintError and RaiseError can be set at the time the database connection is established via the attr argument, using 1 to turn that feature on and 0 to turn it off.

To create a database connection with automatic error checking handled using RaiseError, you would use a statement similar to the following:

  $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "", {   PrintError => 0,   RaiseError => 1 } ); 

The PrintError exception causes the warn() function to be called with the error stringthe value of the errstr propertyas its argument. The error message will be displayed, but program execution will not stop.

The RaiseError exception causes the die() function to be called, halting program execution. If both PrintError and RaiseError are enabled, warn() is called first, then die().

The status of each exception can be changed mid-script simply by reassigning the value of that attribute. To turn off RaiseError, for instance, you would do the following:

 $dbh->{RaiseError} = 0; 

Tracing

The Perl DBI includes a powerful tracing mechanism that allows you to follow a query's execution to aid debugging.

Tracing is enabled using the trace() method, which can be called on a database handle or on the DBI itself to alter the default settings for every database handle opened in the script. Its argument is a trace level from 0 to 15; the values for which are shown in Table 7.2.

Table 7.2. Trace Level Values and Their Meanings

0	Trace disabled.
1	Trace DBI method calls returning with results or errors.
2	Trace method entry with parameters and returning with results.
3	As level 2, with some additional high-level information from the DBD and some internal information from the DBI.
4	As level 3, with more detailed information from the DBD.
515	Each higher level adds more obscure tracing information.

Trace level 1 should give a good overview of what is going on in your script. Level 2 will give some extra information that can be useful when debugging, and the higher levels are really only any use if you are trying to trap a specific problem.

To perform a simple trace of your script using trace level one, use the following command:

 DBI->trace(1); 

Trace output is written to stderr by default; however, an optional second parameter to trace() allows this to be changed.

Listing 7.10 creates a new table, numbers, and performs a simple loop to insert the first few square numbers into the num column. This is a trivial example to show tracing in action, but we will use the data in this listing later in this chapter.

Listing 7.10. Inserting a Series of Square Numbers

 #!/usr/bin/perl -w use strict; use DBI; my $dbh = DBI->connect("DBI:SQLite2:dbname=perldb", "", "")           or die("Cannot connect: " . DBI::errstr() ); $dbh->trace(1); my $sql = "CREATE TABLE numbers (num INTEGER)"; $dbh->do($sql); $sql = "INSERT INTO numbers (num) VALUES (?)"; my $sth = $dbh->prepare($sql)           or die("Cannot prepare: " . DBI::errstr() );  for (my $i=1; $i<=9; $i++) {   $sth->execute($i * $i)          or die("Cannot execute: " . DBI::errstr() ); } 

Tracing is set to level 1 at the DBI level. Running this script produces trace output similar to the following:

 $ ./listing7.10.pl     DBI::db=HASH(0x9b88b90) trace level set to 0x0/1 (DBI @ 0x0/0) in DBI 1.43-ithread (pid 2509)     <- do('CREATE TABLE numbers (num INTEGER)')= '0E0' at listing7.10.pl line 11     <- prepare('INSERT INTO numbers (num) VALUES (?)')= DBI::st=HASH(0x9b8bd0c) at listing7.10.pl line 15     <- execute(1)= 1 at listing7.10.pl line 19     <- execute(4)= 1 at listing7.10.pl line 19     <- execute(9)= 1 at listing7.10.pl line 19     <- execute(16)= 1 at listing7.10.pl line 19     <- execute(25)= 1 at listing7.10.pl line 19     <- execute(36)= 1 at listing7.10.pl line 19     <- execute(49)= 1 at listing7.10.pl line 19     <- execute(64)= 1 at listing7.10.pl line 19     <- execute(81)= 1 at listing7.10.pl line 19 !   <- DESTROY(DBI::db=HASH(9ae4248))= undef during global destruction 

Tracing can also be enabled from the shell without needing to amend your scripts using the DBI_TRACE environment variable. If DBI_TRACE is assigned a non-zero numeric value it will set the trace level for the DBI in Perl scripts to that value. If the number is followed by =filename, trace output will be redirected to that file.

For example, to run a script in trace level 2 with output written to TRace.log, the following would work in the Bourne shell:

 $ DBI_TRACE=2=trace.log perl myscript.pl