9780131855250

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

by ; ;
  • ISBN13:

    9780131855250

  • ISBN10:

    0131855255

  • Edition: 1st
  • Format: Hardcover
  • Copyright: 2006-01-01
  • Publisher: IBM Press

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

Purchase Benefits

  • Free Shipping On Orders Over $35!
    Your order must be $35 or more to qualify for free economy shipping. Bulk sales, PO's, Marketplace items, eBooks and apparel do not qualify for this offer.
  • Get Rewarded for Ordering Your Textbooks! Enroll Now
List Price: $44.99 Save up to $11.25
  • Buy Used
    $33.74

    USUALLY SHIPS IN 2-4 BUSINESS DAYS

Supplemental Materials

What is included with this book?

Summary

Insights and Instruction on Apache Derbies from creators and supporters - Open Source's newest project on the Open Source database front.

Author Biography

Paul C. Zikopoulos is an award-winning writer and speaker with the IBM Database Competitive Technology team Dan Scott, IBM product manager, has been involved in the intersection of DB2, Linux George Baklarz, senior manager in IBM's DB2 Worldwide Pre-sales Support Group

Table of Contents

Preface xxiii
Acknowledgments xxvii
About the Authors xxix
Part 1
On Your Marks ... Get Set ... Go!!! An Introduction to the Apache Derby and IBM Cloudscape Community
1(24)
Introduction
1(4)
If You're Not the Kind of Person Who Reads Introduction Chapters...
3(2)
Let Me Get This Straight, Apache Derby Is IBM Cloudscape?
5(2)
Development of the Apache Derby Database---Who Can Contribute and How?
7(2)
How Can IBM Sell a Product for Profit and Contribute the Same Product to the Open Source Community?
9(1)
How an Open Source Database Like Apache Derby Can Help
10(2)
Open Source Software from a Business Perspective
10(1)
Open Source Software from a Technical Perspective
11(1)
Why the Need for a Local Data Store?
12(1)
Why Use a Relational Database?
13(2)
How the Apache Derby Platform Can Help Your Business
15(2)
A High-Level View of the Apache Derby Database
17(4)
Details on SQL Support in Apache Derby and IBM Cloudscape
19(2)
The Apache Derby Components
21(2)
The Apache Derby Database Engine
21(1)
Apache Derby Network Server
21(1)
ij---The Apache Derby JDBC Scripting Tool
22(1)
Developing Apache Derby Applications
23(2)
Part 2
Deployment Options for Apache Derby Databases
25(12)
Using the Embedded Framework for Apache Derby Applications
25(7)
Examples of the Embedded Framework for Apache Derby Applications
27(4)
Some Final Comments About the Embedded Framework for Apache Derby Applications
31(1)
Using the Server Framework for Apache Derby Applications
32(5)
Examples of the Server Framework for Apache Derby Applications
33(4)
Apache Derby Databases
37(12)
Introduction
37(1)
Database Structure
37(8)
Creating an Apache Derby Database
39(2)
Deleting an Apache Derby Database
41(1)
Database Contents
41(1)
Additional Database Connection Options
42(1)
Security
43(1)
Maintenance
43(2)
Environment
45(1)
Database Objects
45(3)
Tables
46(1)
Data Types
46(1)
Schemas
46(1)
Views
47(1)
Indexes
47(1)
Triggers
47(1)
Procedures
48(1)
Summary
48(1)
Installing Apache Derby and IBM Cloudscape on Windows
49(26)
Migration from Previous Versions of Cloudscape
50(1)
Before You Begin
50(5)
The Java Runtime Environment
51(1)
J Who? How Do I Set It Up? How Do I Know I've Got One?
51(1)
Checking the Version of the JRE on Your System
52(1)
Setting the PATH Environment Variable
52(3)
Using the Windows Installer to Install IBM Cloudscape on Windows---Attention Developers!
55(8)
IBM Cloudscape for Windows Installer Prerequisites
57(1)
Performing the Windows Installation
57(5)
Post-Installation Tasks for Graphical Installation Programs
62(1)
Installing Apache Derby or IBM Cloudscape on Windows Manually
63(4)
Creating and Setting the CLOUDSCAPE_INSTALL Environment Variable
64(1)
Setting the CLASSPATH Environment Variable
65(2)
Verifying the Installation
67(3)
Verifying a Database Installation
67(1)
Verifying a Network Server Installation
68(2)
Troubleshooting an Installation
70(2)
Odds and Ends About Your Installation
72(3)
Files on Disk
72(3)
Installing Apache Derby and IBM Cloudscape on Linux
75(26)
Migration from Previous Versions of Cloudscape
76(1)
Before You Begin
76(5)
The Java Runtime Environment
77(1)
J Who? How Do I Set It Up? How Do I Know I've Got One?
77(1)
Checking the Version of the JRE on Your System
78(1)
Setting the PATH Environment Variable
78(3)
Using the Linux Installer to Install IBM Cloudscape on Linux---Attention Developers!
81(9)
IBM Cloudscape for Linux Installer Prerequisites
82(1)
Performing the Linux Installation
83(7)
Post-Installation Tasks for Graphical Installation Programs
90(1)
Installing Apache Derby or IBM Cloudscape on Linux Manually
90(3)
Creating and Setting the CLOUDSCAPE_INSTALL Environment Variable
90(1)
Setting the CLASSPATH Environment Variable
91(2)
Verifying the Installation
93(3)
Verifying a Database Installation
93(1)
Verifying a Network Server Installation
93(3)
Troubleshooting an Installation
96(2)
Odds and Ends About Your Installation
98(3)
Files on Disk
98(3)
Managing an Apache Derby Database
101(20)
Disaster Prevention and Recovery
101(4)
Backing Up a Database
101(1)
Online Backup with Read Access (Built-in Copy)
102(1)
Online Backup with Read Access (Native Copy)
103(1)
Offline Backup
104(1)
Restoring a Database from a Backup Image
105(1)
Data Movement
105(7)
Importing Data
106(1)
Importing Data into a Table
107(1)
Importing Data into a Subset of Columns in a Table
108(1)
Exporting Data
109(1)
Exporting Data from a Single Table
109(1)
Exporting Data from the Results of a SELECT Statement
110(2)
Database Maintenance and Tuning
112(7)
Checking Database Consistency
112(1)
Investigating Performance Issues with Database Statistics
113(1)
Gathering Runtime Statistics
113(2)
Adding Timing to Runtime Statistics
115(4)
Reorganizing Data
119(1)
Summary
119(2)
Security
121(14)
Introduction
121(1)
Database File Security
121(2)
Database File Encryption
123(3)
Algorithm
125(1)
Feedback Mode
126(1)
User Authentication
126(6)
Enabling Authentication
127(1)
LDAP Directory Service Authentication
128(1)
User-Defined Authentication
129(1)
Built-In Authentication
130(1)
System-Level Users
130(1)
Database-Level Users
131(1)
Security Hierarchy
132(1)
Database Authorization
132(2)
Permissions
133(1)
Errors
133(1)
Summary
134(1)
SQL
135(98)
Introduction
135(1)
Your Momma Loves Drama Database
136(1)
Data Definition Language (DDL)
137(5)
The CREATE Statement
139(1)
The ALTER Statement
139(1)
The DECLARE Statement
139(1)
The DROP Statement
140(1)
Apache Derby Data Types
140(1)
Numeric Data Types
140(1)
Small Integer (Smallint)
141(1)
Integer (Integer)
141(1)
Big Integer (Bigint)
141(1)
Decimal (Decimal/Numeric)
142(1)
Single-Precision Floating-Point (Real/Float)
142(1)
Double-Precision Floating-Point (Double/Float)
142(1)
String Data Types
142(2)
Fixed-Length Character String (Char)
143(1)
Varying-Length Character String (Varchar)
143(1)
Varying-Length Long Character String (Long Varchar)
143(1)
Character String Considerations
143(1)
Binary String Data Types
144(1)
Large Objects
144(1)
Character Large Object (CLOB)
145(1)
Binary Large Object (BLOB)
145(1)
Date and Time Data Types
145(2)
Date String (Date)
146(1)
Time String (Time)
146(1)
Timestamp String (Timestamp)
147(1)
Creating User Tables
147(1)
Sample Table Create Statements
147(1)
Additional Data Type Modifiers
148(5)
Null Considerations
149(1)
Not Null with Default
149(2)
Identity Column
151(2)
Identity_Val_Local Function
153(1)
Declared Tables
153(2)
Modifying a Table
155(1)
Adding a Column to a Table
155(1)
Adding a Constraint to a Table
155(1)
Dropping an Existing Constraint on a Table
156(1)
Altering the Size of a Varchar Column
156(1)
Overriding Row-Level Locking
156(1)
Removing a Table
156(1)
Constraints
156(1)
Unique Constraint
157(1)
Referential Integrity
157(4)
INSERT Rule
158(2)
DELETE Rules
160(1)
UPDATE Rules
161(1)
Check Constraints
161(2)
Adding Check Constraints
162(1)
Modifying Check Constraints
162(1)
System Catalog Tables
163(2)
Views
165(2)
Nested View Definitions
166(1)
Modifying a View
167(1)
Removing a View
167(1)
Indexes
167(3)
Unique Index and Non-Unique Index
167(1)
Referential Integrity and Indexes
168(1)
Null Values and Indexes
168(1)
General Indexing Guidelines
168(1)
Creating an Index
169(1)
Modifying an Index
169(1)
Removing an Index
169(1)
Data Retrieval
170(43)
Retrieving an Entire Table
170(1)
Projecting Columns from a Table
171(1)
Changing the Order of the Columns
172(1)
Restricting the Rows That Are Returned from a Table
173(1)
Restricting Rows Using Multiple Conditions
173(1)
Searching for String Patterns
174(1)
Searching for Data in Ranges
175(1)
Searching for a Set of Values
176(1)
Searching for Null Values
176(1)
Searching for Negative Conditions
177(1)
Selecting Columns from Multiple Tables
178(1)
Cartesian Product
179(2)
Joins
181(2)
Outer Join
183(1)
Left Outer Join
184(2)
Right Outer Join
186(1)
Using Correlation Names
186(1)
Sorting Output
187(2)
Derived Columns
189(2)
Apache Derby Functions
191(1)
Scalar Functions
191(2)
Column Functions
193(1)
Grouping Values
194(1)
Restricting Results with Column Functions
195(1)
Eliminating Duplicates
196(2)
Subqueries
198(4)
Quantified Predicates
202(2)
Case Expressions
204(1)
Nested Table Expressions
205(1)
Scalar Fullselect
206(3)
Union
209(3)
The EXISTS Predicate
212(1)
Data Modification
213(8)
Inserting Rows
213(2)
Inserting Data into Specific Columns
215(1)
Inserting Multiple Rows
215(1)
Inserting a Set of Values
216(1)
Updating Rows
217(2)
Removing Data
219(2)
Development SQL
221(11)
Schemas
221(2)
User-Defined Functions
223(1)
Triggers
224(1)
Trigger Activation
225(1)
Trigger Body
225(1)
Trigger Example---Updating Other Tables
226(2)
Trigger Example---Changing Inserted Values
228(1)
Trigger Example---Sets
229(1)
Stored Procedures
230(1)
Commit and Rollback
231(1)
Summary
232(1)
Part 3
Developing Apache Derby Applications with JDBC
233(74)
Introduction
233(1)
JBDC Program Structure
234(1)
JDBC Imports
235(1)
Using Embedded JDBC Drivers
236(1)
IBM DB2 JDBC Universal Driver for Derby
237(1)
Establishing a Database Connection
238(2)
Connecting with DB2 JDBC Drivers
240(4)
Server Name
240(1)
Port Number
241(1)
Database Name
241(1)
Apache Derby Attributes
241(1)
Universal Driver Attributes
241(3)
Allocating Statements
244(2)
resultSetType and resultSetCurrency
244(1)
Closing Statements
245(1)
Additional Statement Options
245(1)
Executing Dynamic SQL
246(2)
Trapping SQLExceptions
248(3)
getMessage()
248(1)
getSQLState()
249(1)
getErrorCode()
249(1)
Multiple SQLExceptions
250(1)
SQLWarnings Versus SQLExceptions
251(1)
Retrieving Query Results
252(6)
Retrieving Column Values
258(6)
getX() Usage
259(1)
Mismatched Data Types
260(1)
Null Values
260(2)
getByte() Usage
262(1)
getBlob() and getClob() Usage
262(2)
Dynamically Determining Result Sets
264(4)
Prepared Statements
268(4)
AUTOCOMMIT, COMMITs, and ROLLBACKs
272(1)
Batching Requests
273(1)
Using Result Sets to Modify Data
274(4)
Setting a Column to Null
278(1)
Inserting and Updating Large Objects
279(2)
Stored Procedures
281(7)
Creating a Stored Procedure
281(1)
Defining a Stored Procedure
281(1)
Calling a Stored Procedure
282(2)
Handling Result Sets
284(3)
Trapping Errors in Stored Procedures and Functions
287(1)
Multiple Result Sets
288(1)
User-Defined Functions
288(11)
Internal JAR Files
291(1)
Create Function Details
292(1)
Using SQL Within a User-Defined Function
293(6)
Apache Derby Applets
299(3)
Locking Considerations
302(3)
Uncommitted Read
302(1)
Cursor Stability
302(1)
Read Stability
302(1)
Repeatable Read
303(1)
Choosing an Isolation Level
303(1)
Changing an Application's Isolation Level
303(1)
Lock Table
304(1)
Statement-Level Locking
304(1)
Summary
305(2)
Developing Apache Derby Applications with Perl, PHP, Python, and ODBC
307(8)
Communicating with an Apache Derby Network Server
307(1)
Distributed Relational Database Architecture (DRDA)
307(1)
Call Level Interface (CLI)
307(1)
Open Database Connectivity (ODBC)
308(1)
Perl, PHP, and Python
308(1)
Installing the DB2 Runtime Client
308(3)
Linux Operating Systems
309(1)
Windows Operating Systems
310(1)
Cataloging Apache Derby Network Servers
311(1)
Cataloging Apache Derby Databases
311(1)
Testing Your Connection
312(1)
Uncataloged Connections
313(1)
Summary
313(2)
Part 4
``Your Momma Loves Drama'' in JDBC
315(42)
Introduction
315(1)
Your Momma Loves Drama Database
316(8)
Productions Table
317(1)
Performances Table
318(2)
Seats Table
320(2)
Priceplan Table
322(1)
Seatmap Table
323(1)
Transactions Table
323(1)
The Your Momma Loves Drama Application
324(1)
YMLD Overview
325(1)
Installing the YMLD Application
326(1)
YMLD Initialization
326(1)
Connecting to the YMLD System
327(2)
Current Performances at the Theater
329(5)
getSQLError() Method
334(1)
Seat Pricing
334(1)
Theater Seat Map
335(1)
Purchase Tickets by Performance
336(15)
Selecting a Production
336(3)
Selecting a Date
339(1)
setCalendar()
340(3)
getProdDates()
343(1)
Selecting a Seat
344(2)
Confirming the Order
346(3)
Finalizing the Transaction
349(2)
View Transaction Log
351(4)
Exit Routine
355(1)
Summary
355(2)
``Your Momma Loves Drama'' in Windows
357(34)
Introduction
357(1)
The Your Momma Loves Drama Application
357(1)
YMLD Overview
358(1)
Installing the YMLD Application
359(1)
YMLD Initialization
360(1)
Connecting to the YMLD System
360(4)
Bad User ID or Password
363(1)
Changing the Schema
363(1)
Error Handling
364(1)
Current Performances at the Theater
364(3)
Try/Catch Block
367(1)
Seat Pricing
368(1)
Theater Seat Map
369(1)
Purchase Tickets by Performance
370(16)
Selecting a Production
370(3)
Selecting a Date
373(1)
setCalendar()
374(2)
getProdDates()
376(2)
Selecting a Seat
378(4)
Confirming the Order
382(2)
Finalizing the Transaction
384(2)
View Transaction Log
386(4)
Exit Routine
390(1)
Summary
390(1)
``Your Momma Loves Drama'' in PHP
391(48)
PHP Overview
391(3)
What Makes PHP Unique?
391(1)
Most Appropriate Uses for This Language
392(1)
Purchaser Interface
393(1)
Performance Characteristics
393(1)
Popular Editing Environments
393(1)
Learning the PHP Language
394(13)
Delimiting PHP Code
394(1)
Variables
394(1)
Variable Names
394(1)
Declaring Variables
395(1)
Data Types
395(1)
Boolean
395(1)
Numeric Types
396(1)
Arrays
396(1)
Strings
397(2)
Control Structures
399(1)
while and do/while Loops
399(1)
if... elseif... else
400(1)
switch
401(1)
for Iterators
401(1)
foreach Iterators
402(2)
break and continue
404(1)
Defining Functions
404(1)
Creating Classes
404(2)
Including Libraries of PHP Code
406(1)
Installing and Configuring PHP
407(4)
Installing PHP on Linux
408(1)
Configuring PHP on Linux
409(1)
Installing PHP on Windows
410(1)
Downloading PHP
410(1)
Installing PHP
410(1)
Configuring PHP
410(1)
Creating Apache Derby Applications with PHP
411(15)
Running PHP Scripts from the Command Line
411(1)
Connecting to an Apache Derby Database
412(1)
Single Connections
412(1)
Disconnecting from a Database
413(1)
Pooling Connections
413(1)
Issuing SQL Statements
413(1)
Executing a Single Statement
414(1)
Issuing SQL Statements with Placeholders
414(1)
Reissuing the Same SQL Statement with Different Parameters
414(2)
Retrieving Data
416(1)
Calling Stored Procedures
417(4)
Managing Transactions
421(1)
Explicitly Committing and Rolling Back Transactions
422(1)
Setting Autocommit
423(1)
Special Data Types
424(1)
Large Objects: BLOBs and CLOBs
424(2)
Creating Web Interfaces
426(1)
Setting the XHTML Header
426(1)
Handling POST and GET Variables
426(4)
Checking User Input for Tainted Values
430(1)
Input-Filtering Functions
431(1)
Web Application Summary
432(3)
Customizing Web Sites with Sessions
435(2)
Summary
437(2)
``Your Momma Loves Drama'' in Perl
439(34)
Perl Overview
439(2)
What Makes Perl Unique?
439(1)
Most Appropriate Uses for This Language
440(1)
Administrator Interface
440(1)
Purchaser Interface
440(1)
Performance Characteristics
441(1)
Popular Editing Environments
441(1)
Installing and Configuring Perl
441(3)
Installing Perl on Linux
442(1)
Configuring Perl on Linux
442(1)
Installing Perl on Windows
443(1)
Downloading Perl
443(1)
Installing ActivePerl
443(1)
Configuring Perl
443(1)
Creating Apache Derby Applications with Perl
444(28)
Using Perl Modules
444(1)
Running Perl Scripts from the Command Line
445(1)
Connecting to an Apache Derby Database
445(1)
Single Connections
445(1)
Disconnecting from a Database
446(1)
Pooling Connections
446(1)
Issuing SQL Statements
447(1)
Executing a Single Statement Immediately
447(1)
Preparing Statements for Reuse
447(3)
Retrieving Data
450(1)
Calling Stored Procedures
451(3)
Managing Transactions
454(1)
Explicitly Committing and Rolling Back Transactions
454(1)
Setting Autocommit
455(1)
Special Data Types
455(1)
Large Objects: BLOBs and CLOBs
456(2)
Creating Web Interfaces
458(1)
Using Common Web Modules
459(1)
Handling POST and GET Variables
459(3)
Checking User Input for Tainted Values
462(1)
Web Application Summary
462(4)
Customizing Web Sites with Sessions
466(1)
Creating GUI Interfaces
467(5)
Summary
472(1)
``Your Momma Loves Drama'' in Python
473(46)
Python Overview
473(2)
What Makes Python Unique?
473(1)
Most Appropriate Uses for This Language
474(1)
Administrator Interface
474(1)
Purchaser Interface
474(1)
Performance Characteristics
474(1)
Popular Editing Environments
475(1)
Learning the Python Language
475(12)
Variables
475(1)
Variable Names
475(1)
Declaring Variables
476(1)
Data Types
476(1)
Boolean
476(1)
Numeric Types
477(1)
Sequences
478(1)
Strings
479(1)
Dictionaries
480(1)
Control Structures
481(1)
while Loops
481(1)
if... elif... else
482(1)
for Iterators
482(1)
pass, break, and continue
483(1)
Defining Functions
483(1)
Creating Classes
484(2)
Importing Modules
486(1)
Installing and Configuring Python
487(2)
Installing Python on Linux
487(1)
Configuring Python on Linux
488(1)
Installing Python on Windows
488(1)
Downloading Python
489(1)
Installing Python
489(1)
Configuring Python
489(1)
Creating Apache Derby Applications with Python
489(29)
Using Python Modules
489(1)
Running Python Scripts from the Command Line
490(1)
Connecting to an Apache Derby Database
490(1)
Single Connections
490(1)
Disconnecting from a Database
491(1)
Pooling Connections
491(1)
Issuing SQL Statements
492(1)
Executing a Single Statement
492(1)
Issuing SQL Statements with Placeholders
492(1)
Reissuing the Same SQL Statement with Different Parameters
493(1)
Retrieving Data
494(1)
Calling Stored Procedures
495(4)
Managing Transactions
499(1)
Explicitly Committing and Rolling Back Transactions
500(1)
Setting Autocommit
500(1)
Special Data Types
501(1)
Large Objects: BLOBs and CLOBs
501(2)
Creating Web Interfaces
503(1)
Setting the XHTML Header
503(1)
Using Common Web Modules
504(1)
Handling POST and GET Variables
504(3)
Checking User Input for Tainted Values
507(1)
Web Application Summary
508(3)
Customizing Web Sites with Sessions
511(1)
Creating GUI Interfaces
512(6)
Summary
518(1)
Part 5
Appendix A Web Site Contents
519(2)
Introduction
519(1)
Web Site Contents
519(1)
Summary
520(1)
Appendix B Apache Derby and IBM Cloudscape Resources
521(4)
General Information
521(1)
Application Development
521(1)
Getting to Know the IBM Cloudscape and Apache Derby Databases
522(1)
Support
523(1)
Newsgroups and Mailing Lists
523(2)
Appendix C Troubleshooting Hints and Tips
525(12)
Introduction
525(1)
Installation
525(1)
Performing a Graphical Installation and Saving Disk Space
525(1)
Setting Environment Variables on Linux
525(1)
Setting Environment Variables on Windows
526(1)
If You're Installing the Apache Derby Code on a Linux System Using the 2.6 Kernel
526(1)
Choosing a Target Directory for the Apache Derby Installation
526(1)
If You Can't Start Your Apache Derby Database
526(1)
If You Can't Connect to Your Apache Derby Database
526(1)
Performance Tuning
526(1)
Security
527(1)
Database File Security
527(1)
Database File Encryption
527(1)
Booting Encrypted Databases
527(1)
Remote Authentication
528(1)
Built-in Authentication
528(1)
Security Hierarchy
528(1)
Database Features
528(1)
Database Structure
528(1)
Creating an Apache Derby Database
529(1)
Database Names
529(1)
Deleting an Apache Derby Database
529(1)
Special Characters in the Apache Derby Properties File
529(1)
SQL
529(3)
Ensure That Your Environment Variables Are Properly Set
529(1)
Use Declared Tables for Temporary Storage
530(1)
Select the Right Data Types
530(1)
Large Object Support
530(1)
Null Considerations
530(1)
Identity Column
531(1)
Indexes
531(1)
Null Values and Indexes
531(1)
General Indexing Guidelines
531(1)
Searching for Null Values
531(1)
Use Parentheses in Expressions
532(1)
Case Expressions
532(1)
Schema
532(1)
Trigger Data View
532(1)
JDBC Application Development
532(3)
Using Embedded JDBC Drivers
532(1)
IBM DB2 JDBC Universal Driver for Apache Derby
532(1)
Apache Derby Network Servers
533(1)
Universal Driver Attributes
533(1)
Accessing Result Sets in a Random Order
533(1)
Closing Statements
533(1)
try Blocks
533(1)
Multiple SQLExceptions
533(1)
SQLWarnings Versus SQLExceptions
534(1)
Mismatched Data Types
534(1)
Null Values in Java
534(1)
Autocommit Settings
534(1)
Inserting and Updating Large Objects
534(1)
User-Defined Functions
535(1)
Connecting to a Database from a Stored Procedure
535(1)
Summary
535(2)
Index 537

Rewards Program

Write a Review