Method and apparatus for facilitating a database query using a query criteria template

• US 7,529,734 B2
• Filed: 11/12/2004
• Issued: 05/05/2009
• Est. Priority Date: 11/12/2004
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Dirisala, Siva K.
• Primary Examiner(s): Chace; Christian P.
• Assistant Examiner(s): Buckingham; Kellye
• Application Number:
  US10/986,638
• Publication Number:
  US 20060106763A1
• Time in Patent Office: 1,635 Days
• Field of Search: 707 3- 5
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99935 Query augmenting and refining, e.g. inexact access

Abstract

One embodiment of the present invention provides a system that facilitates user queries to a database. During operation, the system stores one or more query criteria templates in a template table in the database. Each record in the template table corresponds to a query criteria template. A query criteria template contains information on search-field names and search-field values for a query. The system also allows a user to select a template from the stored templates prior to performing a query, thereby reducing the number of search-field values the user has to manually enter. In this way, a number of query criteria templates can be stored in a single table with a column containing parsable query criteria.

Claims

1. A method for facilitating user queries to a database, comprising:

determining one or more search fields from query criteria generated by a user in a client-side application;

automatically generating one or more query criteria templates from the determined one or more search fields by

extracting search-field names, search-field data types, and search-field length from a set of user interface metadata;

obtaining search-field values from the one or more query criteria templates; and

representing the search-field names and search-field values in an XML format which can be stored in a column of the record corresponding to the one or more query criteria templates;

storing the one or more query criteria templates in a single template table in the database,

wherein the query template table comprises rows and columns,

wherein each row in the query template table corresponds to a query criteria template and comprises one or more of template name, user name, creation time, and flag type corresponding to a specific query criteria template record, and

wherein a query criteria template contains information on search-field names and search-field values for a query;

populating a query form with appropriate search-field values; and

allowing a user to select a template from the stored query criteria templates prior to performing a query, thereby reducing manual entry of search-field values for similar queries.

2. The method of claim 1, wherein storing the one or more query criteria templates in the template table involves storing a set of query criteria in a parsable format in a column, thereby avoiding storing search-field names and search-field values in separate records.

3. The method of claim 2, wherein storing the query criteria in a parsable format in the column involves storing the query criteria in an XML file as a character large object (CLOB) or a binary large object (BLOB).

4. The method of claim 1, wherein storing the one or more query criteria templates in the template table involves storing a template name, a user name, a template creation time, or a public/private flag in one or more columns within the record corresponding to the template.

5. The method of claim 1, further comprising:

receiving a query to retrieve one or more templates selected by the user;

retrieving a template selected by the user; and

communicating the retrieved template to a middle-tier server or a client, thereby allowing the template to be parsed.

7. The method of claim 1, wherein representing the search-field names and search-field values in a parsable format involves representing the extracted search-field names and search-field values in an XML file and storing the XML file as a CLOB or a BLOB.

8. The method of claim 1, further comprising allowing the user to specify a name for the automatically generated one or more query criteria templates and to specify the automatically generated one or more query criteria templates to be private or public;

wherein

a private template is only visible to the template'"'"'s creator; and

wherein a public template is visible to multiple users.

9. The method of claim 1, further comprising:

querying the database to retrieve a user-selected template;

receiving the user-selected template from the database;

parsing the received template to obtain a set of query criteria; and

populating a query form with search-field names and search-field values contained in the query criteria based on a set of user interface metadata.

6. A method for facilitating user queries to a database, comprising:

determining one or more search fields from query criteria generated by a user in a client-side application;

automatically generating one or more query criteria templates from the determined one or more search fields by

extracting search-field names, search-field data types, and search-field length from a set of user interface metadata;

obtaining search-field values from the one or more query criteria templates; and

representing the search-field names and search-field values in an XML format which can be stored in a column of the record corresponding to the one or more query criteria templates, wherein a query criteria template is a record which can be stored in a single template table,

wherein the query template table comprises rows and columns, and

wherein each row in the query template table corresponds to a query criteria template and comprises one or more of template name, user name, creation time, and flag type corresponding to a specific query criteria template record; and

communicating the one or more query criteria templates to a database where the one or more query criteria templates can be stored in a template table.

10. A computer-readable storage medium storing instructions that when executed by a computer cause the computer to perform a method for facilitating user queries to a database, the method comprising:

determining one or more search fields from query criteria generated by a user in a client-side application;

automatically generating one or more query criteria templates from the determined one or more search fields by

extracting search-field names, search-field data types, and search-field length from a set of user interface metadata;

obtaining search-field values from the query criteria; and

representing the search-field names and search-field values in XML format which can be stored in a column of the record corresponding to the one or more query criteria templates;

storing the one or more query criteria templates in a single template table in the database,

wherein the query template table comprises rows and columns,

wherein each row in the query template table corresponds to a query criteria template and comprises one or more of template name, user name, creation time, and flag type corresponding to a specific query criteria template record, and

wherein a query criteria template contains information on search-field names and search-field values for a query; and

allowing a user to select a template from the stored templates prior to performing a query, thereby reducing manual entry of search-field values for similar queries.

11. The computer-readable storage medium of claim 10, wherein storing the one or more query criteria templates in the template table involves storing a set of query criteria in a parsable format in a column, thereby avoiding storing search-field names and search-field values in separate records.

12. The computer-readable storage medium of claim 11, wherein storing the query criteria in a parsable format in the column involves storing the query criteria in an XML file as a character large object (CLOB) or a binary large object (BLOB).

13. The computer-readable storage medium of claim 10, wherein storing the one or more query criteria templates in the template table involves storing a template name, a user name, a template creation time, or a public/private flag in one or more columns within the record corresponding to the template.

