The Perl DBI | SQLite

The Perl interface to SQLite is the Perl Database Interface (DBI) module using the SQLite Database Driver (DBD) module.

 use DBI;

Loads the DBI module into your script. There is no need to explicitly load a DBD driver as DBI takes care of this automatically.

In this appendix we use $dbh to refer to a database handle object and $sth for a statement handle.

Opening and Closing a Database

 $dbh = DBI->connect($data_source, $username, $auth, \%attr);

Opens a database connection using the arguments given and creates a database handle object $dbh.

The data_source argument for a SQLite connection takes the following form, where dbfile can refer to a file in the current working directory or may contain a relative or absolute path.

 DBI:SQLite2:dbname=dbfile

The username and auth arguments are not required for a SQLite database and can be omitted or passed as empty strings.

The optional attr argument can be used to set certain database handle attributes, described later in this appendix.

 $rc = $dbh->disconnect();

Closes an open database connection, returning a Boolean $rc value.

Executing SQL Statements

The following methods can be called on a statement handle:

 $sth = $dbh->prepare($sql); $sth = $dbh->prepare($sql, \%attr);

Prepares a statement, $sql, for execution by the database and returns a statement handle object, $sth.

The optional attr argument can be used to set certain statement handle attributes, described later in this appendix.

 $sth->execute(); $sth->execute(@bind_values);

Executes a prepared SQL statement. Return value $rv will be the number of rows affected, or -1 if not known. If zero rows are affected, the return value is 0E0, which is treated as a zero value but regarded as true. A zero return code indicates statement failure.

 $dbh->do($sql); $dbh->do($sql, @bind_values);

A convenience function that prepares and executes a query in one step. The return value is the same as for execute().

Using Bind Values

The optional bind_values list in execute() and do() can be used to specify a comma-separated list of values for placeholders in the SQL statement. Upon execution, question mark characters in the SQL are replaced with the bind values in order. The underlying function called to bind a value is bind_param().

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

Parameter number $p_num is replaced with $bind_value and an optional data type hint can be given as $bind_type. Bind parameters are numbered starting at 1.

Making Data Safe

Data inserted using bind parameters is automatically safe; however, to ensure that data from static parameters will be inserted into the database correctly, some delimiting may be necessary.

 $dbh->quote($str);

Returns $str enclosed in single quotes, with any awkward characters correctly delimited.

Getting Information About a Query

The rows() method can be called on a statement handle:

 $dbh->rows();

Returns the number of rows affected by the most recent UPDATE or DELETE statement.

The last_insert_rowid private method from DBD::SQLite returns the last assigned value of an autoincrementing INTEGER PRIMARY KEY field. It is invoked as follows:

 $dbh->func("last_insert_rowid");

Transactions

The following methods can be called on a database handle:

 $dbh->begin_work();

Begins a transaction. Equivalent to issuing the SQL command BEGIN TRANSACTION.

 $dbh->commit();

Ends a transaction, saving any changes that have been made to the database during the current transaction. Equivalent to issuing the SQL command COMMIT TRANSACTION.

 $dbh->rollback();

Ends a transaction without saving the changes made during the current transaction. Equivalent to issuing the SQL command ROLLBACK TRANSACTION.

Fetching Rows from a Query

The following methods can be called on a statement handle:

 $sth->fetchrow_arrayref(); $sth->fetchrow_array(); $sth->fetchrow_hashref(); $sth->fetchrow_hashref($name);

Fetches the next row of data and returns a data structure holding the field values. The data structure is an array reference, list, or hash reference respectively. NULL values are returned as undef.

If there are no more rows to fetch or an error occurs, the return value will be undef for fetchrow_arrayref() and fetchrow_hashref(), and an empty list for fetchrow_array().

The optional $name specifies the statement handle attribute that will be used as the key name in the hash returned by fetchrow_hashref(). The default is NAME, with NAME_uc and NAME_lc used to force the case of the key name.

 $sth->fetchall_arrayref(); $sth->fetchall_arrayref($slice); $sth->fetchall_arrayref($slice, $max_rows);

Returns an array containing the entire dataset from the prepared statement handle.

The optional $slice argument can limit which columns become part of the array using a list of column numbers starting from zero. Negative numbers can be used to count from the end of the column list. If no slice is required the argument can be undef.

The optional $max_rows argument can be used to limit the number of rows copied into the array.

 $sth->fetchall_hashref($key_field);

Returns a hash that contains one entry per row for the entire dataset. The $key_field argument specifies the column name that is to be used as the key field in the hash.

 $sth->finish();

Frees resources associated with a statement resource after it is finished with them.

Error Reporting

The following methods can be called on a database handle to retrieve information about the most recent error.

 $dbh->err();

Returns the error code number. For a list of error code values, refer to Appendix E, "C/C++ Interface Reference."

 $dbh->errstr();

Returns a string containing a description of the error.

Automatic error checking is activated using the PrintError and RaiseError database attributes, which pass the error message to warn() and die() respectively whenever an error is encountered.

 $dbh->{PrintError} = 1;

Enables automatic error reporting via warn(). Program execution will continue after an error is reported.

 $dbh->{RaiseError} = 1;

Enables automatic error reporting via die(). Program execution will terminate when the error occurs.

 $dbh->trace($level); $dbh->trace($level, $filename);

Sets the trace level for DBI methods performed on that database handle and an optional output filename. Level can be 1 to 15, with 1 and 2 being most useful. Level zero turns tracing off.

Tracing can also be enabled via the DBI_TRACE environment variable.

 DBI_TRACE=1 perl myscript.pl

Runs myscript.pl with trace output at level 1 sent to stdout.

 DBI_TRACE=2=trace.log perl myscript.pl

Runs myscript.pl with trace output at level 2 sent to trace.log.

Creating User-Defined Functions

The following calls can be made to private methods implemented by DBD::SQLite:

 $dbh->func( $name, $argc, $func_ref, "create_function" )

Registers a new function, $name, in the SQL language that takes $argc arguments and calls the Perl function referenced by $func_ref. If $argc is -1, the SQL function will accept any number of arguments.

 $dbh->func( $name, $argc, $pkg, 'create_aggregate' )

Registers a new aggregating function $name in the SQL language. $pkg is a package that implements the aggregator interface, comprising three methods:

new() is called once and returns the object on which the other methods should be run.
step() is called once for each row that is to be aggregated.
finalize() is called after all the rows have been passed to step() and returns the aggregated result.