WO1999061969A2

WO1999061969A2 - System for automatically adjusting to database changes

Info

Publication number: WO1999061969A2
Application number: PCT/US1999/011760
Authority: WO
Inventors: Gregory R. Mccallum
Original assignee: Telsoft Consultants, Inc.
Priority date: 1998-05-28
Filing date: 1999-05-27
Publication date: 1999-12-02
Also published as: WO1999061969A3; AU4316999A

Abstract

A system (40) that allows easy manipulation of a database (44) and automatically updates an associated operating program (46) includes three modules. A first module (42), which is an administrator module, facilitates accessing the database, generates generic tables (48A, 48B) that represent the database structure and contents. A second module (52), which is a form reader module, reads any forms created for interacting with the database and generates generic tables (48C, 48D) that represent the structure and content of the forms. A third OCX module (50) preferably is a form that is dropped onto the associated operating program (46). The OCX module (50) utilizes information from the tables (48) generated by the first and second modules and modifies the operation of the application program (46) responsive to any changes represented in the tables (48).

Description

COMPUTER PROGRAMMING SYSTEM FOR AUTOMATICALLY

ADJUSTING OPERATING PROGRAMS TO CHANGES IN THE DATA

AND STRUCTURE OF AN ASSOCIATED DATABASE

BACKGROUND OF THE INVENTION

This invention generally relates to a database driven program for allowing an associated operating program to adjust automatically to changes to the underlying database. More particularly, the inventive program automatically changes the look and functionality of an operating program without having to stop or recompile the operating program.

There are a variety of uses and needs for databases in such fields as accounting, for example. With the growth and increased use of computers, the variety and complexity of databases has increased. One challenge associated with using such databases is keeping them current and suitable for changing needs in a business environment. A particular problem associated with using such databases is that once a single change is made to the database, that change can affect a variety of operating programs and systems within the computer system. This presents a special challenge to computer programmers and developers.

In a typical scenario, when a company utilizes a database for generating account information, for example, there may be more than fifty thousand lines of computer code that are necessary for operating the system that utilizes the information within the database. Whenever the database needs to be updated, such as adding an additional category of information, the associated operating program must be updated accordingly. This presents a special challenge to computer programmers or developers, who have to inspect every line of the operating program to ensure that the code reflects the changes made to the database. This often results in several days of tedious work. This is a problem for two basic reasons. First, the company that utilizes the database has the additional expense of paying the programmer for the additional time required to bring the operating program current with the changes to the database. Secondly, the programmer or developer cannot tend to other, perhaps more pressing or creative projects, but instead must tediously inspect the entire operating program to ensure that the code is now current.

Therefore, it is desirable to have a system that facilitates changing a database. Moreover, it would be extremely beneficial to have a system that automatically changes an operating program whenever a change to the associated database is made. This invention addresses those needs by providing a system that allows relatively quick and easy changes to a database and then automatically changes the associated operating program. With this invention, a programmer or developer is able to quickly service a client's needs without having to laboriously spend several days on tedious work that previously was unavoidable.

SUMMARY OF THE INVENTION

In general terms, this invention is a program that facilitates easily manipulating or changing a database and automatically updating an associated operating program. The preferred embodiment utilizes the Visual Basic programming language and includes three modules. A first module is responsible for inspecting, allowing modification to and updating the database. The first module automatically generates at least one table that indicates the structure and content of the database. A second module reads and stores custom form designs from the Visual Basic Environment and generates at least one table that indicates the structure and content of the forms. The generated tables preferably are placed into the database. A third module utilizes information from the tables generated by the first and second modules to determine whether any changes to the database were made and is responsible for automatically updating the associated operating program to handle any changes.

The various features and advantages of this invention will become apparent to those skilled in the art from the following detailed description of the currently preferred embodiment. The drawings that accompany the detailed description can be briefly described as follows. BRIEF DESCRIPTION OF THE DRAWINGS Figure 1 is an illustration of a database arrangement that is useful as an example implementation of this invention.

Figure 2 diagrammatically illustrates the preferred overall arrangement of a program designed according to this invention and illustrates the interaction between the programming modules.

Figure 2A illustrates an additional feature that preferably is included with the arrangement illustrated in Figure 2.

Figure 3 diagrammatically illustrates the internal structure of the programming module that is referred to as the OCX.

Figure 4 is a flow chart diagram showing how the user control portion of the OCX module reacts to user interaction.

Figure 5 is a flow chart diagram illustrating the general flow of the Connect method used with this invention. Figure 6 is a flow chart diagram illustrating the general flow of the Refresh method used with this invention.

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENT

This invention provides a system for adapting an operating program to changes made to complex databases and easily updating the program based upon the needs of a particular situation. A program designed according to this invention has the ability to automatically change the operation of the operating program that is necessary for utilizing the modified information within the database.

SAMPLE DATABASE

Figure 1 diagrammatically illustrates a database 20 in a treeview form.

The database 20 is for purposes of illustration only and relates to information regarding golf scores. At a first level 22 each tournament is recorded. For each tournament, there is a plurality of teams at a second level 24. For each team, there is a score recorded at a third level 26. In the illustrated example, each total score breaks down into a total for each hole at another level 28. The score at each hole consists of the score for each player on a team (assuming a foursome in the tournament). Lastly, each player's score on each hole consists of a number of shots to the green (STG), the number of putts and a total score for the hole, which is all recorded at a final level 32.

A program associated with a database 20 provides the desired output based upon the information that is placed within the database. That program, for example, may provide a listing of every tournament that a particular player has played in during a particular year. Once such an operating program is developed, however, the challenge is to keep it current with the desired uses and desired outputs from the database.

As an example, assume that the output of the program provides the total score for each team that participated in a particular tournament. Assume further that the desire is to include not only a numerical score but also a word description of the placement of the team within the tournament (i.e. , first place, second place, etc.). A program developer typically must manually append the database 20 to include a written description of the placement for each team in each tournament. The written description that is added to the database then must be handled by the associated operating program. Without the use of this invention, the developer would have to go through the entire code of the operating program to ensure that the portion of the program responsible for reporting the score of each team was capable of producing an output that included both integers (i.e., the numerical score) and letters (i.e., the written description of the placement of the team). With this invention, however, simply updating the database, using a program designed according to this invention, provides an automatic update to the operating program so that the desired output is achieved.

Before proceeding further with the description, it is important to note that the preferred programming language for a program designed according to this invention is Visual Basic. Therefore, terms such as "control," "table," "calls" and

"field" are used with the meaning given for those terms as used in a Visual Basic programming scheme. There may be times in the following description, however, where those terms have their ordinary meaning and the context will enable one skilled in the art to understand which meaning is appropriate.

SYSTEM ARRANGEMENT

Referring now to Figure 2, the preferred arrangement of a system designed according to this invention is schematically illustrated at 40. An administrator program module 42 contains the necessary software that allows the developer to access and manipulate the contents of a database 44. An associated application program or software 46 also is modified by the administrator module 42, without requiring the developer to manually develop new code to make the changes that are desired in the operation of the application program 46.

The administrator module 42 accesses the database 44 through a set of tables 48. The developer uses a connect statement to access the desired database.

Once connected, the administrator module 42 will automatically read all tables within the database.

The preferred embodiment includes generating or completing four generic tables. These four tables cover all the properties of table structure and customization during operation of a program designed according to this invention.

Each table has an associated set of fields. Each table preferably is blank at the beginning of an implementation of the program designed according to this invention.

The first of the generic tables 48 A is known as SysTables. This table holds all information at the table level regarding properties of the various tables within the database 44. Table 48 A places all tables in the database 44 into a set of identified items and assigns identifiers to those items. In other words the

SysTables table 48A contains generic information regarding the structure of the database that is to be accessed and manipulated. For example, the table 48A would contain information indicating the table names from the database shown in Figure 1. Specifically SysTables 48A would contain information that there is a table called tournament, a table called team, and a table called player, for example.

The SysTables table 48A functions as an interface between the administrator module 42 and the actual database 44. In this regard the SysTables table 48A contains information regarding the actual database 44 that allows the administrator module 42 to utilize the database 44 and to perform manipulations on the database without directly accessing the database itself.

