Data base development system with methods facilitating copying of data from one data source to another5937415Abstract A Client/Server Database System with improved methods for performing database queries, particularly DSS-type queries, is described. The system includes one or more Clients (e.g., Terminals or PCs) connected via a Network to a Server. In general operation, Clients store data in and retrieve data from one or more database tables resident on the Server by submitting SQL commands, some of which specify "queries"--criteria for selecting particular records of a table. The system implements a "Data Pipeline" feature for programming replication of data from one database to another in client applications. Specifically, a pipeline object and SQL SELECT statement are built using a Pipeline Painter. The Data Pipeline lets a user (developer) easily move data from a high-end database server (e.g., Sybase) to a local database (Watcom SQL), all without the user having to issue SQL commands. The pipeline object facilitates moving data from one database management system to another, or between databases of the same type. Claims What is claimed is: Description MICROFICHE APPENDIX
______________________________________
Doing this Triggers
______________________________________
Clicking on a particular
The Clicked event of that
CommandButton control in a window
CommandButton control
Clicking on a particular menu item in a
The Clicked event of that menu
window's menu item
Modifying the value in a particular
The Modified event of that
SingleLineEdit control of a window
SingleLineEdit control
Closing a particular window
The Close event of that window
______________________________________
When an event is triggered, a user's application executes a corresponding script, which contains any processing logic a user has written for that event. This is illustrated in FIG. 10B. A user's job as a designer, therefore, is to figure out all of the events of interest that might occur in his/her application and to provide appropriate processing logic for each event (in its script). Each kind of user-interface component has its own set of several different events that can happen to it, including, for instance, the following.
______________________________________
This component
Has
______________________________________
A CommandButton control
About a dozen different events, including:
Clicked, GetFocus, and LoseFocus
A menu item Just a couple of events: Clicked and Selected
A SingleLineEdit control
About a dozen different events, including:
Modified, GetFocus, and LoseFocus
A window More than 25 different events, including:
Open, Close, Resize, Timer, and Clicked
______________________________________
Some events apply to a user's application as a whole, including (1) one that is triggered when the application starts (the Open event), and (2) one that's triggered when the application ends (the Close event). In many cases, a user need only write scripts for just one or two of the events of a particular component (and sometimes a user won't need any for that component). Letting end users drive the flow of processing is appropriate most of the time, but on occasion a user will want the application to temporarily take control. In such situations, the user can write code in the script of one event that manually causes another event to occur. When doing this, a user can either trigger the event so that its script executes right away, or post the event to a queue so that its script execution is deferred (until after the scripts of any earlier events have executed). A user can also define his/her own events for any particular component and then manually trigger or post them to execute their scripts. These "user events" are useful for extending the processing of other event scripts by serving as subroutines; and responding to certain lower-level messages (from a user's operating environment) that the development environment does not provide as standard events. Once a user knows which events he/she needs to handle in his/her application, a user provides an appropriate script for each one. As illustrated in FIG. 11A, a script is a body of procedural code that a user writes in the PowerScript.TM. language to express the processing logic to perform, typically in response to particular events. Most scripts are relatively short (tens of lines long, not hundreds), since they just need to express the processing for particular events and not for the whole application. PowerScript is a high-level language that provides several different syntactic constructs a user can use to write the code he/she needs. As shown in FIGS. 11B-C, these include variable declarations, Powerscript statements, function calls (built-in and user-defined), embedded SQL statements, and comments. PowerScript supports many data types, as well as arrays and structures of variables. It also provides several levels of scoping that a user can choose from for each variable he/she declares. PowerScript statements provide flow-of-control mechanisms (such as branching and looping) that a user can use to steer the processing in a particular script. When a user creates a user-defined function, he/she specifies the arguments required (if any), a script of the code that is to execute, and the value it is to return (if any). A user can then call that user-defined function from event scripts or from other user-defined functions. 6. Mapping the requirements of the application In the example of the hypothetical company, Anchor Bay Nut Company, the user would map processing requirements to development environment features as follows. The Order Entry application needs to handle a number of different events for its various windows, controls, and menus. For instance, consider the application's Customer window. To provide all of the processing for the window of FIG. 8A, for instance, scripts are required for each of the following events.
______________________________________
Type of component
Name Events that need scripts
______________________________________
Window w.sub.-- customer
Open,
RButtonDown
CommandButton control
cb.sub.-- close
Clicked
cb.sub.-- delete
Clicked
cb.sub.-- new
Clicked
cb.sub.-- orders
Clicked
cb.sub.-- retrieve
Clicked
cb.sub.-- save
Clicked
DataWindow control
dw.sub.-- detail
Clicked,
RowFocusChanged,
Uevent.sub.-- keypressed
(a user event)
dw.sub.-- list
EditChanged,
ItemChanged,
ItemError
SingleLineEdit control
sle.sub.-- lname
Modified
Menu item listed
m.sub.-- new Clicked
under File
m.sub.-- retrieve
Clicked
m.sub.-- save
Clicked
m.sub.-- delete
Clicked
m.sub.-- printersetup
Clicked
m.sub.-- reviewdata
Clicked
m.sub.-- exit
Clicked
Menu item listed under
m.sub.-- goto
Clicked
Orders
Menu item listed under
m.sub.-- phoneprocedures
Clicked
Guides m.sub.-- companypolicies
Clicked
m.sub.-- salescompetition
Clicked
inquiry
m.sub.-- employee
Clicked
motivationa
m.sub.-- employee
Clicked
motivationb
m.sub.-- employeefun
Clicked
Menu item listed under
m.sub.-- contents
Clicked
Help m.sub.-- about
Clicked
______________________________________
The Order Entry application may include user-defined functions to provide additional processing services that can be called from the event scripts. For example, the application's Customer window can includes the three following user-defined functions.
______________________________________
User-defined function
Purpose
______________________________________
wf.sub.-- delcustorders
To be called whenever the user asks to
delete a customer to make sure the
customer does not have any outstanding
orders in the database
wf.sub.-- newcustnum
To be called whenever the user asks to
add a customer to compute the next
available ID number for a new customer
in the database
wf.sub.-- warndataloss
To be called at the beginning of various
operations to check whether any unsaved
data might be lost and to warn the user
______________________________________
The wf.sub.-- delcustorders function may be constructed, for examle, as shown in FIG. 11D. With an understanding of how an application is designed in the system of the present invention, the methods of the present invention for "piping" data between data sources can now be understood. Improved "Pipeline" Transfer Of Data Between Data Sources The following description will focus on specific modifications to a client/server database, such as System 240, for providing improved data transfer between data sources. A. Overview Of particular interest to the present invention is the requirement that applications be able to migrate data from one place to another in the course of their work. For example, a user might need to create a new sales summary table in a user's database by joining various regional sales tables and extracting particular data from them, or copy a payroll table from the server database to a local database so that the application can access it without needing the network. To handle these requirements, the developement environment provides "Data Pipelines," such as illustrated in FIG. 12A. A data pipeline is an application component a user can design to "pump" data--including Blob (binary large object) data--from one or more source tables to a new or existing destination table. The source and destination tables a user specifies can either be in the same database or in separate databases (even if they are different kinds of databases that involve different DBMSs). Using the system of the present invention, therefore, the designing user creates a "pipeline object"--a tool for moving data from one database to another, even across different database management systems. The object may be employed for a number of uses. One use, for instance, is migrating data from a local database (e.g., Watcom SQL) to a database server (e.g., Sybase SQL Server). Suppose, for instance, that users of the Order Entry application want to extract quarterly data from the Sales.sub.-- rep and Sales.sub.-- summary tables of the company's sales database (ABNCSALE.DB) and store that data in a new table (which they can use later for querying and reporting). As illustrated in FIG. 12B, the application provides this capability to users by displaying a window 1250, "w.sub.-- sales.sub.-- extract," that lets users execute an appropriate data pipeline. A data pipeline, constructed in accordance with the present invention, makes it possible to copy rows from one or more source tables to a new or existing destination table-either within a database, or across databases, or even across DBMSs. A user can take advantage of data pipelines in different ways: as a utility service for developers, or to implement data migration capabilities in an application. Regarding the former, a user may occasionally want to migrate data for logistical reasons (such as to create a small test table from a large production table), while working in the PowerBuilder development environment. In this case, a user can use the Data Pipeline painter interactively to do that migration right then and there. Regarding the latter, if a user is building an application whose requirements call for migrating data between tables, the user can design an appropriate data pipeline in the Data Pipeline painter, save it, and then enable users to execute it from within the application. This technique can be useful in many different situations, such as when a user wants the application to download local copies of tables from a database server to a remote user, or when the user wants it to roll up data from individual transaction tables to a master transaction table. Use of a data pipeline in one's application entails five basic steps. These steps include: (1) Building the objects needed; (2) Performing some initial housekeeping; (3) Starting the pipeline; (4) Handling row errors; and (5) Performing some final housekeeping. These steps will now be described. B. Building a pipeline object To implement data piping in an application, a user will need to build a few different objects: a pipeline object; a supporting user object; and a window. A user must build a pipeline object to specify the data definition and access aspects of the pipeline that a user wants his/her application to execute. The development environment provides a Data Pipeline painter in to create this object and define the characteristics a user wants it to have. The characteristics a user can define in the Data Pipeline painter include the following. (1) The source tables to access and the data to retrieve from them; (2) The destination table to which a user wants that data piped; (3) The piping operation to perform (create, replace, refresh, append, or update); (4) The frequency of commits during the piping operation (either after every n rows are piped or only after all rows are piped); (5) The number of errors to allow before the piping operation is terminated; and (6) Whether to pipe extended attributes to the destination database (from the PowerBuilder repository in the source database). FIG. 13A illustrates use of Data Pipeline painter 1300 for defining a pipeline object named pipe.sub.-- sales.sub.-- extract1 (one of two pipeline objects employed by the w.sub.-- sales.sub.-- extract window in the sample Order Entry application). As shown, the pipeline object joins two tables (Sales.sub.-- rep and Sales.sub.-- summary) from the company's sales database to provide the source data to be piped. It retrieves just the rows from a particular quarter of the year (which the application must specify by supplying a value for the retrieval argument named quarter). The pipeline object also indicates specific columns to be piped from each source table (srep.sub.-- id, srep.sub.-- lname, and srep.sub.-- fname from the Sales.sub.-- rep table, as well as ssum.sub.-- quarter and ssum.sub.-- rep.sub.-- team from the Sales.sub.-- summary table). In addition, the pipeline object defines a computed column to be calculated and piped. This computed column subtracts the ssum.sub.-- rep.sub.-- quota column of the Sales.sub.-- summary table from the ssum.sub.-- rep.sub.-- actual column, as shown in FIG. 13B. The details of how pipe.sub.-- sales.sub.-- extract1 is to pipe its source data are specified in Data Pipeline 1360, shown in FIG. 13C. Notice that this pipeline object is defined to create a new destination table named Quarterly.sub.-- extract. As illustrated below, the application specifies the destination database in which to put this table (as well as how it specifies the source database in which to look for the source tables). The pipeline object also specifies the following. A "commit" (i.e., database transaction commit) will be performed only after all appropriate rows have been piped; if the pipeline's execution is terminated early, all changes to the Quarterly.sub.-- extract table will be rolled back. No error limit is to be imposed by the application, so any number of rows can be in error without causing the pipeline's execution to terminate early. No extended attributes are to be piped to the destination database. The primary key of the Quarterly.sub.-- extract table is to consist of the srep.sub.-- id column and the ssum.sub.-- quarter column. The computed column that the application is to create in the Quarterly.sub.-- extract table is to be named computed.sub.-- net. C. Building a supporting user object The foregoing illustrates how a pipeline object defines the details of the data and access for the pipeline. The pipeline object itself does not include the logistical supports--attributes, events, and functions--that an application requires to handle pipeline execution and control. To provide these logistical supports, an appropriate user object is constructed which inherits from the PowerBuilder pipeline system object--a core system object. This system object contains various attributes, events, and functions that enable a user's application to manage a pipeline object at execution time, including, for instance, the following.
______________________________________
Attributes Events Functions
______________________________________
DataObject PipeStart Start
RowsRead PipeMeter Repair
RowsWritten PipeEnd Cancel
RowsInError
Syntax
______________________________________
The supporting user object for a pipeline is built using the User Object painter, provided by the system. From a "Select User Object" dialog box, the user clicks a "New" button. In response, the system displays "New User Object" dialog box 1400, as shown in FIG. 14A. This prompts the user to specify which kind of user object the user wants to create. The user selects the Standard Class icon 1401, for selecting this type of user object. A "Select Standard Class Type" dialog box 1420 displays, as shown in FIG. 14B, prompting the user to specify the name of the PowerBuilder system object (class) from which a user wants to inherit his/her new user object. Here, the user selects "pipeline" 1425 and clicks OK. Now, the system displays a "User Object" painter workspace 1450, as shown in FIG. 14C. This painter enables the user to work with his/her new user object, or make any changes the user wants to the user object. This might involve coding events, functions, or variables for use in a user's application. When all changes are complete, the user saves the user object. D. Building a window (user interface) The next object required is a window object to provide a user interface to the pipeline, enabling people to interact with it in one or more ways. This interface supports user tasks of starting the pipeline's execution, displaying and repairing any errors that occur, and canceling the pipeline's execution if necessary. The pipeline provides at execution time its own DataWindow object, for displaying error rows (i.e., rows it cannot pipe to the destination table for some reason). A user can otherwise design the window as he/she likes. The user will typically want to include various other controls, such as CommandButton or PictureButton controls to let the user initiate actions (such as starting, repairing, or canceling the pipeline); StaticText controls to display pipeline status information; and Additional Data Window controls to display the contents of the source and/or destination tables. Several of the controls in the w.sub.-- sales.sub.-- extract window 1250 in FIG. 12B, for the Order Entry application, are used to implement particular pipeline-related capabilities. Exemplary functionality provided by controls of that window include the following.
______________________________________
Control type
Control name Purpose
______________________________________
RadioButton
rb.sub.-- create
Selects pipe.sub.-- sales.sub.-- extract1 as
the pipeline object to execute.
rb.sub.-- insert
Selects pipe.sub.-- sales.sub.-- extract2 as
the pipeline object to execute.
CommandButton
cb.sub.-- write
Starts execution of the selected
pipeline.
cb.sub.-- stop
Cancels pipeline execution or
applying of row repairs.
cb.sub.-- applyfixes
Applies row repairs made by the
user (in the dw.sub.-- pipe.sub.-- errors
DataWindow control) to the
destination table.
cb.sub.-- forgofixes
Cleans all error rows from the
dw.sub.-- pipe.sub.-- errors DataWindow
control (for use when the user
decides not to make repairs).
DataWindow
dw.sub.-- review.sub.-- extract
Displays the current contents of
the destination table
(Quarterly.sub.-- extract).
dw.sub.-- pipe.sub.-- errors
(Required) Used by the pipeline
itself to automatically display
the PowerBuilder pipeline-error
DataWindow (which lists rows
that can't be piped due to some
error).
StaticText
st.sub.-- status.sub.-- read
Displays the count of rows that
the pipeline reads from the
source tables.
st.sub.-- status.sub.-- written
Displays the count of rows that
the pipeline writes to the
destination table or places in
dw.sub.-- pipe.sub.-- errors.
st.sub.-- status.sub.-- error
Displays the count of rows that
the pipeline places in
dw.sub.-- pipe.sub.-- errors (because they
are in error).
______________________________________
E. Initial housekeeping Now that a user has the basic objects needed, he or she is ready to start writing code to make the pipeline work in the application. At the outset, the user addresses some setup chores that will prepare the application to handle pipeline execution. This task involves connecting to the source and destination databases for the pipeline. To do this, the user writes the usual connection code in an appropriate script. One transaction object is used when connecting to the source database and a different transaction object is used when connecting to the destination database (even if it is the same database). Next, an instance of a user's supporting user object is created, so that the application can use its attributes, events, and functions. To do this, a variable is declared whose type is that user object. Then, in an appropriate script, a CREATE statement is employed to create an instance of the user object and assign it to that variable. Then, the user specifies the particular pipeline object to use. This is done through an assignment statement in an appropriate script, for assigning a string containing the name of the desired pipeline object to the DataObject attribute of a user's user-object instance. For the Anchor Bay Nut Company example, the pipeline setup is provided in the Order Entry application as follows. 1. Connecting to the source and destination database In this case, the company's sales database (ABNCSALE.DB) is used as both the source and the destination database. To establish the necessary connections to the sales database, a user event named uevent.sub.-- pipe.sub.-- setup (which is posted from the Open event of the w.sub.-- sales.sub.-- extract window) is created. The connection is established as follows.
______________________________________
// Create a new instance of the transaction object
// and store it in itrans.sub.-- source (a variable
// declared earlier of type transaction)
itrans.sub.-- source = CREATE transaction
// Next, assign values to the attributes of the
// itrans.sub.-- source transaction object.
.
.
.
// Now connect to the source database.
CONNECT USING itrans.sub.-- source;
To establish their destination database connection, they coded:
// Create a new instance of the transaction object
// and store it in itrans.sub.-- destination (a variable
// declared earlier of type transaction).
itrans.sub.-- destination = CREATE transaction
// Next, assign values to the attributes of the
// itrans.sub.-- destination transaction object.
// Now connect to the destination database.
CONNECT USING itrans.sub.-- destination;
______________________________________
2. Creating an instance of the user object To use the supporting user object (u.sub.-- sales.sub.-- pipe.sub.-- logistics) in the application, a variable of its type is declared.
______________________________________
// This is an instance variable for the
// w.sub.-- sales.sub.-- extract window.
u.sub.-- sales.sub.-- pipe.sub.-- logistics iuo.sub.-- pipe.sub.--
logistics
______________________________________
The uevent.sub.-- pipe.sub.-- setup user event includes logic to create an instance of u.sub.-- sales.sub.-- pipe logistics and store this instance in the variable iuo.sub.-- pipe.sub.-- logistics, as follows. iuo.sub.-- pipe.sub.-- logistics=CREATE u.sub.-- sales.sub.-- pipe.sub.-- logistics 3. Specifying the pipeline object to use The application uses one of two different pipeline objects, depending on the kind of piping operation the user wants to perform: pipe.sub.-- sales.sub.-- extract1 creates a new Quarterly.sub.-- extract table; and pipe.sub.-- sales.sub.-- extract2 inserts rows into the Quarterly.sub.-- extract table. Both assume that the table does not currently exist. To choose a pipeline object and prepare to use it, the following logic is added in the Clicked event of the cb.sub.-- write CommandButton (which users click when they want to start piping).
______________________________________
// Look at which radio button is checked in the
// w.sub.-- sales.sub.-- extract window. Then assign the matching
// pipeline object to iuo.sub.-- pipe.sub.-- logistics.
IF rb.sub.-- create.checked = true THEN
iuo.sub.-- pipe.sub.-- logistics.dataobject = &
"pipe.sub.-- sales.sub.-- extract1"
ELSE
iuo.sub.-- pipe.sub.-- logistics.dataobject = &
"pipe.sub.-- sales extract2"
END IF
______________________________________
This code appears at the beginning of the script, before the code that starts the chosen pipeline. F. Starting the pipeline With the setup chores taken care of, the pipeline is ready to execute. The following steps are undertaken. A Start function is placed in an appropriate script. In this function, a user will specify the transaction object for the source database; the transaction object for the destination database; the DataWindow control in which a user wants the Start function to display any error rows; and Values for retrieval arguments a user has defined in the pipeline object. If a user omits these values, the Start function will prompt the user for them automatically at execution time. The pipeline retrieves rows from the source after executing the SQL SELECT statement (or, alternatively, a stored procedure). If the pipeline is either Creating, Replacing, or Refreshing a table at the destination, it performs the following before transfering any data.
______________________________________
OPTION Action (at destination)
______________________________________
CREATE Executes a CREATE TABLE statement
Executes a CREATE INDEX statement (if index
specified)
REPLACE Executes DROP TABLE statement, then
same as CREATE option
REFRESH Executes a DELETE FROM <target table>
statement
______________________________________
The data is transferred to the destination by executing SQL INSERTS and/or SQL UPDATE statements which are constructed by the pipeline engine. The data transfer continues until either all rows have been copied from the source to the destination, the maximum allowable error limit has been exceeded or the application cancels the pipeline. For the Anchor Bay Nut Company example, the pipeline is started as follows. 1. Calling the Start function When users want to start their selected pipeline, they click cb.sub.-- write CommandButton 1501 in the w.sub.-- sales.sub.-- extract window 1500, as shown in FIG. 15A. This executes the Clicked event of cb.sub.-- write, which contains the Start function.
______________________________________
// Now start piping.
integer li.sub.-- start.sub.-- result
li.sub.-- start.sub.-- result = iuo.sub.-- pipe.sub.-- logistics.Start &
(itrans.sub.-- source,itrans.sub.-- destination,dw.sub.-- pipe.sub.--
errors)
______________________________________
In this example, no value is provided for the pipeline's retrieval argument. As a consequence, the Start function prompts the user for it, as indicated by dialog 1530 in FIG. 15B. 2. Testing the result The following lines of code in the Clicked event of cb.sub.-- write check the Start function's return value. This lets the application know whether it succeeded or not (and if not, what went wrong):
______________________________________
CHOOSE CASE li.sub.-- start.sub.-- result
CASE -3
Beep (1)
MessageBox("Piping Error", &
"Quarterly.sub.-- Extract table already exists . . .
RETURN
CASE -4
Beep (1)
MessageBox("Piping Error", &
"Quarterly.sub.-- Extract table does not exist . . .
RETURN
END CHOOSE
______________________________________
G. Monitoring pipeline progress Testing the Start function's return value is not the only way to monitor the status of pipeline execution. Another technique a user can use is to retrieve statistics that a user's supporting user object keeps concerning the number of rows processed. They provide a live count of (1) the rows read by the pipeline from the source tables; (2) the rows written by the pipeline to the destination table or to the error DataWindow control; and (3) the rows in error that the pipeline has written to the error DataWindow control (but not to the destination table). By retrieving these statistics from the supporting user object, a user can dynamically display them in the window and enable users to watch the pipeline's progress. H. Canceling pipeline execution In many cases, a user will want to provide users (or a user's application itself) with the ability to stop execution of a pipeline while it is in progress. For instance, a user might want to give users a way out if they start the pipeline by mistake or if execution is taking longer than desired (e.g., because a large number of rows are involved). To cancel pipeline execution, the user adds a Cancel function in an appropriate script, making sure that either the user or a user's application can execute this function (if appropriate) once the pipeline has started. When Cancel is executed, it stops the piping of any more rows from that moment on. Rows that have already been piped up to that moment may or may not be committed to the destination table, depending on the Commit attribute a user specified when building his/her pipeline object in the Data Pipeline painter. For the Anchor Bay Nut Company example, pipeline execution is canceled in the Order Entry application as follows. During design of the w.sub.-- sales.sub.-- extract window, the user included the cb.sub.-- stop CommandButton control. The application's scripts included code to enable this CommandButton when pipeline execution starts and to disable it when the piping is done. Additionally, the Clicked event of cb.sub.-- stop is associated with a script which calls the Cancel function and tests whether or not it worked properly:
______________________________________
IF iuo.sub.-- pipe.sub.-- logistics.Cancel( ) = 1 THEN
Beep (1)
Message Box("Operation Status", &
"Piping stopped (by a user's request).")
ELSE
MessageBox("Operation Status", &
"Error when trying ta stop piping.")
END IF
______________________________________
Together, these features let a user of the application click the cb.sub.-- stop CommandButton to cancel a pipeline that is currently executing. I. Committing updates to the database When a pipeline object executes, it commits updates to the destination table according to a user's specifications in the Data Pipeline painter. A user does not need to write any COMMIT statements in his/her application's scripts to do this. For instance, both of the pipeline objects in the sample Order Entry application (pipe.sub.-- sales.sub.-- extract1 and pipe.sub.-- sales.sub.-- extract2) are defined in the Data Pipeline painter to commit all rows. As a result, the Start function (or the Repair function, described below) will pipe every appropriate row and then issue a commit. Alternatively, the user can define a pipeline object that periodically issues commits as rows are being piped, such as after every 10 or 100 rows. If the Cancel function is called, the Commit attribute in the Data Pipeline painter determines what to do, as follows.
______________________________________
If a user's Commit value is
Then Cancel does this
All Rolls back every row that was piped by the
current Start function (or Repair function)
A particular number of
Commits every row that was piped up to
rows (such as 1, 10, or 100)
the moment of cancellation
______________________________________
This is the same commit/rollback behavior that occurs when a pipeline reaches a Max Errors limit (which is also specified in the Data Pipeline painter). J. Handling row errors When a pipeline executes, it may be unable to write particular rows to the destination table. This can happen, for instance, with a row that has the same primary key as a row already in the destination table. 1. Using the pipeline-error DataWindow To help a user handle such error rows, the pipeline places them in the DataWindow control a user painted in his/her window and specified in the Start function. It does this by automatically associating its own special DataWindow object (the PowerBuilder pipeline-error DataWindow) with a user's DataWindow control. Consider what happens in the sample Order Entry application. When a pipeline executes in the w.sub.-- sales.sub.-- extract window, the Start function places all error rows in the dw.sub.-- pipe.sub.-- errors DataWindow control, as illustrated in FIG. 16. The control includes an error message column to identify the problem with each row. Once there are error rows in a user's DataWindow control, a user needs to decide what to do with them. A user's alternatives include "repairing" some or all of those rows or "abandoning" some or all of those rows. 2. Repairing error rows In many situations, it is appropriate to try fixing error rows so that they can be applied to the destination table. Making these fixes typically involves modifying one or more of their column values so that the destination table will accept them. This can done by letting the user edit one or more of the rows in the error DataWindow control, or by executing script code in a user's application that edits one or more of the rows in the error DataWindow control for the user. In either case, the next step is to apply the modified rows from this DataWindow control to the destination table. To apply row repairs to the destination table, the user adds a Repair function in an appropriate script. In this function, a user will specify a transaction object for the destination database. The results of the Repair function are tested for success. Suppose, for the Anchor Bay Nut Company example, that users are allowed to edit the contents of the dw.sub.-- pipe.sub.-- errors DataWindow control to fix error rows that appear. Accordingly, a mechanism must be provided to end users to apply those modified rows to the destination table. When painting the w.sub.-- sales.sub.-- extract window, a CommandButton control named cb.sub.-- applyfixes is added (shown at 1603 in FIG. 16). Script commands are added to enable this CommandButton when dw.sub.-- pipe.sub.-- errors contains error rows and to disable it when no error rows appear. A script for the Clicked event of cb.sub.-- applyfixes is created. The following script calls the Repair function and tests whether or not it worked properly.
______________________________________
IF iuo.sub.-- pipe.sub.-- logistics.Repair(itrans.sub.-- destination) &
<> 1 THEN
MessageBox("operation Status", &
"Error when trying to apply fixes.")
END IF
______________________________________
The Repair function commits (or rolls back) database updates in the same way that the Start function does. The end user of the application can click the cb.sub.-- applyfixes CommandButton to try updating the destination table with one or more corrected rows from dw.sub.-- pipe.sub.-- errors. If appropriate, a user can cancel pipeline execution while row repairs are being applied. Sometimes after the Repair function has executed, there may still be error rows left in the error DataWindow control. This may be because these rows were modified by the user or application, but still have errors; were not modified by the user or application; or were never written to the destination table because the Cancel function was called (or were rolled back from the destination table following the cancellation). At this point, the user or application can try again to modify these rows and then apply them to the destination table with the Repair function. Alternatively, the end user can abandon one or more of these rows. 3. Abandoning error rows In some cases, end user should be enabled to completely discard one or more error rows from the error DataWindow control. This can be useful for dealing with error rows that are not desirable to repair. Techniques a user can use for abandoning such error rows include these:
______________________________________
If a user wants to abandon
Use
All error rows in the error
The Reset function
DataWindow control
One or more particular error rows in
The RowsDiscard function
the error Data Window control
______________________________________
For the Anchor Bay Nut Company example, suppose end users can abandon all error rows in the dw.sub.-- pipe.sub.-- errors DataWindow control. This is implemented as follows. When painting the w.sub.-- sales.sub.-- extract window, the designing user includes a CommandButton control named cb.sub.-- forgofixes, shown at 1605 in FIG. 16. Code is added to the application's scripts to enable this CommandButton when dw.sub.-- pipe.sub.-- errors contains error rows and to disable it when no error rows appear. A script for the Clicked event of cb.sub.-- forgofixes is added. This script calls the Reset function as follows. dw.sub.-- pipe.sub.-- errors.Reset () Together, these features let an end user of the application click the cb.sub.-- forgofixes CommandButton to discard all error rows from dw.sub.-- pipe.sub.-- errors. K. Final housekeeping After a user's application is all done processing pipelines, the resources obtained at the beginning to support pipeline execution are released. This occurs as follows. The instance that a user created of his/her supporting user object is destroyed. To do this, a DESTROY statement is added in an appropriate script, specifying the name of the variable that contains that user-object instance. The application disconnects from the pipeline's source and destination databases. To do this, two DISCONNECT statements are added in an appropriate script. In one, the name of the variable that contains a user's source transaction-object instance is specified. In the other, the name of the variable that contains a user's destination transaction-object instance is specified. The results of each DISCONNECT statement are tested. A user's source transaction-object instance and a user's destination transaction-object instance are destroyed. This is done by adding two DESTROY statements in an appropriate script. In one, the name of the variable that contains a user's source transaction-object instance is specified. In the other, the name of the variable that contains a user's destination transaction-object instance is specified. For the Anchor Bay Nut Company example, cleanup entails adding code for the Close event of the w.sub.-- sales.sub.-- extract window. At the beginning of this script, the following statement is added to destroy the instance of the user object u.sub.-- sales.sub.-- pipe.sub.-- logistics (which is stored in the iuo.sub.-- pipe.sub.-- logistics variable). DESTROY iuo.sub.-- pipe.sub.-- logistics To disconnect from the source database, the following statements are added to disconnect from the source database, test the result of the disconnection, and destroy their source transaction-object instance (which is stored in the itrans.sub.-- source variable).
______________________________________
DISCONNECT USING itrans.sub.-- source;
// Check result of DISCONNECT statement.
IF itrans.sub.-- source.SQLCode = -1 THEN
MessageBox("Database Connection Error"&
"Problem when disconnecting from ", +&
"the source (Sales) database. " +&
"Please call the Anchor Bay support team." +&
".about.n.about.r.about.n.about.rDetails follow: " +&
String(itrans.sub.-- source.SQLDBCode) + " " +&
itrans.sub.-- source.SQLErrText)
END IF
DESTROY itrans.sub.-- source
______________________________________
To disconnect from the destination database, the following statements are added to disconnect from the destination database, test the result of the disconnection, and destroy the destination transaction-object instance (which is stored in the itrans.sub.-- destination variable).
______________________________________
DISCONNECT USING itrans.sub.-- destination;
// Check result of DISCONNECT statement.
IF itrans.sub.-- destination.SQLCode = -1 THEN
MessageBOX("Database Connection Error", &
"problem when disconnecting from " +&
"the destination (Sales) database. " +&
"Please call the Anchor Bay support team." +&
".about.n.about.r.about.n.about.rDetails follow: " +&
String(itrans.sub.-- destination.SQLCode) + " " +&
itrans.sub.-- destination.SQLErrText)
END IF
DESTROY itrans.sub.-- destination
______________________________________
L. Methodology summary A method of the present invention for copying (or moving) data from a source data source to a destination data source, comprises selecting source and destination database profiles for the source and destination data sources, each profile comprising sufficient information for supporting a connection to a respective one of the data sources, establishing a connection to the source and destination data sources, displaying a graphical user interface for graphically defining a pipeline object, the pipeline object specifying copying of data from the source data source to the destination data source, saving the pipeline object to a storage device, and copying data from the source data to the destination data by executing the pipeline object. Appended herewith as Microfiche Appendix A are source listings providing additional description of the present invention. Further description is provided in PowerBuilder Chapter 12: Piping Data Between Data Sources, pp. 361-389, appended as Appendix C in the provisional parent application. Additional description is provided in Horwith, M., The Pipeline Object Eases Batch Data Updates, Data-based Adviser, November 1995, appended as Appendix D in the provisional parent application. Still further description is provided in PowerBuilder Chapter 2: Designing an Application, pp. 19-77, appended as Appendix A in the provisional parent application. While the invention is described in some detail with specific reference to a single preferred embodiment and certain alternatives, there is no intent to limit the invention to that particular embodiment or those specific alternatives. Thus, the true scope of the present invention is not limited to any one of the foregoing exemplary embodiments but is instead defined by the appended claims.
|
Same subclass Same class Consider this |
||||||||||