O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Programming the Perl DBI

Programming the Perl DBI

by Alligator Descartes, Tim Bunce
Publisher: O'Reilly Media, Inc.
Release Date: February 2000
ISBN: 1565926994
View table of contents
Start reading

Book Description

One of the greatest strengths of the Perl programming language is its ability to manipulate large amounts of data. Database programming is therefore a natural fit for Perl, not only for business applications but also for CGI-based web and intranet applications.The primary interface for database programming in Perl is DBI. DBI is a database-independent package that provides a consistent set of routines regardless of what database product you use--Oracle, Sybase, Ingres, Informix, you name it. The design of DBI is to separate the actual database drivers (DBDs) from the programmer's API, so any DBI program can work with any database, or even with multiple databases by different vendors simultaneously.Programming the Perl DBI is coauthored by Alligator Descartes, one of the most active members of the DBI community, and by Tim Bunce, the inventor of DBI. For the uninitiated, the book explains the architecture of DBI and shows you how to write DBI-based programs. For the experienced DBI dabbler, this book reveals DBI's nuances and the peculiarities of each individual DBD.The book includes:

  • An introduction to DBI and its design
  • How to construct queries and bind parameters
  • Working with database, driver, and statement handles
  • Debugging techniques
  • Coverage of each existing DBD
  • A complete reference to DBI
This is the definitive book for database programming in Perl.

