Informix Guide to SQL

Introduction
In This Introduction
About This Manual
Types of Users
Software Dependencies
Assumptions About Your Locale
Demonstration Databases
New Features
New Features in Version 8.3
New Features in Version 9.2
Documentation Conventions
Typographical Conventions
Icon Conventions
Sample-Code Conventions
Additional Documentation
On-Line Manuals
Printed Manuals
On-Line Help
Error Message Documentation
Documentation Notes, Release Notes,
Machine Notes
Related Reading
Compliance with Industry Standards
Informix Welcomes Your Comments
Database Concepts
In This Chapter
Illustration of a Data Model
Storing Data
Querying Data
Modifying Data
Concurrent Use and Security
Controlling Database Use
Centralized Management
Important Database Terms
The Object-Relational Model
Tables
Columns
Rows
Operations on Tables
The Object-Relational Model
Structured Query Language
Standard SQL
Informix SQL and ANSI SQL
Interactive SQL
General Programming
ANSI-Compliant Databases
GLS Databases
Summary
Composing SELECT Statements
In This Chapter
Introducing the SELECT Statement
Some Basic Concepts
The Forms of SELECT
Special Data Types
Single-Table SELECT Statements
Selecting All Columns and Rows
Selecting Specific Columns
Using the WHERE Clause
Creating a Comparison Condition
Using a FIRST Clause to Select Specific Rows
Expressions and Derived Values
Using Rowid Values in SELECT Statements
Multiple-Table SELECT Statements
Creating a Cartesian Product
Creating a Join
Some Query Shortcuts
Selecting Tables from a Database Other Than
the Current Database
Summary
Selecting Data From Complex Types
In This Chapter
Selecting Row-Type Data
Selecting Columns of a Typed Table
Selecting Columns That Contain Row-Type
Data
Selecting from a Collection
Selecting Nested Collections
Using the IN Keyword to Search for
Elements in a Collection
Selecting Rows Within a Table Hierarchy
Selecting Rows of the Supertable Without the ONLY Keyword
Selecting Rows from a Supertable with the ONLY Keyword
Using an Alias for a Supertable
Summary
Using Functions in Select Statements
In This Chapter
Using Functions in SELECT Statements
Aggregate Functions
Time Functions
Date-Conversion Functions
Cardinality Function
Smart-Large-Object Functions
String-Manipulation Functions
Other Functions
Using SPL Routines in SELECT Statements
Using Rowid Values In SELECT Statements
Using Rowid Values with the USER Function
Using Rowid Values with the DBSERVERNAME Function
Summary
Composing Advanced SELECT Statements
In This Chapter
Using the GROUP BY and HAVING Clauses
Using the GROUP BY Clause
Using the HAVING Clause
Creating Advanced Joins
Self-Joins
Outer Joins
Subqueries in SELECT Statements
Subqueries in a Select List
Subqueries in WHERE Clauses
Handling Collections in SELECT Statements
Collection Subqueries
Collection-Derived Tables
Set Operations
Union
Difference
Summary
Modifying Data
In This Chapter
Statements That Modify Data
Deleting Rows
Deleting All Rows of a Table
Deleting a Known Number of Rows
Deleting an Unknown Number of Rows
Deleting Rows That Contain Row Types
Deleting Rows That Contain Collection Types
Deleting Rows from a Supertable
Complicated Delete Conditions
Inserting Rows
Single Rows
Inserting Rows into Typed Tables
Inserting into Row-Type Columns
Inserting Rows into Supertables
Inserting Collection Values into Columns
Inserting Smart Large Objects
Multiple Rows and Expressions
Restrictions on the Insert Selection
Updating Rows
Selecting Rows to Update
Updating with Uniform Values
Restrictions on Updates
Updating with Selected Values
Updating Row Types
Updating Collection Types
Updating Rows of a Supertable
Using a CASE Expression to Update a Column
Using SQL Functions to Update Smart Large Objects
Using a Join to Update a Column
Privileges on a Database
Database-Level Privileges
Table-Level Privileges
Displaying Table Privileges
Data Integrity
Entity Integrity
Semantic Integrity
Referential Integrity
Object Modes and Violation Detection
Interrupted Modifications
Transactions
Transaction Logging
Specifying Transactions
Backups and Logs with Informix Database Servers
Concurrency and Locks
Informix Data Replication
Summary Programming with SQL
In This Chapter
SQL in Programs
SQL in SQL APIs
SQL in Application Languages
Static Embedding
Dynamic Statements
Program Variables and Host Variables
Calling the Database Server
SQL Communications Area
SQLCODE Field
SQLERRD Array
SQLWARN Array
SQLERRM Character String
SQLSTATE Value
Retrieving Single Rows
Data Type Conversion
Working with Null Data
Dealing with Errors
Retrieving Multiple Rows
Declaring a Cursor
Opening a Cursor
Fetching Rows
Cursor Input Modes
Active Set of a Cursor
Using a Cursor: A Parts Explosion
Dynamic SQL
Preparing a Statement
Executing Prepared SQL
Dynamic Host Variables
Freeing Prepared Statements
Quick Execution
Embedding Data-Definition Statements
Embedding Grant and Revoke Privileges
Summary
Modifying Data Through SQL Programs
In This Chapter
Using DELETE
Direct Deletions
Deleting with a Cursor
Using INSERT
Using an Insert Cursor
Rows of Constants
An Insert Example
Using UPDATE
Using an Update Cursor
Cleaning Up a Table
Summary
Programming for a Multiuser Environment
In This Chapter
Concurrency and Performance
Locking and Integrity
Locking and Performance
Concurrency Issues
How Locks Work
Kinds of Locks
Lock Scope
Duration of a Lock
Locks While Modifying
Locking with the SELECT Statement
Setting the Isolation Level
Update Cursors
Retaining Update Locks
Locks Placed with INSERT, UPDATE, and DELETE
Understanding the Behavior of the Lock Types
Controlling Data Modification with Access
Modes
Setting the Lock Mode
Waiting for Locks
Not Waiting for Locks
Waiting a Limited Time
Handling a Deadlock
Handling External Deadlock
Simple Concurrency
Hold Cursors
Using the SQL Statement Cache
Summary
Creating and Using SPL Routines
In This Chapter
Introduction to SPL Routines
What You Can Do with SPL Routines
SPL Routine Behavior for Enterprise
Decision Server
Writing SPL Routines
Using the CREATE PROCEDURE or CREATE FUNCTION Statement
Example of a Complete Routine
Creating an SPL Routine in a Program
Dropping an SPL Routine
Defining and Using Variables
Declaring Local Variables
Declaring Global Variables
Assigning Values to Variables
Expressions in SPL Routines
Writing the Statement Block
Implicit and Explicit Statement Blocks
Using Cursors
Using the FOREACH Loop to Define Cursors
Using an IF - ELIF - ELSE Structure
Adding WHILE and FOR Loops
Exiting a Loop