Browse Prior Art Database

Automated Method for Generating Dynamic SQL WHERE Criteria from Complex Variable GUI Controls

IP.com Disclosure Number: IPCOM000015763D
Original Publication Date: 2002-May-05
Included in the Prior Art Database: 2003-Jun-21
Document File: 4 page(s) / 40K

Publishing Venue

IBM

Abstract

The problem elements this solution was designed to solve include: 1) Display a subset of Database Table Data, based on a large number of complex SQL criteria. 2) Criteria must be chosen by a user through a GUI window/panel. 3) User cannot be relied on to know SQL syntax

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 42% of the total text.

Page 1 of 4

  Automated Method for Generating Dynamic SQL WHERE Criteria from Complex Variable GUI Controls

The problem elements this solution was designed to solve include:

1) Display a subset of Database Table Data, based on a large
number of complex SQL criteria.

2) Criteria must be chosen by a user through a GUI window/panel.

3) User cannot be relied on to know SQL syntax

4) Criteria available for User to select can change over time,
and must be easily added and subtracted from the GUI

5) Possible criteria include the full range of SQL operators
(E.G. : LIKE, =, IN, etc.), so the types of controls are variable
as well as the number displayed.

6) Order or placement of the GUI controls on the panel must not
adversely effect creation of the proper SQL WHERE clause.

The solution implementation consists of:

A) A method for grouping the GUI controls to allow "groups" of
controls to be handled at one time to generate a specific SQL
criteria.

B) A method for converting those control "groups" into SQL
criteria using generic conversion functions based on the type of
criteria being generated.

C) A method for iterating through the specified number of control
groups

D) An apparatus implementing the above using:
i) A variable to store the number of control groups
ii) A loop structure that increments a counter from 0 to
the number of control groups
iii) A case/switch structure inside the loop that details
the control names, database field, and group type
involved in each group
iv) A call inside the loop, after the case statement, that
launches one of 6 generic conversion functions based on
the group type to generate the SQL WHERE criteria.
v) A variable to store the growing SQL WHERE clause string

E) An improved method for grouping the GUI controls using the
full name of the database field and operator qualifiers (such as
batch_number_eq or batch_number_leq). This requires the loop
only to reference the database field name and the type, rather
than listing all controls.

1

Page 2 of 4

F) An improved method for converting the control groups into SQL
using the same generic conversion logic, but referencing only the
database field name and a group type as input to the
function, rather than all controls. Also, the control groups can
be maintained in two lists, one containing the database field
names, and the other containing group types. This eliminates the
need to specify the number of control groups explicitly in a
variable.

G) An improved method for iterating through the control groups
using the two lists described in F above. The top element in each
list is "popped" off, resulting in a matched database field
name and group type, which is then sent directly to the proper
conversion function, eliminating the need for the large case
statement described in D.iii above.

Benefits related to the Problem Elements:

A) Any number of GUI controls can be converted to SQL WHERE
criteria using this process.

B) GUI controls are handled in groups, referenced only by the
database field name and a group type, r...