Informit Network

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

Join our site today
for exclusive benefits
and savings up to 30%*

Usually ships in 24 hours.

Your current savings is 10% List Price:$39.99
Your Price:$35.99
* Discounts available on select titles only
Check

Free Instant Online Access with Safari Books Online

(What is this?)

Instant Online Access with Safari Books Online

With your book purchase you are entitled to free, instant online access to that book on Safari Books Online for 45 days. After you've completed your purchase, you will receive instructions on how to log into Safari Books Online. If you do not want to receive online access to the book, simply uncheck the box for Instant Online Access in your cart.

Free ShippingFree FedEx Ground Shipping No minimum purchase
See details.

Save to My Wish ListSave to My Wish List

ISBN-10: 0-13-185525-5; ISBN-13: 978-0-13-185525-0; Published: Oct 27, 2005; Copyright 2006; Dimensions 7x9-1/4; Pages: 600; Edition: 1st.

Sample Chapter(s)

Download the Sample Chapter related to this title.

Product Description

  • Learn installation, configuration, management, and security

  • Delve deeper with coverage on SQL, troubleshooting, application development, and more

Master Apache Derby: the only open source, pure Java™, fully transactional, small-footprint RDBMS

Apache Derby is the world's only enterprise class open source, pure Java, fully transactional relational database. It's small enough to be embedded directly into your Java applications, providing a local data store that requires no DBA support. Powerful enough to support a wide range of traditional applications, this database ships as the default database for WebSphere Application Server and more than fifty other IBM software products. Apache Derby is built on the mature IBM Cloudscape code base IBM recently contributed to the open source community. The IBM Cloudscape code was nominated and won Developer.com's Product of the Year 2005 award.

Now, IBM's own database experts have written a comprehensive guide to making the most of Apache Derby—and its commercial counterpart, IBM Cloudscape— in any development or production environment. This book systematically introduces the Apache Derby/IBM Cloudscape technology: where it fits and how to install and configure it—and how to build robust business applications and Web services with it, one step at a time.

Coverage includes

  • Comparing Apache Derby with the commercial IBM Cloudscape offering

  • Deploying Apache Derby/IBM Cloudscape to support development and production environments, client/server environments, multi-threaded applications, and Web servers

  • Integrating Apache Derby/IBM Cloudscape into a completely open source development environment

  • Specific instruction on installing Apache Derby/IBM Cloudscape on Windows® and Linux® workstations—with tips on how to install the code on any platform that has a supported JVM

  • Understanding how programming languages interact with Apache Derby/IBM Cloudscape

  • Building complete sample applications step-by-step in JDBC, Windows (ODBC and .NET), PHP, Perl, and Python

  • Managing and securing Apache Derby/IBM Cloudscape databases

  • Writing SQL code for creating, retrieving, and updating data

  • Troubleshooting installations, databases, and applications

This book has a companion Web site that contains links to the IBM version of Apache Derby and other downloadable code, documentation, articles, and other resources to give the reader a deeper understanding of some of the features available in Apache Derby.

ibmpressbooks.com/title/0131855255

www-306.ibm.com/software/data/awards/


© Copyright Pearson Education. All rights reserved.

Index

Download the Index file related to this title.

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 Values

        Web Application Summary

        Customizing Web Sites with Sessions

      Creating GUI Interfaces

    Summary

15. "Your Momma Loves Drama" in Python.

    Python Overview

      What Makes Python Unique?

      Most Appropriate Uses for This Language

        Administrator Interface

        Purchaser Interface

      Performance Characteristics

      Popular Editing Environments

    Learning the Python Language

      Variables

        Variable Names

        Declaring Variables

      Data Types

        Boolean

        Numeric Types

        Sequences

        Strings

        Dictionaries

      Control Structures

        while Loops

        if ... elif ... else

        for Iterators

        pass, break, and continue

      Defining Functions

      Creating Classes

      Importing Modules

    Installing and Configuring Python

      Installing Python on Linux

        Configuring Python on Linux

      Installing Python on Windows

        Downloading Python

        Installing Python

        Configuring Python

    Creating Apache Derby Applications with Python

      Using Python Modules

      Running Python 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

        Using Common Web Modules

        Handling POST and GET Variables

        Checking User Input for Tainted Values

        Web Application Summary

        Customizing Web Sites with Sessions

      Creating GUI Interfaces

    Summary

V.

Appendix A. Web Site Contents.

    Introduction

    Web Site Contents

    Summary

Appendix B. Apache Derby and IBM Cloudscape Resources.

    General Information

    Application Development

    Getting to Know the IBM Cloudscape and Apache Derby Databases

    Support

    Newsgroups and Mailing Lists

Appendix C. Troubleshooting Hints and Tips.

    Introduction

    Installation

      Performing a Graphical Installation and Saving Disk Space

      Setting Environment Variables on Linux

      Setting Environment Variables on Windows

      If You're Installing the Apache Derby Code on a Linux System Using the 2.6 Kernel

      Choosing a Target Directory for the Apache Derby Installation

      If You Can't Start Your Apache Derby Database

      If You Can't Connect to Your Apache Derby Database

    Performance Tuning

    Security

      Database File Security

      Database File Encryption

      Booting Encrypted Databases

      Remote Authentication

      Built-in Authentication

      Security Hierarchy

    Database Features

      Database Structure

      Creating an Apache Derby Database

      Database Names

      Deleting an Apache Derby Database

      Special Characters in the Apache Derby Properties File

    SQL

      Ensure That Your Environment Variables Are Properly Set

      Use Declared Tables for Temporary Storage

      Select the Right Data Types

      Large Object Support

      Null Considerations

      Identity Column

      Indexes

      Null Values and Indexes

      General Indexing Guidelines

      Searching for Null Values

      Use Parentheses in Expressions

      Case Expressions

      Schema

      Trigger Data View

    JDBC Application Development

      Using Embedded JDBC Drivers

      IBM DB2 JDBC Universal Driver for Apache Derby

      Apache Derby Network Servers

      Universal Driver Attributes

      Accessing Result Sets in a Random Order

      Closing Statements

      try Blocks

      Multiple SQLExceptions

      SQLWarnings Versus SQLExceptions

      Mismatched Data Types

      Null Values in Java

      Autocommit Settings

      Inserting and Updating Large Objects

      User-Defined Functions

      Connecting to a Database from a Stored Procedure

    Summary

Index.

Sample Files

Untitled Document Articles - Various IBM Cloudscape and Apache Derby articles from the IBM developerWorks site.
http://www.ibm.com/developerworks/db2/zones/cloudscape/

IBM Cloudscape - Installation images for Java, Windows, and Linux environments.
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0408cline/index.html
For non-Java client support
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0409cline2/

Documentation - The IBM Cloudscape Information Center.
http://publib.boulder.ibm.com/infocenter/cscv/v10r1/index.jsp

Eclipse Demo - A demonstration of the Eclipse plug-in for Apache Derby.
http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0509cline/

Examples - Examples and sample code from Apache Derby - Off to the Races.
Chapter 8 CH08-SQL.zip
Chapter 9 CH09-JDBC.zip
Chapter 11 CH11-YMLD-JDBC.zip
Chapter 12 CH12-YMLD-Windows.zip
Chapter 13 CH13-YMLD-PHP.zip
Chapter 14 CH14-YMLD-Perl.zip
Chapter 15 CH15-YMLD-Python.zip

YMLD Database YMLD-Database.zip - SQL script for creating the Your Momma Loves Drama database. The directory also includes a generated copy of the database.