Transcript DBI: The Neophyte`s Guide

What is DBI?
• DBI = DataBase Interface
• DBI is database-independent
• DBI allows you to write code that interacts with
databases independent of the underlying
database
• A DBI program will work with little, or no,
modification on Oracle, Informix, MySQL and
so on...
DBI: The Neophyte's Guide
1
What are DBDs?
• DBD = DataBase Driver
• Drivers are used by DBI to perform the actual
database work
• Drivers are database-dependent
• Drivers exist for most popular databases
including Oracle, Informix, Ingres, mSQL,
MySQL, Solid and many more
DBI: The Neophyte's Guide
2
Why do we need the DBI?
• Abstracted interface hides database-specific
functionality from the programmer
• DBI scripts are generally completely portable
between databases with little modification
• Faster application development and debugging
cycle
DBI: The Neophyte's Guide
3
What about ODBC?
• ODBC and DBI share similar goals and
heritage: platform- and database-independence
• DBI is far more compact and less complex than
ODBC. ODBC has a richer feature set
• Although ODBC drivers exist for many
operating systems, they can be expensive and
difficult to use. DBI is free and easy to
configure and use
• A driver called DBD::ODBC exists and can be
used to connect to ODBC-based databases such
as Microsoft Access
DBI: The Neophyte's Guide
4
DBI Resources
• The DBI home page
– http://www.symbolstone.org/technology/perl/DBI
• ``Programming the Perl DBI’’ -- Alligator
Descartes and Tim Bunce. Can be ordered via
amazon.com from the DBI home page
• This presentation and demonstration source
code can be downloaded from the DBI home
page
DBI: The Neophyte's Guide
5
DBI Architecture
• Architectural Overview
• A Handy Reminder
• Handles
– Driver Handles
– Database Handles
– Statement Handles
• Basic Database Operations
– Connecting to the Database
• Data Sources
– Disconnecting from the Database
DBI: The Neophyte's Guide
6
Architectural Overview
DBI: The Neophyte's Guide
7
A Handy Reminder
• A tip to help you remember which modules do
what in DBI
– DBI is DataBase Independent
– DBD is DataBase Dependent
DBI: The Neophyte's Guide
8
Handles
• All operations in DBI are performed via
–
–
–
–
DBI module as a static method
Driver Handles
Database Handle instance method
Statement Handle instance method
• Driver Handles are not used explicitly by the
programmer in scripts
DBI: The Neophyte's Guide
9
Driver Handles
• Driver Handles encapsulate a DBD
• There exists exactly one driver handle for each
loaded DBD
• For example, a script using both MySQL and
Oracle will have two instantiated driver handles
• Driver handles are never used directly in
programs and are for internal use only by DBI
DBI: The Neophyte's Guide
10
Database Handles
• Database Handles encapsulate a single
connection to a database
• Database handles are created via a driver handle
for the desired database type. This is done via
the DBI->connect() method
$dbh = DBI->connect( … );
DBI: The Neophyte's Guide
11
Statement Handles
• Statement Handles encapsulate a statement to
be issued to the database
• Statement handles are created via a database
handle. That is, a statement is issued to the
database represented by the database handle
$sth = $dbh->prepare( “SELECT yadda FROM blah” );
• Statement handles also allow you to fetch data
from the issued statement from the database
DBI: The Neophyte's Guide
12
Handle Naming Conventions
• Throughout the example code in this
presentation and other DBI texts, you might see
the following naming convention of variables
– $drh = Driver Handle
– $dbh = Database Handle
– $sth = Statement Handle
DBI: The Neophyte's Guide
13
Starting Out with DBI
•
•
•
•
•
•
Initializing the DBI
Data Sources
Checking Available Drivers
Connecting to the Database
Disconnecting from the Database
Example
DBI: The Neophyte's Guide
14
Initializing the DBI
• DBI can be initialized very simply by adding
use DBI;
to your scripts
• DBI internally handles all driver loading
automatically and DBDs should not normally be
explicitly use’d or require’d in your scripts
DBI: The Neophyte's Guide
15
Data Sources
• In summary, Data Source specification is about
the most non-portable aspect of DBI
• In reality, a well-written DBI script that uses
standard SQL can be ported by simply changing
the DSN in the DBI->connect() call
DBI: The Neophyte's Guide
16
Connecting to the Database
• The very first database operation you must do!
• Use the DBI->connect()method
• For example
$dbh = DBI->connect( ‘dbi:Oracle:DEV’,
‘user’, ‘pass’ );
• That is, we minimally specify the Data Source
Name of the database, a username and password
• If successful, this will return a valid database
handle
DBI: The Neophyte's Guide
17
Disconnecting from the Database
• Once all work has been completed, you must
disconnect from the database
• Frees any used database and system resources
• Achieved by calling disconnect() against a valid
database handle. For example:
$dbh->disconnect();
DBI: The Neophyte's Guide
18
Example
• This example connects to a single Oracle database
called DEV
#!/usr/bin/perl -w
### Load the DBI module
use DBI;
### Perform the connection using the Oracle driver
$dbh = DBI->connect( "dbi:Oracle:DEV", "username",
"password" )
or die "Can't connect to Oracle database: $DBI::errstr\n";
### Disconnect from the database
$dbh->disconnect();
exit;
DBI: The Neophyte's Guide
19
Example
• This example demonstrates connecting to two
different databases of different types
#!/usr/bin/perl -w
### Load the DBI module
use DBI;
### Perform the connection using the Oracle driver
$dbh1 = DBI->connect( "dbi:Oracle:DEV", "username", "password" )
or die "Can't connect to Oracle database: $DBI::errstr\n";
$dbh2 = DBI->connect( "dbi:mSQL:host:dbname:1114", "username", "password" )
or die "Can't connect to mSQL database: $DBI::errstr\n";
$dbh1->disconnect();
$dbh2->disconnect();
exit;
DBI: The Neophyte's Guide
20
Interacting with the Database
• Simple Queries
– Preparing Statements
– Executing Statements
– Fetching Data
• Non-SELECT Statements
• Bind Values
• Optimizing $dbh->do()
DBI: The Neophyte's Guide
21
Simple Queries
• The process of retrieving data from the database
is a 4-stage cycle
–
–
–
–
Preparing the statement
Executing the statement
Fetching the data
Finishing the statement
DBI: The Neophyte's Guide
22
Preparing the Statement
• The $dbh->prepare( $statement ) method is
used
• This can mean different things depending on the
underlying DBD used. Oracle will use this stage
to send the statement to the database for
parsing. However, mSQL does nothing here at
all
• The SQL statement is simply a Perl string and
therefore can be supplied as a literal string or as
a variable holding a built on-the-fly statement
DBI: The Neophyte's Guide
23
Preparing the Statement ( cont. )
#!/usr/bin/perl -w
### Load the DBI module
use DBI;
### The database handle
$dbh = DBI->connect( "dbi:Oracle:DEV", "username", "password" );
### Prepare the statement handle
$sth = $dbh->prepare( "SELECT id, name FROM megaliths" );
...
exit;
DBI: The Neophyte's Guide
24
Executing the Statement
• Once the statement has been successfully
prepared, it must be executed
• Depending on the database, this stage typically
executes the prepared statement within the
database and generates a result set ready for
fetching
• Simply a case of calling $sth->execute()
against your prepared statement handle
DBI: The Neophyte's Guide
25
Fetching the Data
• The fetch stage retrieves the data from the
database row-by-row by use of a cursor
• The most commonly used fetch style is to
retrieve the data as a Perl list via
@row = $sth->fetchrow_array()
• You should continuously loop calling
fetchrow_array() until all rows are returned
DBI: The Neophyte's Guide
26
Fetching Data - Example
#/usr/bin/perl -w
### Load the DBI module
use DBI;
### Connect to the database
$dbh = DBI->connect( ‘dbi:Oracle:DEV’, ‘username’, ‘password’ );
### Prepare and execute the statement
$sth = $dbh->prepare( “SELECT name, type FROM megaliths” );
$sth->execute();
### Fetch the data
while ( @row = $sth->fetchrow_array() ) {
print "Megalith site $row[0] is a $row[1]\n";
}
### Disconnect from the database
$dbh->disconnect();
exit;
DBI: The Neophyte's Guide
27
Fetching the Data
• There are additional methods for fetching data
from the statement handle:
fetchrow_arrayref()
fetchrow_hashref()
DBI: The Neophyte's Guide
28
Finishing the Statement
• DBI automatically marks the statement handles
as inactive once all the data has been fetched
from it
• You can manually mark statement handles as
being inactive via the $sth->finish() method,
but you should usually not need to invoke this
method in your scripts
DBI: The Neophyte's Guide
29
Non-SELECT Statements
• Statements other than queries can be issued to
the database via DBI
• For example, UPDATE, DELETE and INSERT
statements
• These statements are issued by calling
$dbh->do()
which combines preparation and execution in a
single step
DBI: The Neophyte's Guide
30
Non-SELECT Statements ( cont. )
• For example:
$dbh->do( “DELETE FROM megaliths” );
$dbh->do( “DROP TABLE megaliths” );
$dbh->do( “INSERT INTO megaliths VALUES ( 1, ‘Stonehenge’,
‘Wiltshire’ )” );
DBI: The Neophyte's Guide
31
Bind Values
• A bind value is a value that can be bound to a
placeholder declared within a SQL statement
• This is similar to creating an on-the-fly SQL
statement such as:
$sth = $dbh->prepare( "
SELECT name, location
FROM megaliths
WHERE name = " . $dbh->quote( $siteName ) . "
" );
DBI: The Neophyte's Guide
32
Bind Values ( cont. )
• However, instead of interpolating the generated
value into the SQL statement, you specify a
placeholder and then bind the generated value
to that
• For example:
$sth = $dbh->prepare( "
SELECT name, location
FROM megaliths
WHERE name = ?
" );
$sth->bind_param( 1, $dbh->quote( ‘Avebury’ ) );
DBI: The Neophyte's Guide
33
Optimizing $dbh->do()
• The default implementation of $dbh->do() calls
prepare(), execute() and finish()for each
invocation of $dbh->do()
• This can be optimized for repeated iterations by
hand-preparing the statement but repeatedly
executing the same statement handle
• This is very efficient when used with bind
values
DBI: The Neophyte's Guide
34
Optimizing $dbh->do()
### Prepare the statement handle
$sth = $dbh->prepare( “DELETE FROM megaliths WHERE id = ?” );
### Remove the first 100 rows one-by-one...
$loopCounter = 0;
while ( $loopCounter < 100 ) {
$sth->execute( $loopCounter );
$loopCounter++;
}
$sth->finish();
...
• This is far faster than repeatedly preparing the same
statement over and over again
DBI: The Neophyte's Guide
35