SQL 3 and SQL Middleware

1
SQL 3 and SQL Middleware

• Letitia Chu

2
SQL Origin

• Structured Query Language
• First developed in mid 1970s.
• English-like front-end query language
• Based on the solid mathematical foundation of set
  theory and predicate calculus
• Different versions SQL-89, 92 and SQL3

3
Front-end, back-end Application

• Front-end
• User does not need to understand SQL
• GUI based, off-the-shelf, commercial software
  product
• Generate SQL requests, that is directed to the
  database

• Back-end
• Where things happen behind the scenes
• Transparent to the database end user
• Includes the actual database server, data
  sources, and middleware

4
What is middleware?

• Distributed software that support interactions
  between clients and servers
• Common API to different vendors databases.
• Responsible to assist database client to find the
  database server.
• Does not include
• Client user interface
• Server application domain
• Database

5
Creating applications - integrating SQL into
existing programming languages. -Two competing
approaches

• Embedded SQL
• ESQL SQLJ
• Properties
• Applications must be pre-compiled bound to DB.
• Only Support static SQL.
• Requires target database to be known ahead of
  time.

• SQL Call-Level Interface (CLI)
• X/Open CLI, JDBC, ODBC SAG CLI
• Properties
• No precompiler required no need to bound to DB.
• Only support dynamic SQL.
• No need to know target database ahead of time.

6
Embedded SQL (ESQL)

• Defined ISO SQL-92
• Syntax for embedding SQL in C, Ada, PL/I COBOL,
  FORTRAN, Pascal and MUMPS
• Syntax
• EXEC SQL
• SQL_STATEMENT

• Disadvantages
• Target database must be known when developing
  application.
• Installation involves binding applications to
  each server database
• Code has to recompile for each vendors database
  server

7
SQLJ (Javas Embedded SQL)

• Put together by IBM, Oracle and Tandem in 1997.
• In 1998, SQLJ became Part 10 in ISO-SQL3.
• Allows user insert SQL inside a Java program
• SQLJ precompiler generates embedded JDBC calls.

• Advantages
• Easier than JDBC
• Easier compiler-time checking
• Syntax
• Static SQL appears in SQLJ clauses - begins with
  sql, e.g.
• sql DELETE FROM Building

8
SQL Call-Level Interfaces (CLI)X/Open CLI

• History
• In 1988, 44 vendors created SQL Access Group
  (SAG) - provides a unified standard for remote
  database access
• In 1994, SAG turned the finished CLI to X/Open,
  SAG CLI now known as X/Open CLI.

• Functions
• CLI wraps SQL generated by an application, and
  submit to DBMS.
• Supports dynamic SQL
• Functions correspond to the SQL-92 specification.
• Provides common SQL semantics
• Provides common error handling and reporting

9
Microsoft ODBC CLI

• About
• Open Database Connectivity Windows API standard
  for SQL - an extended version of SAG CLI.
• Most database server vendors, e.g. IBM, Oracle
  and etc. support ODBC API with their native SQL
  APIs.

• Drawbacks
• ODBC specification is controlled by Microsoft
• future is unknown.
• ODBC drivers are
• difficult to build and maintain
• have different conformance levels, and not well
  documented.
• ODBC layers
• introduce a lot of overhead (especially for
  updates and inserts)
• are seldom as fast as native APIs.

10
Object CLIs JDBC and OLE

• Provide CLI via object interfaces (and classes)
  instead of procedural APIs.
• Both support distributed data access via
  off-the-shelf ORBs

• 1. JDBC
• Java Database Connectivity

• 2. OLE DB
• An open specification designed to build on the
  success of ODBC by providing an open standard for
  accessing all kinds of data .

11
Why is JDBC valuable?

• What does JDBC API do?
• Establish a connection with a data source
• Send queries and update statements to the data
  source
• Process result.

• Advantages
• An application with Java VM can access virtually
  any data source and run on any platform.

12
OLE DB and ADO

• UDA (Universal Data Access)
• Microsofts new strategy where one can access
  data regardless of type, format or location
• Offers a common interface and middleware to
  access all types of data both SQL and non-SQL

• Key components included
• ADO (ActiveX Data Object), OLE DB ( Object
  Linking and Embedding), and ODBC.

13
JDBC API vs.. ODBC and UDA

