Home arrow Perl Programming arrow Page 4 - SELECT Queries and Perl

Introduction to DBI - Perl

In this third part of a four-part series on Perl and DBI, you will learn about using SELECT queries to get information from databases. This article is excerpted from chapter 15 of the book Beginning Perl (Apress; ISBN: 159059391X).

TABLE OF CONTENTS:
  1. SELECT Queries and Perl
  2. The ORDER BY Clause
  3. More Complicated SELECTs
  4. Introduction to DBI
By: Apress Publishing
Rating: starstarstarstarstar / 5
March 27, 2008

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

DBI is the Database Independent module and was written by Tim Bunce. It is a collection of APIs that allow a programmer to connect to and access a database. As the name implies, the module allows us to write programs to access a database independent of the type of database. We can write a program to query an Oracle database, or a Sybase database, or MySQL, or Postgres, or ODBC, and the list goes on and on. All we need on our computers is the DBI module and the appropriate Database Driver (DBD).


Note  Each of the mentioned databases has its own dialect of SQL. Most implement the basic commands such asINSERTandSELECT in a similar way, but when it comes to the details of specific commands, they are sometimes implemented slightly differently from database to database. Keep this in mind if you are creating a Perl script that you want to port from one type of database to anotheróuse the common form of each command even if a database has a nifty feature that you can use that is not supported elsewhere.


We are using MySQL server in this chapter, so we need to installDBIand the MySQLDBDmodules.

Installing DBI and the DBD::mysql

The first step to using DBI is to install the appropriate modules. The first module we need is DBI. As this is being written, the latest version of DBI on CPAN is 1.42, but as usual this version may not be the same by the time you read this.

Follow the instructions in Chapter 13 on installing modules to installDBI. When successful, it is time to install the MySQL driver. The name of this module isDBD::mysql.

Connecting to the MySQL Database

Our first Perl program will simply connect to the MySQL database. If it works correctly, we know that DBI and DBD::mysql were installed correctly, and the real fun can then begin. Letís look at an example ( connect.pl):

#!/usr/bin/perl -w
# connect.pl

use strict;
use DBI;

my $dbh = DBI->connect("DBI:mysql:musicians_db", "musicfan", "CrimsonKing");

die "connect failed: " . DBI->errstr() unless $dbh;

print "connect successful!\n";

$dbh->disconnect();

After the shebang, comment, anduse strict;, weusetheDBI module. Then we see a call to theDBI->connect()method. When we talked about object-oriented programming in Chapter 12, we mentioned that most modules use the methodnew()as their constructor.DBI, however, usesconnect(). This is fineóany method name that we choose can be the constructor, and since to construct aDBIobject we must connect to a database,connect()seems a logical choice.

There are three arguments toconnect(): the DSN, also known as the data source name, the username of the user, and their password. In this invocation, the data source name is

"DBI:mysql:musicians_db"

All data sources will start withDBI, followed by a colon, the termmysqlsince we are using the MySQL server andDBD::mysql, a colon, and the database to which we are connecting, heremusicians_db.


Note  Letís say that one day we want to port our database from MySQL to some other database server such as Oracle. In this script, all we need to do is change the textmysqlin the data source tooracle. Provided thatDBD::oracleis installed on our machine and we donít use any MySQL-specific queries, the script will work perfectly. Talk about portable!


The return value ofDBI->connect()is an object that we can use to do things with the database. We call this the database handler so we name it$dbh. After the call toDBI->connect(), we check the value of$dbh.

die "connect failed: " . DBI->errstr() unless $dbh;

This makes sure$dbhhas a true value. IfDBI->connect()fails, it returns a false value to$dbh, so wedie(), complaining that something went wrong with the database connection. The functionDBI->errstr()will report the error of whatever just went wrong, so as a help to the user we will include this information in the string thatdie()prints.

If all is well, we print a cheerful message and disconnect from the database.

print "connect successful!\n";

$dbh->disconnect();

We use thedisconnect()method to disconnect from the database. This is not really necessary since Perl will disconnect us when the script terminates, but it is still a polite thing to do.

Here is what happens if we execute this program:

$ perl connect.pl
connect successful!
$

Now that we can connect, it is time to execute an SQL query.

Please check back next week for the conclusion to this article.



 
 
>>> More Perl Programming Articles          >>> More By Apress Publishing
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PERL PROGRAMMING ARTICLES

- Perl Turns 25
- Lists and Arguments in Perl
- Variables and Arguments in Perl
- Understanding Scope and Packages in Perl
- Arguments and Return Values in Perl
- Invoking Perl Subroutines and Functions
- Subroutines and Functions in Perl
- Perl Basics: Writing and Debugging Programs
- Structure and Statements in Perl
- First Steps in Perl
- Completing Regular Expression Basics
- Modifiers, Boundaries, and Regular Expressio...
- Quantifiers and Other Regular Expression Bas...
- Parsing and Regular Expression Basics
- Hash Functions

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: