Apache Derby -- Off to the Races: Includes Details of IBM Cloudscape

Table of Contents

Preface.

Acknowledgments.

About the Authors.

I.

1. On Your Marks ... Get Set ... Go!!! An Introduction to the Apache Derby and IBM Cloudscape Community.

    Introduction

      If You're Not the Kind of Person Who Reads Introduction Chapters ...

    Let Me Get This Straight, Apache Derby Is IBM Cloudscape?

    Development of the Apache Derby Database-Who Can Contribute and How?

    How Can IBM Sell a Product for Profit and Contribute the Same Product to the Open Source Community?

    How an Open Source Database Like Apache Derby Can Help

      Open Source Software from a Business Perspective

      Open Source Software from a Technical Perspective

    Why the Need for a Local Data Store?

    Why Use a Relational Database?

    How the Apache Derby Platform Can Help Your Business

    A High-Level View of the Apache Derby Database

      Details on SQL Support in Apache Derby and IBM Cloudscape

    The Apache Derby Components

      The Apache Derby Database Engine

      Apache Derby Network Server

      ij-The Apache Derby JDBC Scripting Tool

    Developing Apache Derby Applications

II.

2. Deployment Options for Apache Derby Databases.

    Using the Embedded Framework for Apache Derby Applications

      Examples of the Embedded Framework for Apache Derby Applications

      Some Final Comments About the Embedded Framework for Apache Derby Applications

    Using the Server Framework for Apache Derby Applications

      Examples of the Server Framework for Apache Derby Applications

3. Apache Derby Databases.

    Introduction

    Database Structure

      Creating an Apache Derby Database

      Deleting an Apache Derby Database

      Database Contents

      Additional Database Connection Options

        Security

        Maintenance

        Environment

    Database Objects

      Tables

      Data Types

      Schemas

      Views

      Indexes

      Triggers

      Procedures

    Summary

4. Installing Apache Derby and IBM Cloudscape on Windows.

    Migration from Previous Versions of Cloudscape

    Before You Begin

      The Java Runtime Environment

      J Who? How Do I Set It Up? How Do I Know I've Got One?

        Checking the Version of the JRE on Your System

      Setting the PATH Environment Variable

    Using the Windows Installer to Install IBM Cloudscape on Windows-Attention Developers!

      IBM Cloudscape for Windows Installer Prerequisites

      Performing the Windows Installation

      Post-Installation Tasks for Graphical Installation Programs

    Installing Apache Derby or IBM Cloudscape on Windows Manually

      Creating and Setting the CLOUDSCAPE_INSTALL Environment Variable

      Setting the CLASSPATH Environment Variable

    Verifying the Installation

      Verifying a Database Installation

      Verifying a Network Server Installation

    Troubleshooting an Installation

    Odds and Ends About Your Installation

      Files on Disk

5. Installing Apache Derby and IBM Cloudscape on Linux.

    Migration from Previous Versions of Cloudscape

    Before You Begin

      The Java Runtime Environment

      J Who? How Do I Set It Up? How Do I Know I've Got One?

        Checking the Version of the JRE on Your System

      Setting the PATH Environment Variable

    Using the Linux Installer to Install IBM Cloudscape on Linux-Attention Developers!

      IBM Cloudscape for Linux Installer Prerequisites

      Performing the Linux Installation

      Post-Installation Tasks for Graphical Installation Programs

    Installing Apache Derby or IBM Cloudscape on Linux Manually

      Creating and Setting the CLOUDSCAPE_INSTALL Environment Variable

      Setting the CLASSPATH Environment Variable

    Verifying the Installation

      Verifying a Database Installation

      Verifying a Network Server Installation

    Troubleshooting an Installation

    Odds and Ends About Your Installation

      Files on Disk

6. Managing an Apache Derby Database.

    Disaster Prevention and Recovery

      Backing Up a Database

        Online Backup with Read Access (Built-in Copy)

        Online Backup with Read Access (Native Copy)

        Offline Backup

      Restoring a Database from a Backup Image

    Data Movement

      Importing Data

        Importing Data into a Table

        Importing Data into a Subset of Columns in a Table

      Exporting Data

        Exporting Data from a Single Table

        Exporting Data from the Results of a SELECT Statement

    Database Maintenance and Tuning

      Checking Database Consistency

      Investigating Performance Issues with Database Statistics

        Gathering Runtime Statistics

        Adding Timing to Runtime Statistics

      Reorganizing Data

    Summary

7. Security.

    Introduction

    Database File Security

    Database File Encryption

      Algorithm

      Feedback Mode

    User Authentication

      Enabling Authentication

      LDAP Directory Service Authentication

      User-Defined Authentication

      Built-In Authentication

        System-Level Users

        Database-Level Users

        Security Hierarchy

    Database Authorization

      Permissions

      Errors

    Summary

8. SQL.

    Introduction

    Your Momma Loves Drama Database

    Data Definition Language (DDL)

      The CREATE Statement

      The ALTER Statement

      The DECLARE Statement

      The DROP Statement

      Apache Derby Data Types

      Numeric Data Types

        Small Integer (SMALLINT)

        Integer (INTEGER)

        Big Integer (BIGINT)

        Decimal (DECIMAL/NUMERIC)

        Single-Precision Floating-Point (REAL/FLOAT)

        Double-Precision Floating-Point (DOUBLE/FLOAT)

      String Data Types

        Fixed-Length Character String (CHAR)

        Varying-Length Character String (VARCHAR)

        Varying-Length Long Character String (LONG VARCHAR)

        Character String Considerations

      Binary String Data Types

      Large Objects

        Character Large Object (CLOB)

        Binary Large Object (BLOB)

      Date and Time Data Types

        Date String (DATE)

        Time String (TIME)

        Timestamp String (TIMESTAMP)

      Creating User Tables

        Sample Table Create Statements

      Additional Data Type Modifiers

        Null Considerations

        Not Null with Default

        Identity Column

        IDENTITY_VAL_LOCAL Function

      Declared Tables

      Modifying a Table

        Adding a Column to a Table

        Adding a Constraint to a Table

        Dropping an Existing Constraint on a Table

        Altering the Size of a VARCHAR Column

        Overriding Row-Level Locking

      Removing a Table

      Constraints

      Unique Constraint

      Referential Integrity

        INSERT Rule

        DELETE Rules

        UPDATE Rules

      Check Constraints

        Adding Check Constraints

        Modifying Check Constraints

      System Catalog Tables

      Views

        Nested View Definitions

        Modifying a View

        Removing a View

      Indexes

        Unique Index and Non-Unique Index

        Referential Integrity and Indexes

        Null Values and Indexes

        General Indexing Guidelines

        Creating an Index

        Modifying an Index

        Removing an Index

    Data Retrieval

      Retrieving an Entire Table

      Projecting Columns from a Table

      Changing the Order of the Columns

      Restricting the Rows That Are Returned from a Table

      Restricting Rows Using Multiple Conditions

      Searching for String Patterns

      Searching for Data in Ranges

      Searching for a Set of Values

      Searching for Null Values

      Searching for Negative Conditions

      Selecting Columns from Multiple Tables

        Cartesian Product

        Joins

        Outer Join

        Left Outer Join

        Right Outer Join

      Using Correlation Names

      Sorting Output

      Derived Columns

      Apache Derby Functions

        Scalar Functions

        Column Functions

        Grouping Values

        Restricting Results with Column Functions

      Eliminating Duplicates

      Subqueries

      Quantified Predicates

      Case Expressions

      Nested Table Expressions

      Scalar Fullselect

      Union

      The EXISTS Predicate

    Data Modification

      Inserting Rows

      Inserting Data into Specific Columns

      Inserting Multiple Rows

      Inserting a Set of Values

      Updating Rows

      Removing Data

    Development SQL

      Schemas

      User-Defined Functions

      Triggers

        Trigger Activation

        Trigger Body

        Trigger Example-Updating Other Tables

        Trigger Example-Changing Inserted Values

        Trigger Example-Sets

      Stored Procedures

      Commit and Rollback

    Summary

III.

9. Developing Apache Derby Applications with JDBC.

    Introduction

    JBDC Program Structure

    JDBC Imports

    Using Embedded JDBC Drivers

    IBM DB2 JDBC Universal Driver for Derby

    Establishing a Database Connection

    Connecting with DB2 JDBC Drivers

      Server Name

      Port Number

      Database Name

      Apache Derby Attributes

      Universal Driver Attributes

    Allocating Statements

      resultSetType and resultSetCurrency

      Closing Statements

      Additional Statement Options

    Executing Dynamic SQL

    Trapping SQLExceptions

      getMessage()

      getSQLState()

      getErrorCode()

      Multiple SQLExceptions

    SQLWarnings Versus SQLExceptions

    Retrieving Query Results

    Retrieving Column Values

      getX() Usage

      Mismatched Data Types

      Null Values

      getByte() Usage

      getBlob() and getClob() Usage

    Dynamically Determining Result Sets

    Prepared Statements

    AUTOCOMMIT, COMMITs, and ROLLBACKs

    Batching Requests

    Using Result Sets to Modify Data

    Setting a Column to Null

    Inserting and Updating Large Objects

    Stored Procedures

      Creating a Stored Procedure

      Defining a Stored Procedure

      Calling a Stored Procedure

      Handling Result Sets

      Trapping Errors in Stored Procedures and Functions

      Multiple Result Sets

    User-Defined Functions

      Internal .JAR Files

      Create Function Details

      Using SQL Within a User-Defined Function

    Apache Derby Applets

    Locking Considerations

      Uncommitted Read

      Cursor Stability

      Read Stability

      Repeatable Read

      Choosing an Isolation Level

      Changing an Application's Isolation Level

      Lock Table

      Statement-Level Locking

    Summary