A secondary table to SysTables 48A is referred to as SysTablesFields 48B. This portion of the tables 48 holds information at the field level within SysTables 48 A. The field level information preferably includes information that identifies a particular entry as string, integer, required, optional, function, appearance, etc. Further, SysTablesFields 48B contains information regarding the relationship between items entered in the database 44 such as the relationships schematically illustrated in Figure 1. Another category of information contained within SysTablesFields 48B describes or identifies relationships between items in the database and any other "foreign" tables including how the information is linked or related. This latter portion of SysTablesFields 48B preferably is created upon an initial reading of the database through a loop procedure that first reads through all the appropriate tables, then reads through all the appropriate columns in each of the tables and then through all the associated properties. The information within SysTablesFields 48B preferably includes a duplicate of the structure of the database contents (without copying the actual contents) and other information that controls how the fields are displayed, validated and manipulated. In the preferred embodiment, all the information in SysTables 48A and

SysTablesFields 48B is stored from a database perspective since it provides the administrator module 42 with information regarding the database and its contents.

The tables 48A and 48B preferably are completed by the administrator module 42.

Another table that provides enhanced functional features is referred to as the SysScenarios table 48C, which preferably holds form information such as the name of various forms created by the developer using a Visual Basic Environment 51 and stored through a form reader module 52. In the preferred embodiment, the form reader 52 is a dynamic link library that is dependant on the Visual Basic environment 51. The form reader 52 responds to a command from the developer, reads any forms that the developer has created on screen and generates tables 48C and 48D.

The SysScenarios table 48C provides the added ability to create custom forms that have a particular look and feel as may be desired by a developer. Accordingly, where this feature is not desired, the SysScenarios table 48C need not be included as part of the tables 48. A subcategory of SysScenarios 48C, which corresponds to SysTablesFields

48B, is known as SysScenanosFields and it contains information about each of the controls for the custom forms stored in SysScenarios 48C such as the appearance and order of the cursor, the type of data, etc. SysScenarios fields 48D is completed by the form reader 52. Once the tables 48 are complete, the developer can use the administrator module 42 to manipulate the contents of the appropriate table (or tables) within the database 44. A significant advantage to this invention is that the tables 48 and the administrator module 42 present the developer with much easier access to the contents of the database 44 than was otherwise possible. Moreover, the administrator module 42 can be used with virtually any database, provided that the database can be accessed using either RDO, ADO or DAO techniques, which are known in the art.

The administrator module 42 not only communicates with the tables 48 but also directs an OCX module 50 that preferably becomes part of the application program 46. The term OCX is a term of art that is understood by those having knowledge of Visual Basic programming. The OCX 50 is the portion of the system 40 that communicates with the administrator module 42 and automatically causes the application program 46 to behave in a manner consistent with the desires of the developer who modifies or otherwise manipulates the database 44. In the preferred embodiment, the OCX 50 is placed on a form that is created by the developer. The form containing the OCX 50 preferably is placed on the application or operating program 46 using a conventional technique to drop the OCX 50 on the program 46. Once the form is created and placed onto the application program 46, the developer can interact with the OCX 50 through conventional Visual Basic programming techniques. The various modules of the system 40 communicate with each other to accomplish the automated changes to the operating program 46 responsive to the changes to the database 44. The administrator module 42 administrates the database 44 and delegates the appropriate work functions to the OCX 50, which causes the operating program 46 to operate according to the changes made to the database 44. As schematically illustrated in Figure 2, the administrator module

42 and the tables 48 preferably have two-way communication so that information is exchanged in both directions. The administrator module 42 preferably directs the OCX 50 so only one-way communication occurs with the OCX module 50 and the tables 48. In other words, the OCX module 50 is a read only form. This one-way communication allows the application program 46 to behave according to changes to the contents of the tables 48 that were effected by the administrator module 42. In the preferred embodiment, the administrator module 42 does not directly access the actual contents of the database 44. Instead, an offline databinding technique is employed as schematically illustrated by the communication arrows in Figure 2.

