Programming Informix SQL/4GL: A Step-By-Step Approach (Bk/CD), 2nd Edition

Description

• Copyright 1998
• Dimensions: 7" x 9-1/4"
• Pages: 512
• Edition: 2nd

• Book
• ISBN-10: 0-13-675919-X
• ISBN-13: 978-0-13-675919-5

67591-8

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-part reporting, stored procedures, and triggers. Discover sophisticated SQL techniques, including views, synonyms, aggregates, subqueries, and query optimization. Learn how and when to mix C with 4GL. This new edition includes a full chapter on the INFORMIX-4GL Interactive Debugger.

If you need an in-depth understanding of the realities of Informix database programming, you don't have to wade through all those huge Informix manuals. All the answers are right here—in Programming Informix SQL/4GL.

Anyone who has used Informix manuals knows their indexes can be difficult to use because there are so many of them and sometimes the references are quite difficult to locate if they are there at all. Programming Informix SQL/4GL includes a comprehensive index making topics easy to find.



Sample Content

Table of Contents

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. close. 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.

25. Basic Report Writing.

Report Overview. Getting the Report Data. Formatting the Report. Output. Order by. Control Blocks. On every row. Headers and Footers. Aggregates. Grouping. Final Totals. Sample Report. Summary.

26. Complex Reports.

Using Temp Tables, Unions, and Other SQL Tricks. Matrix Reports. Multi-Part Reports. Summary.

27. The 4GL Debugger.

Overview. Get Me Started, Quick! Environment. DBSRC. Debugger Files (.4db). fgldb Command Line Options. Creating a Custom Debugger Runner. Debugger Commands. Summary.

28. Mixing C and 4GL.

Calling C Functions from 4GL. Linking Compiled 4GL Code. Linking Rapid Development System Code. Calling 4GL Functions from C. Linking Compiled 4GL Code. Linking Rapid Development System Code. Summary.

Appendix A. INFORMIX-4GL Functions Called from C.


Appendix B. CD Contents and Contributors.

CD Contents. Software Contributors.

Bibliography and Other Informix Books from Prentice Hall PTR.

Books on Informix. Informix Manuals. General Database.

Index.

Preface

Preface Conventions used in this book.

About This Book.

Are you tired of wading through all those Informix manuals? This book will take you step-by-step from database design to working with Informix's implementation of SQL, and finally, to implementing programs in INFORMIX(-4GL.

• Topics are discussed in the order in which you will need to know the material.
• There are plenty of examples.
• The index is comprehensive.
• The chapter summaries provide a useful reference.
• The CD-ROM included with the book includes code examples from this book, and more. The book has been revised and updated to cover the most recent Informix product features, plus a whole new chapter on the INFORMIX-4GL Interactive Debugger.
  
  This book takes on the broad task of describing how to design and develop a database application using Informix's implementation of SQL and INFORMIX-4GL.
• The first chapter of the book contains an overview of relational database design.
• The next few chapters (2-6) show you how to create and maintain an Informix database, and explains the use of all the basic commands in Informix's implementation of SQL.
• Chapters 9-19 cover basic INFORMIX-4GL in an order designed to get you up and working quickly.
• Chapter 19 explains how to use Informix's version of SQL in INFORMIX-4GL and is followed by two chapters of advanced SQL topics.
• The rest of the book includes more advanced INFORMIX-4GL topics, including user defined searches, more complex screen input/output, report writing, the debugger and using INFORMIX-4GL with C.
  
  Going through this book chapter by chapter will allow you to design, create, maintain, and write applications for your own database system. The subjects are covered in the order in which you will typically need them when building a new system. A comprehensive table of contents and index allow quick reference to specific topics. A summary at the end of each chapter provides a quick reference for all the syntax and information covered in each chapter.
  
  Please feel free to e-mail any comments about this book to the author, Cathy Kipp (ckipp@verinet.com).



Updates

Submit Errata

