Full HTML for

Basic foilset Examples of JDBC -- Microsoft Access

Given by Sangeeta Aggarwal,Tom Pulikal,Udayan Pravate(Nancy McCracken) at CPS616 -- Information Track of CPS on Spring Semester 97. Foils prepared April 15 1997
Outside Index Summary of Material

We show the details of using JDBC methods in two examples now working at NPAC: the 2-tier architecture with the client on the same machine as the Oracle server, and the 3-tier architecture, using Java servlets.
Next, we discuss background material on the database MicroSoft Access.
We show more details of the different types of JDBC architecture.
We show a hetergeneous JDBC example that connects and uses information from bothAccess97 and Oracle.
And we mention a useful JBDC Teaching Tool.

Table of Contents for full HTML of Examples of JDBC -- Microsoft Access

 Denote Foils where Image Critical
Denote Foils where HTML is sufficient
1 Java DataBase Connectivity, Part II
2 Abstract of JDBC, Part II
3 Simple JDBC 2 Tier Applet - An Example
4 JDBC 2 Tier Applet
5 JDBC 2 Tier Applet
6 JDBC 3 Tier Example
7 JDBC 3 Tier Example (contd.)
8 Java Servlets
9 Java Servlets (contd.)
10 Java Servlets (contd.)
11 JDBC Servlet Example
12 JDBC Servlet Example (contd.)
13 JDBC Servlet Example (contd.)
14 JDBC Servlet Example (contd.)
15 DBDiagram Example
16 Microsoft Access
17 MS Access - Features (cont'd)
18 Cool Features of Access - I
19 Cool Features of Access - II
20 The Access Event Model
21 VBA class modules
22 Database Design - I
23 Database Design - II
24 Database Design - III
25 Access SQL - I
26 Access SQL - II
27 Access SQL - III
28 Access SQL - IV
29 Access Controls
30 Access Forms and Reports
31 Developing Multiuser Applications
32 Security in Access
33 Client-Server Applications
34 Web-Enabled Applications -I
35 Web-Enabled Applications -II
36 Web-Enabled Applications -III
37 Sample code for ASP files - I
38 Sample code for ASP files - II
39 Access as an Automation Client-I
40 Access as an Automation Client-II
41 Access as an Automation Client-III
42 More Details on JDBC Architectures
43 Driver Classification- I
44 Driver Classification - II
45 JDBC Driver API - I
46 JDBC Driver API - II
47 JDBC Driver API - III
48 Remote Database Access Current Options
49 mSQl jdbc driver for mSQl database
50 Weblogic jdbcKona driver for Oracle
51 Remote jdbc-driver features
52 PPT Slide
53 Remote jdbc Driver Client-side Description
54 Remote jdbc Driver Server-side Description - I
55 Remote jdbc Driver Server-side Description - II
56 JDBC based Heterogeneous Databases -- An Example
57 LCU Demo - I
58 LCU Demo - II
59 LCU Demo - III
60 Intersolv JDBCTEST demonstration
61 A JDBC Teaching Tool
62 Use as a Query Tool

Outside Index Summary of Material

HTML version of Basic Foils prepared April 15 1997

Foil 1 Java DataBase Connectivity, Part II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
Presented by Nancy McCracken
Prepared by Sangeeta Aggarwal, Tom Pulikal, and Udayan Parvate under the direction of Geoffrey Fox and Wojtek Furmanski
4/16/97
HTML version of Basic Foils prepared April 15 1997

Foil 2 Abstract of JDBC, Part II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
We show the details of using JDBC methods in two examples now working at NPAC: the 2-tier architecture with the client on the same machine as the Oracle server, and the 3-tier architecture, using Java servlets.
Next, we discuss background material on the database MicroSoft Access.
We show more details of the different types of JDBC architecture.
We show a hetergeneous JDBC example that connects and uses information from bothAccess97 and Oracle.
And we mention a useful JBDC Teaching Tool.
HTML version of Basic Foils prepared April 15 1997

Foil 3 Simple JDBC 2 Tier Applet - An Example

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 4 JDBC 2 Tier Applet

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 5 JDBC 2 Tier Applet

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 6 JDBC 3 Tier Example

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 7 JDBC 3 Tier Example (contd.)

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 8 Java Servlets

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 9 Java Servlets (contd.)

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 10 Java Servlets (contd.)

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 11 JDBC Servlet Example

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 12 JDBC Servlet Example (contd.)

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 13 JDBC Servlet Example (contd.)

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 14 JDBC Servlet Example (contd.)

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 15 DBDiagram Example

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 16 Microsoft Access

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 17 MS Access - Features (cont'd)

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 18 Cool Features of Access - I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 19 Cool Features of Access - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 20 The Access Event Model

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 21 VBA class modules

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 22 Database Design - I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 23 Database Design - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 24 Database Design - III

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 25 Access SQL - I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 26 Access SQL - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
Syntax of SELECT clause:
SELECT {*|expression1[ASalias1][,expression2[ASalias2][,..]]]}
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 April 15 1997

Foil 27 Access SQL - III

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 28 Access SQL - IV

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 29 Access Controls

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 30 Access Forms and Reports

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 31 Developing Multiuser Applications

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 32 Security in Access

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 33 Client-Server Applications

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 34 Web-Enabled Applications -I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 35 Web-Enabled Applications -II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 36 Web-Enabled Applications -III

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 37 Sample code for ASP files - I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 38 Sample code for ASP files - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 39 Access as an Automation Client-I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 40 Access as an Automation Client-II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 41 Access as an Automation Client-III

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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
HTML version of Basic Foils prepared April 15 1997

Foil 42 More Details on JDBC Architectures

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 43 Driver Classification- I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 44 Driver Classification - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 45 JDBC Driver API - I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 46 JDBC Driver API - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 47 JDBC Driver API - III

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 48 Remote Database Access Current Options

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 49 mSQl jdbc driver for mSQl database

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 50 Weblogic jdbcKona driver for Oracle

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 51 Remote jdbc-driver features

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 52 PPT Slide

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
Remote jdbc
Client
Remote jdbc
Server
Java Program
using JDBC API
Database2
Remote jdbc Driver Architecture
Local jdbc
Driver
Internet/intranet
M1
M2
Database1
Database3
HTML version of Basic Foils prepared April 15 1997

Foil 53 Remote jdbc Driver Client-side Description

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 54 Remote jdbc Driver Server-side Description - I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 55 Remote jdbc Driver Server-side Description - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 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 April 15 1997

Foil 56 JDBC based Heterogeneous Databases -- An Example

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
The Language Connect University, developed by Syracuse Language Systems, enables people to learn Spanish over the Internet. The students are required to submit quizzes, tests, grammar, vocabulary exercises etc. The large volume of data is stored in an Oracle Server and the user-end format is HTML pages on the web.
The vocabulary, grammar exercises, quizzes etc. each comprise of a workpage which further consists of problems. The problems have several questions which in turn have various options which have to be selected by the student as the correct answer.
HTML version of Basic Foils prepared April 15 1997

Foil 57 LCU Demo - I

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
One inherent problem in managing such a vast amount of data was content editing. ACCESS 97, a powerful relational database developed for Windows, has the exciting feature of exporting Access based forms to the web so that Access data can be created, modified, deleted, by using the Internet Explorer browser from any PC.
Jeeves, the Java server from JavaSoft is used. It is extended by writing servlets which make use of the JDBC API along with Java API, to establish contact with the heterogeneous databases. The Weblogic driver for connecting to Oracle server is used.
HTML version of Basic Foils prepared April 15 1997

Foil 58 LCU Demo - II

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
The steps involved are:
  • The Java servlet establishes contact with the Oracle server installed on a UNIX machine (here osprey7) using sockets. By passing SQL statements, which contain the Oracle data tables and field rows, the LCU data of all quizzes, test, exercises etc. is retrieved. This data contains HTML tags and the workpages consisting of the problems and questions are displayed on the Internet browser.
  • The user can then select a particular problem and its related questions which needs to be modified. Upon selection, the Java servlet is called which makes a connection to the Acces database using the JDBC-ODBC driver on Windows and transfers data to Access's database's tables.
HTML version of Basic Foils prepared April 15 1997

Foil 59 LCU Demo - III

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
The data can now be modified in the exported web forms and committed back to the Access database.
A reverse process, when called, invokes the servlet to transfer the data from the Access database to the Oracle server.
HTML version of Basic Foils prepared April 15 1997

Foil 60 Intersolv JDBCTEST demonstration

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
The JDBC Test tool provided by Intersolv makes it easy to experiment with JDBC commands. It allows users and driver developers to test JDBC commands via an easy to use GUI interface. It is written in pure Java and can be used either as a program or as an applet.
  • The tool was mainly designed and built to allow users to test any feature of any JDBC driver and their supported databases.
  • Each step in the function sequence can be controlled by the user, allowing both correct and incorrect operations to occur.
  • The tool covers all methods defined in the JDBC specification.
HTML version of Basic Foils prepared April 15 1997

Foil 61 A JDBC Teaching Tool

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
The JDBCTest Tool can be used to teach programmers about the JDBC itself.
Every time a JDBC method call is executed from the JDBCTest Tool, the corresponding Java code is written into a text window. That window can be displayed or not, depending on the task at hand
A log file can be specified so that all Java code is simultaneously written to an output file as well.
HTML version of Basic Foils prepared April 15 1997

Foil 62 Use as a Query Tool

From Examples of JDBC -- Microsoft Access CPS616 -- Information Track of CPS -- Spring Semester 97. *
Full HTML Index
The JDBCTest Tool can be used for simple query and update processing. With only a small number of inputs, a user can connect to a database, submit an SQL statement, and view the results.
Example : "Load And Go" command takes a given SQL statement, creates a JDBC Statement object, executes the statement, and then displays the contents of the JDBC ResultSet, all without further user input.
© 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 Aug 14 1997