Table of Contents

  1. Dedication
  2. A Note Regarding Supplemental Files
  3. Preface
    1. Resources
    2. Typographical Conventions
    3. How to Contact Us
    4. Code Examples
    5. Acknowledgments
  4. 1. Introduction
    1. From Mainframes to Workstations
    2. Perl
    3. DBI in the Real World
    4. A Historical Interlude andStanding Stones
  5. 2. Basic Non-DBI Databases
    1. Storage Managers and Layers
    2. Query Languages and Data Functions
    3. Standing Stones and the Sample Database
    4. Flat-File Databases
      1. Querying Data
      2. Inserting Data
      3. Updating Data
      4. Deleting Data
    5. Putting Complex Data into Flat Files
      1. The Perl Data::Dumper Module
      2. The Storable Module
      3. Summary of Flat-File Databases
    6. Concurrent Database Access and Locking
    7. DBM Files and the BerkeleyDatabase Manager
      1. Creating a New Database
      2. Locking Strategies
      3. Inserting and Retrieving Values
        1. Localized storage and retrieval
        2. Packing in Perl objects
        3. Object accessor methods
        4. Querying limitations of DBM files and hashtables
        5. Chaining multiple values into a hash
      4. Deleting Values
    8. The MLDBM Module
    9. Summary
  6. 3. SQL and Relational Databases
    1. The Relational Database Methodology
    2. Datatypes and NULL Values
    3. Querying Data
      1. Simple Queries
      2. Queries and Condition Clauses
      3. Queries over Multiple Tables
      4. Grouping and Ordering Data
        1. Ordering data
        2. Grouping data
    4. Modifying Data Within Tables
      1. Inserting Data
        1. Using INSERT for data transfers
      2. Deleting Data
      3. Updating Data
      4. Committing and Rolling Back Modifications
    5. Creating and Destroying Tables
  7. 4. Programming with the DBI
    1. DBI Architecture
    2. Handles
      1. Driver Handles
      2. Database Handles
      3. Statement Handles
    3. Data Source Names
    4. Connection and Disconnection
      1. Connection
      2. Disconnection
    5. Error Handling
      1. Automatic Versus Manual Error Checking
        1. Manual error checking
        2. Automatic error checking
        3. Mixed error checking
      2. Error Diagnostics
    6. Utility Methods and Functions
      1. Database-Specific Quote Handling
      2. Tracing DBI Execution
      3. Neat and Tidy Formatting
      4. Numeric Testing
  8. 5. Interacting with the Database
    1. Issuing Simple Queries
      1. Preparing SQL Statements
        1. Constructing “on-the-fly” statements
      2. Executing Select Statements
      3. Fetching Data
        1. A quick way to fetch and print
      4. Finishing a Data Fetch Early
      5. Deallocating Statement Handles
    2. Executing Non-SELECT Statements
    3. Binding Parameters to Statements
      1. Bind Values Versus Interpolated Statements
      2. Bind Values and Data Typing
      3. Binding Input and Output Parameters
      4. Binding Values Without bind_ param( )
    4. Binding Output Columns
    5. do( ) Versus prepare( )
    6. Atomic and Batch Fetching
      1. Atomic Fetching
      2. Batch Fetching
        1. No arguments
        2. Slice array reference argument
        3. Slice hash reference argument
  9. 6. Advanced DBI
    1. Handle Attributes and Metadata
      1. Passing Attributes to DBI Methods
      2. Connecting with Attributes
      3. The Significance of Case
      4. Common Attributes
      5. Database Handle Attributes
      6. Database Metadata
      7. Statement Handle Attributes or Statement Metadata
    2. Handling LONG/LOB Data
      1. Inserting and Updating LONG/LOB Columns
    3. Transactions, Locking, and Isolation
      1. Automatic Transaction Handling
      2. Forcing a Commit
      3. Rolling Back Changes
      4. Disconnecting, One Way or Another
      5. Combining Automatic Error Handling with Transactions
  10. 7. ODBC and the DBI
    1. ODBC—Embraced and Extended
    2. DBI—Thrashed and Mutated
    3. The Nuts and Bolts of ODBC
      1. A Standard SQL Syntax
      2. Standard Error Codes
      3. Rich Metadata
      4. Many Attributes and Options
    4. ODBC from Perl
      1. DBD::ODBC
      2. Win32::ODBC
    5. The Marriage of DBI and ODBC
    6. Questions and Choices
    7. Moving Between Win32::ODBC and the DBI
    8. And What About ADO?
  11. 8. DBI Shell and Database Proxying
    1. dbish—The DBI Shell
      1. Starting Up dbish
      2. Handling Statements
      3. Some Miscellaneous dbish Commands
    2. Database Proxying
      1. The Database Proxy Architecture
        1. Setting up a proxy server
        2. Connecting to the proxy server
      2. Advanced Topics
        1. Access configuration
        2. Compression
        3. Ciphers
  12. A. DBI Specification
    1. Synopsis
      1. Getting Help
    2. Description
      1. Architecture of a DBI Application
      2. Notation and Conventions
      3. Outline Usage
      4. General Interface Rules and Caveats
      5. Naming Conventions and Name Space
      6. SQL—A Query Language
      7. Placeholders and Bind Values
        1. Null values
        2. Performance
    3. The DBI Class
      1. DBI Class Methods
      2. DBI Utility Functions
      3. DBI Dynamic Attributes
    4. Methods Common to All Handles
    5. Attributes Common to All Handles
    6. DBI Database Handle Objects
      1. Database Handle Methods
      2. Database Handle Attributes
        1.  
        2. Databases that don’t support transactions at all
        3. Databases in which a transaction is always active
        4. Databases in which a transaction must be explicitly started
    7. DBI Statement Handle Objects
      1. Statement Handle Methods
        1.  
      2. Statement Handle Attributes
    8. Further Information
      1. Threads and Thread Safety
      2. Signal Handling and Canceling Operations
    9. See Also
      1. Driver and Database Documentation
      2. Books and Journals
      3. Manual Pages
      4. Mailing List
      5. Assorted Related WWW Links
      6. FAQ
    10. Authors
    11. Copyright
    12. Acknowledgments
    13. Translations
    14. Support/ Warranty
    15. Training
  13. B. Driver and Database Characteristics
    1. Acquiring the DBI and Drivers
    2. DBD::ADO
    3. DBD::CSV
    4. DBD::DB2
    5. DBD::Empress and DBD::EmpressNet
    6. DBD::Informix
    7. DBD::Ingres
    8. DBD::InterBase
    9. DBD::mysql and DBD::mSQL
    10. DBD::ODBC
    11. DBD::Oracle
    12. DBD::Pg
    13. DBD::SearchServer
    14. DBD::Sybase—For Sybase and Microsoft SQL Server
    15. DBD::XBase
  14. C. ASLaN Sacred Site Charter
  15. Index
  16. About the Authors
  17. Colophon
  18. Copyright