Once the developer creates the forms, initializes the database and the tables 48 are complete, the administrator module 42 enables the developer to monitor any changes to the database 44. The automatic updating of the application program 46 in response to such changes is accomplished by the OCX module 50 as it generates new SQL statements to handle the changes that are made to the tables 48, which reflect the changes to the database 44. In general, these automatically generated SQL statements are created based upon the values that are stored in SysTablesFields 48B. Since that table contains generic structure information regarding the database 44, the SQL statements can be written generically, also, and then they are utilized by the OCX 50 to drive the application software 46. Figure 2A illustrates a preferred enhancement compared to the arrangement illustrated in Figure 2. The most preferred embodiment includes having the database 44 with the System tables 48 supported on a server 49. A database monitor 51 preferably is a COM object that receives address information of each OCX module 50 associated with each application program 46 that utilizes the database 44. The database monitor 51 monitors the system tables 48 and detects when any changes are made. The database monitor 51 then notifies the appropriate OCX modules 50 that a change has occurred. The OCX module 50 responds by reading the contents of the system tables 48 so that the appropriate modification to the application program 46 is accomplished.

Providing a database monitor 51 is preferred so that each OCX module 50 need not continuously read the system tables 48 to detect when any changes are made. The database monitor 51 , therefore, renders the system more efficient by notifying each OCX module 50 when it is appropriate to read the system tables 48 and then to respond accordingly.

Having introduced the main components of the system 40, designed according to this invention, the following description provides additional details regarding the preferred contents of the OCX module 50 and then provides functional details regarding the same to describe the total operation of the system 40.

OCX MODULE CONTENT

Referring to Figure 3, a User Control 59 supplies the visual user interface to the OCX module 50 and acts as the medium by which information is supplied to and gathered from the user. In other words, the user control 59 portion of the

OCX 50 is a view or window into the database 44. The OCX 50 contains generic code to support the controls on a display screen as they are accessed by a user of the database 44. It is important to note that the chosen display method preferably serves as the "brain" of the control. The chosen display method directs and controls the way that the use control looks, functions and responds to inputs from the user or developer. All display methods preferably allow a developer to make changes to the database. User interaction with the User Control 59 is sent to a cRADMain class 60 to be processed. The cRADMain class 60 then responds back to the User Control 59, which then passes the information back to the user through a display screen, for example.

The OCX module 50 preferably includes a plurality of classes that correspond to the tables 48 within the database 44. A colTableDefs collection 61 corresponds to the SysTables 48 A. A cTableDef class 62 corresponds to the SysTables fields 48B. Similarly, a colFieldDefs collection 63 corresponds to the SysScenarios 48C with a cFieldDef class 64 corresponding to the SysScenarios fields 48D.

A colControls collection 65 contains cControls classes 66, which each include information to create a control at run time.

The User Control 59 preferably is used to display the appropriate controls created in the cControls class 66 based on the current display setting. Once a control is placed on the User Control 59, the handle for that control remains in the cControls class 66 and all interaction between the user and that created control is processed directly by the cControls class 66. Therefore, the cControls class 66 is the parent of the control placed on the User Control 59.

The cTableDef class 62 is created for each table that is referenced by both the Primary Table property sent to the Connect method and the tables referenced in the Record Source property sent to the Refresh method. The class 62 then retrieves the row in SysTables 48A for a specific table that contains all of the information for this table. The TableDef classes 62 are collected in the colTableDefs collection 61, which preferably is exposed outside of the OCX 50.

The FieldDef 64 class is created for each field in an instance of cTableDef

62 or cScenario 67. The class 64 then retrieves the row in SysTablesFields 48B for a cTableDef 62 field or SysScenariosFields 48D for a cScenario 67 field. The row that is retrieved will contain all of the information for this specific field. For a cTableDef 62 field, the FieldDef class 64 will contain information about a database field such as field name, data type, data size, etc. For a cScenario 67 field, the cFieldDef class 64 will contain information about a control used in a custom form. The cFieldDef classes 64 are collected in the colFieldDefs collection 63, which preferably is both exposed outside of the OCX 50 directly and as a subclass within two other classes known as cTableDef 62 and cScenario

67.

The cScenario class 67 contains information about the current display method. If the display method is Custom or FreeForm, then this call holds a reference to all of the cFieldDef classes 64 containing information about the controls that will be created and used in the OCX 50.

