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

12 reviews
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. Programming the Perl DBI
    1. SPECIAL OFFER: Upgrade this ebook with O’Reilly
    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
          1. connect
          2. connect_cached (NEW )
          3. available_drivers
          4. data_sources
          5. trace
        2. DBI Utility Functions
          1. neat
          2. neat_list
          3. looks_like_number
        3. DBI Dynamic Attributes
      4. Methods Common to All Handles
        1. err
        2. errstr
        3. state
        4. trace
        5. trace_msg
        6. func
      5. Attributes Common to All Handles
        1. Warn ( boolean, inherited)
        2. Active ( boolean, read-only)
        3. Kids (integer, read-only)
        4. ActiveKids (integer, read-only)
        5. CachedKids (hash ref)
        6. CompatMode ( boolean, inherited)
        7. InactiveDestroy ( boolean)
        8. PrintError ( boolean, inherited)
        9. RaiseError ( boolean, inherited)
        10. ChopBlanks ( boolean, inherited)
        11. LongReadLen (unsigned integer, inherited)
        12. LongTruncOk ( boolean, inherited)
        13. Taint ( boolean, inherited)
        14. private_*
      6. DBI Database Handle Objects
        1. Database Handle Methods
          1. do
          2. selectrow_array
          3. selectall_arrayref
          4. selectcol_arrayref
          5. prepare
          6. prepare_cached
          7. commit
          8. rollback
          9. disconnect
          10. ping
          11. table_info (NEW )
          12. tables (NEW )
          13. type_info_all (NEW )
          14. type_info (NEW )
          15. quote
        2. Database Handle Attributes
            1. AutoCommit ( boolean)
          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
            1. Driver ( handle)
            2. Name (string)
            3. RowCacheSize (integer) (NEW )
      7. DBI Statement Handle Objects
        1. Statement Handle Methods
            1. bind_ param
            2. bind_param_inout
            3. execute
            4. fetchrow_arrayref
            5. fetchrow_array
            6. fetchrow_hashref
            7. fetchall_arrayref
            8. finish
            9. rows
            10. bind_col
            11. bind_columns
            12. dump_results
        2. Statement Handle Attributes
          1. NUM_OF_FIELDS (integer, read-only)
          2. NUM_OF_PARAMS (integer, read-only)
          3. NAME (array-ref, read-only)
          4. NAME_lc (array-ref, read-only)
          5. NAME_uc (array-ref, read-only)
          6. TYPE (array-ref, read-only) (NEW )
          7. PRECISION (array-ref, read-only) (NEW )
          8. SCALE (array-ref, read-only) (NEW )
          9. NULLABLE (array-ref, read-only)
          10. CursorName (string, read-only)
          11. Statement (string, read-only) (NEW )
          12. RowsInCache (integer, read-only)
      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
        1. DBD::ADO
      3. DBD::CSV
        1. DBD::CSV
      4. DBD::DB2
        1. DBD::DB2
      5. DBD::Empress and DBD::EmpressNet
        1. DBD::Empress and DBD::EmpressNet
      6. DBD::Informix
        1. DBD::Informix
      7. DBD::Ingres
        1. DBD::Ingres
      8. DBD::InterBase
        1. DBD::InterBase
      9. DBD::mysql and DBD::mSQL
        1. DBD::mysql and DBD::mSQL
      10. DBD::ODBC
        1. DBD::ODBC
      11. DBD::Oracle
        1. DBD::Oracle
      12. DBD::Pg
        1. DBD::Pg
      13. DBD::SearchServer
        1. DBD::SearchServer
      14. DBD::Sybase—For Sybase and Microsoft SQL Server
        1. DBD::Sybase—For Sybase and Microsoft SQL Server
      15. DBD::XBase
        1. DBD::XBase
    14. C. ASLaN Sacred Site Charter
    15. Index
    16. About the Authors
    17. Colophon
    18. SPECIAL OFFER: Upgrade this ebook with O’Reilly