Programming informix SQL/4GL : a step by step approach

Library	Item Barcode	Call Number	Material Type	Item Category 1	Status
Searching... PSZ JB	30000004498725	CP 1204	Computer File Accompanies Open Access Book	Compact Disc Accompanies Open Access Book	Searching... Unknown
Searching... PSZ KL	30000004498659	CP 1204	Computer File Accompanies Open Access Book	Compact Disc Accompanies Open Access Book	Searching... Unknown

The up-to-date primer for every INFORMIX SQL/4GL database developer This example-rich primer covers everything you need to know to successfully design, implement, and maintain databases with Informix's version of SQL. You'll quickly review the basics of database design, with easy-to-understand explanations of normalization, indexing strategies, and more. Then discover the SQL working environment, DB-Access, and the unique capabilities and features of Informix's SQL implementation; and how INFORMIX-SE and INFORMIX-OnLine compare. Walk step by step through creating and maintaining a database, reviewing database statements, data types, insert, update, delete, load/unload, dbexport/dbimport, locking, and select. Then take a close look at the 4GL programming environment, both r4gl and c4gl, including: * Program structure * Data types and definitions * Menus, output, and much more Programming Informix SQL/4GL presents detailed, end-to-end coverage of screen forms, options, creating context-sensitive help, and error handling. You'll learn how to make the most of advanced Informix capabilities, including user-defined searches, input array statements, complex screen I/O, matrix, and multi-

1 Relational Database Design

What is a Relational Database? Elements of a Database: Tables, Columns, and Rows

Tables

Columns

Rows

Primary Keys

Foreign Keys

Types of Relationships

One-to-One or Zero

One-to-Many (or Many-to-One)

Many-to-Many

What Is Normalization? Formal Normalization

First Normal Form

Second Normal Form

Third Normal Form

Intuitive Normalization

Indexing Strategies

Composite Indexes

Unique Indexes

Duplicate Indexes

Shorter Is Better

Numeric versus Character Indexes

Clustered Indexes

Small Tables

Description of the Sample Database

Summary

2 The SQL Working Environment

About Informix''s Implementation of SQL

About DB-Access

Setting UNIX Environment Variables

Environment Variables Needed by Informix

Optional Environment Variables

Using DB-Access

DB-Access Options with Ring Menus

Getting Around in Informix Ring Menus

Choosing Options from a List

Working From the Command Line

Viewing Error Messages

Summary

3 Creating the Database

The Database Administrator

INFORMIX Database Engines

Database Statements

Data Types

Character and String Data Types

Numer Data Types

Date, Time, and Interval Data Types

Extra data types for INFORMIX-OnLine

Summary of Data Type Conversions

Tables

Creating a New Table

Changing an Existing Table

Deleting a Table

Renaming Tables and Columns

Privileges

Granting Database Privileges

Granting Table Privileges

Revoking Privileges

Roles

Creating Schema Authorization

Indexes

Creating the Sample Database

Viewing the Database

Summary

4 Maintaining the Database

Update Statistics

Checking Database Integrity

INFORMIX-SE Database Integrity

INFORMIX-OnLine Database Integrity

Database Backups

INFORMIX-SE Backups

INFORMIX-OnLine Backups

Logging

What Is a Transaction? Transaction Logging in INFORMIX-SE

Transaction Logging in INFORMIX-OnLine

Audit Trails-INFORMIX-SE Only

Summary

5 Data: Adding, Changing, and Deleting

Insert

Update

Delete

Locking

Locking a Database

Locking a Table

Set Lock Mode

INFORMIX-OnLine Table Option

Load and Unload

Loading Data

Unloading Data

dbload

The Command File

Examples

dbexport and dbimport

dbexport

dbimport

Tips for Faster Data Loads

Summary

6 The Select Statement

Overview of the Select Statement

Select

From

Where

Conditional Expressions

Matches and Like

Other Conditionals

Group By

Having

Order By

Into Temp

Union

Joins

Outer Joins

Output To

Summary

7 The 4GL Programming Environment

INFORMIX-4GL Flavors: r4gl and c4gl

INFORMIX-4GL File Name Suffixes