The cControls class 66 is created for each instance of the cFieldDef class 64 that is contained within the colFieldDefs collection 63 being referenced by the cScenario class 67. The cControls class 66 contains all the information needed to create a control at run time for a particular instance of cFieldDef class 64. This information consists of the type of control to create, a handle to the actual object, positioning information, event handling routines and how to fill and read the control. When the cControls class 66 creates a new class it sets the container of the control to the User Control 59, which allows the user to see the control. While the new control is contained by the User Control 59, all interaction with the control is processed directly by the cControls class 66 that created the control.

The cDatalnformation class 69 acts as the medium between a DataAccess module 75 and the cRADMain class 60 and is created for each database request that is sent to the external DataAccess module 75. The cDatalnformation class 69 contains a handle to a database request that is being processed by the external DataAccess module 75. Additional information contained in this class preferably includes the alias information for the fields and tables included in the SQL statement sent to the external DataAccess module 75 and an identifier that allows the cDatalnformation class 69 to identify to the cRADMain class 60 what request this class references. As the DataAccess module 75 sends status reports about the SQL statement being processed, the cDatalnformation class 69 will see that this status information is correctly reported to the cRADMain class 60. These cDatalnformation classes 69 are collected in the colDataAccess collection 68 which is a subclass within the cRADMain class 60.

The FieldSub class 71 is created for each field or table that is referenced in the SQL statement that is stored in the cDatalnformation class 69 and contains alias information for that specific field or table. This class is used to resolve any ambiguity when referencing fields from the resulting dataset that exists due to duplication of field or table names in the SQL statement. These FieldSub classes 71 are collected in the colDataAlias collection 70 which is a subclass within the cDatalnformation class 69.

The Search class 76 preferably is used to prepare a visual search interface, operate that interface and send results back to the cRADMain class 60. This class 76 is initialized when a subroutine such as an Execute Search or Display Search routine 15 called by the developer. The class 76 uses another copy of the

UserControl 59 to reuse all of the capabilities described above and gains the same benefits that the developer is given by using the OCX 50.

The cSearch class 76 allows the developer or user to search for records in the cDatalnformation class 69 that contains the RecordSource property sent by the developer in the Refresh method. This class allows the user to enter in the search criteria using either a custom format or the freeform display method. The results of the search are displayed to the user in any of the supported display methods to allow the user to select the desired record(s). The user's selection then is sent back to the cRADMain class 60 and the display is updated. OCX MODULE OPERATION

Figure 4 schematically illustrates the operation of the OCX module 50 in response to user interaction with the user control 59. As mentioned above, the user control 59 provides a display that allows interaction between a user and the database 44. The user control 59 typically will include a plurality of controls that can be accessed by a user.

When one of the controls in the user control 59 receives interaction from the user, this event is processed directly by the cControls class 66, which is the parent to the control that receive interaction from the user. The cControls 66 creates a structure that contains all of the pertinent information about the event that is to be raised (based upon the user interaction) and sends it to the cRADMain class 60 for handling. The cRADMain class 60 then creates an Eventlnfo class 72 that represents the structure received from the cControls class 66. The Eventlnfo class then creates an EventParameter 74 for each piece of information in the structure. The Eventlnfo class 72 then wraps all of the EventParameter 74 information into a colEventParameter collection 73. The Eventlnfo class 72 is then sent to the cRADMain class 60, which exposes it to the user through an ObjectEvent.

As schematically shown in Figure 4, an Eventlnfo class 72 is created in response to the user interacting with a control created by the cControls class 66 and placed in the User Control 59. The Eventlnfo class 72 is used to gather all of the information about the interaction or event. This information preferably includes a flag to allow the developer to cancel the event, a handle to the actual control that raised the event, the type of control, the name of the event being raised, and any parameters supplied by the event. This class then will be passed back to the developer through the ObjectEvent event in the UserControl.

The developer preferably has the ability to use this information to handle the event or notify the developer's application of changes to the state of a control. The developer can change certain information in this class to send back to the cControls class 66 that supplied it. The developer information can modify information including canceling the event and changing the values or state of the cControls class 66 that raised the event.

An EventParameter class 74 is created for each piece of information that the cControls class 66 wishes to expose to the developer through the Eventlnfo class 72. This information preferably includes basic parameters that are supplied by the event raised along with the identifier of the field, whether it is an actual database field and the current value of the field.

The developer can make changes to some of the EventParameter classes 74 by specifying how the cControls 66 should treat the interaction from the user.

