Full HTML for

Basic foilset Overview of JDBC and its use with Microsoft Access

Given by Nancy McCracken(Sangetta Aggarwal, Udayan Parvate, Tom Pulikal) at UC Web Applications Certificate on June 26 97. Foils prepared 23 June 1997
Outside Index Summary of Material

JDBC provides a set of classes for Java with a standard SQL database access interface.
  • Goal is uniform access to a wide range of relational databases
Provides an API for database "drivers" to make actual connections and transactions to database products.
  • includes JDBC-ODBC bridge to standard ODBC drivers, a common interface for relational databases from C
JDBC is "low-level" interface, calling SQL commands directly but is meant to be a base for higher-level interfaces.
This talk has three sections:
  • Using JDBC in Java
  • JDBC drivers, including how to use examples at NPAC
  • MicroSoft Access and its JDBC interface

Table of Contents for full HTML of Overview of JDBC and its use with Microsoft Access

 Denote Foils where Image Critical
Denote Foils where Image has important information
Denote Foils where HTML is sufficient
1 JDBC Java - Database Connectivity Overview for CPS616 Technologies of Information Age Summer 97
2 JDBC Main Features
3 Traditional Web/RDBMS access Compared to using Java
4 JDBC Software Architecture
5 Driver Access Architecture
6 JDBC API is Package java.sql
7 Driver Manager
8 Driver class creates Connection
9 SQL Statements
10 Statement Class
11 ResultSet class
12 Data from ResultSets
13 PreparedStatement class
14 CallableStatement class
15 Generalized example of getting results
16 A typical JDBC API usage sequence
17 Simple JDBC 2 Tier Applet - An Example
18 JDBC 2 Tier Applet
19 JDBC 2 Tier Applet
20 DBDiagram Example
21 More Details on JDBC Architectures
22 Driver Classification- I
23 Driver Classification - II
24 JDBC Driver API - I
25 JDBC Driver API - II
26 JDBC Driver API - III
27 Remote Database Access Current Options
28 mSQl jdbc driver for mSQl database
29 Weblogic jdbcKona driver for Oracle
30 Remote jdbc-driver features
31 PPT Slide
32 Remote jdbc Driver Client-side Description
33 Remote jdbc Driver Server-side Description - I
34 Remote jdbc Driver Server-side Description - II
35 JDBC 3 Tier Example
36 JDBC 3 Tier Example (contd.)
37 Java Servlets
38 Java Servlets (contd.)
39 Java Servlets (contd.)
40 JDBC Servlet Example
41 JDBC Servlet Example (contd.)
42 JDBC Servlet Example (contd.)
43 JDBC Servlet Example (contd.)
44 List of vendors
45 Practical Options at NPAC
46 Practical Options at NPAC contd.
47 Example - Using Imaginary's mSQL Driver
48 Example - using Imaginary's mSQL Driver -II
49 Example - Using Weblogic's Oracle Driver
50 Example - Using Weblogic's Oracle Driver -II
51 Example - Using Weblogic's Oracle Driver -III
52 Example - Using JDBC-ODBC Bridge - I
53 Example - Using JDBC-ODBC Bridge - II
54 Example - Using JDBC-ODBC Bridge -III
55 Example - usingJDBC-ODBC Bridge -IV
56 Example - usingJDBC-ODBC Bridge (contd.)
57 Microsoft Access
58 MS Access - Features (cont'd)
59 Cool Features of Access - I
60 Cool Features of Access - II
61 The Access Event Model
62 VBA class modules
63 Database Design - I
64 Database Design - II
65 Database Design - III
66 Access SQL - I
67 Access SQL - II
68 Access SQL - III
69 Access SQL - IV
70 Access Controls
71 Access Forms and Reports
72 Developing Multiuser Applications
73 Security in Access
74 Client-Server Applications
75 Web-Enabled Applications -I
76 Web-Enabled Applications -II
77 Web-Enabled Applications -III
78 Sample code for ASP files - I
79 Sample code for ASP files - II
80 Access as an Automation Client-I
81 Access as an Automation Client-II
82 Access as an Automation Client-III

Outside Index Summary of Material

HTML version of Basic Foils prepared 23 June 1997

Foil 1 JDBC Java - Database Connectivity Overview for CPS616 Technologies of Information Age Summer 97

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Nancy McCracken
Assisted by Sangeeta Agrawaal,
Udayan Parvate, and Tom Pulikal
http://www.npac.syr.edu/projects/tutorials/JDBC
HTML version of Basic Foils prepared 23 June 1997

Foil 2 JDBC Main Features

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
JDBC provides a set of classes for Java with a standard SQL database access interface.
  • Goal is uniform access to a wide range of relational databases
Provides an API for database "drivers" to make actual connections and transactions to database products.
  • includes JDBC-ODBC bridge to standard ODBC drivers, a common interface for relational databases from C
JDBC is "low-level" interface, calling SQL commands directly but is meant to be a base for higher-level interfaces.
This talk has three sections:
  • Using JDBC in Java
  • JDBC drivers, including how to use examples at NPAC
  • MicroSoft Access and its JDBC interface
HTML version of Basic Foils prepared 23 June 1997

Foil 3 Traditional Web/RDBMS access Compared to using Java

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Traditional web access to the database was done with the use of CGI script accessing the database and creating output in the form of HTML document presented in WWW client.
The requests and responses were transmitted using HTTP protocol and there was no session notion and it was not able to preserve the database transaction logic
JDBC expands these options:
Java programs can be executed on client and on server side. In this case it is possible to create three tier client-server application.
Client applications can also access database directly.
HTML version of Basic Foils prepared 23 June 1997

Foil 4 JDBC Software Architecture

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Java applications
Jdbc API
Jdbc Driver manager
Jdbc Driver
Jdbc Driver
DBMS
DBMS
JDBC API
JDBC Driver API
Jdbc-Odbc
Bridge
ODBC Driver
DBMS
HTML version of Basic Foils prepared 23 June 1997

Foil 5 Driver Access Architecture

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Java application
Java applet or
HTML browser
Application Server (Java)
Jdbc Driver
DBMS
Two-tier Model
Jdbc Driver
DBMS
Three-tier Model
DBMS Proprietary Protocol
DBMS Proprietary Protocol
HTTP (RMI, CORBA or other) Protocol
HTML version of Basic Foils prepared 23 June 1997

Foil 6 JDBC API is Package java.sql

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The JDBC API is contained in JDK 1. Note that many of the classes such as "Statement" are interfaces, as they have abstract methods which must be supplied by a driver.
Interface Index
Callable Statement
Connection
DatabaseMetaData
Driver
PreparedStatement
ResultSet
ResultSetMetaData
Statement
Class Index
Date
DriverManager
DriverPropertyInfo
Numeric
Time
Timestamp
Types
Exception Index
DataTruncation
SQLException
SQLWarning
HTML version of Basic Foils prepared 23 June 1997

Foil 7 Driver Manager

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The DriverManager class keeps track of the drivers that are available and handles establishing a connection between a driver and a particular database.
User loads the Driver explicitly Class.forName("acme.db.Driver"); This loads the driver class, which should itself call DriverManager.registerDriver with its instance and thus be available for a creating a connection.
HTML version of Basic Foils prepared 23 June 1997

Foil 8 Driver class creates Connection

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The user requests the DriverManager class to make a connection to a particular database with user and password. The DriverManager then asks each available driver if it can handle that request until one can; the driver then makes the connection. String url = "jdbc:odbc:fred"; Connection conn = DriverManager.getConnection (url, "userid", "password");
The form of the url is &#060protocol&#062:&#060subprotocol&#062:&#060subname&#062
  • the protocol is always jdbc
  • the subprotocol is a keyword registered by the driver authors, odbc is a standard one
  • the subname format is very flexible and is designed by the driver author - just follow their examples!
HTML version of Basic Foils prepared 23 June 1997

Foil 9 SQL Statements

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
createStatement makes an instance of Statement class
  • used for simple SQL statements with no paramters
prepareStatement makes an instance of PreparedStatement class, a subclass of Statement
  • used for SQL statements with one ore more IN parameters
  • or simple SQL statements that are executed frequently
prepareCall makes an instance of CallableStatement
  • used for calls to stored procedures in the database
  • inherits methods for IN parameters from PreparedStatement
  • also has methods for OUT and INOUT parameters
All the above statements are executed in "autocommit" mode for individual SQL statements. Transactions can be programmed with explicit methods commit and rollback.
HTML version of Basic Foils prepared 23 June 1997

Foil 10 Statement Class

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Use connection to send SQL statements to database: Statement stmt = conn.createStatement( ); ResultSet rs = stmt.executeQuery("select * from table1"); The return value of the method executeQuery is a single result set.
The method executeUpdate is used for
  • insert, update and delete statements, which return no. of rows affected by change
  • DDL statements such as create table, drop table, . . .
The method execute is used for SQL statements that may return more than one result set or update count.
HTML version of Basic Foils prepared 23 June 1997

Foil 11 ResultSet class

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
A ResultSet contains all the rows which satisfied the conditions of an SQL statement.
The cursor of a ResultSet is initially pointed to the first row, the method next( ) moves the cursor down one row. ResultSet r = stmt.executeQuery("select a,b,c from table1"); while (r.next( )) { //print values for current row int i = r.getInt("a"); float f = r.getFloat("b"); String s = r.getString("c"); System.out.println("row" + i + f + s); }
HTML version of Basic Foils prepared 23 June 1997

Foil 12 Data from ResultSets

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The getxxx methods, where xxx is a Java type, can take column labels or column position numbers for an argument. (findColumn ("label") can return column position.)
JDBC API contains a table telling which getxxx methods can be used with particular SQL types.
ResultSet.getMetaData returns a ResultSetMetaData object which gives the number, types and properties of the ResultSet object's columns.
Can use streams on LONG SQL types.
For optional or multiple ResultSets, there are methods getResultSet, getUpdateCount, and getMoreResults.
HTML version of Basic Foils prepared 23 June 1997

Foil 13 PreparedStatement class

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
PreparedStatements inherit all functionality of Statements.
It may have an IN parameter, which will be indicated by a "?" in the SQL statement.
Each IN parameter is specified in number order from left to right by a setxxx method. ResultSet rs = stmt.executeQuery("select * from tb1 where name = ?"); stmt.setString(1, "bill");
The setxxx methods will convert parameters to appropriate SQL types, or user may use more general stmt.setObject(1, value, "SQLtype");
HTML version of Basic Foils prepared 23 June 1997

Foil 14 CallableStatement class

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Calling a stored procedure in the database CallableStatment cstmt = conn.prepareCall ("{call getTestData( ? , ? ) }");
The OUT parameters must be registered before the statement is executed cstmt.registerOutParameter(1, Types.NUM); cstmt.registerOutParameter(2, Types.VARCHAR2); cstmt.executeQuery( ); int I = cstmt.getInt(1); String s = cstmt.getString(2);
HTML version of Basic Foils prepared 23 June 1997

Foil 15 Generalized example of getting results

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
String query = "select * from table1"; ResultSet rs = stmt.executeQuery(query); ResultSetMetaData rsmd = rs.getMetaData ( ); int columnCount = rsmd.getColumnCount ( ); for (int i = 1; i &#060= columnCount; i++) { String s = rsmd.getColumnTypeName (i); System.out.println ("Column" + i + " is type " + s); }
HTML version of Basic Foils prepared 23 June 1997

Foil 16 A typical JDBC API usage sequence

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
DriverManager
getConnection()
method returns a
Connection Object
Connection
Statement
ResultSet
Column values
from Query results
method returns a
Statement object
createStatement()
executeQuery()
next()
method returns a
ResultSet Object
method gets the
next row
HTML version of Basic Foils prepared 23 June 1997

Foil 17 Simple JDBC 2 Tier Applet - An Example

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The 2-Tier Applet consists of a Java applet which communicates with an Oracle server via JDBC.
The weblogic JDBC driver for Oracle is used.
The important java package to include is java.sql.*
Currently, the weblogic installation is on `osprey7'. The environment variable `classpath' has to include /usr/npac/weblogic/classes
The applet directly calls the java class which has the code for communicating with the Oracle server.
HTML version of Basic Foils prepared 23 June 1997

Foil 18 JDBC 2 Tier Applet

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Implementation:
  • A new instance of the SQL driver is created.
    • Syntax: java.sql.Driver d = (java.sql.Driver)Class.forName("weblogic.jdbc.oci.Driver").newInstance();
  • A connection to the Oracle server is opened by supplying the user's name, password and the machine to connect to.
    • Syntax: Connection conn = DriverManager.getConnection("jdbc:weblogic:oracle:kayak", "username", "password");
HTML version of Basic Foils prepared 23 June 1997

Foil 19 JDBC 2 Tier Applet

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The SQL query is created and executed.
  • Syntax: PreparedStatement pstmt = conn.prepareStatement("select * from datatable where NAME = ?");
  • ResultSet rs = pstmt.executeQuery();
The data is read from the database and passed back to the applet where it is displayed.
At the end of the session, the connection to the database is closed.
  • Syntax: conn.close();
HTML version of Basic Foils prepared 23 June 1997

Foil 20 DBDiagram Example

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Java application that shows the Database Metadata details in a graph layout
Shows the relations between the various database objects
To run, connect to the database giving the username, password and schema(username itself in this case) in the dialog box and press start
The lines between the boxes represent the existence of one or more foreign keys to primary key relationships
Tableviewer shows a list of tables and their structure as well as the data
HTML version of Basic Foils prepared 23 June 1997

Foil 21 More Details on JDBC Architectures

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The JDBC API defines Java classes to represent database connections, SQL statements, result sets, database metadata, etc. It allows a Java programmer to issue SQL statements and process the results. JDBC is the primary API for database access in Java.
The JDBC DriverAPI represents (which is also a part of package java.sql) the set of interface classes , any new jdbc compliant driver should implement. This imparts inherent extendibility for different types of databases.
The remote access to a database over the network completely depends upon the particular installation of the jdbc driver for that database.
HTML version of Basic Foils prepared 23 June 1997

Foil 22 Driver Classification- I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Following is a broad classification of the driver types :
  • JDBC-ODBC bridge provides JDBC access via most ODBC drivers. Some ODBC binary code and in many cases database client code must be loaded on each client machine that uses this driver, so this kind of driver is most appropriate on a corporate network, or for application server code written in Java in a 3-tier architecture.
  • Native-API partly-Java driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
HTML version of Basic Foils prepared 23 June 1997

Foil 23 Driver Classification - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
A net-protocol all-Java driver translates JDBC calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect its all Java clients to many different databases. The specific protocol used depends on the vendor.
Native-protocol all-Java driver converts JDBC calls into the network protcol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver.
HTML version of Basic Foils prepared 23 June 1997

Foil 24 JDBC Driver API - I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Any jdbc driver has to implement the following interfaces as specified under the java.sql package and has to fulfil the specified responsibilities.
  • CallableStatement - Acts to give the effect of a stored procedure and allows to get result-set fields after executing the statement.
  • Connection - It represents a session with a specific database. Within the context of a Connection, SQL statements are executed and results are returned. This is the object returned by the getConnection method of the DriverManager class.
  • DatabaseMetaData - Provides methods to know about the database as a whole by returning a ResultSet object for each of the meta-data related queries.
HTML version of Basic Foils prepared 23 June 1997

Foil 25 JDBC Driver API - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Driver - Responsible for determining whether a given JDBC URL is valid for the concerned database and registers itself with the DriverManager class.
PreparedStatement - A SQL statement is pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.
ResultSet - It provides access to a table of data generated by executing a Statement. The table rows are retrieved in sequence. Within a row its column values can be accessed in any order. A ResultSet maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The 'next' method moves the cursor to the next row.
HTML version of Basic Foils prepared 23 June 1997

Foil 26 JDBC Driver API - III

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
ResultSetMetaData - A ResultSetMetaData object can be used to find out about the types and properties of the columns in a ResultSet.
Statement - A Statement object is used for executing a static SQL statement and obtaining the results produced by it
HTML version of Basic Foils prepared 23 June 1997

Foil 27 Remote Database Access Current Options

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
To Access a database residing on machine M2 from Machine M1 over the internet/intranet is dependent currently upon the specific database you wish to connect to, and the type of the jdbc driver you at your disposal.
  • In case the driver is a Native-protocol all-Java driver ( e.g mSql jdbc driver for mSql database ) , it can connect to the remote database directly since such kind of driver knows the net-protocol the particular database supports, and thus is able to contact and talk to the database server directly via opening socket connections over the network.
  • In case the driver is a Native-API partly-Java driver (e.g WebLogic jdbcKona driver for Oracle database ) , it can connect to the database if the driver and the database resides on the same machine.This is a limitation of this type of driver.
HTML version of Basic Foils prepared 23 June 1997

Foil 28 mSQl jdbc driver for mSQl database

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
mSQl is a minimal implementation of a RDBMS.
For the mSQL implementation of JDBC, it looks for URL's in the form of
  • jdbc:msql://[host_addr]:[port]/[db_name]
Example URL jdbc:msql://lion.cat.syr.edu:4333/test
This is a Native-protocol all-Java driver.
  • In the implementation of the Driver interface for this driver a direct socket connection is opened at the given host_addr and at the specified port. On success a Connection object is returned.
  • Thus with this driver , a mSQL database on any machine can be accessed by any application running on any other machine provided that they are connected over the internet.
HTML version of Basic Foils prepared 23 June 1997

Foil 29 Weblogic jdbcKona driver for Oracle

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Weblogic Inc. provides a Native-API partly-Java driver for Oracle RDBMS.
For the Oracle implementation of JDBC, it looks for URL's in the form of jdbc:weblogic:oracle:[host_addr]:[db_name]
Example URL jdbc:weblogic:oracle:t:ss2:DEMO
The driver classes should be present at run-time at every client. The database should reside on the same machine. The communication utility SQL_Net, a part of the Oracle suite, enables routing the database calls from the client to the server only if this utility is running on both the client and the database server which is a limitation in itself.
In the following slides, an alternate approach is proposed to develop a Remote jdbc driver to eliminate the restrictions of having the driver classes/binary code to be loaded on each client machine.
HTML version of Basic Foils prepared 23 June 1997

Foil 30 Remote jdbc-driver features

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The remote jdbc driver has two parts :
  • Client side implementation ( JDBC driver API)
  • Server side implementation ( Middleware )
At the client side, the implementation will be as a database independent driver (with Router like functionality) which will route the requests made by the Application program with jdbc API to the concerned machine where the server implementation of this driver will be active.
At the database server side the implementation of this driver will consist of a server process which can actually use the specific JDBC driver for the local database on that machine and connect and talk to the database.
HTML version of Basic Foils prepared 23 June 1997

Foil 31 PPT Slide

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Remote jdbc
Client
Remote jdbc
Server
Java Program
using JDBC API
Database2
Remote jdbc Driver Architecture (Three-Tier)
Local jdbc
Driver
Internet/intranet
M1
M2
Database1
Database3
HTML version of Basic Foils prepared 23 June 1997

Foil 32 Remote jdbc Driver Client-side Description

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
It consists of a driver which implements the JDBC driver API (all the interfaces from the java.sql package). Thus the application program can still adhere to the JDBC user API but register and load the remote jdbc driver in case of any database instead of having the specific jdbc driver classes for that database to be present at the client machine.
Communication is both from the client-side to server-side (to request connection, prepare and execute query statements) and from server-side to the client-side (for getting the result-set for any query execution).
HTML version of Basic Foils prepared 23 June 1997

Foil 33 Remote jdbc Driver Server-side Description - I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
This will consist of a server process running at a specified port (own java server with socket implementation) or at a specified URL (implemented as a Java Web Server URL).
Will receive the method to be called from the client-side and executes the same with the specific jdbc driver for the concerned database which needs to be present at this side only
Should be able to support multiple connections to multiple databases on the same machine, multiple result sets at any time etc.
HTML version of Basic Foils prepared 23 June 1997

Foil 34 Remote jdbc Driver Server-side Description - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Advantages
  • Allows access to a remote database using JDBC API from a java program transparently with a single driver existence at the client side.
  • Tries to attain uniformity at the client side over the use of the driver and the jdbc API used.
  • Will work with any type of driver (out of 4 categories mentioned previously) which only needs to be at the server side now.
Limitations
  • Implementation of the server side of this driver has many alternatives such as Socket communication/RMI/CORBA.
  • Involves network traffic at a higher magnitude which makes it prone to failure depending upon the reliability of the network.
HTML version of Basic Foils prepared 23 June 1997

Foil 35 JDBC 3 Tier Example

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
"3 Tier" refers to the presence of a "middle tier" service between the client and the database server
Commands are send to the middle-tier first, which then sends the SQL to the database and returns results from the database to the client
Advantages are :
  • avoids installations of any vendor specific libraries(.so or .DLL files) at the client side
  • control over access and updates of database
HTML version of Basic Foils prepared 23 June 1997

Foil 36 JDBC 3 Tier Example (contd.)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
user can employ higher level API, which is translated by the middle-tier to lower level calls (eg. Operations on multiple rows or row sets etc.,)
Performance advantages in many cases
Weblogic's T3 server, Bulletproof's JAGG server or Java servlets are examples of middle tier services
HTML version of Basic Foils prepared 23 June 1997

Foil 37 Java Servlets

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Servlets are protocol- and platform-independent server side components that can be dynamically loaded like applets
or can be considered as server side counterpart to applets
or Java application components which are loaded, on demand
Servlets differ from applets in that they are faceless ( without any graphics )
can be invoked in many ways one of which of the form
  • http://host-name:port/servlet/servlet-name
HTML version of Basic Foils prepared 23 June 1997

Foil 38 Java Servlets (contd.)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Servlets :
  • must import the java.servlet package
  • must extend the GenericServlet class or HttpServlet class if implementing http features
  • must override the service method in the class
eg Hello World Servlet:
import java.io.*;
import java.servlet.*;
public class HelloWorldServlet extends GenericServlet {
HTML version of Basic Foils prepared 23 June 1997

Foil 39 Java Servlets (contd.)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
public void service(ServletRequest req, ServletResponseres)
throws ServletException, IOException{
PrintStream out = newPrintStream(res.getOutputStream());
out.println("Hello world!");
}
public String getServletInfo() {
return "Hello World Servlet";
}
}
HTML version of Basic Foils prepared 23 June 1997

Foil 40 JDBC Servlet Example

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Demonstrates how to use servlets to access the database
This example uses Weblogic's JDBC driver to connect to the Oracle database on osprey7
The servlet allows to connect to a user's database and run queries on the same
It displays the query results as well as the driver details
The main java packages imported are java.servlet, java.servlet.http and java.sql among others.
HTML version of Basic Foils prepared 23 June 1997

Foil 41 JDBC Servlet Example (contd.)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Implementation :
public class DBServlet extends HttpServlet {
//overriding service method
public void service(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
//fetch the parameters from the form input
String username = req.getParameter("username");
String password = req.getParameter("password");
String query = req.getParameter("query");
PrintStream out = new PrintStream (res.getOutputStream() );
String url = "jdbc:weblogic:oracle"; // JDBC driver url
HTML version of Basic Foils prepared 23 June 1997

Foil 42 JDBC Servlet Example (contd.)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
try {
Class.forName("weblogic.jdbc.oci.Driver"); /* load the driver classes and get the connection to the database */
Connection con = DriverManager.getConnection(url,username,password);
//create and execute the query statement
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(query);
dispResultSet(rs,out);
//close connections here
} Catch(SQLException ex) {
//print the exceptions caught
}
}
HTML version of Basic Foils prepared 23 June 1997

Foil 43 JDBC Servlet Example (contd.)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
private void dispResultSet(ResultSet rs, PrintStream out)
throws SQLException {
//get the resultset metadata from the result set
ResultSetMetaData rsmd = rs.getMetaData();
//get the # of columns in the table and display the results
while (rs.next() ) {
for (int I;I &#060= numcols; I++) {
//display the values for each column
}
}
}
HTML version of Basic Foils prepared 23 June 1997

Foil 44 List of vendors

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Some of the leading database, connectivity and tools vendors who have already endorsed the JDBC API and the DBMS's they support are
Borland International Inc. - InterBase 4.0
IBM - DB2 Version 2
Imaginary Software Inc. - mSQL
Intersolv - Oracle and Sybase
Javasoft - Several dozen through ODBC drivers
Sybase Inc. - Sybase SQL server
Weblogic Inc., etc. - Oracle, Sybase, MS SQL server
HTML version of Basic Foils prepared 23 June 1997

Foil 45 Practical Options at NPAC

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The JDBC technologies that are currently being explored at NPAC are :
mSQL Database - JDBC driver from Imaginary
- a lightweight SQL engine that supports a subset of ANSI SQL
- shareware product that can be built on most Unix OS and Win32 OS
- one of the first database products to be supported by a Java API
- avialable from anonymous ftp ftp://bond.edu.au/pub/Minerva/msql
- JDBC driver for mSQL database can be downloaded from
http://www.imaginary.com/Java/
Oracle - JDBC driver from Weblogic
- Weblogic has got a set of proprietary solutions to access DB
- Works with Oracle, Sybase, MS SQL server
- Classes built on top of JDBC to handle result datasets etc.
- Access the DB either through JDBC application or applets
- Test versions of their products can be downloaded from
http://www.weblogic.com
HTML version of Basic Foils prepared 23 June 1997

Foil 46 Practical Options at NPAC contd.

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Odbc using Jdbc-Odbc bridge from Intersolv Inc.
- ODBC is an open programming interface based on structured query language (SQL). The interface provides a standard, universal library of functions for building and deploying cross-platform database-independent applications.
-ODBC functions can be called from any programming language that can call a dynamic link library (DLL) or shared library.
-"ODBC is not appropriate for direct use from Java since it is a C interface."
-Non-availability of database drivers prompted javasoft to leverage the broad base of installed ODBC drivers, so the JDBC/ODBC Bridge enables data access from Java to ODBC interface via JDBC
HTML version of Basic Foils prepared 23 June 1997

Foil 47 Example - Using Imaginary's mSQL Driver

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
import java.net.URL;
import java.sql.*;
import imaginary.sql.*;
class Select {
public static void main (String argv[] ) {
try {
//load the class imaginary/sql/iMsqlDriver
Class.forName (" imaginary.sql.iMsqlDriver");
String url = jdbc:msql://host.npac.syr.edu:4333/test";
//connect to the test database using the driver URL
Connection conn = DriverManager.getConnection( url, "scott", "tiger");
//create the query statement and get the result set
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery (" select * from
test_table order by id");
System.out.println ("Got results :");
//print the results in a loop
HTML version of Basic Foils prepared 23 June 1997

Foil 48 Example - using Imaginary's mSQL Driver -II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
while (rs.next() ) {
int a = rs.getInt(1);
String str = rs.getString(2);
System.out.println("key = " + a);
System.out.println("str = " + str); }
//close the query execution and the database
connection before exiting
stmt.close();
conn.close();
}
//catch any exception and print it to the
system output
catch ( Exception e) {
System.out.println (e.getMessage() );
e.printStackTrace();
}
} // End main
} // End class Select
HTML version of Basic Foils prepared 23 June 1997

Foil 49 Example - Using Weblogic's Oracle Driver

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
package tutorial.jdbc.oracle;
import java.sql.*;
public class myTest {
public static void main (String argv[] ) throws Exception
{ //load the driver class from the local CLASSPATH
Class.forname("weblogic.jdbc.oci.Driver");
//connect to the database using the driver url and
// other properties like username, password and server
Connection conn =
DriverManager.getConnection("jdbc:weblogic:oracle:kayak",
"scott", "tiger")
//statement class is instantiated using the connection
// class for executing queries
Statement stmt = conn.createStatement();
stmt.execute("select * from empdemo");
HTML version of Basic Foils prepared 23 June 1997

Foil 50 Example - Using Weblogic's Oracle Driver -II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
//result set or hit list is obtained after
// executing the query
//and using a cursor it is displayed
ResultSet rs = stmt.getResultSet ( );
while (rs.next () ) {
System.out.println( rs.getString(1) + "-" +
rs.getString(2) + "-" + rs.getString(3) );
}
//get the metadata using the result set
ResultSetMetaData rsmd = rs.getMetaData ();
System.out.println("Number of Columns:"
+ rsmd.getColumnCount() );
HTML version of Basic Foils prepared 23 June 1997

Foil 51 Example - Using Weblogic's Oracle Driver -III

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
//display the metadata information like max size. column type name etc.
for (int i = 1; i &#060= rsmd.getColumnCount(); i++) {
System.out.println("Column Name :" + rsmd.getColumnName(i));
System.out.println("Nullable :" + rsmd.isNullable(i));
System.out.println("Precision :" + rsmd.getPrecision(i));
System.out.println("Scale :" + rsmd.getScale(i));
System.out.println("Size :" + rsmd.getColumnDisplaySize(i));
System.out.println("Column Type :" + rsmd.getColumnType(i));
System.out.println("ColType Name :" + rsmd.getColumnTypeName(i));
System.out.println(" ");
}
//close the query connection states and then close the database connection
stmt.close();
conn.close();
} // end main
} // end myTest class
HTML version of Basic Foils prepared 23 June 1997

Foil 52 Example - Using JDBC-ODBC Bridge - I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
import java.net.URL;
import java.sql.*;
class simpleselect {
public static void main (String args[]) {
String url = "jdbc:odbc:my-dsn";
String query = "SELECT * FROM emp";
try {
// Load the jdbc-odbc bridge driver
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
// Attempt to connect to a driver. Each one
// of the registered drivers will be loaded until
// one is found that can process this URL
// SEE NEXT FOIL!!
HTML version of Basic Foils prepared 23 June 1997

Foil 53 Example - Using JDBC-ODBC Bridge - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Connection con = DriverManager.getConnection (
url, "my-user", "my-passwd");
// If we were unable to connect, an exception
// would have been thrown. So, if we get here,
// we are successfully connected to the URL
// Get the DatabaseMetaData object and display
// some information about the connection
DatabaseMetaData dma = con.getMetaData ();
System.out.println("\nConnected to " + dma.getURL());
System.out.println("Driver " +
dma.getDriverName());
System.out.println("Version " +
dma.getDriverVersion());
System.out.println("");
HTML version of Basic Foils prepared 23 June 1997

Foil 54 Example - Using JDBC-ODBC Bridge -III

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
// 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
dispResultSet (rs); // see later
// Close the result set
rs.close();
// Close the statement and then the connection
stmt.close();
con.close();
}
HTML version of Basic Foils prepared 23 June 1997

Foil 55 Example - usingJDBC-ODBC Bridge -IV

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
catch (SQLException ex) {
// A SQLException was generated. Catch it and
// display the error information. Note that there
// could be multiple error objects chained
// together
System.out.println ("\n*** SQLException caught***\n");
while (ex != null) {
System.out.println ("SQLState: " +
ex.getSQLState ());
System.out.println ("Message: " +
ex.getMessage ());
System.out.println ("Vendor: " +
ex.getErrorCode ());
ex = ex.getNextException ();
System.out.println ("");
}
}
HTML version of Basic Foils prepared 23 June 1997

Foil 56 Example - usingJDBC-ODBC Bridge (contd.)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
catch (java.lang.Exception ex) {
// Got some other type of exception. Dump it.
ex.printStackTrace ();
}
}
} // End Main
// dispResultSet
// Displays all columns and rows in the given result set
private static void dispResultSet (ResultSet rs)
throws SQLException
{
//Display routine for displaying the result set goes here
}
} // End simpleselect
HTML version of Basic Foils prepared 23 June 1997

Foil 57 Microsoft Access

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Features
Microsoft ACCESS is a desktop relational database interactive management system developed for Microsoft Windows.
The databases of Microsoft ACCESS can be managed via the ACCESS interactive system or via a library of executable functions callable from any PC programming language, like Visual Basic, Visual C++, etc.
HTML version of Basic Foils prepared 23 June 1997

Foil 58 MS Access - Features (cont'd)

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
ACCESS is particularly useful because of its completeness and its user friendliness of its editing interface, the tabular visualization of the data (table browser) and for the ease of query definitions and graphic output.
Access 97 integrates data from spreadsheets and other databases and is the easy way to find answers, share information over intranets and the Internet, and build faster business solutions.
The interface usability of ACCESS offers the possibility to consult and edit the data and to execute the queries in an immediate and intuitive way. Defining forms allows the development of the GUI interfaces for the database.
HTML version of Basic Foils prepared 23 June 1997

Foil 59 Cool Features of Access - I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Form and Report Wizards help create Access forms and reports from database information with little user intervention
Table Analyzer Wizard analyzes the structure of flat-file data, and undertakes the normalization process for users
Simple Query Wizard helps users find exactly the information they seek
Database Wizard allows users without any database experience to create one of more than 20 fully functional database applications
HTML version of Basic Foils prepared 23 June 1997

Foil 60 Cool Features of Access - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Performer Analyzer Wizard analyzes the structure of a database and suggests areas for improvement
OLE Automation allows users to share information across applications.
Support for ActiveX Controls - ActiveX controls can be embedded directly in Office files, allowing developers to create fully programmable documents and forms with enhanced capabilities for online viewing
HTML version of Basic Foils prepared 23 June 1997

Foil 61 The Access Event Model

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
An 'object' is what can be seen in the database window --tables, queries, forms, fields, indexes, controls etc. To create an Access application, objects have to be created and manipulated in response to events which are simply change in state occurring for example, when the mouse is moved, a button is clicked etc..
Event Handlers in Access are Macro, Event procedure, Global module function and Form module function. All except the Macro use the VBA (Visual Basic for Applications) language. 3 type of events can occur for a form:
  • Form events
  • Section Events
  • Control Events
HTML version of Basic Foils prepared 23 June 1997

Foil 62 VBA class modules

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The tool provided for Visual Basic provides ability to create and manipulate one's classes of objects and encapsulating data and processes within an object. Class modules also allow the ability to abstract complex processes.
By using object-oriented techniques, one can create classes, their objects, properties, define methods and hence make development simple.
Class modules have 2 events -- Initialize (creation of a class) and Terminate (destruction of the class). Class hierarchies can be built depending on relationships.
HTML version of Basic Foils prepared 23 June 1997

Foil 63 Database Design - I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The relational database model was conceived in 1969 by E.F.Codd.
The model is based on mathematical theory viz. a database consists of a series of unordered tables (or relations) that can be manipulated using non-procedural operations that return tables.
Benefits of a relational database:
  • Data entry, updates and deletions are efficient.
  • Data retrieval, summarization, and reporting are efficient
  • Database behaves predictably.
  • Changes to the database schema are easy to make.
HTML version of Basic Foils prepared 23 June 1997

Foil 64 Database Design - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Each table in a relational database is made unique by designating a 'primary key' - a column or set of columns that have unique values. A table can have only one primary key. A 'foreign key' is a column in one table that references the primary key of another table.
There are 3 types of relationships amongst two tables:
  • One-to-One - For each row in the first table, there is atmost one row in the second table.
  • One-to-Many - For each row in the first table, there can be zero, one, or many rows in the second table, but for each row in the second table, there is exactly one row in the first table.
HTML version of Basic Foils prepared 23 June 1997

Foil 65 Database Design - III

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Many-to-Many - For each row in the first table, there can be many rows in the second table, and for each row in the second table, there can be many rows in the first table.
The relational model specifies two Integrity rules:
  • Entity integrity rule - Primary keys cannot contain null (missing) data.
  • Referential integrity rule - The database must not contain any unmatched foreign key values.
HTML version of Basic Foils prepared 23 June 1997

Foil 66 Access SQL - I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The textual query definition is done using SQL (Structured Query Language), a non-procedural data access language.
Access SQL support a subset of SQL-89 and some elements of the newer SQL-92 standard.
Most of Access SQL directly maps to Access QBE (Query by Example) in both directions. Queries can be constructed using QBE and viewed in SQL by switching to SQL view. Queries can also be directly entered using SQL view.
SQL Statements:
  • The SELECT Statement: The most basic and main statement of SQL. It queries select rows of data and returns them as a dynaset recordset.
  • Syntax: SELECT column-list
  • FROM table-list
  • [WHERE where-clause]
  • [ORDER BY order-by-clause];
HTML version of Basic Foils prepared 23 June 1997

Foil 67 Access SQL - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Syntax of SELECT clause:
SELECT {* | expression1 [AS alias1][, expression2 [AS alias2][,..]]]}
Example: SELECT *
SELECT LastName
SELECT [Customer#], LastName, FirstName
Syntax of FROM clause:
FROM table-or-query [AS alias]
Example: SELECT * from tblOrder;
SELECT OrderId, OrderDate FROM tblOrder as [Orders Table];
Syntax of WHERE clause:
WHERE expression1 [{And | Or} expression2[..]]
Example: SELECT OrderId FROM tblOrder WHERE OderTakerId = 2;
SELECT OrderId, OrderDate FROM tblOrder WHERE Sex="Female" AND Age BETWEEN 21 AND 29;
Syntax of ORDER BY clause:
ORDER BY column1 [{ASC | DESC}][,column2[{ASC | DESC}][,..]]
Example: SELECT * FROM tblCustomer ORDER BY LastName, FirstName;
HTML version of Basic Foils prepared 23 June 1997

Foil 68 Access SQL - III

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The UPDATE Statement: Used to change values in one or more columns in a table.
Syntax: UPDATE table-or-query
SET column1=expression1[,column2=expression2][,..]
[WHERE criteria];
Example: UPDATE tblMenu SET tblMenu.Price = {Price]*1.1 WHERE MenuDescription Not Like "*Pizza";
The DELETE Statement: Used to delete rows from tables.
  • Syntax: DELETE [table.*]
FROM from-clause
[WHERE criteria];
Example: DELETE FROM tblMenu WHERE Discontinued = True;
The INSERT INTO Statement: Used to copy rows from one table into another or to add a single row of data to a table using a list of values.
Syntax: INSERT INTO target-table
select-statement;
Example: INSERT INTO tblCustomer SELECT * FROM tblCustomerNew;
HTML version of Basic Foils prepared 23 June 1997

Foil 69 Access SQL - IV

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The SELECT INTO Statement: It is unique to Access SQL, Used to create a new table from the rows in another table or query.
Syntax: SELECT column1 [,column2[,..]] INTO new-table
Example: SELECT OrderId, OderDate INTO tblJonesOrders FROM tblOrder WHERE CustomerId=9;
Access SQL supports 4 DDL (Data Definition Language) statements:
  • CREATE TABLE - Creates a new table schema
  • ALTER TABLE - Modifies an existing table schema
  • CREATE INDEX - Creates a new index
  • DROP - Deletes a table schema or an index
Data Access Objects (DAO) is another method, like DDL, to programmatically create and manipulate table schemas in Access.
HTML version of Basic Foils prepared 23 June 1997

Foil 70 Access Controls

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Controls are the workhorses of Access. They are used for inputting and outputting data, and for displaying static information. They can be used as global variables, to calculate intermediate values. Forms and reports share the same controls.
Some controls have values, given by user at design time, by the data from which the control is being fed, or by the user at run time. Eg. for a text box, the value is the text inside the box; for a list box, the control value is the one chosen.
Controls have properties which can be set or changed by the application. Access provides the Tag property which allows the user to specify and store upto 2048 characters of information attached to any control.
HTML version of Basic Foils prepared 23 June 1997

Foil 71 Access Forms and Reports

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Beyond the tabular vision, for consultation and editing of data, there is a form editor and a form interpreter that allows the generation and management of specific input/output data interfaces (forms).
The usage of forms allows the visualization in a single window interface of data belonging to different tables and query results.
In Access, program code and a form (or report) is stored in a neat package. Each form and report can carry its own module with it. The event procedures are subroutines and their scope, by default, is private to the form.
Reports are information organized and formatted to fit the user's specifications. They provide a way to retrieve and present data as meaningful information. Examples are mailing labels, invoices, sale summaries, phone lists etc.
HTML version of Basic Foils prepared 23 June 1997

Foil 72 Developing Multiuser Applications

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Main issue is handling errors that occur when locks are placed on pages of records. The following considerations are important:
  • Balance network security and maintenance against efficiency and speed when choosing to install Access on each workstation.
  • Balance ease of use, data integrity, and ease of programming in developing the locking strategy.
  • Awareness of errors that can occur when multiple users share data.
  • Use custom error-handling code when using bound forms with optimistic locking.
  • Use custom lock notification code when using bound forms with pessimistic locking.
  • Splitting database into separate data and application databases for increased performance.
  • Using VBA code to manage the linked tables in a split database structure.
HTML version of Basic Foils prepared 23 June 1997

Foil 73 Security in Access

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
There are two overlapping security models:
  • WorkGroup-based security model - Also called user-bases security model. Security revolves around a database and is self-contained within the confines of that database. Security is based on users and their permissions. Objects can be assigned different permissions.
  • Database Password security model - This simpler model requires setting of a single password that all users must know to open the database. But it can't track individual users' activity in a shared database. But both the models can be used at the same time.
  • Encryption is another method provided by Access to secure the database from hackers. But it reduces database performance by 10-15%. Also database is made uncompressible by some programs. Also, the Access Security Wizard helps to secure the databases.
HTML version of Basic Foils prepared 23 June 1997

Foil 74 Client-Server Applications

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Client-Server database applications shift the burden of data storage and manipulation to a dedicated application. In such applications Access provides the interface to the server data.
Some methods for retrieving data from a server are:
  • File-server - database is moved to a network server's hard drive. Inefficient due to the data moved across the network to serve queries.
  • Linked ODBC tables - User's SQL is translated to a universal SQL dialect and is passed to the ODBC manager running on the client workstation.
  • Direct connection using Data Access Objects - The OpenDatabase method is used. The ODBC connection is given information to open a server database directly.
  • SQL pass-through (SPT) queries - Used when some advanced feature of the database server is required that ODBC SQL can't understand.
  • ODBCDirect - Instead of the query being translated, it is sent directly to the database server.
HTML version of Basic Foils prepared 23 June 1997

Foil 75 Web-Enabled Applications -I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Access 97 makes it easy to share up-to-date information with others, regardless of where they are. With features of exporting dynamic forms to HTML and publishing them on the Web, it provides for sharing of dynamic or static database information across the Internet or intranet.
HyperLinks - Users can store hyperlinks in all MS Access databases to connect to information anywhere. Hyperlinks can be control-based i.e text box, combo box, label, command button, image control, can be bound to fields with a Hyperlink datatype.
  • Save to HTML - Users can publish static views of their data for a workgroup or on the Web.
  • HTML importing and linking - Users can import or attach their databases to HTML pages.
  • Internet replication - Users can extend the replication capabilities introduced in Access 95 over the Internet using FTP.
  • Publish to the Web Wizard - This wizard automates the publishing of database information to the Web dynamically.
HTML version of Basic Foils prepared 23 June 1997

Foil 76 Web-Enabled Applications -II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Data can be published in 3 formats:
  • Static HTML - Wizard output is HTML files. Supported by all browsers; simple, standard.
  • Dynamic Internet Database Connector (IDC) pages - Output is IDC and HTX (HTML extension) files. Supported by all browsers. Requires the MS Internet Information Server 3.0 (IIS) with an ODBC connection to the database, data is dynamic but read-only.
  • Dynamic Active Server Pages (ASP)- Output is ASP files. Requires Internet Explorer 3.0 browser with HTML ActiveX Layout control, IIS 3.0; data is dynamic and editable (forms only). ActiveX Server is a component of the IIS. The Access form-like pages can browse, update, insert and delete records. The ASP files are a combination of HTML and the VBScript language. VBScript is a special version of VBA adapted for Web use. The ASP files contain HTML tags interspersed with queries as SQL statements, template directives and VBScript code containing references to ActiveX Server Controls.
HTML version of Basic Foils prepared 23 June 1997

Foil 77 Web-Enabled Applications -III

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
The ASP files also contain ODBC connection information to connect to an ODBC data source (Access database). The connection information includes the data source name, and user name and password (if user-level security is needed).
After the database has been published, the IIS, upon request from the web browser, runs the VBScript code, calls the ActiveX Server Controls, opens the Access database, runs the queries present in the ASP files to access the data, merges the results and HTML tags in the ASP file into one file and then sends it back to the browser for display as a Web page.
The adjoining figure shows the components for connecting to databases from Peer Web Services.
HTML version of Basic Foils prepared 23 June 1997

Foil 78 Sample code for ASP files - I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
&#060HTML&#062
&#060TITLE&#062Employees Directory&#060/TITLE&#062
&#060BODY&#062
&#060h2&#062 &#060center&#062&#060font color=red&#062Form for Employees Directory&#060/font&#062&#060/center&#062&#060/h2&#062
&#060%
If IsObject(Session("workpg_conn")) Then
Set conn = Session("workpg_conn") workpg is ODBC System
Else DSN for an Access db
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "workpg","",""
Set Session("workpg_conn") = conn
End If
%&#062
&#060%
If cstr(Request.QueryString("ok_ans")) &#060&#062 "" Then
rs.Fields("ok_ans").Value = Request.QueryString("ok_ans")
End If
If cstr(Request.QueryString("OptBody6")) &#060&#062 "" Then
rs.Fields("OptBody6").Value = Request.QueryString("OptBody6")
End If
% &#062
HTML version of Basic Foils prepared 23 June 1997

Foil 79 Sample code for ASP files - II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
&#060SCRIPT LANGUAGE=VBScript&#062
&#060!--
Dim rgszCtrls(24, 2)
Dim cMaxCtrls
cMaxCtrls = 24
Sub AddCtrlToList(szCtrl, szCtrlSrc)
Dim i
for i = 1 to cMaxCtrls
if rgszCtrls(i, 1) = szCtrl Then Exit Sub
if rgszCtrls(i, 1) = "" Then
rgszCtrls(i, 1) = szCtrl
rgszCtrls(i, 2) = szCtrlSrc
Exit Sub
End If
next
End Sub
Sub UpdateRefreshBtn()
nav_btn_MoveCancelUpdate.Caption = "Cancel"
End Sub
--&#062
&#060/SCRIPT&#062
HTML version of Basic Foils prepared 23 June 1997

Foil 80 Access as an Automation Client-I

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Automation is a technology that allows two separate applications components to communicate with each other as either data exchanges or commands issued by one component for the other to perform.
It requires a client and a server. The client uses the services of an automation server. It implements a development language used to write code to control the server. Capacity to use pre-built, robust and debugged software components in applications.
Automation Clients are Access, MS Excel, Word, PowerPoint, Visual Basic, and any application supporting VBA.
Sessions begin with the client applications creating an object, i.e. establishing a conversation with the server application and telling it which of its objects are required to be controlled.
HTML version of Basic Foils prepared 23 June 1997

Foil 81 Access as an Automation Client-II

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Class Objects exposed by MS Office 97 applications
------------------------------------------------------------------------------------------
Server Name Class Name Description
-------------------------------------------------------------------------------------------
Access Application Pointer to an instance of MS Access.
Excel Application Pointer to an instance of MS Excel Chart Pointer to a new Chart object. Launches Excel & opens a new workbook if reqd
Sheet Pointer to new Worksheet object. Launches Excel and opens a new workbook if reqd.
Outlook Application Pointer to an instance of MS Outlook.
PowerPoint Application Pointer to an instance of MS PowerPoint.
Word Application Pointer to an instance of MS Word. Document Pointer to a new Document object. Launches Word if reqd.
HTML version of Basic Foils prepared 23 June 1997

Foil 82 Access as an Automation Client-III

From Overview of JDBC and its use with Microsoft Access UC Web Applications Certificate -- June 26 97. *
Full HTML Index
Examples of the types of automation solutions that can be built are:
  • A mail-merge tool that inserts data from any form into a new Word document
  • A tool that creates and runs a PowerPoint presentation based on Access data.
  • A data analysis system that uses Excel to summarize date from Access table
Power Point
Access
Word
Automation
Web
Web
Web
Excel
Web
© Northeast Parallel Architectures Center, Syracuse University, npac@npac.syr.edu

If you have any comments about this server, send e-mail to webmaster@npac.syr.edu.

Page produced by wwwfoil on Thu Jan 8 1998