Accessing Relational Databases from the World Wide Web

1
Accessing Relational Databases from the World
Wide Web

• by
• Tam Nguyen V. Srinivasan
• Presented by
• Megan Thomas and Randi Thomas
• CS294-7
• February 11, 1999

2
Outline

• Overview from the Users perspective
• Step through the path of a single query
• Brief Clarification of the Macro Language
• Alternative Approach to DB2 WWW
• Advantages of DB2 WWW System
• Disadvantages of DB2 WWW System
• Whats Not Presented in the Paper

3
Overview of the Whole Shebang
Web Browser
Web Server
First Step
User asks for form
DB2 WWW
URL
HTML form
Macro file
Second Step
User fills out HTML form
DB2 WWW
SQL
HTML form data
Query results
Database
Query report in HTML
HTML
HTML
SQL
CGI
4
First Step

• How user got here is irrelevant!
• First URL used
• http//web-server/cgi-bin/db2www.exe
• /macro-file/input?nameval
• Web server starts db2www cgi-bin program
• db2www program
• uses macro-file to create HTML form
• sends HTML form back to the user
• User fills out HTML form containing the
  information which will drive the SQL query

5
HTML Input Form
6
Second Step

• Second URL used
• http//web-server/cgi-bin/db2www.exe
• /macro-file/report?nameval
• web server starts db2www cgi-bin program
• db2www program uses macro-file to
• translate data in HTML input form into SQL
• submit SQL query to DB2 database
• translate database results into HTML
• db2www sends HTML report to user

7
HTML Report Form
8
Macro Language (Bulk of Paper)

• Definition A mechanism for cross language
  variable substitution between
• HTML input and SQL query strings
• SQL query results and HTML output
• A Macro Contains
• SQL sections
• HTML sections
• Variable substitutions which tie the sections
  together
• Example in Back up slides

9
Whats In A Macro File?

• One or more Variable Definition Sections
• Used to define and manipulate macro variables
• One or more SQL Command Sections
• Contains one SQL statement
• May contain a user-defined report format for the
  SQL statement (optional)
• One HTML Input Section
• Used to get input variable values from user
• One HTML Report Section
• Used to generate reports from SQL query results

10
Alternative Approach to DB2 WWW

• Stand-alone cgi program (for each application)
  to
• accept and generate HTML
• access the database
• This cgi program would need alteration in order
  to
• use new HTML features
• use new SQL features
• or to conform to changes in
• cgi protocol
• or DBMS API

11
Advantages of DB2 WWW System

• Application programmer can focus on desired
• HTML input/output forms
• SQL queries
• Programmer is not encumbered with the translation
  mechanisms
• Is a General Purpose solution
• Encompasses all of HTML and SQL

12
Disadvantages of DB2 WWW System

• Process startup overhead
• Two db2www processes are started per query
• One to send the appropriate HTML input form
• One to do the query and send the HTML report
• Application logic that cannot be expressed in
  HTML or SQL cannot be done

13
Not Presented in This Paper

• DB2 WWW Transactional support
• Can be done in two ways
• Whole macro can be one transaction
• Each SQL statement is a separate transaction
• Not clear how this is done
• DB2 WWW Security will work with
• Web Server
• DBMS
• Firewalls
• Not clear how this is done
• Other DBMSs

14
Not A Real Slide --gt FYI

• DB2 WWW Transactional support
• Not clear how this is done
• DB2 WWW Security will work with
• Not clear how this is done
• Joe - not clear how this stuff is done because
  the on-line reference to the DB2 WWW Connection
  Application Developers Guide is dead

15
Back up Slides
16
URL Explanations

• http//web-server/cgi-bin/db2www.exe
• /macro-file/cmd
  ?nameval
• db2www.exe
• cgi-bin program
• macro-file
• file written in DB2 WWW macro language for
  cross-language variable substitution
• If cmd is
• input db2www processes the HTML input
  section of the macro-file
• report db2www processes the HTML report
  section of the macro-file
• ?nameval
• Optional input variables from the Web client

17
HTML to create HTML FORM
18
Sample Macro file Variable Definition Section
19
Sample Macro file SQL Command section
20
Sample Macro file HTML Input Section
21
Sample Macro fileHTML Report Section
22
Application Development Environment
DB2 WWW System Overview
Query Tools
HTML Editor
HTML forms
SQL Query

• Macro File
• SQL Command
• input form
• report form

User RunTime Environment
HTML forms SQL command
Web Browser
Web Server
DB2 WWW Gateway
CGI