These changes are then read back by the cRADMain class 60. The cControls 66 class then receives notification from the cRADMain class 60 of the requests from the developer. The cControls class 66 then updates the control displayed in the UserControl 59 with the required information based on the event raised and the developer's requests. The illustration in Figure 4 is intended to show the various flows of information between the classes and to illustrate how the OCX module 50 handles user interaction with a control on the user control 59.

The DataAccess module 75 most preferably is an external application that processes actions against the database 44. The module 75 is called by the cRADMain class 60 and supplied an SQL statement to process. The DataAccess module 75 is designed to run asynchronous, which allows the cRADMain 60 to continue working until the request is complete or at a stable state. When this point is reached, the DataAccess module 75 will notify the cRADMain class 60 that it now can use the results of the query. This notification preferably is handled by interrupting the current activities of the cRADMain class 60 to handle the notification. This class is referenced by the cDatalnformation class 69 that preferably contains other information about the query and connection to the DataAccess module 75. PROGRAM OPERATION

To use the program system 40, the first step is to load the Administrator Module 42 and connect to the database 44. The Administrator Module 42 then will perform an operation that will read all of the information available about the database 44 and fills the SysTables 48A and SysTablesFields 48B with this information. The developer can then change or add information about how to use this information using the Administrator Module 42. Such changes or additions will vary depending on the needs of a particular situation.

The next step is to create one or more custom forms for each table in the database 44. This is done by using the Form Reader module 52 (Figure 2). The

Form Reader module 52 runs as an add-in to the Visual Basic Environment 51 and allows the developer to read the contents of a form being displayed. This module 52 reads all of the properties and settings for each of the controls on the form and saves them to the SysScenarios 48C and SysScenariosFields 48D. This process is repeated as many times as necessary to read in all of the forms that the developer wishes to use to represent the contents of the database 44.

Once these forms have been stored in the System Tables 48, they can be viewed, modified and assigned to a particular table using the Administrator Module 42. Assigning a custom form to a table makes that form the default form to display to the programmer when viewing the specific table.

The previous steps need to be done before the OCX module 50 can be used in the developer's project. At this point, the developer can open up a project in the Visual Basic Environment 51 and use as many instances of the OCX module 50 as needed. To initialize the OCX module 50, the Connect method needs to be called to tell the OCX module 50 the display method, primary table name, custom form, database connection and record source to use. With this information the OCX module 50 is able to assume the appearance and reactions requested by the developer. The appearance and performance of the User Control 59 depends upon the display method that is chosen. If the display method is a TreeView (see figure 1 , for example) then the fields and tables supplied in the record source act as a template to direct the control to format all SQL statements regarding a selected node based on the structure of the record source. If no particular fields are supplied in the record source, then the primary table name and related key fields preferably will be used.

If the chosen display method is a FlexGrid then the fields and tables supplied in the record source act as a template to direct the control to format all SQL statements regarding a selected row based on the structure of the record source and create the appropriate column headers for the FlexGrid. If no particular fields are supplied in the record source, then the primary table name and related key fields preferably will be used.

If the chosen display method is a Custom or FreeForm then the fields and tables supplied in the record source act as a template to direct the control to format all SQL statements regarding a selected row based on the structure of the record source and create the appropriate column headers for the FlexGrid. If no particular fields are supplied in the record source, then the primary table name and related key fields will be used. These display methods use the primary table name to direct the control to build all insert, update and delete SQL statements based on the structure of the primary table.

All of the display methods are supported so that various SQL statements to represent a particular selected node, row or record can be produced. The SQL statements can take several forms. One form of SQL statement is based solely on the primary table name. Another includes foreign lookup information as set in the Administrator Module 42. Still another includes formatting or grouping of fields for each field as set in the Administrator Module 42.

It is important to note that the term "user" refers to an end user of the application created by the developer using this invention. The "developer" is the programmer who is called upon to update the database and modify the associated program so that the desired results are obtained. A program designed according to this invention preferably also provides a plurality of options during the Connect method. For example, the user can select a particular desired display method, identify specific tables or identify portions of the database that should receive attention. Once the desired parameters are supplied by the user, they are verified for correctness, existence and relevancy to the current state of the control. The database connection is further checked to determine the database engine that it is pointing to, if this engine is from a particular source and the method of connection. An example of such a source is Oracle and, if that is the case, then an owner name preferably is stored and used as a prefix for all tables used to build SQL statements produced by the control.

