|l||l|lllllflllllllllllllllllllllllllllllllllllllllllllllllllll
`US005737592A
`
`United States Patent
`
`[19]
`
`[11] Patent Number:
`
`5,737,592
`
`
`
`[45] Date of Patent: Apr. 7, 1998
`Nguyen et a].
`
`[54] ACCESSING A RELATIONAL DATABASE
`OVER THE INTERNET USING MACRO
`LANGUAGE FILES
`
`[’75]
`
`Inventors: Tam Minh Nguyen. San Jose;
`Venkataehary Srinivasan. Santa Clara.
`both of Calif.
`
`[73] Assignee:
`
`International Business Machines
`Corporation. Armonk. NY.
`
`[21] App]. No.: 491,742
`
`[22] Filed:
`
`Jun. 19,1995
`
`Int. Cl.6
`[51]
`.................................. G06F 17/30
`[52] us. at. .....
`395/604;395l610; 395/602
`
`[53] Field of Seardl .........
`395/611. 609.
`3937604. 610. 602. 615
`
`[56]
`
`References Cited
`U.S. PATENT DOCUMENTS
`
`3/1985 Shaw et a1.
`4.506326
`....... 395/604
`
`5,175,814 12/1992 Anicketa]. .
`. 395/348
`
`5,201,046
`4/1993 Goldbergetal.
`. 395/611
`
`5,355,474 1011994 Thnraisngham
`395/609
`
`
`..........
`.. 395/610
`5,530,852
`6/1996 Meske, Jr. et a1,
`FOREIGN PATENT DOCUMENTS
`
`6/1992 EuropeanPat.0fi‘..
`0491517
`W092/16903 1011992 WIPO.
`
`OFHER PUBLICATIONS
`
`IBM Research Report; “Graqula: A Graphical Query Lan-
`guage for Entity—Relationship or Relational Databases";
`Gary H. Socknt et 31.; NY (US) Mar. 14, 1991.
`
`Jasis; “A Graphical Filter/Flow Representation of Boolean
`Queries: A Prototype Implementation and Evaluation"; Degi
`Young et at; vol. 44. #9; Jul. 1993.
`IBM Technical Disclosure Bulletin; “Dynamic. Interactive
`Show SQL Window"; K. R. Banning et 111.: vol. 35; No. 4A;
`Sep. 1992.
`13th International Conference on Research and Develop-
`ment in Information Retrieval; “A Direct Manipulatin Inter-
`face for Boolean Information Retrieval via Natural Lan-
`guage Query"; Peter G. Aniok et al.; Sep. 1990.
`IBM. “DB2 World Wide Web Connection”. interact. httpzl
`/www.software/ib...data. 1995.
`
`James Powell “World Wide Web Creating A Hypertext
`Library Information Systems". Database. pp. 59—66. 1994.
`Steven Baker. “Hypertext browsing on the Internet”. Unix
`Review v12 n9 p21(6). 1994.
`
`Primary Examiner—Thomas G. Black
`Assistant Examiner—Greta L. Robinson
`Attorney, Agent, or Firm—Merchant. Gould. Smith. Edell.
`Welter & Schmidt
`
`[57]
`
`ABSTRACT
`
`The present invention discloses a method and apparatus for
`executing SQL queries in a relational database management
`system via the World Wide Web of the Internet. In accor-
`dance with the present invention. Web users can request
`information from RDBMS software via HTML input forms.
`which request is then used to create an SQL statement for
`execution by the RDBMS software. The results output by
`the RDBMS software are themselves transformed into
`HTML format for presentation to the Web user.
`
`12 Claims, 6 Drawing Sheets
`
`20
`
`PROCEDURE
`
`
`FILES
`IHTML 4 Still
`
`
`Illlll
`
`FDHM HEUUEST
`FORM REDUEST
`
`
`
`
`11ml INPUT FORM
`
`
`WEE
`
`
`DBZ
`
`WEB
`SERVER
`
`
`
`WWW
`
`lHTTPD)
`BROWSER SUBMIT INPUT FORM
`
`
`HTML REPORT FURM
`
`
`SUBMIT INPUT FORM
`
`HTML REPURT FDRM
`
`l Sill [lUERY RESULTS
`
`
`COMMON
`DYNAMIC Sill
`
`HIM
`GATEWAY
`INTERFACE (CGI)
`
`Unified Patents |nc., Ex. 1007, pg. 1
`
`Unified Patents Inc., Ex. 1007, pg. 1
`
`

`

