The Single Tier Database

The original database system was located on a single machine.
The user interface or presentation logic, the application logic,
and database access code all resided on a single mainframe.
The mainframe might have had hundreds or even thousands of
terminals attached to it.

The terminals were simply relay devices for simple input and
output. The keystrokes conveyed ASCII characters into the
mainframe while the mainframe output was sent to a printer
or a monitor.

In the early days, there was no CPU present on what were
called 'dumb terminals.  Data might have been stored in files
according to a number of data schemes. In the early days the
relational model had not become dominant yet.

The geographic scope of the system was likely to have been
very limited as the networks we take for granted today were in
early development.     

// pre-relational database era

The Mainframe and the so-called 'Dumb' Terminals
of the One Tier Model

                 Mainframe
  terminal 1 ___|  O _ O  |___  terminal 2
  terminal 3 ___|         |___  terminal 4
  terminal 5 ___|         |___  terminal 6
  terminal 7 ___|         |___  terminal 8
  terminal 9 ___|________ |___  terminal N
                _|_     _|_
      

The Two-Tier Model

With the advent of 'Socket' technology in the 80s, the database
evolved into a two-tier design. Socket connections allowed a
client machine to communicate with a server locally or at some
distance. The 'presentation' logic, perhaps by this time a graphical
user interface, would be coupled to 'application' logic that ran on a
client's machine. The database logic would reside on a database
server. This is still a popular model in use today.

The two-tier model had short comings. A major sticking point,
this model frequently locked a user into vendor version updates.
Switching vendors involved re-writing and or re-installing front-end
applications. The combination of user interface and application
logic running on the client systems, at the time used a large part
of the clients resources and were very costly to maintain.          

// front end maintenence costs were high & the model locked
// the user to vendor updates hard to update client software

Two Tier Model

  Database Server  <--Socket Connection-->    'Fat' Client

     

Three or Multi-Tier Database Model

A third tier evolved providing several advantages over the simple
two-tier model. The 'fat' client could be reduced to a 'thin client',
providing a user interface and some connectivity software. The
middle tier could concentrate on supplying the application logic
where parameters from the client would be marshalled into SQL
statements and executed against a database.

Scaling and Load Balancing

// Three tier models allow the addition of 'thin' clients

The middle tier could be multithreaded to accept multiple client
requests and could also be attached to one or more database
servers. The middle tier also provides a good vantage point to
scale the system, adding more data resources as required with
minimum disruption to the users.

// a multithreaded middle tier allows enhanced performance and scalability

Business Rule Support

Business rules are easily implemented at the middle tier.
(Business rules are any organization directives, procedures
or policies that have been established.) Business rules also
may originate outside an institution for example government
regulations. From a practical standpoint, the middle tier
is an easy place to implement management rules. 

// the middle-tier can serve as a base for business rule implementation

As an example, real time on-line credit spending limits can
be imposed based on a customer's payment history or
regional pricing can be implemented based on taxes and
tariffs of a locality.

// examples, credit limits or imposing local taxes and tariffs

Security Location

Security controls also benefit from being located on a middle
tier. The middle tier is a good place to center firewalls and
create DMZs, or 'demilitarized zones' between the Internet
and a company's intranet.

// middle tier was a good security vantage point for firewalls

Facilitates Upgrading

The middle tier facilitates adding or upgrading application and
data management software without disturbing the front or back
ends of the system. The middle tier can also mediate the use of
different protocols by clients.

N-Tier or Multi-tier Database Models

Today network architectures are often referred to as n-tiered
designs. Note that a multi-tier system does not neccessary
require a different machine for every tier. All the tiers can run
on a single machine and this is useful for the development
phase. Nonetheless, it is more typical for each tier to have it's
own dedicated computer to support it's tasks.

Middleware

The software that is used on the middle tier is often referred
to as 'middleware'. Java has had much success supplying
middleware solutions. Corba, RMI, Servlets, Java Server Pages
and Enterprise Java Beans all are 'middleware' APIs that work
on the middle tier. 

Java Middleware Solutions

• CORBA
• RMI
• Servlets, JSPs, Struts etc.
  
• Enterprise JavaBeans
  

JDBC can be used on a two-tier system, executed in client
application software or can be hosted on the middle tier where
an application carries out the JDBC calls on a clients behalf.

Java Database Models