• Before JDBC API was developed, Microsofts ODBC
  API was the most widely used, so why do we need
  JDBC?

• JDBC
• keep simple things simple
• allowing more advanced capabilities where
  required.
• ODBC cannot be use directly in Java because it
  uses a C interface. Java native calls are error
  prone.

• ODBC is hard to learn.
• It mixes simple and advanced features together
• It has complex options even for simple queries.
• Translating ODBC C API into Java API is not
  desirable.
• Programmers no need to worry memory management
  when using Java API

14
Open SQL Gateways

• What is ?
• Translate SQL calls into an industry-standard
  common Format and Protocol (FAP).
• FAP provides common wire-level protocol between
  client and server.
• Example-
• IBIs EDA/SQL
• ISO/SAG Remote Data Access (RDA),
• IBM DRDA

• Functions
• Acts as the broker to translate client API calls
  into FAP, transport to Server.
• Provides a standard SQL interface (ESQL or CLI)
• Must be able to locate server and provide catalog
  service.

15
IBI (Information Builders Incorporated) EDA
(Enterprise Data Access)/ SQL

• Features
• It is a family of open gateway using SQL to
  access over 72 relational non-relational
  database servers.
• Owns over 10 of the data access middleware
  market
• Allows CORBA and Java clients to access data on
  the 72 databases and file structures that EDA
  supports today.

• Supports
• JDBC interface
• Java-based COBRA ORB called Enterprise Component
  Broker (ECB)

16
ISO/SAG RDA

• Why not popular?
• RDA is not very tolerant of SQL deviations and
  extensions
• Thus, it is not very popular because of its lack
  of vendor interest
• Major vendors are not implementing it, only
  support by small companies.

• About
• One of SAG s original goals - port (and extend)
  the RDA FAP to the TCP/IP protocol.
• Functionality is equivalent to SQL-89 and SQL-92
  specifications.

17
IBM DRDA (Distributed Relational Database
Architecture)

• IBM is promoting DRDA as the standard for
  federated database interoperability.
• Goal - provides interoperability for fully
  distributed heterogeneous relational database
  environments by defining the protocols (FAP) for
  database client-server and server-server
  interactions.

• DRDA supporters
• Major Database and gateway companies - Oracle,
  Sybase, XDB, Ingres, Novell, Informix, Cincom,
  Micro Decisionware, Inprise (i.e. Borland), IBI,
  Progress, and Centura.
• DRDA is the bound that ties the DB2 family
  together.

Drawbacks DRDA requires all client compiles
with the servers SQL syntax and semantics,
limits its level of compliance.
18
SQL3

• What is SQL3?
• Latest version of SQL.
• Weighs over 2000 pages
• developed by ANSI and ISO for the last three
  years.
• Upward compatible with SQL-92.
• major extension - addition of an extensible,
  object-oriented type system.

• Why develop SQL3?
• In relational databases, tables are usually basic
  types - integer, character, no extension of basic
  type is allowed.
• OO applications, make extensive use of object
  features user extensible type, complex and
  composite objects.
• Object relational database, extends relational
  data model, these extensions became SQL3

19
SQL3 new feature Part 2 - object
extensions

• SQL-92 offers no facilities to define complex
  data types.
• SQL3 Part 2 - defines the new object extension
  features, allow users capture the
  application-specific behavior as part of the
  database.

• Extend functions
• User defined functions
• Extend Data types
• Large objects, Distinct types, Structured types

20
Extended Function

• Extended Functions
• Allows user to create functions by Java or other
  languages.
• How to create?
• use CREATE FUNCTION.
• Support function overloading.
• FENCED or UNFENCED FENCED - function must run in
  an address space separate from the database to
  protect database integrity against accidental
  damage that might be inflicted by the function.

21
Extended data types

• Distinct types are the supplement of the database
  systems built-in data types

• Extended data types
• For storage of very large objects (images, audio,
  video) and manipulation of complex behavior (
  components of a software design) by database
  application.

CREATE TYPE DOLLAR AS NUMERIC (10, 2)

• Structured types transform relational databases
  into object-relational databases, once created,
  can used as
• Type of column in a table
• Type of a table

• Two new build-in types
• BLOB contains up to 2 gigabytes of binary data.
• CLOB contains up to 2 gigabytes of single-byte
  character data

CREATE TYPE RESIDENCE ( street VARCHAR
(100), city VARCHAR (100), occupant REF(PERSON
))
22
SQL Part 1 to 4