`US. Patent
`
`Apr. 7, 1998
`
`Sheet 1 of 6
`
`5,737,592
`
`>>>>>>Nm‘o
`
`><>>m:,<0
`
`>£E<ummom_-_
`
`
`
`nil...-mszEdmagma
`
` ZOmmmmgommmmts
`
`N.mE
`
`Unified Patents |nc., Ex. 1007, pg. 2
`
`Unified Patents Inc., Ex. 1007, pg. 2
`
`
`

`

`US. Patent
`
`Apr. 7, 1998
`
`Sheet 2 of 6
`
`5,737,592
`
`$5835
`
`m3:
`
`Souls—C:
`
`a3E
`
`:25 53.6mm
`
`SEE5&2.
`
`5E9—
`
`559ESEE
`
`
`
`sEE5&2.‘52.:
`
`mm;
`
`22:aE;
`.2
`
`
`3.53m>525Smm8ESE._s_.:._5E9—Eamwm:25.
`
`
`8&2:EmESn—zfrgmamEmgamm
`
`
`
`
`
`Ema”..252..533
`
`
`|.Ill><3wh<m[III
`Em252233%A2535:
`
`
`
`:38qumEE
`
`
`
`Unified Patents |nc., Ex. 1007, pg. 3
`
`Unified Patents Inc., Ex. 1007, pg. 3
`
`
`
`
`

`

`US. Patent
`
`Apr. 7, 1998
`
`Sheet 3 of 6
`
`5,737,592
`
`_
`
`_
`
`n
`
`_____n
`
`
` __nneES58:.n_.2:38.__n25:58.___23:25.“nnann_may:“gaze225:E;__I__Jillian:
`IIIIIIIIIIIIIIIIIIIIIIIIIIIIII4nn_5:23“
`zotgznac.___rllllllllllllllllllllllllllllllllllllllll_
`.sEEEB_“EaE;n_:3;25m;__a_uaIIn“EmEzomSZm".Emzmodin
`
`_ngtfiz“.1.....................................J.u"$82$553w
`
`u22333a33
`
`
`
`233.5E>Ew
`
`ME;2::Em:
`
`EmzzoEEm
`
`N—
`
`mm;
`
`593%
`
`Unified Patents |nc., Ex. 1007, pg. 4
`
`Unified Patents Inc., Ex. 1007, pg. 4
`
`
`
`
`

`

`US. Patent
`
`Apr. 7, 1998
`
`Sheet 4 of 6
`
`5,737,592
`
`Fig.4
`
`CREATE MACRO
`LANGUAGE FILE
`
`26
`
`
`
`28
`
`DEFINE VARIABLES
`IN THE FILE
`
`
`
`
`
`
`
`INSERT VARIABLES
`
`IN 30L AND HTML
`
`
`‘ USING A CUMMDN
`NAME SPACE
`
`3D
`
`
`
`32
`
`
`
`STORE MACRO
`LANGUAGE FILE
`
`
`
`Unified Patents |nc., Ex. 1007, pg. 5
`
`Unified Patents Inc., Ex. 1007, pg. 5
`
`

`

`US. Patent
`
`Apr. 7, 1998
`
`Sheet 5 of 6
`
`5,737,592
`
`Fig.5
`
`READ MACRO
`LANGUAGE FILE
`
`36
`
`
`
`
`
`
`LOOP:
`VARIABLES IN
`HTML AND SOL
`
`STATEMENTS?
`
`
`
`38
`
`LOOK UP VARIABLE
`
`DEFINITIONS IN
`
`SUBSTITUTION TABLE
`
`
`VARIABLE PRESENT '
`IN TABLE?
`
`40
`
`
`
`
`SUBSTITUTE TABLE
`
`VALUES INTI] THE
`
`SOL 0R HTML
`
`STATEMENTS
`
`RECURSIVELY
`
`
`42
`
`Unified Patents |nc., Ex. 1007, pg. 6
`
`Unified Patents Inc., Ex. 1007, pg. 6
`
`

`

`US. Patent
`
`Apr. 7, 1998
`
`Sheet 6 of 6
`
`5,737,592
`
`F196
`
`RETRIEVE MACRO
`LANGUAGE FILE
`
`PERFORM THE
`SQL STATEMENTS
`TO GENERATE AN
`OUTPUT TABLE
`
`NAME SPACE
`
`MAP OUTPUT
`TABLE TNTO
`HTML USING
`
`THE COMMON
`
`Unified Patents |nc., Ex. 1007, pg. 7
`
`Unified Patents Inc., Ex. 1007, pg. 7
`
`

`

`5,737,592
`
`1
`ACCESSING A RELATIONAL DATABASE
`OVER THE INTERNET USING MACRO
`LANGUAGE FILES
`
`BACKGROUND OF THE INVENTION
`1. Field of the Invention
`
`This invention relates in general to database management
`systems performed by computers. and in particular. to a
`method and apparatus for accessing a relational database
`over the Internet using macro language files.
`2. Description of Related Art
`With the fast growing popularity of the Internet and the
`World Wide Web (also known as “WWW“ or the “Web”),
`there is also a fast growing demand for Web access to
`databases. However. it is especially difficult to use relational
`database management system (RDBMS) software with the
`Web. One of the problems with using RDBMS software on
`the Web is the lack of correspondence between the protocols
`used to communicate in the Web with the protocols used to
`communicate with RDBMS software.
`
`the Web operates using the Hyper’I‘ext
`For example.
`Transfer Protocol (HTTP) and the HyperText Markup Lan-
`guage (HTML). This protocol and language results in the
`communication and display of graphical information that
`incorporates hyperlinks. Hyperlinks are network addresses
`that are embedded in a word. phrase, icon or picture that are
`activated when the user selects a highlighted item displayed
`in the graphical information. H'ITP is the protocol used by
`Web clients and Web servers to communicate between
`themselves using these hyperlinks. HTML is the language
`used by Web servers to create and connect together docu-
`ments that contain these hyperlinks.
`In contrast. most RDBMS sofiware uses a Structured
`Query Language (SQL) interface. The SQL interface has
`evolved into a standard language for RDBMS software and
`has been adopted as such by both the American Nationals
`Standard Organization (ANSI) and the International Stan-
`dards Organization (ISO).
`Thus. there is a need in the an for methods of accessing
`RDBMS software across the Internet network, and espe-
`cially via the World Wide Web. Further. thae is a need for
`simplified development environments for such systems.
`SUMMARY OF THE INVENTION
`
`To overcome the limitations in the prior art described
`above. and to overcome other limitations that will become
`apparent upon reading and understanding the present
`specification. the present invention discloses a method and
`apparatus for executing SQL queries in a relational database
`management system via the Intm‘net. In accordance with the
`present invention. Web users can request information from
`RDBMS software via HTML input forms. which request is
`then used to create an SQL statement for execution by the
`RDBMS software. The results output by the RDBMS soft-
`ware are themselves transformed into HTML format for
`presentation to the Web user.
`BRIEF DESCRIPTION OF THE DRAWINGS
`
`Referring now to the drawings in which like reference
`numbers represent corresponding parts throughout:
`FIG. 1 schematically illustrates the hardware environment
`of the preferred embodiment of the present invention;
`FIG. 2 shows schematically an overview of the preferred
`embodiment of the present invention, and in particular.
`shows the interaction among components in the present
`invention;
`
`2
`FIG. 3 shows schematically an overview of the preferred
`embodiment of the present invention. and in particular.
`shows the relationship between the user runtime environ-
`ment and the application development environment of the
`present invention;
`FIG. 4 is a flowchart illustrating the steps involved in
`creating macro language files according to the present
`invention;
`FIG. 5 is a flowchart illustrating the steps involved in
`using macro language files to provide access to a relational
`database according to the present invention; and
`FIG. 6 is a flowchart illustrating the steps involved in
`using a macro language file to generate a dynamic SQL
`query and then merging the results of that query into an
`HTML report form.
`
`DETAILED DESCRIPTION OF THE
`PREFERRED EMBODIMENT
`
`In the following description of the preferred embodiment.
`reference is made to the accompanying drawings which
`form a part hereof. and in which is shown by way of
`illustration :1 specific embodiment in which the invention
`may be practiced. It is to be understood that other embodi-
`ments may be utilized and structural and functional changes
`may be made without departing from the scope of the
`present invention.
`
`0V ERV IEW
`
`With the fast growing popularity of the Internet and the
`World Wide Web (also lmown as “W’ or me “Web").
`there is also an increasing demand for Web access to
`relational databases. The present invention is a DB2 WWW
`gateway that facilitates communication between Web
`clients. Web servers. and servers executing RDBMS soft-
`ware such as IBM’s DB2 family of products. The DB2
`WWW gateway enables an application developer to build
`Web applications for the RDBMS software using HTML
`fonns and dynamic SQL.
`An end user of these applications sees only the input
`forms for his/her requests and the resulting reports. Users fill
`out the input forms. point and click to navigate the forms,
`and to access the RDBMS software and relational database.
`A complete SQL command is dynamically built by the DB2
`WWW gateway with the user inputs and sent to the server
`executing the RDBMS software. The SQL command is
`performed by the RDBMS software. and the resulting output
`is merged into the HTML forms by the DB2 WWW gateway
`for presentation to the user.
`An application developer creates HTML forms and SQL
`queries and stores them in macro language files at the
`computer executing the DB2 WWW gateway. The macro
`language provides “cross-language variable substitution".
`thus enabling the application developer to use the full
`capabilities of: (a) HTML for creation of query or input
`forms and report forms. and (b) SQL for queries and
`updates. Since the DB2 WWW gateway uses native HTML
`and SQL languages. and not some new or hybrid language.
`van‘ous oE-the—shelf tools may be used for creation of
`HTML forms and for generation of the SQL query.
`HARDWARE MONMENT
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`45
`
`50
`
`55
`
`65
`
`FIG. 1 schematically illustrates the hardware environment
`of the preferred embodiment of the present invention. and
`more partiallarly, illustrates a typical distributed computer
`system using the Internet 10 to connect client systems 12
`
`Unified Patents |nc., Ex. 1007, pg. 8
`
`Unified Patents Inc., Ex. 1007, pg. 8
`
`

`

`5.737.592
`
`3
`executing Web browsers to server systems 14 executing Web
`daemons. to connect the server systems 14 executing Web
`daemons to server systems 16 executing the D132 WWW
`gateway. and to connect the server systems 16 executing
`DB2 WWW gateways to server systems 18 executing the
`RDBMS software. A typical combination of resources may
`include clients 12 that are personal computers or
`workstations. and servers 14. 16. and 18 that are personal
`computers. workstations. minicomputers. or mainframes.
`These systems are coupled to one another by various
`networks. including LANs. WANs. SNA networks. and the
`Internet.
`
`A client system 12 typically executes a Web browser and
`is coupled to a server computer 14 executing a Web server.
`The Web browser is typically a program such as IBM’s Web
`Explorer. or NetScape or Mosaic. The Web server 14 is
`typically a program such as IBM’s HTTP Daemon or other
`WWW daemon. The client computer 12 is bi—directionally
`coupled with the server computer 14 over a line or via a
`wireless system. In turn.
`the server computer 14 is
`bi-directionally coupled with a DB2 WWW server 16 over
`a line or via a wireless system. In addiu'on. the DB2 WWW
`server 16 is bidirectionally coupled with a RDBMS server
`18 over a line or via a wireless system.
`The DBZ WWW gateway 16 supports access to a server
`18 executing the RDBMS software. The DB2 WWW gate-
`way 16 and RDBMS server 18 may be located on the same
`server as the Web server 14. or they may be located on
`separate machines. The servers 18 executing the RDBMS
`software may be geographically distributed and may oom-
`prise difierent vendor systems. such as a DB2. ORACLE.
`SYBASE. etc.
`Those skilledin the art will recognize many modifications
`may be made to this configuration without departing from
`the scope of the present invention. For example. in a two-tier
`configuration. the server system executing the functions of
`the DB2 WWW gateway 16 may also execute the functions
`of the Web server 14 and/or the RDBMS server 18.
`Alternatively. in a three-tier configuration. the Web server
`14. DB2 WWW gateway 16. and RDBMS server 18 may all
`be performed by diEerent servers.
`DB2 WWW GATEWAY
`
`The DB2 WWW gateway 16 is designed to be suficiently
`flexible and powerful. yet be available on multiple
`platforms. such as 08/2. AIX. MVS. etc. Further. the DB2
`WWW gateway 16 is designed to work with existing Web
`and database application development tools. with minimal
`modifications required to such tools.
`These goals led also to the development of the macro
`language of the present invention. The macro language is a
`combination of HTML and SQL, and incorporates a “cross-
`language variable substitution” mechanism that allows input
`data from an HTML-format input form to be inserted in an
`SQL-format query for the RDBMS software. The “cross-
`language variable substitution” mechanism also allows SQL
`query results to be merged into HTML report forms.
`The runtime engine of the D132 WWW gateway 16 reads
`the macro language files to generate the appropriate query or
`input forms. SQL queries. and report forms. The use of
`native HTML and SQL. instead of a new or hybrid language.
`allows the full expressive power of these languages without
`artificial limitations. Both query and report forms can be laid
`out in any fashion.
`Moreover. existing tools may be used to generate the
`macro language files. For example. HTML editors from
`
`4
`various vendors can be used to generate the HTML forms
`component. while SQL query interfaces. such as IBM’s
`Visualizer Query. can be used to generate the SQL query
`component.
`
`INTERACTION AMONG COMPONENTS
`
`FIG. 2 shows schematically an overview of the preferred
`embodiment of the present invention. and in particular.
`shows the interaction among components in the present
`invention. The user interacts with the Web browser execut-
`ing on a client computer 12 remotely located from the Web
`server 14. At some point.
`the user executes an HTTP
`command via the Web browser 12 that results in commu-
`nication with an HTTP daemon executing on the Web server
`14. The Web server 14 would then transmit an initial or
`home page in HTML format to the Web browser 12 for
`presentation to the user. The DB2 WWW gateway 16 would
`be invoked by the user selecting a hyperlinked item from the
`home page. It is envisioned that the DBZ WWW gateway l6
`conforms to the Common Gateway Interface (CGI) defined
`for Web servers 14. and thus can be invoked from an HTML
`page in one of two ways: either by an HTTP anchor
`reference or by an HTTP form action.
`An HTTP anchor reference would typically be formatted
`as follows: “<A HREF=http:/I{web-server}/{ cgi—name}/
`{db2-www-gateway }/{macro-language-file}/{ command}
`[?variable-name=variable—value & .
`.
`. )>".
`An HTTP form action would typically be formatted in a
`similar manner as follows: “<FORM METHOD:
`{method}ACTION=http:/I{web-server}/{cgi—name}/{db2-
`WWW—gateway }/{macro-language-file}/{ command}
`[?variable-name=va1iable-value & .
`.
`. ]>".
`In both of the above examples. the following parameters
`are used:
`
`“{web—server}” identifies the Web server;
`“{cgi—name}” identifies the Common Gateway Interface
`(CGI) to the DB2 WWW gateway:
`“{de-www—gateway}" identifies the DB2 WWW gate-
`way;
`“{macro—language-file}” is the name of the macro lan-
`guage file to be executed;
`“{command}" is generally either “input" or “report”. If
`“input". then an HTML input form is displayed for the
`end user. If “report".
`then SQL commands in the
`{macro-language—file} are executed and an HTML
`report form is displayed for the end user.
`“{method}” is either “GET” or “POST”. as specified
`under the HTML standard;
`. 1" are optional
`.
`“[?variable-name=variable—value & .
`parameters that may be passed to the macro language
`file executed by the DB2 WWW gateway.
`At some point in the interaction between the Web browser
`12. the Web server 14. and the DBZ WWW gateway 16, the
`user would request data from a relational database managed
`by an RDBMS server 18. The DB2 WWW gateway 16
`would retrieve a macro language file 20. extract an HTML
`input form from the macro language file 20. and transmit the
`HTML input form to the Web server 14. The Web server 14
`transmits the HTML input form to the Web browser 12 for
`display to the user.
`The user manipulates the HTML input form via the Web
`browser 12, by selecting functions and/or entering data into
`input boxes. When the user invokes a “submit" or “post"
`command, the data from the HTML input form. along with
`the command. is transmitted from the Web browser 12 to the
`
`10
`
`15
`
`20
`
`35
`
`45
`
`50
`
`55
`
`65
`
`Unified Patents |nc., Ex. 1007, pg. 9
`
`Unified Patents Inc., Ex. 1007, pg. 9
`
`

`

`5,737,592
`
`6
`1. An SQL command section identified as follows: % SQL
`{sql command section %}
`2. An HTML input form section identified as follows: %
`HTML_INPUT {html input form section %}
`3. An HTML report form section identified as follows: %
`HTML_REPORI‘{html report form section %}
`The macro language contains directives. which are
`reserved keywords with a prefix symbol “%” (e.g.. % SQL).
`Many of the directives have both a single line form and a
`block form. which supports multiple lines of text. These
`block directives are terminated with “%}”. which can be
`anywhere on a line. In the preferred embodiment. nestings of
`blocks are not allowed. with the exception of the % EXEC-
`SQL block for the report form as described below.
`SQL Directive
`The SQL directive has the following format:
`
`
`%SQL{ my-vnlid-sql-oommmdsm-l-line ‘11}
`95501,
`
`{a
`
`ny-valid-sql-commands-on—lmature-lines
`96}
`
`The SQL directive identifies one or more SQL commands
`for execution by the RDBMS software. These SQL com-
`mands are executed sequentially. and the query results. if
`any, from the SQL commands are returned to the user for
`display.
`HTML Input Form Directive
`The HTML input form directive has the following format:
`
`‘inIMPUT { my—valid~hnn1—text-on—l-line ‘70)
`%HTML_1NPUT
`
`{a
`
`ny—validhtml-text-ond-or—momlines
`95}
`
`The HTML input form directive contains the HTML com-
`mands comprising the input form used to accept input data
`from the user before generating the SQL query. The HTML
`input form section is needed only when user inputis required
`to complete the SQL query.
`HTML Report Form Directive
`The HTML report form directive has the following for
`mat:
`
`
`%HM_REPOKI’{ uny-vnlk‘l-hhnl-text-m-l-lines %}
`%HTML_REPOR1“
`
`ny-vaiid-html-text-on- l-or-more—lines
`%EXECSQL { ‘17}
`any-valid-hnnl-text-m-l-or-mcre-lines
`
`‘17} '
`
`{a
`
`The HTML report form directive contains the HTML com-
`mands comprising the report form used to format query
`results for display to the user. The % EXIKISQL sub-
`directive contains SQL commands to execute. as well as the
`query result variables and how they are to be formatted in
`the report. Any HTML commands before and after the %
`EXECSQL block may contain hyperlinks to other HTML
`pages or macro language files. Note. too. that if the %
`HTML_REPORT directive is missing. then a default table
`format is used to print out the query results.
`
`Unified Patents |nc., Ex. 1007, pg. 10
`
`5
`Web server 14. The command would comprise a universal
`resource locator (URL)
`that invokes communications
`between the Web server 14 and the DB2 WWW gateway 16.
`The DB2 WWW gateway 16 extracts the user inputs from
`the HTML input form. retrieves the associated macro lan-
`guage file 20. and substitutes these inputs into an SQL query
`from the macro language file 20. The DB2 WWW gateway
`16 then transmits the SQL query to the RDBMS server 18
`for execution. After performing an SQL query. the RDBMS
`server 18 returns the results of the SQL query. i.e.. an output
`table. to the DB2 WWW gateway 16. The DB2 WWW
`gateway 16 extracts data from the output table and substi-
`tutes it into an HTML report form from the macro language
`file 20 using a common name space. A common name space
`arises from the use of common variables in both the HTML
`and SQL statements in the macro language file. and the
`cross-language variable substitution mechanism of the
`present invention. The resulting HTML report form is trans-
`mitted by the DB2 WWW gateway 16 to the Web server 14.
`The Web server 14 transmits the HTML report form to the
`Web browser 12 for presentation to the user. This interaction
`between the Web browser 12. the Web server 14. the DB2
`WWW gateway 16. and the RDBMS server 18 may continue
`in a similar manner according to the user’s direction.
`DEVELOPMENT ENVIRONMENT
`
`FIG. 3 shows schematically an overview of the preferred
`embodiment of the present invention. and in particular.
`shows the relationship between the user runtirne environ—
`ment and the application development environment of the
`present invention.
`As mentioned earlier. the runtime environment includes
`the interaction between clients 12 executing Web browsers
`and Web servers 14. DB2 WWW gateways 16. and RDBMS
`servers 18. Access to the RDBMS server 18 and associated
`relational database via the DB2 WWW gateway 16 is
`controlled by mogramming stored in macro language files
`20.
`
`According to the present invention. the development of
`Web applications for accessing relational databases typically
`involves the following steps:
`1. Create an HTML input form for display to the user;
`2. Extract user inputs from HTML inth forms and
`generate the appropriate SQL query via a common
`name space using a variable substitution mechanism;
`3. Merge the SQL query results into an HTML report
`form; and
`4. Allow for additional queries and HTMLforms. possibly
`from the hyperlinks embedded in the HTML report
`forms.
`The key challenge in writing applications for the DB2
`WWW gateway is to understand both HTML and SQL
`languages. since these languages are embedded inside the
`macro language files. In its simplest forms. basic knowledge
`of SQL and HTML can be easily acquired. However. these
`languages can be quite complex and tedious to write in orda
`to utilize their advanced functions. Fortunately. there are
`existing HTML editors 22 and SQL query tools 24 that can
`help to greatly reduce the complexity of writing the macro
`language files. Using HTML editors 22 and SQL query tools
`24. the application developer creates the macro language
`files 20. wherein each of the macro language files 20
`containing SQL commands and the associated query and
`report forms in HTML format
`MACRO LANGUAGE DEFINITIONS
`
`According to the preferred embodiment of the present
`invention. each macro language file typically contains three
`sections:
`
`5
`
`10
`
`15
`
`25
`
`35
`
`45
`
`55
`
`65
`
`Unified Patents Inc., Ex. 1007, pg. 10
`
`

`

`5,737,592
`
`8
`I. Input data from the HTML input or query forms to be
`inserted into the SQL statements; and
`2. SQL query results to be merged into HTML report
`forms.
`The macro language allows variables to be defined in one
`of several ways:
`1. DEFINE declaration. For example: % DEFINE
`varname="value-string”
`2. HTML form’s <SELECT> and <INPUT> statements.
`For example: <lNPUT NAME=“varname1”> or
`(SELECT NAME=“varnarne2">. These variables are
`set by user inputs or preset by hidden fields in the
`HTML forms.
`
`3. Query result variables are automatically set by the DB2
`WWW gateway with the values from the SQL query
`results.
`‘
`Variables are referenced in the macro language by using
`the syntax “$(variable)”. whaein “variable” is the variable
`name. Variable references may appear in either SQL or
`HTML sections and nested variable references are allowed
`However. at the time of its use. all variables must be
`de-referenced to their final values. Variables that have not
`been defined before use will be treated as having a value
`equal to an empty or null string.
`As previously described. a variable may contain other
`variables (e.g.. % DEFINE varx=“.
`.
`. $ (var2) .
`.
`. ").
`Variables are de-referenced (substituted with their values) in
`an HTML section. where the values of these variables need
`to be printed out either for the HTML input form or the
`HTML report form. However. variables are not
`de-referenced at the time of their use in a % DEFINE
`section.
`Consider the examples below:
`% DEFINE X=“One$(Y)$(Z)”
`% DEFINE Y=“"I\vo”
`
`HTML_INPUT {$(X) %}
`% DEFINE Z=‘Three"
`Variable X contains references to variables Y and Z. When
`the HTML input section is processed, Y is already defined.
`but Z is still undefined and is equivalent to NULL. Thus.
`$(X) is substituted with “One Two".
`In addition. a “hidden variables” mechanism is available
`for the application developer to hide database internal struc—
`ture (e.g.. table and column names) and other sensitive
`information from the end user (because end users can view
`HTML source via their Web Browser). This is achieved by
`the following process:
`1. Define a variable for each string that should be hidden.
`and put the DEFINE section for these variables after
`the HTML section where the variables are referenced.
`but before the SQL section where they are typically
`used.
`2. In the HTML form section where the variables are
`referenced. use double dollar instead of single dollar to
`reference the variables (e.g. $(X) instead of $00).
`Consider the following example:
`
`%HTML_INPUT
`FORM...>
`Please select field to view:
`<SELECI' NAME=“Field”>
`<0FI'ION VALUE=“$$(name)”> Name
`<OPTION VALE=“$$(addr)”> Address
`
`{<
`
`Unified Patents |nc., Ex. 1007, pg. 11
`
`10
`
`15
`
`7
`
`Comment Directive
`The HTML comment directive has the following format:
`——_—.————
`%{ any-text-on—l-line %}
`
`01'
`
`%{
`any-text-on-l acme-lines
`95}
`_—_——_-—
`
`The comment directive contains the comments to be inserted
`anywhere in the macro language file. so long as it is not
`nested inside another component block. The terminating
`symbol “%" is required for terminating cements on a
`single line.
`DEFINE Directive
`The DEFINE directive has the following format:
`———_————
`%DE7ENE define-statement
`
`%DEFI'NE
`
`{d
`
`efine-statement— l
`define-statement-Z
`
`or
`
`at
`
`The DEFINE directive defines the variables used in the
`macro language file. The “define-statement" above may be
`one of the following:
`1. A simple variable assignment: varname=“string—value"
`2. A conditional variable assignment: varname:
`vamame2?“value1”:“value2”
`3. A list variable declaration: % LIST ‘Walue-separator”
`varname
`The DEFINE directive defines the value strings that are to be
`substituted for the specified variables. When referenced. a
`simple variable is always substituted with the value string.
`A conditional variable is substituted with the first value
`string. if the tested variable name exists and is not null. or
`it is substituted with the second value string. A list variable
`is declared in the DEFINE section with the list property,
`wherein multiple value strings assigned to the list variable
`are concatenated together with the value-separator inbe—
`tween. The conditional and list variables are typically used
`together to construct portions of the SQL clause based on
`user inputs and selections from the HTML forms. The
`value-strings may contain other variables. which are
`de-referenced when used.
`Variables may contain other variables. For example:
`% DEFINE varl=“$(varZ).abc"
`is permitted. However. circular references (i.e.. cycles) are
`not allowed. For example. the DEFINE declarations below
`are not allowed:
`% DEFINE a=“$(b)”
`% DEFINE b=“$(a)”
`A quote character (“) may be included in a value string by
`using two consecutive quotes (“"). Further. an empty string
`with two consecutive quotes is equivalent to a NULL string.
`For example. the DEFINE sections:
`% DEFINE x=“say ““hello’"'”
`% DEFINE y=“”
`result in the variable x with the value ‘say “hello" and
`variable y with the value NULL. By definition. undefined
`variables are equivalent to NULL variables.
`Variable Substitution
`The “cross-language variable substitution" mechanism of
`the present invention is a key feature of the macro language.
`which allows:
`
`35
`
`45
`
`50
`
`55
`
`65
`
`Unified Patents Inc., Ex. 1007, pg. 11
`
`

`

`5,737,592
`
`10
`the variable substitution table. If not. no substitution occurs
`and control transfers to block 34 to read additional records
`from the macro language file. Otherwise. control transfers to
`block 42. which represents the D82 WWW gateway 16
`substituting the table values for the variable into the SQL or
`HTML statements in a recursive manner.
`FIG. 6 is a flowchart illustrating the steps involved in
`using a macro language file to generate a dynamic SQL
`query and then merging the results of that query into an
`HTML report form Block 44 represents the DB2 WWW
`gateway 16 retrieving the indicated maao language file.
`extracting the SQL statements therefrom. and performing
`the necessary variable substitution using the input data from
`the user. Block 46 represents the D112 WWW gateway 16
`transmitting the SQL statement to the RDBMS server 18 and
`the RDBMS server 18 performing the SQL statement to
`generate an output table. The output table is then returned to
`the DBZ WWW gateway l6 processing. Block 48 represents
`the DB2 WWW gateway 16 mapping the output table into
`the HTML report form from the macro language file using
`the common name space. Again. the common name space
`indicates that variables may be embedded in the HTML
`report form that allows crossdanguage variable substitution
`to occur. so that the output table is oorrecdy merged into the
`HTML report form. Thereafter. the DB2 WWW gateway 16
`transmits the HTML report form to the Web server 14 and
`then on to the Web browser 12 for presentation to the user.
`Example Macro Language Files
`The use of the macro language and variable substitution
`mechanism of the present invention are best illustrated by
`the examples set forth below. In these examples. the macro
`language files have an extension “.d2w". The DB2 WWW
`gateway looks for file names with this extension when
`opening macro language files for processing.
`Example 1: HTML-to-SQL Substitution
`Consider the following macro language file “examplel”
`using Hl‘ML to SQL variable substitution:
`
`%HTML_IN'PUT
`
`{<
`
`FORM METHOD = “post” ACTION = http:/I ibrneom
`/ db2www / examplel I report”>
`Please select one or more fields:
`<SELECT NAME=“Fields" MUL'I'JPIE SIZE=4>
`<OFI'ION> Name
`<0PTION> Address
`<OPTION> Phone
`<0FIION> Fax
`<fSEIJiC1>
`<INPUT TYPE=“submir" Value=“Submit Query">
`<IPORM>
`95:}
`%DEFINE DATABASE=“CUSTOMERDB"
`%SQL SEIECI‘ $(Fields) FROM Customer’lbl
`«summon
`
`iI
`
`nformation Reqweted:
`%EXECSQL{ 96}
`<A]-IREF=". . .">Return to Homepaged»
`95}
`
`The macro language file above may first be invoked by the
`URL ‘han/ibmcom/db2www/example llinput”. which is
`embedded as an anchor reference in an HTML home page.
`When the end user clicks on the anchor item. the DB2
`WWW gateway is activated. and the user is presented with
`the HTML input form.
`The macro language file above allows the user to select a
`field from the customer table. Multiple fields. such as
`“Name" and “Phone”. may be selected When the user clicks
`
`Unified Patents |nc., Ex. 1007, pg. 12
`
`10
`
`15
`
`20
`
`25
`
`30
`
`35
`
`45
`
`55
`
` 9
`
`-coutinued
`
`Quorum
`95}
`%DEFJNE{
`name = “customername”
`adrk = “custorneraddress”
`%}
`%SQL SELECT $(Field) FROM customer
`
`
`When the HTML input section is processed to return a form
`back to the user, $$(name) and $$(addr) are replaced with
`$(name) and $(addr). Hence.
`the real table and column
`values never appear on the HTML form. When the user
`submits the input form. the SQL sec