A basic Java model that is popular is based on a browser
supplying the front end or client. The client communicates
over the HTTP protocol to a servlet or Java Server Page
which in turn would access a database via JDBC.  Following
is a diagram of a simple Java JDBC moded on based on
using browsers, servlets and JDBC beiing used to access
a database.

Example        

Browser <->  Servlet (or JSP) using JDBC <-> Database

A more complex architecture might have a browser as a
front end communicating again with a servlet. The servlet
in turn would communicate with an Enterprise JavaBean.
The EJB in turn takes care of applying business logic
and managing connecting to any of a number of database
resources. RMI and CORBA could supply the role of the
Enterprise JavaBean as well, though issues involving
security, transaction management and concurrency
become the developer responsibility.

Example     

Browser <-> Servlet <-> Enterprise JavaBean <-> Database

We begin with a quote from the JDBC API Tutorial &
Reference by White, Fisher, Catell, Hamilton and Harper,
regarding the name JDBC. "JDBC is a trademarked name
and not an acronym." For whatever reasons this was done,
'JDBC' started as an abbreviation for "Java Database
Connectivity".

The JDBC API is a set of Java classes and interfaces
which provide a standard specification that developers
can use to create Java database applications. The primary
function of JDBC is to send SQL statements to database
systems. This functionality is being extended in JDBC 2
to include different data types.

// JDBC lets Java programs send SQL statements to databases

The great advantage of JDBC is that it allows a single,
portable Java program to communicate with several
different databases regardless of who the vendor is,
whether it be Oracle, IBM, Microsoft or MySQL, just to
name a few.

// one Java JDBC program can communicate with many vendor databases

JDBC is part of the standard library that accompanies
the Standard Edition of Java Development Kit. JDBC is
relatively easy to program allowing the creation of secure
and robust database applications. The ease and speed
with which programs can be developed and deployed
makes using Java's JDBC API very economical.

// JDBC is 'built into' the Java Development Kit making
// building database applications easy, fast and economical

The basic operations that are performed in a JDBC
program include establishing a connection to  a data
source (a database), sending qeuries in the form of SQL
statements and processing the results that are returned.

JDBC may be thought of as an environment created inside
a Java program that allows a database to be queried using
SQL statements.

JDBC may also provide a foundation on which other
database applications can be built. 

SQLJ

An example of an API that builds on JDBC is SQLJ. SQLJ is
a specification that has been developed by a consortium which
includes IBM, Oracle and Sun. SQLJ supplies a preprocessor
that allow a programmer to intersperse SQL statements into
Java code. The SQLJ preprocessor sorts the mix and restores
the code to a form that can be executed using regular JDBC
calls.
  

JDBC & ODBC

Microsoft's ODBC predates Java's JDBC API. ODBC influenced
the design of the JDBC API. ODBC is an acronym that abbreviates
"Open Database Connectivity". JDBC builds on some of ODBC's
basic design features. Both ODBC and JDBC are based on Open
Group's earlier design called SQL CLI, where CLI stands for Call
Level Interface. (Open Group was formerly known as X/Open.)

Relationship of ODBC and JDBC to SQL CLI

SQL CLI // Open Group previously X/Open
        |
        |____ODBC // Microsoft
        |____ JDBC // Java

While ODBC is written in the C programming language, JDBC
is written all in Java code.  ODBC is a widely used interface for
accessing relational databases, offering the ablility to connect
to almost all databases.
 

The JDBC-ODBC Bridge

The JDBC development team have supplied the  'JDBC-ODBC
Bridge' to take advantage of ODBC's wide spread adoption. The
bridge was created for several reasons. Because ODBC is written
in 'C' it could not be used directly in Java. Because of the extensive
use of pointers in ODBC, the program did not translate well directly
into Java.

// ODBC written in C with many pointers didn't translate well into Java

In addition, calling ODBC directly using Java's Native Interface
was not a good solution as Java's portability was lost. There
were also security concerns to this approach as the C code is
'outside' Java's security perimeter. ODBC is also considered
complicated and hard to learn. Java's JDBC greatly simplifies
connecting to a database making it easy to learn by comparison.

For these reasons, the Java team created the JDBC-ODBC Bridge
that encapsulates a Java connection to the functionality of ODBC.
This was a clever way of leveraging the large support for ODBC
for Java developers.
 

Microsoft's UDA in Relation to JDBC 2.0

