Introduction to SQL for Reality

This topic introduces the concepts of SQL, ODBC and JDBC, and provides an overview of the SQL for Reality feature. It includes a list of standards adhered to, a brief description of the implementation of the SQL/ODBC and SQL/JDBC interfaces for Reality, on both the client and on the host system server, and a description of the Reality database as an SQL catalog. It also discusses the purpose and structure of a Reality SQL catalog, and hints on setting up a Reality SQL catalog.

It also describes some useful tools for listing columns and indexes in Reality.

Introduction

SQL (Structured Query Language) is a data management language that has become the established standard for retrieving information from a database management system (DBMS). An SQL database is defined as a set of tables with rows and columns defining fields that contain stored information. SQL uses an English-like syntax to form commands consisting of one or more keywords with the names of tables and columns and sometimes values supplied as parameters. The SQL processor finds and returns the information requested.

Reality provides two mechanisms by which SQL can be used: ODBC and JDBC:

The advantages of these interfaces include the application's ability to extract data from different types of database, and compatibility with nearly all DBMS data sources. This permits a single spreadsheet, for example, to include data from one or more independent databases, of different types, on different hosts.

What are ODBC and JDBC?

Open Database Connectivity (ODBC) is a Microsoft standard API which allows any ODBC-compliant application to access data in a database management system (DBMS) using SQL as the means of formulating queries. An ODBC-compliant application can therefore update and extract data from different databases, independent of the type of DBMS.

The application generates SQL queries based on point-and-click operations carried out by a user to select the data needed. The PC user generally requires no SQL knowledge, while the database administrator needs only a limited amount of SQL knowledge to perform routine maintenance tasks.

For example, a spreadsheet application, such as Excel, can be used to merge data from one or more different types of databases, such as Reality, Oracle and Access, on different hosts.

Client application using SQL to obtain data from different sources

An increasing number of PC applications support ODBC, including all Lotus and Microsoft major spreadsheet, word processing and reporting packages, and numerous other PC applications from other vendors.

Java Database Connectivity (JDBC) is similar to ODBC, but is a standard API for applications, applets and servlets written in Java.

SQL for Reality

SQL/ODBC

The 'SQL/ODBC for Reality' feature consists of a RealSQL driver DLL (client) on a PC and a Reality SQL server process on the host which together provide SQL access to a Reality database using the standard ODBC programming interface.

PC connections to the network are made via the PCi Standard Network Interface (PCSNI). Connections to the Reality database on the host computer are made via UNIX-Connect on a UNIX host and via networking software integral to Reality on a Windows host.

The diagram below shows the configuration of a complete system using SQL/ODBC to access a Reality database.

Configuration of a complete system that uses SQL/ODBC to access a Reality database

Note

On a UNIX host, the networking software comprises UNIX-Connect software. On a Windows host, the networking software is integral to Reality.

SQL/JDBC

In 'SQL/JDBC for Reality', the client consists of a package of Java classes that can be called from a Java application, applet or servlet. The RealSQL-JDBC driver is a 100% pure Java implementation based on the JDBC 1.2 standard. Because it is written entirely in Java, the driver is platform independent.

Note that when the driver is used in a Java applet, it is subject to the restrictions imposed by the controlling Java Security Manager. The applet may require a digital signature.

The RealSQL-JDBC driver can connect to a Reality database (V9.0 or later). The driver uses the JReal DDA package to make a connection and access data on the database. Classes in this package transmit DDA messages over a TCP/IP network.

Standards

Reality SQL

The SQL syntax used by Reality is based on the X/Open and SQL Access Group SQL specification (1992) and is broadly compliant with the ODBC 2.5 level 2 API core language. See SQL Statements Supported by Reality for details.

SQL/ODBC

The ODBC interface to Reality complies with:

SQL/JDBC

The JDBC interface to Reality complies with:

It should be noted, however, that the Reality JDBC Driver does not claim to be JDBC compliant, because Reality does not fully comply with SQL-92 Entry Level.

See SQL Statements Supported by Reality for details.