An example of a method of connection is Remote Data Objects (RDO) and, if that is the case, then the appropriate commands are used to create, read and save data to the database.

The input parameters from the Connect method are stored in the OCX module 50 cScenario class 67, which acts as the "personality" of the control. The cScenario class 67 defines the appearance of the control and is used to delegate the creation of the controls necessary and requested to display in the UserControl

59.

At this point, the colTableDefs class 61 is created and a reference to the primary table name is added to the collection. A subroutine known as the

ReadSysTables routine is called to read, store and decipher the contents of the System Tables 48 for the primary table name. If the display method is Custom then another subroutine known as ReadScenarios is called to look for any custom attached to this primary table name. This information is used to make another copy of the colFieldDefs class 63, which is populated with the information from the System Tables 48 regarding the controls used for the specific custom form.

A subroutine called CreateForm is called that is responsible for creating the necessary instances of cControls 66 to represent the controls that need to be created and displayed in the user control. This subroutine is responsible for the visual aspects of the user control based upon the currently chosen display method which determines how the visual interface should be created and handled.

In the event that there is an error within this function, then all of the state variables are cleared. The control becomes unusable at this point until the Connect method is again attempted with the proper settings.

Figure 5 summarizes the Connect method for initializing the OCX module 50 in flow chart form. First, the parameters are stored and validated at 150. Then the display method is determined at 152. If the display method is custom or freeform, then the OCX module 50 reads the SysTables 48 A and SysTablesFields 48B and builds the cTableDefs collection 61 with its corresponding cTableDef classes 62 and the colFieldDefs collection 63 with its cFieldDef classes 64. This step is completed at 154. Next, the determination is made whether there is a custom form at 156. In the event there is a custom form, the OCX module at 158 reads the SysScenarios 48C and SysScenarios Fields 48D tables and builds the cScenario class 67, which contains the display method information. The OCX module 50 also builds the colFieldDefs class 63 at this time. Next, the visual interface is created at 160 by building the colControls collection 65 of cControls classes 66. The visual interface is created based upon the display method detected at 152.

In the event that the display method is a tree view or a flex grid, the OCX module 50 preferably destroys the current view at 162 and exits the initialization process.

Once the Connect method is successfully completed, the control is considered to be connected and ready to use. As this point, the control is at one of several stages depending on the chosen display method. All of the possible configurations and stages need not be explained here.

The Refresh Method, which is summarized in Figure 6, must be called after the Connect method to have the control retrieve the records requested by the record source. The record source is supplied to the Refresh method as a parameter and represents a select SQL statement. The Refresh method performs some of the same validations that the Connect method did. This allows the developer to call the Refresh method to check for changes in the System Tables 48 without having to supply the database connection, primary table name, display method and other parameters required by the Connect method.

The developer then uses conventional Visual Basic programming techniques to control and use the OCX module 50 to make any desired changes to the database 44. The developer can use the Administrator module 42 to make changes to the System Tables 48 and direct the OCX 50 module to update itself to reflect any changes made to the System Table 48. This allows the user to be presented with the desired changes the next time that the application software 46 calls the Refresh method of the OCX 50 module. It is this last step that had to be manually programmed previous to this invention. With this invention, however, substantial time and effort is saved because the OCX 50 module automatically generates the necessary SQL Statements and actions that allow it to influence the operation of the application program 46 so that user is presented with the desired results.

The automatic development of the SQL Statements is summarized in flow chart form in Figure 6. As schematically illustrated in Figure 6, the state of the OCX module 50 is reset at 170. The RecordSource is parsed at 172. The OCX module 50 then reads the SystemTables at 48 at 174 and generates the classes within the collection 61 and 63. Next the OCX module 50 determines whether there is a custom format 176. If there is a custom form then the tables 48C and D are read at 178 and the classes 67 and 63 are built. Next the classes 65 and 66 are built to create the visual interface at 180. Then, at 182 the RecordSource is sent to the data accesses module 75. Execution is then returned to the caller. When the results are ready, the data access 75 will call back and the display will be updated with the results automatically performed by the OCX module 50. As can be appreciated from the above description, this invention includes three program modules that facilitate easily manipulating a database and automatically updating an associated operating program to handle the changes to the database. The three essential modules discussed above, namely the administrator module 42, the OCX modular 50 and the form reader module 52, provide the working components of a system designed according to this invention. Although three separate modules are described for purposes of illustration, it may be possible to integrate different portions of them into fewer or more modules, provided that the functions of each are still present within the system. Given this description, those skilled in the art will be able to develop the code necessary to realize the function of the administrator module 42, OCX module 50 and form reader module 52.