10. Developing Apache Derby Applications with Perl, PHP, Python, and ODBC.

    Communicating with an Apache Derby Network Server

      Distributed Relational Database Architecture (DRDA)

      Call Level Interface (CLI)

      Open Database Connectivity (ODBC)

      Perl, PHP, and Python

    Installing the DB2 Runtime Client

      Linux Operating Systems

      Windows Operating Systems

    Cataloging Apache Derby Network Servers

    Cataloging Apache Derby Databases

    Testing Your Connection

    Uncataloged Connections

    Summary

IV.

11. "Your Momma Loves Drama" in JDBC.

    Introduction

    Your Momma Loves Drama Database

      PRODUCTIONS Table

      PERFORMANCES Table

      SEATS Table

      PRICEPLAN Table

      SEATMAP Table

      TRANSACTIONS Table

    The Your Momma Loves Drama Application

    YMLD Overview

    Installing the YMLD Application

    YMLD Initialization

    Connecting to the YMLD System

    Current Performances at the Theater

      getSQLError() Method

    Seat Pricing

    Theater Seat Map

    Purchase Tickets by Performance

      Selecting a Production

      Selecting a Date

        setCalendar()

        getProdDates()

      Selecting a Seat

      Confirming the Order

      Finalizing the Transaction

    View Transaction Log

    Exit Routine

    Summary

12. "Your Momma Loves Drama" in Windows.

    Introduction

    The Your Momma Loves Drama Application

    YMLD Overview

    Installing the YMLD Application

    YMLD Initialization

    Connecting to the YMLD System

      Bad User ID or Password

      Changing the Schema

      Error Handling

    Current Performances at the Theater

    Try/Catch Block

    Seat Pricing

    Theater Seat Map

    Purchase Tickets by Performance

      Selecting a Production

      Selecting a Date

        setCalendar()

        getProdDates()

      Selecting a Seat

      Confirming the Order

      Finalizing the Transaction

    View Transaction Log

    Exit Routine

    Summary

13. "Your Momma Loves Drama" in PHP.

    PHP Overview

      What Makes PHP Unique?

      Most Appropriate Uses for This Language

        Purchaser Interface

      Performance Characteristics

      Popular Editing Environments

    Learning the PHP Language

      Delimiting PHP Code

      Variables

        Variable Names

        Declaring Variables

      Data Types

        Boolean

        Numeric Types

        Arrays

        Strings

      Control Structures

        while and do/while Loops

        if ... elseif ... else

        switch

        for Iterators

        foreach Iterators

        break and continue

      Defining Functions

      Creating Classes

      Including Libraries of PHP Code

    Installing and Configuring PHP

      Installing PHP on Linux

        Configuring PHP on Linux

      Installing PHP on Windows

        Downloading PHP

        Installing PHP

        Configuring PHP

    Creating Apache Derby Applications with PHP

      Running PHP Scripts from the Command Line

      Connecting to an Apache Derby Database

        Single Connections

        Disconnecting from a Database

        Pooling Connections

      Issuing SQL Statements

        Executing a Single Statement

        Issuing SQL Statements with Placeholders

        Reissuing the Same SQL Statement with Different Parameters

        Retrieving Data

        Calling Stored Procedures

      Managing Transactions

        Explicitly Committing and Rolling Back Transactions

        Setting Autocommit

      Special Data Types

        Large Objects: BLOBs and CLOBs

      Creating Web Interfaces

        Setting the XHTML Header

        Handling POST and GET Variables

        Checking User Input for Tainted Values

        Input-Filtering Functions

        Web Application Summary

        Customizing Web Sites with Session

    Summary

14. "Your Momma Loves Drama" in Perl.

    Perl Overview

      What Makes Perl Unique?

      Most Appropriate Uses for This Language

        Administrator Interface

        Purchaser Interface

      Performance Characteristics

      Popular Editing Environments

    Installing and Configuring Perl

      Installing Perl on Linux

        Configuring Perl on Linux

      Installing Perl on Windows

        Downloading Perl

        Installing ActivePerl

        Configuring Perl

    Creating Apache Derby Applications with Perl

      Using Perl Modules

      Running Perl Scripts from the Command Line

      Connecting to an Apache Derby Database

        Single Connections

        Disconnecting from a Database

        Pooling Connections

      Issuing SQL Statements

        Executing a Single Statement Immediately

        Preparing Statements for Reuse

        Retrieving Data

        Calling Stored Procedures

      Managing Transactions

        Explicitly Committing and Rolling Back Transactions

        Setting Autocommit

      Special Data Types

        Large Objects: BLOBs and CLOBs

      Creating Web Interfaces

        Using Common Web Modules

        Handling POST and GET Variables

        Checking User Input for Tainted V