The plethora of acronyms that stream from software companies
is hard to keep up with. 'The JDBC API Tutorial and Reference'
by White et al. puts the different Microsoft APIs in context relative
to the JDBC API. (Hereafter we may refer this text as the 'JDBC
API Reference' or just the 'JDBC Reference'.)

Microsoft has a number of APIs such as OLE ( Object Linking and
Embedding) DB, ADO (ActiveX Data Objects) and RDS (Remote
Data Service). OLE DB and ADO are object-oriented interfaces to
databases that can do SQL. ADO is a newer version with many
functionalities that are similar to JDBC 2. RDS is a database API
written in Microsoft's Java which is not portable.

Microsoft has created an umbrella term UDA or Universal Data
Access to encompass and streamline these APIs. JDBC 2.0
contains all the functionality described in UDA and goes a step
further, for instance, by providing support for SQL3.

Comparing Microsoft Database APIs to JDBC

UDA  // Microsoft     <-->       JDBC 2.0    // Java
   |___OLE
   |___DB
   |___ADO

 // should check to see what SQL3 is now present in Microsoft offerings

JDBC Database Driver Categories

The JDBC Driver encapsulates a connecting to a data source,
querying the database and returning the results from a query.
The JDBC driver provides the conduit for querying a database
via SQL from a Java program. JDBC drivers are generally
categorized into four varieties.

The JDBC-ODBC Bridge driver - The JDBC reference text refers
to this driver as 'the JDBC-ODBC bridge plus ODBC driver'.
This captures the idea that the ODBC binary file has to be
loaded on every client that uses this driver.  It is also refered
to a Type 1 driver. This driver can be used wherever installing
the ODBC driver on the client is not a problem.

Type 1   

client -- JDBC-ODBC driver  --  ODBC driver  --  database

// bridge driver to ODBC
 

Native-API to Java driver -The 'Native-API Partly-Java' driver has
a Java part that interfaces to a native binary driver which in turn
connects to the database. This style of driver needs some OS
specific binary code to be installed on each client. The major
database companies have all supplied this type of driver,
companies like Oracle, Sybase, Informix and IBM. This driver
is also called a Type 2 driver.

Type 2  

client -- JDBC driver  -native-  Vendor API  -- database  

// JDBC to native vendor driver  

Network-Protocol Java driver -The JDBC API reference calls this
the 'JDBC-Net Pure Java' driver. This also is called the 'Open
Protocol-Net' driver or a Type 3 driver. There are two component
parts involved in defining this driver. There is a 'Net' aspect and
an 'all-Java' element.

With this driver type JDBC calls are sent over an independent
Net protocol which is then translated into a DBMS specific call
by a server. This driver is understood to represent a middleware
program that connects to a pure Java client over a Network
protocol. The vendor is free to to choose the protocol.

Type 3 

client -- JDBC driver - Net - ProxyServer -native- any database 

// JDBC via neutral Net protocol
// think of our E-mailer sending SMTP calles from a Java App
 

DBMS-protocol all-Java driver - This driver converts JDBC calls
directly to a Network protocol that is used by the database
management system. Level 4 drivers are written by the vendor
entirely in Java to connect to a Java-based socket and calls
are made directly on the DBMS server via it's native protocol.

Since the DBMS protocols are often proprietary, the DBMS
vendor would be the primary source of this type of driver. These
types of drivers are provided by the major database vendors.
There is a driver of this type available for Microsoft SQL server.
This driver is also referred to as a Type 4 driver. Other names
include 'Native-protocol pure Java driver' and 'Proprietary
Protocol Net' driver both competing to best describe the driver.

Type 4  

client -- JDBC driver -- directly to database via net socket 

// JDBC to vendor Net protocol

The Type 3 and 4 drivers are preferred because they have no
dependence on platform specific native code on the client side.
Of the four driver types all represent direct connections to the
database except for driver Type 3 which is based on the
middleware connection through a neutral Net protocol.
 

Structured Query Language

SQL, an acronym for Structured Query Language, is, for the
most part, a vendor-neutral programming language designed
for exchanging data with databases. SQL, (many pronounce
it 'sequel'),  has been standardized by ANSI, the American
National Standards Institute. The standard version is called
ANSI SQL. Fortunately, though all database vendors provide
their own extensions, they all support the ANSI version of SQL.

// everyone supports ANSI SQL

A Brief History

SQL was originally developed at IBM in the late 1960s under
the direction of Ted Codd. Many companies have developed
different versions of the language. In the early 1980's, ANSI,
the American National Standards Institute, began work on the
standardization of SQL. ANSI published a specification in
1986 and ISO, the International Standards Organization
published their standard in 1987. ANSI and ISO worked jointly
on a standard was called variably, SQL2, SQL-92 or SQL/92.

// ANSI published in 1986 & ISO in 1987

This version is today's de facto standard while the newer
SQL3, which supports object-oriented and other scientific
features continues to gain wide-spread acceptance.

JDBC drivers must support at least ANSI SQL-92 Entry
Level. Entry Level is a specific list of requirements that is
stipulated with the standard. JDBC 2.0 goes beyond the
minimum requirements and supplies support for the new
features of SQL3 which integrates object-oriented ideas
into standard SQL. JDBC 2.0 also supports SQLJ but there
is no requirement for drivers to support SQL3 or SQLJ.

JDBC 2.0, 3.0 and 4.0

JDBC 3.0 is supported in the Java Development Kit 1.4.0
and later. JDBC 4.0 came out as a draft in 2006.  JDBC 4
features are currently supported in Java SDK 1.6.

Relational Database Overview

Databases are all around us. When you shop for a car part or
use your bank card you are accessing a database. A 'database'
is a collection of stored data, the collection normally referring to
a set of tables. The software that manages a database is called
a Database Management System or DBMS. 

A 'relational database' is one that stores data in data structures
called 'tables'.

// DBMS manages a database, relational databases use tables

Tables provide a useful way to organize data in files. Tables are
made up of one or more 'columns'. The data in any given column
is all of the same type. ( SQL has specific names for the types
that can be stored in columns. ) Data in tables are stored in 'rows'.
Each row will have a value entry for each column in the table. A
'record' refers to the set of data contained in a single row. In the
relational model, a table is referred to as a 'relation' which is
where the table model gets the 'relational database' name.

// a table is referred to as a relation

Below are two very simple tables.

Example 1
 

Customer

John Doe

//  here's a table of one column and one row with
//  a single field containing the value John Doe

Example 2
 

Customer	Address
John Doe	RR# 2 Oron
Bill May	12 June St., Springstown

//  here's a table of two columns and two rows
//  there are two records stored here in the two rows
 

Each row in a table requires a unique identifier. The column
containing each row's unique identifier is called the primary
key. This identifier may exist in one of the columns of the
table or may be composed from a set of columns.

Typically a unique number is used to describe each record
whether a customer, order or an account. The primary key
makes extraction of the information from each row possible. 

Integers make good primary keys as is shown in the following
example where the Account_No values are the primary keys.

Example 3
 

Account_No	Customer	Address
1000001	John Doe	RR# 2 Oron
1000002	Bill May	12 June St., Springstown

// each row will have a primary key, an identifier in one row
// or composed from information from a number of rows

Primary Keys

There are some rules governing establishing primary keys.
No two rows can share the same primary row value. Every
row must have a primary key value ( that is not NULL ). The
column containing key values can't be modified. Primary
keys cannot be reused.

These rules serve to ensure every record has a unique
identifier which cannot be replaced or reassigned. When
multiple columns are used to create keys, the rules extend
to all the columns.

Rules Governing Primary Keys

• two rows cannot share a primary key value
• key value column cannot be modified
• every row must have a primary key value (not NULL)
• primary keys cannot be reused

These and other rules came out of seminal work done by
Ted Codd who worked at IBM in the late 60s. The rules
Codd espoused made the use of a Structured Query
Language a requirement of the relational model. This led
to the development of SQL as we know it today.

Data Manipulation Language (DML)

The set of SQL commands that are used to manipulate the
contents of a database is called the 'Data Manipulation
Language'. These commands are summarized in the
following table.

DML Commands
 

SELECT	Retrieves data from the database.
INSERT	Inserts rows into a table
UPDATE	Updates rows in a table.
DELETE	Deletes rows from a table.

Data Definition Language (DDL)  // structural commands

The set of  commands used to change the structure of
database tables is called the Data Definition Language.
Following are SQL commands are used to create, delete
or alter database tables.

DDL Commands
 

CREATE	Creates a new database object.
DROP	Drops an existing database object.
ALTER	Modifies the structure of a database object.

A Few SQL Examples

Usually a database will already exist and information
is retrieved from it using SELECT statements or if there
needs to be a change to the table an UPDATE statement
is used. If you are creating a database from scratch you
will need to create tables to store your data.
 

The CREATE Statement

The CREATE command is followed by the keyword
TABLE and the name the table will be given. Following
these keywords are the column names in parentheses
which must be unique, followed by their type.

In addition a setting is specified to determine if the initial
value of fields of columns can be NULL. NULL represents
an absence of a value. A row does not need to specify a
value for columns that are specified as NULL. Here is an
example of a table being created using the CREATE
command. In this case NULL values are not accepted.

CREATE Example

CREATE TABLE  Cars
    (
    InventoryNo  INTEGER     NOT NULL,
    Make         CHAR(40)    NOT NULL,
    Model        CHAR(40)    NOT NULL,
    Year         CHAR(10)    NOT NULL
    );

// column names are followed by type

Once a table has been created data can be inserted into
it. Values stored can be changed via updates. Data also
can be selectively removed. The commands to do these
operations are Insert, Update and Delete.
 

The INSERT command

Once a table has been created data can be inserted into
it. INSERT is used to insert rows or parts of rows of data
into a table. Following the INSERT command the name of
the table is specified. Following the VALUES keyword,
the row items to be inserted are specified.

INSERT Example

INSERT INTO Cars
   VALUES ( 82123,
            'Ford',
            'Focus',
             2000
          );

  // inserts actual data values

The SELECT Statement

Once a table is created, perhaps containing a companies
inventory, users will want to access the information. This is
where the SELECT statement comes in. It is perhaps the
most used statement in SQL.  A select statement is also
called a 'query' because it is asking for information from
the database. In it's simplest form it can be used to select
all the information in the rows and columns of a table as in
the following example.

SELECT Example   

SELECT * FROM Cars;

Assuming that a number of INSERT statements were
performed the result of a SELECT everthing statement
like the above might be a table that looks like the following.

OUTPUT
 

InventoryNo	Make	Model	Year
00123	Ford	Taurus Wagon	1996
00456	Toyota	Camry	1997
00789	Chrysler	Intrepid	2000
01401	Jeep	Cherokee	1993
01578	GM	Pickup	1988

If a Single column is specified the column of values will
be returned.
 

Example   SELECT Make             
                FROM  Cars;

// specify column(s) after SELECT

The output of this statement would be a table like the following.

Output
 

Make

Ford

Toyota

Chrysler

Jeep

GM

WHERE Clauses

A number of conditions can be placed on what is selected
using WHERE clauses. The following example selects
vehicles whose year is less than 1996.  

Example

SELECT Make, Model, Year
FROM cars
WHERE Year > 1996;

Output
 

Make	Model	Year
Toyota	Camry	1997
Chrysler	Intrepid	2000

The UPDATE command

To change data in a table the UPDATE command
is used. The statement takes the following form.

UPDATE Example

UPDATE table_name
SET column_name = 'new_value'
WHERE key_name= '10101';

// for example reset a price

Notice the UPDATE statement first addresses the table
name and then specifies the column that the new value
will be assigned to with the SET keyword. The condition
specifies which row will have it's data updated. If there
was no WHERE clause, all the row's values in this
column would be changed to 'new_value'.

UPDATE Example 2

UPDATE Cars
SET Model = 'sedan DX'
WHERE key_name= 'lexus';

// with no conditional where phrase all row
// values in column would be effected

The DELETE Command

To complete our mini-tour of SQL we add the command
that is used to remove data from a table.  One, many or
all the rows of a table can be deleted using the DELETE
command. To delete a row use the following form.

DELETE Example

DELETE FROM Cars
WHERE key_name= 'lexus';

Omitting the WHERE clause will cause every row to be
deleted from the specified table so you have to use
DELETE with care!  Delete is a row specific operator
and takes no column names or wild cards.

// DELETE is a row specific operator

There of course is a lot more to SQL. This gives you
the basic idea and the most commonly used commands.
These commands are encapsulated in Java String
objects and supplied to methods of the JDBC API to
query a target database.
 

Result Sets

The rows that satisfy the conditions specified in an SQL
query are called the 'result set'. The number of rows
returned can be any number including zero. A cursor is
supplied which is a kind of file pointer that provides the
means of accessing the data from the result set one row
at a time. In the JDBC API 1.x, the result set could be
accessed a row at a time in sequence from top to bottom.

In the new JDBC 2.0 the cursor is given random access
abilities, able to move forward and backwards or to a
specified row.

Transactions // makes a set of SQL commands 'atomic'

If a database is being accessed concurrently by two users
and both are accessing the same range of data, where one
is updating while the other is selecting, there is the possibility
that the user doing the selection will get an incorrect result
set containing only partially updated information. To avoid
this situations database management systems use
transactions to ensure data consistency.

// transactions ensure data consistency, like a lock

Transactions allow several users to use a database at
the same time without there being a danger of obtaining
inconsistent data.

A Transaction defines a set of SQL statements with a
well-defined start and end point. The set of statements
work as a unit of change for a database. A transaction
is ended with either a COMMIT or ROLLBACK command.

The COMMIT command makes the changes described
in the set of SQL statements permanent. The ROLLBACK
statement 'undoes' any changes that were made by the
transaction statements.  A lock is a device that prevents
two transactions from manipulating data at the same time.
A lock synchronizes access to data. A row lock prevents
two transactions from modifying the the same row
simultaneously.

An Oracle Example

insert into R values (1, 2);
rollback;
insert into R values (3, 4);
commit;

// from 'Oracle 9i SQL versus Standard SQL, 
// http://infolab.stanford.edu/~ullman/fcdb/oracle/or-nonstandard.html#transactions

JDBC specifies one transaction can exist at a time per
connection. Also, drivers are required to allow concurrent
request from different threads on the same connection.
Transactions are by default in 'autocommit' mode where
commit( ) is called automatically when the statement
completes.

// default for autocommit is true

In Java JDBC code, setting setAutoCommit( ) to 'false'
turns 'autocommit' off. If autocommit is disabled and a
transaction is performed which obtains a lock on the
database, the lock will remain in effect until the commit( )
or abort( ) are called , or some other form of disconnection
occurs.

// if autocommit is set to false, then an explicit commit( ) is required

A 'local transaction' is one executed against a single Data
Resource. A transaction that is coordinated by a transaction
manager across multiple heterogeneous Data Resources
is called a 'global transaction'. The Java Transaction API ,
JTA, provides a standard Java interface for tranaction
management that is global in nature.
 

Stored Procedures

A stored procedure is a set of SQL statements that can
be called by name the same way a function or method
can be called. A stored procedure is a routine that is
stored with the database. Normally stored procedures
are written in scripts provided by the database system
software. Many new databases allow stored procedures
to be written in Java. Once a stored procedure is written
it is ported to the DBMS and stored so that it can be
called by name anytime it is needed.

The use of stored procedures have advantages. The code
can be executed quickly and efficiently on the database
which may lead to an improvement in  the efficiency of
network exchanges.

Metadata    // DatabaseMetaData interface 

Most databases maintain tables that describe layout details
of the various tables that the database contains, for example,
"What are the column names of a table?".
 
Each database management system supplies functions for
getting this information. It is the responsibility of the JDBC
driver developer to supply access to this information via the
DatabaseMetaData interface. Another informational interface,
the ResultSetMetaData interface supplies information about
the result set.

In our next note we look at the technical aspects of using
JDBC in some detail along with the classes and methods
of the different classes of JDBC API. To finish this section
we create a 'Hello JDBC' program that will be a preview
of what we are looking at in the next section. We borrow
Peter Van der Linden's 'simple.java' sample from his Java
text 'Just Java'.

Using the Access Database as a JDBC Database

One reason we use this code is that it makes use of the
JDBC-ODBC bridge to connects to a database file located
in the Access database package. Access is a Windows
database that is on the machines at the school so we can
get a quick demonstration of JDBC without having to set
up drivers and the database.

// next week we use MySQL which is well established on Linux and
// is growing in popularity on Windows

Setting a DSN

It is not without some complications as a Microsoft 'DSN'
has to be set up. A Microsoft DSN is an important part
of Microsoft's naming architecture and deserves some
attention. Information on how to set up a DSN is provided
in the Exercise below.

Notice in the code there is a URL which represents the
actual database file. It is a special JDBC URL. There is a
SELECT query. A call on class Class locates the JDBC
driver for the name provided, here it is the JDBC-ODBC
bridge. A connection is made on the URL. A Statement
object is obtained and the SQL querying method is called,
passing in the SQL statement that was created earlier. A
ResultSet object is returned that is iterated row by row in
the printResultSet method.

The Java code used here is taken from Peter Van der
Linden's 'Just Java' which in any edition is a good book
to have.

Java JDBC Code Example 

// from ' Just Java ' by Peter Van der Linden,
// Sun Microsystems Press

import java.sql.*;

class simple{
public static void main(String[]args){
  String url =  "jdbc:odbc:myDSN" ;
  String query = "SELECT  * FROM  Customers"
                        + " WHERE CustomerID = 'QUICK' ";
  try{
         // Load the jdbc-odbc bridge driver
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         // Attempt to connect to a driver
      Connection con = DriverManager.getConnection( url," "," " );
         // Create a Statement object so we can submit SQL statements to the driver
     Statement stmt = con.createStatement( );
         // Submit a query, creating a ResultSet object
     ResultSet rs = stmt.executeQuery(query);
         // Display all columns and rows from the result set
     printResultSet(rs);
     rs.close( );
     stmt.close( );
     con.close( );
     }
     catch(SQLException ex){
            while (ex != null){
               System.out.println("SQL Exception: " + ex.getMessage( ));
                                            ex = ex.getNextException();
                      }
              }
      catch(java.lang.Exception ex){
            ex.printStackTrace( );
            }
    }

private static void printResultSet(ResultSet rs) throws SQLException{
         int numCols = rs.getMetaData( ).getColumnCount( );
             while( rs.next( ) ){
                     for ( int i = 1; i < numCols; i ++ ){
                           System.out.print(rs.getString(i) + "  |  " );
                           }
                    System.out.println( );
                    }
          }
}

Next week we will go through the code in detail looking at the
different classes that are used. We also connect to a different
database, the popular MySQL.  We can consider other choices
as well.

1) Which of the following statements is not true?

