Accessing a relational database over the Internet using macro language files5737592Abstract 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 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 software are themselves transformed into HTML format for presentation to the Web user. Claims What is claimed is: Description BACKGROUND OF THE INVENTION
______________________________________
%SQL { any-valid-sql-commands-on-1-line %}
or
%SQL
{
any-valid-sql-commands-on-1-or-more-lines
%}
______________________________________
The SQL directive identifies one or more SQL commands for execution by the RDBMS software. These SQL commands 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:
______________________________________
%HTML.sub.-- INPUT { any-valid-html-text-on-1-line %}
or
%HTML.sub.-- INPUT
{
any-valid-html-text-on-1-or-more-lines
%}
______________________________________
The HTML input form directive contains the HTML commands 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 input is required to complete the SQL query. HTML Report Form Directive The HTML report form directive has the following format:
______________________________________
%HTML.sub.-- REPORT{ any-valid-html-text-on-1-lines %}
or
%HTML.sub.-- REPORT
{
any-valid-html-text-on-1-or-more-lines
%EXECSQL { %}
any-valid-html-text-on-1-or-more-lines
%}
______________________________________
The HTML report form directive contains the HTML commands comprising the report form used to format query results for display to the user. The % EXECSQL 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.sub.-- REPORT directive is missing, then a default table format is used to print out the query results. Comment Directive The HTML comment directive has the following format:
______________________________________
%{ any-text-on-1-line %}
or
%{
any-text-on-1-or-more-lines
%}
______________________________________
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 comments on a single line. DEFINE Directive The DEFINE directive has the following format:
______________________________________
%DEFINE define-statement
or
%DEFINE
{
define-statement-1
define-statement-2
. . .
%}
______________________________________
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=varname2?"value1":"value2" 3. A list variable declaration: % LIST "value-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 inbetween. 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 var1="$(var2).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: 1. 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: <INPUT NAME="varname1"> or <SELECT NAME="varname2">. 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)", wherein "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="Two" HTML.sub.-- 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 structure (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 $(X)). Consider the following example:
______________________________________
%HTML.sub.-- INPUT
<FORM . . . >
Please select field to view:
<SELECT NAME="Field">
<OPTION VALUE="$$(name)"> Name
<OPTION VALE="$$(addr)"> Address
. . .
</FORM>
%}
%DEFINE{
name = "customer.name"
addr = "customer.address"
%}
%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 section is processed and "$(Field)" is properly substituted with "customer.name, customer, addr". There are also a number of "special system variables" that have special meaning to the DB2 WWW gateway 16. These special variables may be defined by the application developer in the macro language file or by the user from an input form. The current special variables include: DATABASE: name of the database to be accessed; this variable must be defined. LOGIN: database login name. PASSWORD: database password associated with LOGIN name. SHOWSQL: flag to show the SQL command on the report form; default is NULL. In addition, predefined query report variables are available and currently include: $(FN1), $(FN2), . . . , $(FNn): field names for each of the columns defined in the table. $(FV1), $(FV2), . . . , $(FVn): row values for each of the columns defined in the table. $(NR): total number of rows or records retrieved by the query. USING THE MACRO LANGUAGE FILE FIG. 4 is a flowchart illustrating the steps involved in creating macro language files according to the present invention. Block 26 represents the creation of the macro language file. Block 28 represents the user defining variables in the macro language file using the % DEFINE directive. Block 30 represents the user inserting the variables into the SQL and HTML statements in the macro language file using a common name space, wherein a common name space indicates that the same variables may be used in either SQL or HTML statements in order to provide cross-language variable substitution capabilities. Block 32 represents the storing of the macro language file, typically on a disk drive or other peripheral attached to the DB2 WWW gateway 16. 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. Block 34 represents the DB2 WWW gateway 16 reading a macro language file in response to a command received from a Web browser 12 via a Web server 14. As each record is read from the macro language file, block 36 examines the record to determine whether or not a variable is found in the HTML or SQL statements stored in the macro language file. If so, control transfers to block 38, which looks up variable definitions in the variable substitution table maintained by the DB2 WWW gateway 16. Block 40 is a decision block that determines whether or not the variable can be found in 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 DB2 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 macro language file, extracting the SQL statements therefrom, and performing the necessary variable substitution using the input data from the user. Block 46 represents the DB2 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 DB2 WWW gateway 16 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 cross-language variable substitution to occur, so that the output table is correctly 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 "example1" using HTML to SQL variable substitution:
______________________________________
%HTML.sub.-- INPUT
<FORM METHOD = "post" ACTION = http:// ibm.com
/ db2www / example1 / report">
Please select one or more fields:
<SELECT NAME="Fields" MULTIPLE SIZE=4>
<OPTION> Name
<OPTION> Address
<OPTION> Phone
<OPTION> Fax
</SELECT>
<INPUT TYPE="submit" Value="Submit Query">
</FORM>
%}
%DEFINE DATABASE="CUSTOMERDB"
%SQL SELECT $(Fields) FROM CustomerTb1
%HTML.sub.-- REPORT
{
Information Requested:
%EXECSQL{ %}
<A HREF=". . .">Return to Homepage</A>
%}
______________________________________
The macro language file above may first be invoked by the URL "http://ibm.com/db2www/example1/input", 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 on the "›Submit Query!" button, the macro language file is again processed by the form action "http://ibm.com/db2www/example1/report". The user inputs obtained from the HTML <SELECT> statement are substituted into the $(Fields) in the SQL clause. The submission of the HTML input form returns the user-selected result in the form "Fields=Name & Fields=Phone". The variable "$(Fields)" in the SQL statement are replaced with "Name, Phone" (including the comma). When the HTML report section is processed, the HTML text proceeding the % EXECSQL is printed first, followed by a default query table report, and the HTML text after the report. The report is displayed as follows:
______________________________________
Information Requested:
Name Phone
______________________________________
Smith, John 415-555-1212
Doe, David 408-555-1212
›Return to Home Page!
______________________________________
Example 2: Conditional and List Variables Consider the following macro language file:
______________________________________
%HTML.sub.-- INPUT
Specify Search string patterns for
Name: <INPUT TYPE="text" NAME="SubString1">
Addr: <INPUT TYPE="text" NAME="SubString2">
%}
%DEFINE
{
%LIST "and" Condition
Condition = "Name LIKE ""$(SubString1)"""
Condition = "City LIKE ""$(SubString2)"""
WhereClause = Condition ? "WHERE $ (Condition)" : ""
%}
%DEFINE DATABASE="CUSTOMERDB"
%SQL
{
SELECT Name, City FROM CustomerTb1
$ (WhereClause)
%}
______________________________________
The HTML input form asks the users to type in the string patterns to match with the database fields Name and Addr. The user may type in one, two or no pattern at all. The variable condition is declared to be a list variable, and the variable WhereClause is conditionally assigned. Thus, $(WhereClause) will be substituted with one of the following values: 1. NULL (if the user leaves both inputs empty); 2. WHERE Name LIKE "S %" (if the user types in S % for Name and leave Addr empty); or 3. WHERE Name LIKE "S %" AND City LIKE "San Jose" (if the user types in S % for Name and San Jose for City). Example 3: HTML-to-SQL Substitution Consider the following macro language file "example3" using HTML to SQL variable substitution:
______________________________________
%DEFINE DATABASE="CUSTOMERDB"
%SQL SELECT $(Fields) FROM CustomerTb1
%HTML.sub.-- INPUT
<FORM METHOD = "post" ACTION = "http:// ibm.com /
db2www / example3 / report">
Please select one or more fields:
<SELECT NAME="Fields" MULTIPLE SIZE=4>
<OPTION> Name
<OPTION> Address
<OPTION> Phone
<OPTION> Fax
</SELECT>
<INPUT TYPE="submit" Value="Submit Query">
</FORM>
%}
______________________________________
The macro language file is invoked by the following URL: "http://ibm.com/db2www/example3/input", which is embedded as an anchor reference in an HTML home page. When the end-user clicks on the anchor, the DB2 WWW gateway is activated via the URL, and the user is presented with the HTML input form from the macro language file. The macro language file allows the user to select a field from the customer table file named "CustomerTb1". Multiple fields, such as "Name", "Address", "Phone", and "Fax" may be selected. The user inputs obtained from the HTML <SELECT> statement are substituted into the $(Fields) in the SQL clause. The HTML input form returns the user-selected result in a form such as "Fields=Name & Fields=Phone". The resulting $(Fields) in the SQL statement are replaced with "Name, Phone" (including the comma). Since the % HTML.sub.-- REPORT directive is missing from the macro language file, a default table output is displayed as follows (assuming the fields Name and Phone are selected):
______________________________________
Name Phone
______________________________________
Smith, John 415-555-1212
Doe, David 408-555-1212
. . .
______________________________________
Example 4: DEFINE Substitution Consider the following macro language file "example4" using alias substitution:
______________________________________
%DEFINE Tb1="CustomerTb1"
%DEFINE Htxt="<H1>Customer Report </H1>"
%DEFINE DATABASE="CUSTOMERDB"
%SQL SELECT Name FROM $(Tb1)
%HTML.sub.-- INPUT
$ (Htxt)
. . .
%}
______________________________________
In this macro language file, the variable $(Tb1) in the % SQL section is substituted with "CustomerTb1" and the variable $(Htxt) in the % HTML.sub.-- INPUT section is substituted with the text string "<H1>Customer Report </H1>". Example 5: HTML Report Form Substitution Consider the following macro language file using report form substitution:
______________________________________
%DEFINE DATABASE="CUSTOMERDB"
%SQL SELECT Name, Phone, Fax FROM CustomerTb1
%HTML.sub.-- REPORT
Address Query Result:"
%EXECSQL
{
$(FN1) : $(FV1)
Phone: $(FV2) Fax: $(FV3)
______________________________________
%}
Total records retrieved: $(NR)
%}
______________________________________
When invoked by the URL: "http://ibm.com/db2www/example5/report", the following report form will be displayed to the user:
______________________________________
Address Query Result:
Name: Smith, John
Phone: 415-555-1212 Fax: 415-555-1212
______________________________________
Name: Doe, David
Phone: 408-555-1212 Fax: 408-555-1212
______________________________________
Total records retrieved: 2
______________________________________
Example 6: Embedded Links to Other Macro Language Files Due to the flexible variable substitution mechanism of macro language items displayed in the report form may link to other URLs, which include other HTML forms, CGI programs, or other macro language files. Examples of such linkages are provided below in macro language file "example6a":
______________________________________
%DEFINE DATABASE="CUSTOMERDB"
%SQL
SELECT Name, Company FROM CustomerTb1
WHERE Name LIKE "$(custname)"
%}
%HTML.sub.-- INPUT
{
<FORM METHOD = "post" ACTION = "http:// ibm.com /
db2www / example6a /report">
Enter name of customer for search:
<INPUT Name="custname">
<INPUT Type="Submit" Value="Search Now">
</FORM>
%}
%DEFINE URLprefix="http://ibm.com/"
%HTML.sub.-- REPORT
{
Query Results. Please click on customer name to
get detailed description of customer information.
%EXECSQL
{
Name: <A HREF = "$(URLprefix) / db2www /
example6b / report / cust = $(V1)">
$ (FV1) </A>
Company: $(FV2)
%}
<A HREF="$ (URLprefix) /homepage.html>
Go to home page</A>
%}
______________________________________
When invoked by the URL: "http://ibm.com/db2www/example6a/input", the following input form will be displayed to the user:
______________________________________
Enter name of customer for search: .sub.--------------------
›Search Now!
______________________________________
The user enters the name of the customer for the query into the input form, and then selects the "›Search Now!" button. The input form invokes the DB2 WWW gateway with the URL: "http://ibm.com/db2www/example6a/report", and the following report form is displayed to the user:
______________________________________
Query Results. Please click on customer name to
get detailed description of customer information.
Name: ›John Smith!
Company: Mikrotuf Corporation
Name: ›David Doe!
Company: Orakel Limited
›Go to home page!
______________________________________
The text between the brackets are displayed as highlighted hyperlinks that may be selected by the user. For example, if the user selects "›Go to home page!", then he or she will jump to the HTML page "homepage.html". Alternatively, if the user selects "›John Smith!", then DB2 WWW gateway is invoked with the following URL: "http://ibm.com/db2www/example6b/report/›?cust=John Smith!", and the following macro language file is retrieved:
______________________________________
%DEFINE DATABASE="CUSTOMERDB"
%SQL
SELECT Name, Title,
Phone FROM CustomerTb1
WHERE Name LIKE "$(cust)"
%}
%HTML.sub.-- REPORT
{
Detailed customer information
______________________________________
%EXECSQL
Name = $(FV1)
Title = $(FV2)
Phone = $(FV3)
%}
%}
______________________________________
After the SQL query in the macro language file is executed, with "John Smith" replacing the "$(cust)" variable, then the HTML report form is displayed for the user:
______________________________________
Detailed customer information
______________________________________
Name = John Smith
Title = VP of Research and Development
Phone = 415-555-1212
______________________________________
CONCLUSION This concludes the description of the preferred embodiment of the invention. The following describes some alternative embodiments for accomplishing the present invention. For example, any type of computer, such as a mainframe, minicomputer, or personal computer, could be used with the present invention. In addition, any software program adhering (either partially or entirely) to the HTTP protocol or the HTML or SQL language standards could benefit from the present invention. In summary, 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 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 software are themselves transformed into HTML format for presentation to the Web user. The foregoing description of the preferred embodiment of the invention has been presented for the purposes of illustration and description. It is not intended to be exhaustive or to limit the invention to the precise form disclosed. Many modifications and variations are possible in light of the above teaching. It is intended that the scope of the invention be limited not by this detailed description, but rather by the claims appended hereto.
|
Same subclass Same class Consider this |
||||||||||