Preface

The DBI is the standard database interface for the Perl developing language. The DBI is database-independent, which means that it can work with just about any database, such as Oracle, Sybase, Informix, Access, MySQL, etc.

While we assume that readers of this tutorial have some experience with Perl, we don't assume much familiarity with databases themselves. The tutorial starts out slowly, describing different types of databases and introducing the reader to common terminology.

This tutorial is not solely about the DBI -- it also concerns the more general subject of storing data in and retrieving data from databases of various forms. As such, this tutorial is split into two related, but standalone, parts. The first part covers techniques for storing and retrieving data without the DBI, and the second, much larger part, covers the use of the DBI and related technologies.

Throughout the tutorial, we assume that you have a basic grounding in developing with Perl and can put together simple scripts without instruction. If you don't have this level of Perl awareness, we suggest that you read some of the Perl tutorials listed in "Resources ".

Once you're ready to read this tutorial, there are some shortcuts that you can take depending on what you're most interested in reading about. If you are interested solely in the DBI, you can skip "Basic Non-DBI Databases " without too much of a problem. On the other hand, if you're a wizard with SQL, then you should probably skip "SQL and Relational Databases " to avoid the pain of us glossing over many fine details. "ODBC and the DBI " is a comparison between the DBI and ODBC and is mainly of interest to database geeks, design aficionados, and those people who have Win32::ODBC applications and are desperately trying to port them to DBI.

Here's a rundown of the tutorial, chapter by chapter:

• "Introduction "
• This introduction sets up the general feel for the tutorial.
• "Basic Non-DBI Databases "
• This chapter covers the basics of storing and retrieving data either with core Perl functions through the use of delimited or fixed-width flat-file databases, or via non-DBI modules such as AnyDBM_File, Storable, Data::Dumper and friends. Although the DBI isn't used in this chapter, the way the Storable and Data::Dumper modules are used to pack Perl data structures into strings can easily be applied to the DBI.
• "SQL and Relational Databases "
• This chapter is a basic overview of SQL and relational databases and how you can write simple but powerful SQL statements to query and manipulate your database. If you already know some SQL, you can skip this chapter. If you don't know SQL, we advise you to read this chapter since the later chapters assume you have a basic knowledge of SQL and relational databases.
• "Developing with the DBI "
• This chapter introduces the DBI to you by discussing the architecture of the DBI and basic DBI operations such as connecting to databases and handling errors. This chapter is essential reading and describes the framework that the DBI provides to let you write simple, powerful, and robust programs.
• "Interacting with the Database "
• This chapter is the meat of the DBI topic and discusses manipulating the data within your database -- that is, retrieving data already stored in your database, inserting new data, and deleting and updating existing data. We discuss the various ways in which you can perform these operations from the simple "get it working" stage to more advanced and optimized techniques for manipulating data.
• "Advanced DBI "
• This chapter covers more advanced topics within the sphere of the DBI such as specifying attributes to fine-tune the operation of DBI within your applications, working with LONG/LOB datatypes, statement and database metadata, and finally transaction handling.
• "ODBC and the DBI "
• This chapter discusses the differences in design between DBI and ODBC, the other portable database API. And, of course, this chapter highlights why DBI is easier to program with.
• "DBI Shell and Database Proxying "
• This chapter covers two topics that aren't exactly part of the core DBI, per se, but are extremely useful to know about. First, we discuss the DBI shell, a command-line tool that allows you to connect to databases and issue arbitrary queries. Second, we discuss the proxy architecture that the DBI can use, which, among other things, allows you to connect scripts on one machine to databases on another machine without needing to install any database networking software. For example, you can connect a script running on a Unix box to a Microsoft Access database running on a Microsoft Windows box.
• Appendix A, "DBI Specification "
• This appendix contains the DBI specification, which is distributed with DBI.pm.
• Appendix B, "Driver and Database Characteristics "
• This appendix contains useful extra information on each of the commonly used DBDs and their corresponding databases.
• Appendix C, "ASLaN Sacred Site Charter"
• This appendix contains the charter for the Ancient Sacred Landscape Network, which focuses on preserving sites such as the megalithic sites used for examples in this tutorial.

Resources

To help you navigate some of the topics in this tutorial, here are some resources that you might want to check out before, during, and after reading this tutorial:

• http://www.symbolstone.org/technology/perl/DBI
• The DBI home page. This site contains lots of useful information about DBI and where to get the various modules from. It also has links to the very active dbi-users mailing list and archives.
• http://www.perl.com/CPAN
• This site includes the Comprehensive Perl Archive Network multiplexer, upon which you find a whole host of useful modules including the DBI.
• An Introduction to Database Systems, by C. J. Date
• This tutorial is the standard texttutorial on database systems and is highly recommended reading.
• Guide to the SQL Standard, by C. J. Date and Hugh Darwen
• An excellent tutorial that's detailed but small and very readable.
• http://w3.one.net/~jhoffman/sqltut.htm
• http://www.jcc.com/SQLPages/jccs_sql.htm
• http://www.contrib.andrew.cmu.edu/~shadow/sql.html
• These websites contain information, specifications, and links on the SQL query language, of which we present a primer in "SQL and Relational Databases ". Further information can be found by entering "SQL tutorial" or similar expressions into your favorite web search engine.
• Learning Perl, by Randal Schwartz and Tom Christiansen
• A hands-on tutorial designed to get you writing useful Perl scripts as quickly as possible. Exercises (with complete solutions) accompany each chapter. A lengthy new chapter introduces you to CGI developing, while touching also on the use of library modules, references, and Perl's object-oriented constructs.
• Developing Perl, by Larry Wall, Tom Christiansen, and Randal Schwartz
• The authoritative guide to Perl version 5, the scripting utility that has established itself as the developing tool of choice for the World Wide Web, Unix system administration, and a vast range of other applications. Version 5 of Perl includes object-oriented developing facilities. The tutorial is coauthored by Larry Wall, the creator of Perl.
• The Perl tutorial, by Tom Christiansen and Nathan Torkington
• A comprehensive collection of problems, solutions, and practical examples for anyone developing in Perl. Topics range from beginner questions to techniques that even the most experienced of Perl developers will learn from. More than just a collection of tips and tricks, The Perl tutorial is the long-awaited companion volume to Developing Perl, filled with previously unpublished Perl arcana.
• Writing Apache Modules with Perl and C, by Lincoln Stein and Doug MacEachern
• This tutorial teaches you how to extend the capabilities of your Apache web server regardless of whether you use Perl or C as your developing language. The tutorial explains the design of Apache, mod_perl, and the Apache API. From a DBI perspective, it discusses the Apache::DBI module, which provides advanced DBI functionality in relation to web services such as persistent connection pooling optimized for serving databases over the Web.
• Boutell FAQ (http://www.boutell.com/faq/) and others
• These links are invaluable to you if you want to deploy DBI-driven websites. They explain the dos and don'ts of CGI developing in general.
• MySQL & mSQL, by Randy Jay Yarger, George Reese, and Tim King
• For users of the MySQL and mSQL databases, this is a very useful tutorial. It covers not only the databases themselves but also the DBI drivers and other useful topics like CGI developing.