Release 10.1A: OpenEdge Data Management:
SQL Development

> ## Preface

> This Preface contains the following sections:

> - Purpose

> - Audience

> - Organization

> - Typographical conventions

> - Examples of syntax diagrams (SQL)

> ### Purpose

> OpenEdge Data Management: SQL Development provides information for developers who are using SQL within the OpenEdge® application development environment. The information in this manual is also useful for database administrators, and, to a lesser degree, to application end users.

> ### Audience

> The audience for this book is composed of three specific groups:

> - Database Administrators who will use the book to:

> - Create and maintain databases.
> - Create, modify, and revoke user privileges.
> - Tune database performance.
> - Perform installation and setup of servers and clients.
> - Application developers who will use the book to:

> - Manage database connections and set up data sources.
> - Create queries to draw information from the database.
> - Tune database queries.
> - Develop application business logic.
> - End users who will use the book to:

> - Connect to databases.
> - Call stored procedures.
> - Issue queries.
> - Understand error messages.

> ### Organization

> Chapter 1 "Introduction"

> Provides an overview of OpenEdge SQL and the OpenEdge SQL client/server architecture.

> Chapter 2 "JDBC Client"

> Presents an overview of the JDBC client, details on setting environment variables, and procedures for connecting to a database with the JDBC driver.

> Chapter 3 "ODBC Client"

> Offers an overview of the ODBC client and information on configuring data source.

> Chapter 4 "Data Control Language and Security"

> Reviews information on working with database security, creating users, and granting, modifying, and revoking privileges.

> Chapter 5 "OpenEdge SQL Data Definition Language"

> Furnishes information on OpenEdge SQL database structure and methods for creating, altering, and dropping database objects.

> Chapter 6 "OpenEdge SQL Data Manipulation Language"

> Provides information on the Data Manipulation Language statements, indexes, and join operations.

> Chapter 7 "Progress 4GL and OpenEdge SQL Interoperability"

> Addresses the interoperability of Progress 4GL and OpenEdge SQL.

> Chapter 8 "Data Control Language and Transaction Behavior"

> Summarizes information on transactions, isolation levels, and locking.

> Chapter 9 "Stored Procedures and Triggers"

> Provides information on using stored procedures and triggers.

> Chapter 10 "Optimizing Query Performance"

> Presents information on the operation of the SQL Query Optimizer and offers recommendations on how to get the best out of its performance.

> ### Typographical conventions

> This manual uses the following typographical conventions:

> Convention | Description
> --- | ---
> Bold | Bold typeface indicates commands or characters the user types, provides emphasis, or the names of user interface elements.
> Italic | Italic typeface indicates the title of a document, or signifies new terms.
> SMALL, BOLD CAPITAL LETTERS | Small, bold capital letters indicate OpenEdge® key functions and generic keyboard keys; for example, GET and CTRL.
> KEY1+KEY2 | A plus sign between key names indicates a simultaneous key sequence: you press and hold down the first key while pressing the second key. For example, CTRL+X.
> KEY1 KEY2 | A space between key names indicates a sequential key sequence: you press and release the first key, then press another key. For example, ESCAPE H.
> Syntax:
> `Fixed width` | A fixed-width font is used in syntax statements, code examples, system output, and filenames.
> `Fixed-width italics` | Fixed-width italics indicate variables in syntax statements.
> `Fixed-width bold` | Fixed-width bold indicates variables with special emphasis.
>     UPPERCASE
>     fixed width
> | Uppercase words are Progress® 4GL language keywords. Although these are always shown in uppercase, you can type them in either uppercase or lowercase in a procedure.
> | This icon (three arrows) introduces a multi-step procedure.
> | This icon (one arrow) introduces a single-step procedure.
>     lowercase
> | Lowercase words are source language elements that are not SQL keywords.
> [ ] | Large brackets indicate the items within them are optional.
> { } | Large braces indicate the items within them are required. They are used to simplify complex syntax diagrams.
> | | A vertical bar indicates a choice.
> ... | Ellipses indicate repetition: you can choose one or more of the preceding items.

> ### Examples of syntax diagrams (SQL)

> In this example, `GRANT`, `RESOURCE`, `DBA`, and `TO` are keywords. You must specify `RESOURCE`, `DBA`, or both, and at least one `user_name`. Optionally you can specify additional `user_name` items; each subsequent `user_name` must be preceded by a comma:

> Syntax
>     GRANT { RESOURCE, DBA } TO user_name [, user_name ] ... ;

> This excerpt from an ODBC application invokes a stored procedure using the ODBC syntax { `call` `procedure_name` `( param )` }, where braces and parentheses are part of the language:

> Syntax
>     proc1( param, "{ call proc2 (param) }", param);

> In this example, you must specify a `table_name`, `view_name`, or `synonym`, but you can choose only one. In all SQL syntax, if you specify the optional `owner_name` qualifier, there must not be a space between the period separator and `table_name`, `view_name`, or `synonym`:

> Syntax
>     CREATE [ PUBLIC ] SYNONYM synonym
>       FOR [ owner_name.]{table_name |view_name |synonym } ;

> In this example, you must specify `table_name` or `view_name`:

> Syntax
>     DELETE FROM [ owner_name.]{table_name |view_name }
>       [ WHERE search_condition ] ;

> In this example, you must include one expression `(expr)` or column position `(posn)`, and optionally you can specify the sort order as ascending (`ASC`) or descending (`DESC`). You can specify additional expressions or column positions for sorting within a sorted result set. The SQL engine orders the rows on the basis of the first `expr` or `posn`. If the values are the same, the second `expr` or `posn` is used in the ordering:

> Syntax
>     ORDER BY { expr | posn } [ ASC | DESC ]
>       [ , [ { expr | posn } [ ASC | DESC ] ] ... ]

> #### Long syntax descriptions split across lines

> Some syntax descriptions are too long to fit on one line. When syntax descriptions are split across multiple lines, groups of optional and groups of required items are kept together in the required order.

> In this example, `CREATE VIEW` is followed by several optional items:

> Syntax
>     CREATE VIEW [ owner_name.]view_name
>       [ ( column_name [, column_name ] ... ) ]
>       AS [ ( ] query_expression [ ) ] [ WITH CHECK OPTION ] ;

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095