Accessing Relational Databases from the World Wide Web

Accessing Relational Databases from the World Wide Web by Tam Nguyen & V. Srinivasan Presented by Megan Thomas and Randi Thomas CS 294 -7 February 11, 1999

Outline • Overview from the User’s perspective • Step through the path of a single query • Brief Clarification of the Macro Language • Alternative Approach to DB 2 WWW • Advantages of DB 2 WWW System • Disadvantages of DB 2 WWW System • What’s Not Presented in the Paper

Overview of the Whole Shebang You Are Here User asks form URL DB 2 WWW HTML form Macro file User fills out HTML form data SQL DB 2 WWW Query results Query report in HTML

First Step • How user got here is irrelevant! • First URL used: http: //[{web-server}]/cgi-bin/db 2 www[. exe] /{macro-file}/{input}[? name=val&…] • Web server starts db 2 www cgi-bin program • db 2 www 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

HTML Input Form

Second Step • Second URL used: http: //[{web-server}]/cgi-bin/db 2 www[. exe] /{macro-file}/{report}[? name=val&…] • web server starts db 2 www cgi-bin program • db 2 www program uses macro-file to: – translate data in HTML input form into SQL – submit SQL query to DB 2 database – translate database results into HTML • db 2 www sends HTML report to user

HTML Report Form

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

What’s 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

Alternative Approach to DB 2 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

Advantages of DB 2 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

Disadvantages of DB 2 WWW System • Process startup overhead: – Two db 2 www 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

Not Presented in This Paper • DB 2 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 • DB 2 WWW Security will work with: – – Web Server DBMS Firewalls Not clear how this is done • Other DBMSs

Not A Real Slide --> FYI • DB 2 WWW Transactional support: – Not clear how this is done • DB 2 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 DB 2 WWW Connection Application Developers Guide is dead

Back up Slides

URL Explanations http: //[{web-server}]/cgi-bin/db 2 www[. exe] /{macro-file}/{cmd }[? name=val&…] • db 2 www[. exe]: – cgi-bin program • macro-file: – file written in DB 2 WWW macro language for cross-language variable substitution • If cmd is: – “input”: db 2 www processes the HTML input section of the macro-file – “report”: db 2 www processes the HTML report section of the macro-file • [? name=val&…]: – Optional input variables from the Web client

HTML to create HTML FORM

Sample Macro file: Variable Definition Section

Sample Macro file: SQL Command section

Sample Macro file: HTML Input Section

Sample Macro file: HTML Report Section

Application Development Environment HTML Editor HTML forms User Run. Time Environment Web Browser Query Tools SQL Query Macro File: • SQL Command • input form • report form HTML forms & SQL command Web Server CGI DB 2 WWW Gateway