Perl DBI模块的例子

 

Perl DBI Examples Contents Basics Connecting to the database Options Issuing SQL Intermediate SELECT statements Advanced Transactions Notes Other Resources The purpose of this document is to provide some examples for using the Perl DBI modules to access DBMaker databases. The motivation for this is to relieve some of the FAQ traffic on the DBI mailing list. Over the course of this document, we will build a robust DBI program to access a DBMaker database. We will start with DBI basics, then introduce concepts to improve performance and reliability. Basics The first thing we need to do is build and install DBI. Instructions for this are in the DBI INSTALL document. Then we will need to build a database driver, or DBD. Instructions for building the DBDs are also included with each package. As with most Perl modules, installing DBI/DBD is usually as easy as localhost:/opt/src/perl_modules/DBI-1.13$ perl Makefile.PL && make && make test && make install After DBI and a DBD are installed properly, you can get (a lot) more information by issuing: localhost:~$ perldoc DBI Connecting to the database Connecting to different databases requires different techniques. For exhaustive information, be sure to read the documentation that comes with your DBD. This example will cover connecting to DBMaker. use strict; use DBI; my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', ); The connect string above takes three arguments: a data source name, a username, and a password. The DSN is in the form dbi:DriverName:instance. But how do we know if the connect succeeded or not? First, connect will return a true value on success, untrue otherwise. Second, DBI will place an error message in the package variable $DBI::errstr. use strict; use DBI; my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', ) || die "Database connection not made: $DBI::errstr"; $dbh->disconnect(); Using the disconnect() method will avoid the error "Database handle destroyed without explicit disconnect". Options The connect() method can take a hash of options. Often-used options include: AutoCommit, which when true will automatically commit database transactions; RaiseError, which tells DBI to croak $DBI::errstr upon errors; and PrintError, which tells DBI to warn $DBI::errstr. In this program, we will want to use transactions, so we will turn AutoCommit off, RaiseError on, and leave PrintError at its default of on. use strict; use DBI; my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; $dbh->disconnect(); Note that setting AutoCommit off with a database that doesn't support transactions will result in a fatal error. Issuing SQL Now we are ready to do something useful with our database. There are two ways to get an SQL statement to your database. For queries which return rows, such as SELECT, we will use the prepare method. For other queries, such as CREATE and DELETE, we will use the do method. Let's stick to the latter for now and move on to the former later. This program will create an employee table in the database. use strict; use DBI; my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; my $sql = qq{ CREATE TABLE employees ( id INTEGER NOT NULL, name VARCHAR2(128), title VARCHAR2(128), phone CHAR(8) ) }; $dbh->do( $sql ); $dbh->commit(); $dbh->disconnect(); Intermediate We have seen how to connect to the database, detect errors, and issue simple SQL statements. Now let's move on to some more useful code. SELECT Statements The SELECT statement is probably the most often used statement in SQL. To use SELECT, we will first prepare the statement and then execute it. In the following code, the $sth is the statement handle, which we will use to access the result of the SELECT. use strict; use DBI; my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; my $sql = qq{ SELECT * FROM employees }; my $sth = $dbh->prepare( $sql ); $sth->execute(); $dbh->disconnect(); The listing above will cause the database to make an execution plan for the statement, then execute that statement. It doesn't actually do anything with the rows returned. In the next listing, we use bind_columns to get the records out of the database. bind_columns binds each column to a scalar reference. When fetch is called, those scalars are filled with the values from the database. use strict; use DBI; my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; my $sql = qq{ SELECT id, name, title, phone FROM employees }; my $sth = $dbh->prepare( $sql ); $sth->execute(); my( $id, $name, $title, $phone ); $sth->bind_columns( undef, /$id, /$name, /$title, /$phone ); while( $sth->fetch() ) { print "$name, $title, $phone/n"; } $sth->finish(); $dbh->disconnect(); That's a nice program for printing out a company phone book, but how about a WHERE clause? We will use bind_param to prepare an SQL statement one time, and execute it several times very quickly. use strict; use DBI; my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; my @names = ( "Larry%", "Tim%", "Randal%", "Doug%" ); my $sql = qq{ SELECT id, name, title, phone FROM employees WHERE name LIKE ? }; my $sth = $dbh->prepare( $sql ); for( @names ) { $sth->bind_param( 1, $_, SQL_VARCHAR ); $sth->execute(); my( $id, $name, $title, $phone ); $sth->bind_columns( undef, /$id, /$name, /$title, /$phone ); while( $sth->fetch() ) { print "$name, $title, $phone/n"; } } $sth->finish(); $dbh->disconnect(); Advanced Transactions So far, we haven't done anything that would require transactions, but if we need to issue UPDATE or DELETE statements, we will want to use them. The best way to implement robust transactions with DBI, according to the DBI documentation, is to use eval{...} blocks to trap errors, then use commit or rollback to finish the transaction. That is what we will do in the following listings. This program loads four records into our database. use strict; use DBI qw(:sql_types); my $dbh = DBI->connect( 'dbi:DBMaker:dbsample', 'jerry', 'jerrypassword', { RaiseError => 1, AutoCommit => 0 } ) || die "Database connection not made: $DBI::errstr"; my @records = ( [ 0, "Larry Wall", "Perl Author", "555-0101" ], [ 1, "Tim Bunce", "DBI Author", "555-0202" ], [ 2, "Randal Schwartz", "Guy at Large", "555-0303" ], [ 3, "Doug MacEachern", "Apache Man", "555-0404" ] ); my $sql = qq{ INSERT INTO employees VALUES ( ?, ?, ?, ? ) }; my $sth = $dbh->prepare( $sql ); for( @records ) { eval { $sth->bind_param( 1, @$_->[0], SQL_INTEGER ); $sth->bind_param( 2, @$_->[1], SQL_VARCHAR ); $sth->bind_param( 3, @$_->[2], SQL_VARCHAR ); $sth->bind_param( 4, @$_->[3], SQL_VARCHAR ); $sth->execute(); $dbh->commit(); }; if( $@ ) { warn "Database error: $DBI::errstr/n"; $dbh->rollback(); #just die if rollback is failing } } $sth->finish(); $dbh->disconnect(); Notes The use of finish in these examples is not strictly necessary. You should call it if you are done with the statement handle but not with your program. Always use strict.