• Part 1 SQL / Framework
• basic definition and description of SQL3
• Part 2 SQL/ Foundation
• Covers triggers, recursive queries, roles,
  collections, and object SQL
• Part 3 CLI
• Callable Level Interface
• Part 4 PSM
• Persistent Storage Modules

• Part 4 PSM
• The procedure specified in SQL/PSM cannot be
  invoked directly from an application, but only
  through SQL code.
• Applied SQL invoked routine by the CALL statement
• A common language to write stored procedure.
• Re-evaluate error handling by defining exception
  handlers -allows SQL routines to respond to
  exception or completion conditions raised when
  executing stored procedures.

23
Part 4PSM

• CREATE PROCEDURE get_info
• (catalog CHARACTER (10),
• CD_name CHARACTER VARYING (50),
• first_track CHARACTER VARYING (40)),
• - The codes to do the work goes here
• SET CD_name
• SET first_track
• END PROCEDURE get_info

• Calling this procedure-
• Directly invoked from a host
• language e.g. C, COBOL
• is not permitted
• Cdname get_name (MD105299 A)
• SQL invoked functions can only be invoked as part
  of the execution of an SQL statement
• EXEC SQL SET Cdname get_name(MD105299
  A)
• To invoke this procedure
• CALL get_info (MD105299-A, var1,
  var2)
• Calling it directly from ESQL
• EXEC SQL CALL get_info (MD105299-A, var1,
  var2)

24
SQL3 Part 5 to 10

• Part 8SQL Object
• This is deleted since most of the extend object
  constructs now folded into the core SQL object
  model.
• Part 9 SQL/Med
• Defines management of external data - datalink
  type, abstract table, large BLOBs, federal DB
  support.
• Will not be completed until late 2000 or early
  2001.
• Part 10SQL/OLB
• Defines object language binding for SQL - first
  binding is SQLJ

• Part 5 SQL/Binding
• defines SQL binding to other languages via ESQL
  and dynamic SQL.
• Part 6 SQL/Transactions
• defines how database participate in global
  transaction.
• Might be cancelled due to lack of a need, DBMS
  vendors seems to be interfacing SQL with X/Open
  XA standard just fine.
• Part 7SQL/Temporal
• defines how SQL handles time-series data, so that
  users are able to submit time as a variable.

25
New Technological Terms

• Triggers special user-defined actions usually
  in the form of stored procedures, they are
  automatically invoked by the server based on the
  data-related events.

• Roles permissions (and privileges) that user
  assign to groups, not to individuals users are
  assigned to roles so that they inherit the
  permissions and privileges that come with a role.

• Persistent, implies that the stored modules are
  unlikely to come and go without specific
  application or administrative action, similar to
  persistent data in SQL systems

26
New Technological Terms

• ORB (Object Request Broker) With an object
  server, the client/server application is written
  as a set of communicating objects. Client
  objects communicate with server objects using an
  Object Request Broker. The client invokes a
  method on a remote object. The ORB locates an
  instance of that object server class, invokes the
  requested method, and returns the results to the
  client object.

• Java Bean A component architecture that enables
  creation, assembly, and use of dynamic Java
  components. JavaBeans API defines a standard set
  of interfaces and behaviors that enable user to
  build reusable Java

27
New Technological Terms

• ADO (ActiveX Data Objects) The architecture from
  Microsoft that users high-level components and
  objects for combining and creating applications.
  It is a set of component interfaces that allows
  developers to build both desktop applications and
  Web application.

• OLE DB Microsofts low-level interface to data
  across the organization. For more information,
  go to http//www.oledb.com
• http//www.microsoft.com/data/oledb/oledb20/

28
Bibliography

• Robert Orfali, Dan Harkey, Jeri Edwards. (1999)
  Client /Server Survivial Guide, Third Edition,
  Wiley.
• Ryan K. Stephens and Ronald R. Plew, (1998) Teach
  Yourself SQL in 24 Hours, Sams Publishing.
• Dan Chang, Dan Harkey, (1998) Client / Server
  Data Access with Java and XML, John Wiley Sons,
  Inc.
• Jim Melton (1998), SQLs Stored Procedures, A
  Complete Guide to SQL/PSM, Morgan Kaufmann
  Publishers, Inc. San Francisco, California.