SQL is an Internet data access language. Web servers use SQL as a standard language for accessing corporate databases. Commercial SQL database vendors offer two basic techniques for using SQL within a web application program: API and embedded SQL. This is a discussion of some of the API and embedded SQL options.
Application Program Interface (API)
In API's, the program communicates with DBMS through a set of
functions. The program passes SQL statements to the DBMS through
the API calls and uses API calls to retrieve query results. This
approach does not require a special precompiler. Microsoft introduced
the Open Database Connectivity (ODBC) API shortly after the debut
of SQL Server. More recently, Java Database Connectivity (JDBC)
has emerged as an important API for accessing a relational database
from within programs written in Java. Oracle also has its own
API called Oracle Call Interface (OCI).
A Java API is a collection of classes, methods and data members that you can import into Java. Sun has categorized the various API packages as either part of the core API or as a standard extension. The core API is defined as the base set of Java classes. The graphic below will help illustrate how Java JDBC API fits into the larger picture.
The Java Database Connectivity (JDBC)
Java Database Connectivity (JDBC) is a set of Java classes that
provide an ODBC-like interface to SQL databases. JDBC is now part
of the Java JDK core. The JDBC classes provide standard features
such as: simultaneous connections to several databases, transaction
management, simple queries, manipulation of pre-compiled statements
with bind variables, calls to stored procedures, streaming access
to long column data, access to the database dictionary, and descriptions
of cursors. JDBC supports both static and dynamic SQL. Programming
in JDBC involves creating objects and then setting their properties
and calling their methods. JDBC provides uniform access to a wide
range of relational databases, using platform-independent Java
code. JDBC also provides a common base on which higher level tools
and interfaces can be built. All the classes associated with the
JDBC API are grouped together in the java.sql package. The Java
1.2 software bundle includes JDBC and the JDBC-ODBC bridge.
Oracle JDBC thin driver is a Type 4 driver that uses Java sockets to connect directly to Oracle. It provides its own implementation of a TCP/IP version of Oracle's SQL*Net. Because it is 100% Java, this driver is platform independent and can also run from a Web Browser. The JDBC thin driver provides access to Oracle from the Web. It is smaller and faster than the OCI drivers, and does not require a pre-installed version of the JDBC drivers.
The Oracle Call Interface
Oracle provides a callable API, known as Oracle Call Interface,
or OCI. It consists of a library of C language routines to allow
C programs to send SQL statements to the database and interact
with it in other ways. OCI has been available for many years and
remained fairly stable. With the introduction of Oracle8, OCI
underwent a major revision, and many of the original OCI calls
were replaced by new, improved versions. However, the original
OCI calls are still supported.
With the introduction of Oracle8, the Oracle Call Interface was effectively replaced with a newer, more modern, and far more complex OCI. The "new" OCI uses many of the same concepts as the ODBC, including the use of handles to identify interface "objects." Several hundred routines are defined in the API. Oracle's JDBC OCI drivers are Type 2 JDBC drivers. You must use a JDBC OCI driver appropriate to your Oracle client installation. The OCI driver works through either SQL*NET or Net8. The JDBC OCI drivers allow you to call the OCI directly from Java, thereby providing a high degree of compatibility with a specific version of Oracle. Because they use native methods, they are platform specific.
Embedded SQL
In this approach, SQL statements are embedded directly into the
program's source code, intermixed with the other programming language
statements. Special embedded SQL statements are used to retrieve
data into the program. A special SQL precompiler accepts the combined
source code and, along with other programming tools, coverts it
into an executable program.
JSQL and SQLJ
JSQL is an ANSI standard way of interfacing SQL and Java. JSQL
enables developers to embed SQL statements directly into Java.
SQLJ is Oracle's implementation of the JSQL. SQLJ supports static
SQL. Developers can use familiar and relatively compact SQL statements,
without having to manually write all the low-level JDBC code.
SQLJ provides compile-time schema validation and syntax checking.
At compile time, the source files that contain embedded SQLJ statements
are run through a translator. The SQLJ translator converts the
SQL statements into 100% pure Java code, using standard JDBC calls
for data access. Because SQLJ programs are converted to JDBC,
they retain their multi-vendor interoperability, and can be used
with any JDBC drivers. The SQLJ precompiler translates the SQLJ
clauses into their equivalent JDBC calls. Oracle developers familiar
with Oracle precompilers such as Pro*C can think of SQLJ as offering
the same benefits. SQLJ makes it much easier to produce accurate
code. SQLJ dramatically reduces the number of lines of code required
when compared to JDBC API programming.
The SQLJ syntax specification was developed through the cooperative efforts of Oracle and other vendors, including Sybase, Tandem, IBM, and Javasoft. Oracle has delivered the first implementation of a SQLJ translator and runtime classes, and JDeveloper is the first Java tool with SQLJ integrated into the source-level compiler and debugger.