Advantage Database Server : The Official Guide

by Jensen, Cary; Anderson, Loy

• ISBN13: 9780072230840
• ISBN10: 0072230843
• Edition: 1st
• Format: Paperback
• Copyright: 2003-10-30
• Publisher: McGraw-Hill Osborne Media

Summary

Developers working with ADS have been asking for a book for more than two years. With the launch of ADS 7.0, we can satisfy the needs of this growing community with an Official Guide on ADS.

Author Biography

Cary Jensen, Ph.D. (Kingwood, TX) is president of Jensen Data Systems, Inc. (www.JensenDataSystems.com), a Houston-based company that provides application development and support.

Loy Anderson, Ph.D. (Kingwood, TX) is Vice President of Jensen Data Systems, Inc., and best-selling co-author of eighteen books with Cary Jensen.

Table of Contents

Acknowledgments	xix
Introduction	xxi
Part I ADS and the Advantage Data Architect
Chapter 1 Introduction to Advantage Database Server	3	(26)
Overview of Advantage Database Server	4	(3)
ADS Is High Performance	4	(2)
ADS Is Low Maintenance	6	(1)
ADS Is a Remote Database Server	7	(1)
Advantage Database Server Versus Advantage Local Server	7	(4)
Reducing Network Traffic	8	(1)
Improved Performance	8	(1)
Available Transactions	9	(1)
Improved Stability	9	(1)
When to Use the Advantage local Server	10	(1)
Advantage Tools	11	(9)
The Advantage Configuration Utility	11	(6)
Advantage Data Architect	17	(1)
Advantage ANSI Collation Utility	18	(2)
Building Database Applications Using ADS	20	(5)
Creating the Database	20	(2)
Creating the Client Applications	22	(3)
Features Added in ADS Version 1	25	(4)
Triggers	25	(1)
Full Text Search	25	(1)
Data Dictionary Upgrade functionality	25	(1)
The Advantage .NET Data Provider	26	(1)
The Advantage JDBC Driver	26	(1)
Extended SQL Support	26	(1)
Other Enhancements	26	(3)
Chapter 2 Creating Tables	29	(34)
ADS Supported Table Types	31	(2)
DBF formats	31	(1)
The Advantage Proprietary (ADT) format	31	(1)
Free Tables Versus Database Tables	32	(1)
Creating ADS Tables	33	(5)
Working with Tables	38	(5)
Opening a Previously Created Table	39	(2)
Navigating and Editing Data in a Table	41	(2)
Other Table Support Features	43	(20)
Data Security	43	(3)
Table Maintenance	46	(7)
Importing and Exporting Data	53	(6)
Exporting Table Structures as Code	59	(4)
Chapter 3 Defining Indexes	63	(28)
Overview of Index files	64	(7)
Why Create Indexes?	65	(1)
Types of Index Orders	66	(5)
Creating Indexes	71	(3)
Deleting or Changing Index Orders	74	(1)
Testing Indexes	74	(4)
Indexes and Performance	78	(2)
How Many Indexes Should You Define?	78	(1)
Scopes, Seeks, and AOFs	78	(2)
Full Text Search Indexes	80	(11)
Creating FTS Index Orders	81	(2)
Testing FTS Index Orders	83	(2)
Options for FTS Index Orders	85	(6)
Chapter 4 Understanding and Using Data Dictionaries	91	(32)
Data Dictionary Overview	92	(2)
Creating a Data Dictionary	94	(6)
Adding Tables to a Data Dictionary	100	(3)
Opening a Data Dictionary	100	(2)
Adding Existing Tables to a Data Dictionary	102	(1)
Removing a Table from a Data Dictionary	103	(1)
Data Dictionaries and Table Encryption	103	(3)
Enabling Table Encryption	103	(2)
Encrypting Database Tables	105	(1)
Binding Tables with Table Encryption Enabled	105	(1)
Implementing Security with Data Dictionaries	106	(12)
Controlling User Access	106	(2)
Defining Users and Groups	108	(1)
Adding Users	109	(2)
Checking User Rights	111	(1)
Understanding Groups	112	(1)
Using Access Rights and Groups	112	(4)
Changing Objects and Permissions	116	(1)
How field-level Permissions Are Enforced	117	(1)
Comparing Data Dictionaries	118	(5)
Chapter 5 Defining Constraints and Referential Integrity	123	(26)
Understanding Constraints	124
Alternatives to Constraints	125
Advantages of Constraints	121	(1)
Creating Constraints	121	(24)
Defining field-level Constraints	121	(12)
Defining Record-Level Constraints	133	(3)
Defining Referential Integrity Constraints	136	(1)
Defining Referential Integrity Using RI Objects	137	(4)
Update and Delete Rules	141	(1)
Defining RI Rules Using the ER Diagram Tool	142	(3)
Comments About Constraints and Referential Integrity	145	(4)
Using Field-Level Constraints	146	(1)
Using Record-Level Constraints	146	(1)
Using Referential Integrity	147	(2)
Chapter 6 Working with Views	149	(18)
Overview of Views	150	(1)
Creating Editable Views	151	(7)
Testing a View	152	(3)
Making Views Accessible	155	(2)
Changing a View	157	(1)
Views That Use Views	158	(3)
Modularizing Operations with Views	158	(2)
Views as temporary Tables	160	(1)
Selecting Record Subsets from Views	161	(1)
Views That Use Other Database Tables	162	(5)
Qualifying a Table Using a Dictionary Path	162	(2)
Qualifying a Table Using Data Dictionary Links	164	(3)
Chapter 7 Creating Advantage Extended Procedures (AEPs)	167	(30)
Overview of Stored Procedures	168	(2)
The Structure of AEPs	170	(4)
The Startup Function	171	(1)
The Shutdown function	172	(1)
The Get Interface Version function	172	(1)
Stored Procedure functions	173	(1)
Writing AEPs	174	(15)
Creating AEPs Using Delphi	176	(4)
Creating AEPs Using C# with Visual Studio .NET	180	(6)
Creating AEPs Using VB.NET	186	(2)
Creating AEPs Using Visual Basic 6	188	(1)
Using AEPs	189	(8)
Installing AEPs	189	(4)
Testing AEPs	193	(1)
Debugging AEPs	193	(2)
Deploying AEPs	195	(2)
Chapter 8 Defining Triggers	197	(36)
Overview of Triggers	198	(5)
BEFORE Triggers	200	(1)
INSTEAD OF Triggers	201	(1)
AFTER Triggers	202	(1)
Creating Triggers	203	(2)
Triggers and Transactions	203	(1)
Trigger Priority	204	(1)
Triggers and Performance	204	(1)
Getting Started	205	(1)
Example Triggers	205	(21)
Writing Triggers in SQL	207	(4)
Creating Triggers in Delphi	211	(6)
Creating Triggers in C# with Visual Studio .NET	217	(6)
Creating Triggers in VB.NET with Visual Studio .NET	223	(3)
Using Triggers	226	(7)
Registering Triggers	226	(3)
Updating and Deleting Triggers	229	(1)
Debugging Triggers	230	(1)
Deploying Triggers	230	(3)
Part II Using Advantage SQL
Chapter 9 Introduction to Using Advantage SQL	233	(36)
Overview of Advantage SQL Queries	234	(18)
Field Names and Table Names	236	(1)
Field Names and Table Aliases	237	(1)
Keywords	238	(2)
SQL Literals	240	(3)
Operators in SQL Statements	243	(3)
SQL Scalar functions	246	(2)
Parameters in SQL Statements	248	(1)
Comments	249	(1)
SQL Scripts	250	(2)
Additional ADS SQL Topics	252	(5)
SQL Statement Dimensions	252	(1)
Live Cursors Versus Static Cursors	252	(4)
Using Subqueries	256	(1)
Advantage SQL Utilities	257	(12)
Advantage Query Builder	258	(4)
Native SQL Utility	262	(3)
ODBC SQL Utility	265	(4)
Chapter 10 Using SQL to Perform Common Database Operations	269	(32)
Creating and Modifying Objects	271	(4)
Creating Tables and Indexes	271	(4)
Selecting Data	275	(21)
Selecting and Expressions	216	(65)
Conditional Selection	281	(2)
Sorting Data	283	(2)
Grouping Data	285	(5)
Multitable Queries	290	(3)
Full Text Search Queries	293	(3)
Changing Data	296	(3)
Inserting Records	296	(1)
Updating Records	297	(1)
Deleting Records	298	(1)
Using Transactions	299	(2)
Chapter 11 Using SQL to Access Metadata and Managed Data Dictionaries	301	(28)
System Tables	302	(8)
Getting Dictionary Information	304	(1)
Getting Table-Related Information	304	(3)
Getting User and Group Information	307	(2)
Getting Other Object Information	309	(1)
Other System Tables	310	(1)
Dictionary-Related SQL Statements	310	(4)
Creating a Data Dictionary	311	(1)
Granting Rights	311	(2)
Revoking Rights	313	(1)
System Stored Procedures	314	(15)
Executing Stored Procedures	316	(1)
Changing Database Properties	316	(1)
Adding Free Tables and Indexes	317	(2)
Creating and Dropping RI Definitions	319	(1)
Creating and Dropping links	320	(2)
Changing Table and Field Properties	322	(2)
Creating and Modifying Users and Groups	324	(5)
Part III Accessing ADS Data
Chapter 12 Using ADS from Delphi	329	(22)
ADS and Delphi	330	(2)
Understanding the TDataSet Descendants	331	(1)
Performing Basic Tasks with ADS and Delphi	332	(7)
Connecting to Data	332	(3)
Accessing an ADS Table	335	(1)
Reading and Writing Data	335	(1)
Executing a Query	336	(1)
Using a Parameterized Query	337	(1)
Calling a Stored Procedure	337	(2)
Navigational Actions with ADS and Delphi	339	(6)
Setting an Index	340	(1)
Finding a Record Based on Data	340	(1)
Setting a Range	341	(1)
Setting a Filter	342	(2)
Scanning a Result Set	344	(1)
Administrative Operations with ADS and Delphi	345	(6)
Creating a Table and Granting Rights to It	345	(3)
Changing a User Password	348	(3)
Chapter 13 Using ADS from Java	351	(20)
ADS and Java	352	(2)
Performing Basic Tasks with ADS and Java	354	(10)
Connecting to Data	354	(2)
Executing a Query	356	(4)
Using a Parameterized Query	360	(1)
Reading and Writing Data	361	(2)
Calling a Stored Procedure	363	(1)
Navigational Actions with ADS and Java	364	(1)
Scanning a Result Set	364	(1)
Administrative Operations with ADS and Java	365	(5)
Creating a Table and Granting Rights to It	366	(2)
Changing a User Password	368	(2)
The Advantage JDBC Driver and JBuilder's DataExpress	370	(1)
Chapter 14 Using ADS from Visual Basic and ADO	371	(26)
MDAC, OLE DB, ADO, and ADS	372	(4)
Performing Basic Tasks with ADS and ADO	376	(10)
Connecting to Data	376	(4)
Executing a Query	380	(1)
Using a Parameterized Query	381	(2)
Reading and Writing Data	383	(2)
Calling a Stored Procedure	385	(1)
Navigational Actions with ADS and ADO	386	(5)
Setting an Index	387	(1)
Finding a Record Based on Data	388	(1)
Setting a filter	389	(1)
Scanning a Result Set	390	(1)
Administrative Operations with ADS and ADO	391	(6)
Creating a Table and Granting Rights to It	392	(2)
Changing a User Password	394	(3)
Chapter 15 Using the Advantage .HET Data Provider	397	(22)
ADS and ADO.NET	398	(2)
Performing Basic Tasks with ADS and ADO.NET	400	(10)
Connecting to Data	401	(2)
Executing a Query	403	(2)
Using a Parameterized Query	405	(1)
Reading and Writing Data	406	(3)
Calling a Stored Procedure	409	(1)
Navigational Actions with ADS and ADO.NET	410	(1)
Scanning a Result Set	410	(1)
Administrative Operations with ADS and ADO.NET	411	(8)
Creating a Table and Granting Rights to It	412	(2)
Changing a User Password	414	(5)
Chapter 16 Using ADS with ODBC, PHP, and DBI/Pert	419	(22)
Accessing ADS Using the Advantage ODBC Driver	420	(6)
Who Should Use the Advantage ODBC Driver	421	(1)
Connecting to ADS Using the Advantage ODBC Driver	421	(5)
Accessing ADS Using the Advantage PHP Extension	426	(2)
A Sample PHP Web Site	428	(9)
Connecting to ADS Using PHP	430	(1)
Using Parameterized Queries in PHP	431	(2)
Getting Tables from Result Sets Using PHP	433	(1)
Editing Data	434	(1)
Scanning Result Sets	435	(1)
Calling a Stored Procedure	435	(2)
Accessing Data Using the Advantage DBI Driver (for Peri)	437	(4)
Part IV Appendixes
Appendix A ADS Installation and Other Setup Issues	441	(10)
Installation	442	(2)
Running ADS Without a Network	444	(4)
Disabling DHCP Media Sensing	445	(1)
Installing the Microsoft Loopback Adapter	446	(2)
Getting Information About Advantage Error Codes	448	(1)
The Error Table	449	(1)
Exclusive Locks and Cursor Caching	450	(1)
Appendix B Installing the Code Samples	451	(4)
Installing the Sample Database and Code	452	(2)
Downloading the Code Samples	454	(1)
Appendix C Book Web Support Site	455	(2)
Index	457