a) Both ODBC and JDBC are based on X/Open SQL Command Line Interface.
b) The JDBC-ODBC Bridge takes advantage of ODBC's wide spread adoption.
c) Because ODBC is written in C there was no way it could be used in Java.
d) Because of the extensive use of pointers in ODBC the program didn't translate
    well directly into Java.

2) Which of the following Microsoft names describes an umbrella for the others?

a) OLE DB
b) UDA
c) ADO
d) RDS

3) Which of the following is not an advantage of adding a  middle tier to a network
     architecture?

a) thinner clients
b) multithreading allows concurrent services to clients
c) good location for business logic
d) faster data retrieval

4) Which of the following drivers works using a vendor specific proprietary
     net protocol?

a) The JDBC-ODBC Bridge driver
b) Native-API to Java driver
c) Network-Protocol Java driver
d) DBMS-protocol all-Java driver

5) True of False? DML is an abreviation of the category of SQL commands
used to manipulate the data content of a database.

6) Which of the following SQL statements is used to modify data in a database?

a) Create
b) Insert
c) Select
d) Update
e) Delete
 

Using Access

1) Run Peter Van der Lindens simple class. First you will have to
   set up a Microsoft type DSN. Here are the steps if you are not
   familiar with it.
 

Setting up a Microsoft DSN   1) open odbcad32.exe in c:\windows\system32   2) when the screen opens, press add   3) highlight Microsoft Access Driver (*mdb) & press Finish   4) in ODBC Microsoft Access Setup enter myDSN   5) the other field is not important   6) Press 'select' to make a database file to associate with the DSN   7) The file to find is under c:-> Program Files-> Microsoft Office -> Office 11      -> Samples  ->  Press  Enter  ( Northwind.mdb shows  up in  left column )   8) Press OK and it should be ready to go!   .

Hand in a screen shot of the command line output.

2) Create a text file that stores the SQL statement to create a
table that represents a particular type of inventory for a small
store. It should have 4 or 5 columns. Because we didn't talk
about SQL types you might use the SQL type INTEGER to
describe an inventory number and CHAR( ) type for the other
values. The column that will serve as the key identifier for
convenience should be the inventory number column.

Example from the Note

CREATE TABLE  Cars
   (
    InventoryNo     INTEGER             NOT NULL,
    Make              CHAR(40)            NOT NULL,
    Model             CHAR(40)            NOT NULL,
    Year               CHAR(10)            NOT NULL
    );
 

In a second section of the file prepare insert statements to
  occupy the table with at least 8 rows of data.

INSERT INTO Cars
   VALUES ( 82123,
            'Ford',
            'Focus',
             2000
          );

Show these statements in your submission for Question 2.
Save the file for future use. Use these statements in the
following question.

3) Use the GUI in Access to create the table and rows
described above.

4) Adapt the simple Hello JDBC program exampled above
to select the data from the table created above.