Programming the Perl DBI by Bunce, Tim -- Read -- Imperial Library of Trantor

Index

Programming the Perl DBI

SPECIAL OFFER: Upgrade this ebook with O’Reilly A Note Regarding Supplemental Files Preface

Resources Typographical Conventions How to Contact Us Code Examples Acknowledgments

1. Introduction

From Mainframes to Workstations Perl DBI in the Real World A Historical Interlude andStanding Stones

2. Basic Non-DBI Databases

Storage Managers and Layers Query Languages and Data Functions Standing Stones and the Sample Database Flat-File Databases

Querying Data Inserting Data Updating Data Deleting Data

Putting Complex Data into Flat Files

The Perl Data::Dumper Module The Storable Module Summary of Flat-File Databases

Concurrent Database Access and Locking DBM Files and the BerkeleyDatabase Manager

Creating a New Database Locking Strategies Inserting and Retrieving Values

Localized storage and retrieval Packing in Perl objects Object accessor methods Querying limitations of DBM files and hashtables Chaining multiple values into a hash

Deleting Values

The MLDBM Module Summary

3. SQL and Relational Databases

The Relational Database Methodology Datatypes and NULL Values Querying Data

Simple Queries Queries and Condition Clauses Queries over Multiple Tables Grouping and Ordering Data

Ordering data Grouping data

Modifying Data Within Tables

Inserting Data

Using INSERT for data transfers

Deleting Data Updating Data Committing and Rolling Back Modifications

Creating and Destroying Tables

4. Programming with the DBI

DBI Architecture Handles

Driver Handles Database Handles Statement Handles

Data Source Names Connection and Disconnection

Connection Disconnection

Error Handling

Automatic Versus Manual Error Checking

Manual error checking Automatic error checking Mixed error checking

Error Diagnostics

Utility Methods and Functions

Database-Specific Quote Handling Tracing DBI Execution Neat and Tidy Formatting Numeric Testing

5. Interacting with the Database

Issuing Simple Queries

Preparing SQL Statements

Constructing “on-the-fly” statements

Executing Select Statements Fetching Data

A quick way to fetch and print

Finishing a Data Fetch Early Deallocating Statement Handles

Executing Non-SELECT Statements Binding Parameters to Statements

Bind Values Versus Interpolated Statements Bind Values and Data Typing Binding Input and Output Parameters Binding Values Without bind_ param( )

Binding Output Columns do( ) Versus prepare( ) Atomic and Batch Fetching

Atomic Fetching Batch Fetching

No arguments Slice array reference argument Slice hash reference argument

6. Advanced DBI

Handle Attributes and Metadata

Passing Attributes to DBI Methods Connecting with Attributes The Significance of Case Common Attributes Database Handle Attributes Database Metadata Statement Handle Attributes or Statement Metadata

Handling LONG/LOB Data

Inserting and Updating LONG/LOB Columns

Transactions, Locking, and Isolation

Automatic Transaction Handling Forcing a Commit Rolling Back Changes Disconnecting, One Way or Another Combining Automatic Error Handling with Transactions

7. ODBC and the DBI

ODBC—Embraced and Extended DBI—Thrashed and Mutated The Nuts and Bolts of ODBC

A Standard SQL Syntax Standard Error Codes Rich Metadata Many Attributes and Options

ODBC from Perl

DBD::ODBC Win32::ODBC

The Marriage of DBI and ODBC Questions and Choices Moving Between Win32::ODBC and the DBI And What About ADO?

8. DBI Shell and Database Proxying

dbish—The DBI Shell

Starting Up dbish Handling Statements Some Miscellaneous dbish Commands

Database Proxying

The Database Proxy Architecture

Setting up a proxy server Connecting to the proxy server

Advanced Topics

Access configuration Compression Ciphers

A. DBI Specification

Synopsis

Getting Help

Description

Architecture of a DBI Application Notation and Conventions Outline Usage General Interface Rules and Caveats Naming Conventions and Name Space SQL—A Query Language Placeholders and Bind Values

Null values Performance

The DBI Class

DBI Class Methods

connect connect_cached (NEW ) available_drivers data_sources trace

DBI Utility Functions

neat neat_list looks_like_number

DBI Dynamic Attributes

Methods Common to All Handles

err errstr state trace trace_msg func

Attributes Common to All Handles

Warn ( boolean, inherited) Active ( boolean, read-only) Kids (integer, read-only) ActiveKids (integer, read-only) CachedKids (hash ref) CompatMode ( boolean, inherited) InactiveDestroy ( boolean) PrintError ( boolean, inherited) RaiseError ( boolean, inherited) ChopBlanks ( boolean, inherited) LongReadLen (unsigned integer, inherited) LongTruncOk ( boolean, inherited) Taint ( boolean, inherited) private_*

DBI Database Handle Objects

Database Handle Methods

do selectrow_array selectall_arrayref selectcol_arrayref prepare prepare_cached commit rollback disconnect ping table_info (NEW ) tables (NEW ) type_info_all (NEW ) type_info (NEW ) quote

Database Handle Attributes

AutoCommit ( boolean)

Databases that don’t support transactions at all Databases in which a transaction is always active Databases in which a transaction must be explicitly started

Driver ( handle) Name (string) RowCacheSize (integer) (NEW )

DBI Statement Handle Objects

Statement Handle Methods

bind_ param bind_param_inout execute fetchrow_arrayref fetchrow_array fetchrow_hashref fetchall_arrayref finish rows bind_col bind_columns dump_results

Statement Handle Attributes

NUM_OF_FIELDS (integer, read-only) NUM_OF_PARAMS (integer, read-only) NAME (array-ref, read-only) NAME_lc (array-ref, read-only) NAME_uc (array-ref, read-only) TYPE (array-ref, read-only) (NEW ) PRECISION (array-ref, read-only) (NEW ) SCALE (array-ref, read-only) (NEW ) NULLABLE (array-ref, read-only) CursorName (string, read-only) Statement (string, read-only) (NEW ) RowsInCache (integer, read-only)

Further Information

Threads and Thread Safety Signal Handling and Canceling Operations