Menu Driven Development

Command Line Development

Compiling and Running 4GL Code in the Rapid Development System

Compiling and Running Compiled 4GL Code

Compiling Informix Screen Forms

Make Files

Make Rules

The Make File

Prototyping

Summary

8 4GL Program Structure

Overview

Database

Globals

Main

Modules

Functions

Exit Program

Comments

Summary

9 Data Types and Definitions

4GL Data Types

Character and String Data Types

Numeric Data Types

Date, Time, and Interval Data Types

BLOB Data Types

INFORMIX-4GL Only Data Types and Indirect Typing

Defining Variables

Scope of Variables

Use of Nulls

Operators in 4GL

Converting between Data Types

Character and String Data Types

Numeric Data Types

Date, Time, and Interval Data Types

Formatting Data with the using Clause

Formatting Dates

Formatting Numbers

Summary

10 Menus

Overview of Menus

A Simple Ring Menu

Menu Simplicity and Consistency

Menu Features

Command Keys

Help

Next Option

Continue Menu

Hidden Menu Options

Hide Option

Show Option

Before Menu

Summary

11 Output and Standard Program Statements

Overview

Assignment

Output

Display

Error

Message

Prompt

Sleep

Conditionals

If-then-else

case

Looping

for

while

goto-label

Running UNIX Commands

Summary

12 Screen Forms

What Is a Screen Form? Screen Form Design

Screen Form Implementation

Database

Screen

Tables

Attributes

Instructions

Example of an INFORMIX-4GL-Generated Form

Example of a Modified Generated Form

Single Record Form

Multiple Record Form

Combination Form

Form Attributes

upscol

Summary

13 Adding Forms and Windows & Displaying Data

Window Handling

Displaying Forms

Overview of Display

Display

Display Example

Sample Module

Display Array

Display Array Example

Scroll

Sample Module

Display a One-to-Many Relationship

Summary

14 Setting Options

Overview of Options

Output Lines

Keys

Help File

Attributes

Input Wrap

Field Order

Sql Interrupt

Line or Form Mode

Example

Summary

15 Help! About Context-Sensitive Help

Help Files

mkmessage

Using Help

Summary

16 Error Handling

Defer Interrupt

Defer Quit

Whenever

Error Log

Sample Main Program and Error Function

Summary

17 Built-in Functions, Constants, and Variables

INFORMIX-4GL Functions Called from 4GL

INFORMIX-4GL Functions Called from C

INFORMIX-4GL Constants

INFORMIX-4GL Global Variables

Summary

18 The Input Statement

Input Overview

Editing the Input

Data Validation

Adding/Changing a Row

Summary

19 Using SQL in 4GL

Using Direct SQL in 4GL

prepare

execute

declare

foreach

open

fetch

where current of

put

flush

free

Summary

20 More Sophisticated SQL

Views

Synonyms

Expressions

Operators

Columns

Literal Constants

Built-in Functions

Aggregates

Using Aggregate Functions

Subqueries

Subqueries with Relational Operators

in and not in

exists and not exists

all, any, and some

insert

update

delete

Self-Joins

Query Optimization

Set explain on

Temp Tables

or versus union

Indexes

Forcing Use of Indexes

Pattern Matching and Substrings

Subqueries

INFORMIX-OnLine Topics

Accessing Tables in Other Databases on the Same INFORMIX-OnLine System

Isolation Levels

Summary

21 Stored Procedures and Triggers

Stored Procedures

dbinfo

Sample Stored Procedures

Privileges

Changing and Deleting Existing Stored Procedures

Update Statistics

Triggers

Examples

Looking at Existing Triggers

Changing and Deleting Existing Stored Procedures

Summary

22 User-Defined Searches

Overview

From the User''s Perspective

Constructing a where Clause

Construct Features

Completing the select Statement

Sample Function

Summary

23 The Input Array Statement

Input Array Overview

4GL Functions

Editing the Input

Data Validation

Sample Function

Summary

24 Input of a One-to-Many Relationship

One-to-Many Input Overview

Sample Module

Summary

Available:*

On Order

Summary

Summary

Table of Contents