System and method for generating reusable database queries

• US 6,285,998 B1
• Filed: 02/23/1999
• Issued: 09/04/2001
• Est. Priority Date: 02/23/1999
• Status: Expired due to Term

• Current Assignee: Microsoft Technology Licensing LLC (Microsoft Corporation)
• Original Assignee: Microsoft Corporation
• Inventors: Black, Neil W., Kennamer, Walter, Morrow, Dan
• Primary Examiner(s): Choules, Jack
• Assistant Examiner(s): LEWIS, CHERYL RENEA
• Application Number:
  US09/255,741
• Time in Patent Office: 924 Days
• Field of Search: 705/1, 705/35, 707/1, 707/2, 707/3, 707/4, 707/503, 707/504, 707/10, 345/326
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2423 Interactive query statement specification based on a database schema
• CPC Class Codes: G06Q 40/00 Finance; Insurance; Tax strategies; Processing of corporate or income taxes
• CPC Class Codes: Y10S 707/99931 Database or file accessing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation

Abstract

An improved user interface and query method permits a computer user to quickly specify field names, operators, and desired values for a database query. One or more of the desired values can be specified as an “ask me” value that will cause the user to be prompted (each time the query is to be executed) for a desired value prior to executing the query. A “wizard-like” set of dialog boxes guides the user through prompts to supply values for all such “ask me” values in a compound query. Rather than presenting the user with arcane logical operators and a formalistic query language, logical operators are converted into ordinary English phrases. The user can save the query for later re-use or modification. Another feature prompts the user with a constrained list of possible values that a field can assume, forcing the user to select from among the constrained values rather than requiring the entry of a discrete value. When the user specifies an “ask me” value for one of these fields, the constrained list is presented to the user before the query is executed. The invention can be implemented as an ActiveX control that executes in a web browser on a client computer.

Claims

1. A computer-implemented method of creating and executing a database query, comprising the steps of:

(1) specifying on a computer display a plurality of database query criteria, each criterion comprising a field identifier, an operator, and a desired value, wherein one or more of the desired values are specified as an “

ask me”

value that requires user input at query execution time;

(2) upon detecting that a database query is to be executed, prompting a user to supply a value for all “

ask me”

values present in the plurality of database query criteria;

(3) constructing a query string comprising the specified plurality of database query criteria and any values supplied in step (2); and

(4) executing the database query using the constructed query string.

2. The method of claim 1, wherein step (1) comprises the steps of:

(a) displaying a query grid comprising a plurality of cells arranged into field names, operators, and desired values; and

(b) displaying a context-sensitive menu in each cell upon detecting that each cell has been selected, wherein the context-sensitive menu provides choices that can be specified for that cell.

3. The method of claim 1, wherein step (2) comprises the step of displaying a dialog box comprising a question that prompts the user, a data entry area, and a display element that advances to a next dialog box that prompts the user for a next “

ask me”

value.

4. The method of claim 1, wherein step (2) comprises the step of displaying a dialog box comprising a question that prompts the user;

a constrained list of choices from which the user must select one; and

a display element that advances to a next dialog box that prompts the user for a next “

ask me”

value.

5. The method of claim 4, further comprising the step of displaying a last dialog box comprising a “

finish”

display element which, when selected, causes the query to be executed.

6. The method of claim 1, further comprising the step of storing the plurality of database query criteria specified in step (1) under a user-selected query name.

7. The method of claim 1, wherein steps (1) through (3) are executed on a client computer using a web browser, and wherein step (5) is executed on a server computer that contains a database.

8. A computer-implemented method of querying a database, comprising the steps of:

(1) displaying on a computer screen a query grid comprising a plurality of cells that define a database query, the cells including a first group of cells each of which permits a user to specify a database field;

a second group of cells each of which permits the user to specify an operator to be applied to a corresponding one of the database fields; and

a third group of cells each of which permits the user to specify a desired value for one of the database fields specified in the first group of cells;

(2) specifying a database field for each of the first group of cells;

(3) specifying an operator for each of the second group of cells;

(5) specifying an “

ask me”

value for at least one of the third group of cells, wherein the “

ask me”

value causes the user to be prompted for a value just prior to the time that the query is executed; and

(5) executing the database query by prompting the user to supply a value for any cell for which an “

ask me”

value was specified in step (5), adding any such prompted values to the previously specified fields and operators, and submitting the resulting query to a database engine.

9. The method of claim 8, wherein step (5) comprises the step of prompting the user for a numeric value.

10. The method of claim 8, wherein step (5) comprises the step of prompting the user to select a value from a list of choices, wherein the list corresponds to one of the database fields.

11. The method of claim 8, wherein step (5) comprises the step of displaying a dialog box comprising a simple question to prompt the user;

a data entry area for specifying a value, and a “

next”

display element that advances to another dialog box to prompt the user for another “

ask me”

value.

12. The method of claim 8, wherein step (5) comprises the step of sequentially displaying multiple dialog boxes each comprising a simple context-sensitive question to prompt the user;

a data entry area for specifying a value; and

a “

next”

display element that advances to a next dialog box for specifying another “

ask me”

value.

13. The method of claim 8, further comprising the step of storing the database query on a computer for future use.

14. The method of claim 8, further comprising the steps of:

(6) specifying a database field as a comparison point;

(7) querying the database for results corresponding to the comparison point; and

(8) displaying the results of the database query from step (7) in close proximity to the database results obtained in step (5) in order to permit comparison by the user.

15. A system for creating and executing database queries, comprising:

a client computer including a web browser and a browser control element; and

a server computer coupled to the client computer over a network, the server computer including a query parser that receives a query string from the network and generates a database query, and a query engine that executes the database query;

wherein the browser control element generates a query grid display on the client computer comprising a plurality of cells arranged into fields, operators, and values; and

wherein the browser control element permits a user of the client computer to specify an “

ask me”

value for one or more of the cells and which, when query execution is requested, prompts the user to supply a value for each such “

ask me”

value, generates the query string including any such supplied values, and transmits the query string to the query parser on the server computer.

16. The system of claim 15, wherein the browser control element, in response to detecting selection of a cell that can take on only a constrained set of values, displays to the user a list of possible values for that cell.

17. The system of claim 15, wherein the browser control element prompts the user to supply a value for each such “

ask me”

value by displaying a dialog box comprising a question phrase, a data entry area, and a “

next”

display element that causes a next dialog box to be displayed for entry of a next “

ask me”

value.

18. The system of claim 17, wherein the browser control element, when displaying a final dialog box corresponding to the last “

ask me”

value for the cells, displays a “

finish”

display element which causes the query to be executed.

19. The system of claim 15, wherein the browser control element further displays a comparison pane that permits comparison of database query results with a user-specified record separately queried from the database.

20. The system of claim 15, wherein the browser control element permits a user of the client computer to specify an “

ask me”

value of “

All”

for one or more of the cells, which causes the database to not restrict its results as to that value.

21. The system of claim 15, wherein the browser control element prompts the user to supply the value by displaying a simple phrase that incorporates a field name and operator without using algebraic syntax, and wherein the browser control element provides information explaining what the field name represents.

22. The system of claim 15, wherein the cells are further arranged into rows each corresponding to a discrete query criterion, and wherein the browser control generates the query string by logically “

ANDing”

each row to form a compound query.