14. The computer-readable storage medium of claim 10, wherein the method further comprises:

receiving a query to retrieve one or more templates selected by the user;

retrieving a template selected by the user; and

communicating the retrieved template to a middle-tier server or a client, thereby allowing the template to be parsed.

15. A computer-readable storage medium storing instructions that when executed by a computer cause the computer to perform a method for facilitating user queries to a database, the method comprising:

determining one or more search fields from query criteria generated by a user in a client-side application;

automatically generating one or more query criteria templates from the determined one or more search fields by

extracting search-field names, search-field data types, and search-field length from a set of user interface metadata;

obtaining search-field values from the query criteria; and

representing the search-field names and search-field values in XML format which can be stored in a column of the record corresponding to the one or more query criteria templates, wherein a query criteria template is a record which can be stored in a single template table;

wherein the query template table comprises rows and columns, and

wherein each row in the query template table corresponds to a query criteria template and comprises one or more of template name, user name, creation time, and flag type corresponding to a specific query criteria template record; and

communicating the one or more query criteria templates to a database where the one or more query criteria templates can be stored in a template table.

16. The computer-readable storage medium of claim 15, wherein representing the search-field names and search-field values in a parsable format involves representing the extracted search-field names and search-field values in an XML file and storing the XML file as a CLOB or a BLOB.

17. The computer-readable storage medium of claim 15, wherein the method further comprises allowing the user to specify a name for the automatically generated one or more query criteria templates and to specify the automatically generated one or more query criteria templates to be private or public;

wherein a private template is only visible to the template'"'"'s creator; and

wherein a public template is visible to multiple users.

18. The computer-readable storage medium of claim 15, wherein the method further comprises:

querying the database to retrieve a user-selected template;

receiving the user-selected template from the database;

parsing the received template to obtain a set of query criteria; and

populating a query form with search-field names and search-field values contained in the query criteria based on a set of user interface metadata.

19. An apparatus for facilitating user queries to a database, comprising:

a processor;

a memory;

a database;

a determining mechanism configured to determine one or more search fields from query criteria generated by a user in a client-side application;

a template-generating mechanism configured to automatically generate one or more query criteria templates from the determine one or more search fields by

extracting search-field names, search-field data types, and search-field length from a set of user interface metadata;

obtaining search-field values from the query criteria; and

representing the search-field names and search-field values in an XML format which can be stored in a column of the record corresponding to the one or more query criteria templates;

a storage mechanism configured to store the one or more query criteria templates in a single template table in the database,

wherein the query template table comprises rows and columns,

wherein each row in the query template table corresponds to a query criteria template and comprises one or more of template name, user name, creation time, and flag type corresponding to a specific query criteria template record, and

wherein a query criteria template contains information on search-field names and search-field values for a query;

a populating mechanism configured to populate a query form with appropriate search-field values; and

a selection mechanism configured to allow a user to select a template from the stored templates prior to performing a query, thereby reducing manual entry of search-field values for similar queries.

20. The apparatus of claim 19, wherein while storing the one or more query criteria templates in the template table, the storage mechanism is configured to store a set of query criteria in a parsable format in a column, thereby avoiding storing search-field names and search-field values in separate records.

21. The apparatus of claim 20, wherein while storing the query criteria in a parsable format in the column, the storage mechanism is configured to store the query criteria in an XML file as a character large object (CLOB) or a binary large object (BLOB).

22. The apparatus of claim 19, wherein while storing the one or more query criteria templates in the template table, the storage mechanism is configured to store a template name, a user name, a template creation time, or a public/private flag in one or more columns within the record corresponding to the template.

23. The apparatus of claim 19, further comprising:

a receiving mechanism configured to receive a query to retrieve one or more templates selected by the user;

a retrieval mechanism configured to retrieve a template selected by the user; and

a communication mechanism configured to communicate the retrieved template to a middle-tier server or a client, thereby allowing the template to be parsed.

24. The apparatus of claim 23, wherein while representing the search-field names and search-field values in a parsable format, the template-generating mechanism is configured to represent the extracted search-field names and search-field values in an XML file and to store the XML file as a CLOB or a BLOB.

25. An apparatus for facilitating user queries to a database, comprising:

a processor;

a memory;

a database;

a determining mechanism configured to determine one or more search fields from query criteria generated by a user in a client-side application;

a template-generating mechanism configured to automatically generate one or more query criteria templates from the determined one or more search fields by extracting search-field names, search-field data types, and search-field length from a set of user interface metadata;

obtaining search-field values from the query criteria; and

representing the search-field names and search-field values in a an XML format which can be stored in a column of the record corresponding to the one or more query criteria templates, wherein a query criteria template is a record which can be stored in a single template, table,

wherein the query template table comprises rows and columns, and wherein each row in the query template table corresponds to a query criteria template and comprises one or more of template name, user name, creation time, and flag type corresponding to a specific query criteria template record; and

a communication mechanism configured to communicate the one or more query criteria templates to a database where the one or more query criteria templates can be stored in the template table.

26. The apparatus of claim 25, wherein the template-generating mechanism is configured to allow the user to specify a name for the automatically generated templates and to specify the automatically generated one or more query criteria templates to be private or public;

wherein a private template is only visible to the template'"'"'s creator; and

wherein a public template is visible to multiple users.

27. The apparatus of claim 25, further comprising:

a query mechanism configured to query the database to retrieve a user-selected template;

a second receiving mechanism configured to receive the user-selected template from the database;

a parsing mechanism configured to parse the received user-selected template to obtain a set of query criteria; and

a query-populating mechanism configured to populate a query form with search-field names and search-field values contained in the query criteria based on a set of user interface metadata.