Web to Database Connectivity Tools

1
Web to Database Connectivity Tools

• Frank Cervone
• Assistant Director for Systems
• DePaul University Libraries
• Access 98
• October 3, 1998

2
Introduction

• Databases
• Database APIs
• Programming Languages
• Access Models

3
Databases

• Flat files
• Relational databases
• Object-oriented databases

4
Flat Files

• Simple implementation
• regular text
• basic encoding
• word, excel files

5
dbm Files

• UNIX/gnu
• library of routines that manage data files
  containing key/data pairs
• read, write, delete by key
• nonsorted traversal of all keys

6
dbm File Example
There is no inherent mechanism for ensuring data
consistency
7
Relational Databases

• Most common data management scheme
• Data is organized into two-dimensional tables of
  rows and columns
• Data is decomposed to its simplest form
• Normalization reduces data inconsistency
• Referential integrity

8
Relational Database Example
Course Table
Enrollment Table
Student Table
9
Object-oriented Databases

• Can mean many things
• Data exists as objects
• each object encapsulates
• data (attributes)
• methods (procedures)
• is a member and instance of a class of objects
• may be a subset of a class and inherit
  characteristics of the superclass

10
Object-oriented Example
Students
Courses
11
Database APIs

• Native-interface
• ODBC
• JDBC
• CORBA

12
Native Interface

• Low-level interface
• Direct call from a program to an access method to
  retrieve data
• Typically only used directly when accessing flat
  files
• Often used from compiled programming languages

13
ODBC

• Open Database Connectivity
• Standard method for applications to request
  database information from other applications
  (i.e., database servers)
• Common interface and language (SQL) to disparate
  database systems
• Examples Oracle, MS-SQL Server, MS-Access, DB2

14
ODBC Example
Driver Manager
Application
MS-Access Driver
Oracle Driver
MS-SQL Driver
MS-Access Database
Oracle Database
MS-SQL Database
15
JDBC

• Java Database Connectivity
• Interface to ODBC for Java programs
• Standard method for Java programs to request
  database information from other applications
  ( i.e., database servers)
• Common interface and language (SQL) to
  ODBC-compliant database servers

16
CORBA

• Common Object Request Broker Architecture
• Standard method for requesting objects from
  object-oriented database servers
• Adoption has been slow, but is growing

17
Programming Languages

• Java
• C and/or C
• PERL/CGI
• VBScript/ASP

18
CGI

• Common Gateway Interface
• A standard programming interface to web server
  applications
• These applications act as gateways between the
  web server and the database

19
CGI Overview
Client Web Browser
Web Server
Program or Script
Database
20
CGI Details

• Browser sends URL request with embedded CGI
  program name
• Web server set environment variables, executes
  program, and passes form data via STDIN
• Program examines environment variables, reads
  STDIN, performs requested functions, calls
  database
• Database receives request from program and
  retrieves data

21
CGI Details Continued

• Database returns data to calling program
• Program acts on returned data
• HTML page, text page, redirect command
• sends results to web server via STDOUT
• Web server creates HTTP-formatted result
• Browser displays web page

22
CGI Interaction
Client Web Browser
HTTP
Web Server
CGI
Program or Script
ODBC
Database
23
Using Databases from the Web

• CGI program
• C, C
• PERL
• Intermediate program
• mSQL/Lite
• Database with web support
• FileMaker Pro
• Special web server
• VBScript/IIS
• ColdFusion

24
Displaying the Data

• CGI program -
• generates the web page on the fly
• Database with web support
• page is generated by database program
• Intermediate program/Special web server -
• page is interpreted and a new one is generated in
  its place

25
Interpreted Page Example
ltHTMLgt ltBODYgt lt set connserver.createobject(
adodb.connection) conn.open
DSNStudentsuidmasterpwdtest set
resultconn.execute(select fromenrollment)
response.write ltTABLE BORDER1gt
response.write ltTRgt for I0 to
howmanyfields response.write ltTDgtltBgt
result(i).name lt/Bgtlt/TDgt next
response.write lt/TRgt . . . (etc.)
26
Scenarios

• FileMaker Pro
• MS-IIS/VBScript
• mSQL/Lite
• ColdFusion/Oracle

27
Suggestions

• Understand your current needs
• Project for your future plans
• Keep server diversity to a minimum
• Hire/train appropriate personnel