The description of this invention provides details that are exemplary rather than limiting in nature. Variations and modifications to the disclosed embodiment may become apparent to those skilled in the art that do not necessarily depart from the basis of this invention. The scope of legal protection for this invention can only be limited by the following claims.

Claims

CLAIMSThe following is claimed:

1. A system for manipulating a database and automatically updating an associated computer program, comprising:

a first module that inspects the database and automatically generates at least one table indicating the structure and content of the database;

a second module that inspects any forms that are used to interact with the database and automatically generates at least one table indicating the structure and content of the forms; and

a third module that receives information from the generated tables and automatically modifies the associated computer program responsive to changes made to the database that are indicated in the generated tables.

2. The system of claim 1 , wherein the first, second and third modules each comprise software.

3. The system of claim 1, wherein the first module provides access to the database for modification of the database and wherein the first module automatically updates the generated table to indicate any modification.

4. The system of claim 1 , wherein the first module generates a first table indicating the structure of the database and a second table indicating the characteristics of the database contents.

5. The system of claim 1, wherein the first module effectively duplicates the contents of the database when generating the at least one table and subsequently access the table without directly accessing the database when facilitating later modification of the database.

6. The system of claim 1 , including blank tables associated with the database that are filled in by the first module to indicate the structure and content of the database.

7. The system of claim 1 , wherein the second module generates a first table that indicates the structure of the forms and a second table that indicates the nature of the contents of each form.

8. The system of claim 1, wherein the second module is a direct link library that is supported in a Visual Basic Environment.

9. The system of claim 1 , wherein the third module is a form that is placed on the associated program.

10. The system of claim 1 , wherein the third module includes generic code that allows the third module to utilize the contents of the generated tables and automatically change how the associated program interacts with the database responsive to any changes to the database indicated in the generated tables.

11. The system of claim 10, wherein the third module generates SQL statements that dictate the way that the associated program interacts with the database.

12. The system of claim 1 , wherein the third module includes a plurality of classes that include information regarding contents of the table that is generated by the first module and a class that includes information regarding the table that is generated by the second module.

13. The system of claim 1, wherein the third module includes a user control that provides a visual interface to a user of the system for interacting with the database and a control class that includes information for automatically generating a control responsive to the user interacting with the user control.

14. The system of claim 13, wherein the control class receives information regarding an interaction between the user control and a user and the third module includes a main class that receives information from the control class and processes that information to bring about a response that is provided to the user through the user control.

15. The system of claim 1, wherein the third module comprises an OCX.

16. The system of claim 1, wherein there are a plurality of associated programs and a corresponding plurality of third modules and including a database monitor that monitors the generated tables and communicates with each third modules so that each third module can responsively adapt a corresponding program to modifications to the database.

17. A method of manipulating a database and automatically updating an associated program, comprising the steps of:

(A) determining the structure and contents of the database;

(B) automatically generating at least one table indicating the structure and content of the database;

(C) determining the structure and contents of any forms used to facilitate interaction with the database;

(D) automatically generating at least one table indicating the structure and content of the forms;

(E) determining when a modification to the database has occurred by referencing the generated tables; and

(F) automatically updating the associated program responsive to the modification determined in step (E).

18. The method of claim 17, wherein steps (B) and (D) include providing blank tables that are associated with the database and then filling in the tables with the appropriate structure and content information.

19. The method of claim 17, wherein step (F) includes generating at least one generic SQL statement that drives the associated program such that the associated program provides the desired interaction with the database.

20. The method of claim 17, including generating the table of step (B) to effectively duplicate the contents and structure of the database and then subsequently accessing only the table directly and not accessing the database directly.