Overview

Introduction

This manual describes the ACCENT R interface to Oracle and how ACCENT R manipulates Oracle data.  The Oracle database is a relational database engine that supports SQL (Structured Query Language).  This manual shows how to establish communication between Oracle and ACCENT R and how to retrieve, update, insert, or delete data using On-line SQL and ACCENT R SPL (Structured Programming Language).  The interface between ACCENT R and Oracle that is covered in this manual is for VAX/VMS systems only.

This manual describes some of the capabilities of the Oracle relational database management system and how ACCENT R Open SQL opens that system to other database methodologies.  ACCENT R Open SQL removes restrictions in database access typically found using 4th generation application development and SQL as a data manipulation paradigm.  Open SQL statements can be embedded directly into SPL (Structured Programming Language) Process Modules or can be generated through the 4th Generation Tools.  See Figure 4.

This manual assumes that you are somewhat familiar with ACCENT R and Oracle.  ACCENT R and Oracle are terms used throughout this manual to refer to all ACCENT R products and Oracle license types, respectively.  For specific information, see the ACCENT R Professional Desk Reference Set and the Oracle RDBMS SQL Language Reference Manual.

The SQLgateway to Oracle

Oracle is a relational database management system (RDBMS) marketed by Oracle Corporation.  The use of Oracle is defined by your VAX/VMS license agreement.  ACCENT R Open SQL connects to all license types of Oracle through the SQLgateway to Oracle.

ACCENT R relies upon Oracle to function as a database server or database engine.  ACCENT R functions as a client in this relationship.  The database engine, Oracle in this case, handles the “nuts and bolts” of the application development system.  Together with the central application repository within ACCENT R, Oracle stores and manages the definitions of data structure, handles the indexing, and conducts data storage, retrieval and updating operations.

The SQLgateway to Oracle can be used with ACCENT R to develop applications which interact with other Oracle software products such as SQL* Forms and SQL* Report writer.  Through Open SQL, ACCENT R permits interoperability among many Oracle software products and across hardware platforms.  For example, it is possible to access Oracle databases created with ACCENT R from the IBM PC and mainframe.  ACCENT R connects to Oracle using Pro* C, an ANSI-compliant implementation of SQL data manipulation language.

Click here to view Figure 4 Database Access Model

Click here to view Figure 5 Software Implementation/Maintenance Model

ACCENT R’s 4GL combined with Open SQL offers a total development environment.  ACCENT R provides Open SQL statements to retrieve and update data on the Oracle database from within a Process Module (Table 1).  Also within ACCENT R is a powerful version of On-line SQL called OSQL.  OSQL contains ANSI-standard SQL syntax which has been extended to support fast application prototyping and development (Table 2).  Some of the unique features are:

• Open SQL is a concise and powerful implementation of SQL which supports transparent access and migration among SQL database servers.

• All table definitions, 4GL programs, system documentation, menus, screens, and reports are stored within a central application repository called the Data Base Library (DBL) so ACCENT R applications can easily be interfaced to other tools and applications on VAX/VMS.

• ACCENT R’s On-line Structured Query Language (OSQL) serves as an on-line, window-based, tool for accessing Oracle databases.  The DEFINE PM clause of each command allows for quick prototyping of applications.

Figure 6 Open SQL Statements
Extensions to SPL and embedded in PM (Process Module) Objects

• The ability to use dynamic string expressions in the clauses for the SELECT, INSERT, DELETE, and UPDATE statements.  In other words, you can compose Open SQL statements “on the fly” within the Process Module.  This is in contrast to “embedded” SQL statements in other languages in which the WHERE and ORDER BY clauses must be hard-coded.

• The FETCH statement retrieves an individual row after a group of qualified rows has been identified with a SELECT statement.

• A single Process Module can access and manipulate data from several different sources through Open SQL.  You can access data from RMS and Oracle, as well as ACCENT R’s DB-MACH2.

Organization of this Manual

Here are the topical sections presented in this manual.

• Getting Started with SQLgateway to Oracle - Everyday Examples
• Accessing Interactive SQL*Plus
• Starting an ACCENT R Session
• Declaring an Oracle Server for Use
• Creating or Using a Data Base Library
• Defining an SD object for Oracle
• Establishing and Manipulating Oracle Tables from ACCENT R
• ACCENT R 4th Generation Tools
• ACCENT R OSQL Commands for Data Manipulation and Application Prototyping
• Process Module Statements that Manipulate the Oracle Data
• A Syntax Summary
• Appendix A - The Sample Database BOOKS
• Appendix B - A Comprehensive Example

Terminology

Figure 8 shows ACCENT R terms and the corresponding Open SQL terms.

Data Base Library Objects

All of the objects or pieces of the application are stored in the Data Base Library (DBL).  Each of the 13 different object types plays a role in the development of the application.  For more information about the function of DBL objects, read the Technical Survey of ACCENT R which is included as a preface to this manual.  Figure 9 depicts the function of the DBL.  Figure 5 shows the function for each DBL object type.

Figure 9 Function of the DBL

More information about the function of each object can be obtained from Volume I of the ACCENT R Professional Desk Reference Set or from the Technical Survey of ACCENT R included as a preface to this manual.

The simplest Oracle application will consist of just a few of the possible object types.  An application requires that a DBL (Data Base Library) be present.  The DBL serves as a data dictionary, a configuration and control mechanism, and an application repository.

• Associated Documentation
• ACCENT R 4GL Basic System - Reference Manual, Volume I
• ACCENT R 4GL Basic System - Reference Manual, Volume II
• ACCENT R Quick Reference Guide
• Getting Started with Express
• Getting Started with Reporter