Apparatus and method for annotated bulk copying files to a database6032159Abstract A method of copying files to a database includes the step of constructing a table of file attributes characterizing each file of a set of files stored on a computer. A specific file from the set of files is then identified. A file selected field corresponding to the specific file is then marked within the table. The specific file is then transferred to a database where it is recorded without acknowledgment. A file transfer status field for the specific file is then updated. Claims I claim: Description BRIEF DESCRIPTION OF THE INVENTION
TABLE 1
______________________________________
File Name
File Selected
Transfer Status
Attribute.sub.-- 1
Attribute.sub.-- N
______________________________________
123 1 1 XYX ABC
456 1 0
DEF
789 0 0
GHI
.
.
______________________________________
The example attribute table includes a field for the file name, a field to indicate whether the file has been selected, a transfer status field, and a set of attribute fields (Attribute.sub.-- 1 to Attribute.sub.-- N). Attribute fields may include such attributes as the date and time the file was created. The next step shown in FIG. 3 is to process a new file (step 44). That is, a single file from the set of received files is selected for processing. The table is then marked to indicate that the specific file is being processed (step 46). Referring to the example of Table I, the first column of the first row specifies a file "123". The next column of the first row is a "file selected" field. By way of example, a digital one "1" is used to indicate that the file has been selected and a digital zero "0" is used to indicate that the file has not been selected. The next processing step shown in FIG. 3 is to copy the file to the database (step 48). As discussed below, this operation entails loading data from the test data files 24 in an array, and then bulk copying the array to the database 30. The next processing step shown in FIG. 3 is a determination whether the copy was successful (step 50). This decision is made with error messages associated with the copy operation, as discussed below. In the case of an unsuccessful copy operation, the table is marked accordingly (step 52). In the example table shown above, file 456 has been selected (as indicated by a digital one "1" in the file selected field), but the transfer has not been successful (as indicated by the digital zero "0" in the transfer status field). Since the transfer is not acceptable, the bulk copying operation is terminated (step 54). At this point, the table can be used to determine what has been copied to the database and what has not been copied to the database. Again relying upon the example of Table I above, file "123" has been successfully transferred, file "456" has been selected, but not transferred, and file "789" has not been selected or transferred. File transfer status codes indicative of a variety of file transfer results may be used, as discussed below. If the copy operation is successful, then the bulk copy tracking routine 32 marks the table accordingly (step 56). As indicated above, Table I shows that file "123" has been successfully copied. Thereafter, a new file is selected for processing (step 44) and processing continues. Observe that with the present invention, a file is treated as a unit. If one byte of the file is not successfully transferred, the entire file is considered as not being transferred to the database. While the prior art allows a grouping of records (arguably a "file") to be inserted or extracted from a database, this operation is performed by treating each entry of the grouping as an individual element. That is, for a standard copying operation, if the final element of a group of records is not successfully transferred, the remaining elements associated with the group will still have been transferred into the database and an acknowledgment will have been sent for the entered elements. However, in the case of a bulk copy operation, the database will not generate an acknowledgment as individual elements of the file are loaded into the database. In the case of a bulk copying failure, there is no way to determine what portion of the file is in the database. That is, the prior art bulk copy operation is not annotated, thus, in the case of a system failure there is no reliable method for determining what information has been transferred to the database. With the present invention, when a file transfer fails, all records associated with the unsuccessfully transferred file may be deleted from the database. Although the invention has been fully described, attention presently turns to a more detailed description of the operation of an embodiment of the invention so that the invention can be more fully appreciated. The following computer code in the C++ language is an example of a bulk copy tracking routine 32 in accordance with an embodiment of the invention. More particularly, the following computer code is directed toward step 48 of the bulk copy tracking routine 32. Step 48 relates to the operations of preparing for bulk copying and bulk copying to the database. While the following code explicitly describes an embodiment of step 48, it also serves to illustrate the other processing steps associated with the bulk copy tracking routine 32. Table I above is an example of a file table 34 that may be used in accordance with the invention. After the table is constructed (step 42), and a new file is selected for processing (step 44), and the table is marked to indicate that processing has commenced (step 46), the bulk copying operation (step 48) is performed. The following code shows that the bulk copying operation can be implemented by loading data from a test data file 24 into a data array and then using the bulk copy "bcp" command to load the data array into the database program 30. The initial block of code establishes a class "ExampleTableData". A new class is created for each set of data that is written to the database program 30. The ExampleTableData class has data members and functions. The data members include a number of integer variables (e.g., iField1, iField2) and character variables (e.g., acField3, dField4). The data members are used to define the columns in the database 30 that will be populated. The code also defines a number of functions, including "ExampleTableData();" and "Read (long iFileDescriptor)".
______________________________________
class ExampleTableData
{
public:
int iField1;
int iField2;
char[128]
acField3;
double dField4;
char[64]
acField5;
ExampleTableData( );
.about.ExampleTableData( );
int Read (long iFileDescriptor);
...
}
______________________________________
The following code refers to DBPROCESS, which is a class forming a portion of the "Open Client-DB Library/C" product sold by SYBASE, Incorporated, Emeryville, Calif. DBPROCESS is declared and initialized externally. The term "dproc" is a variable name. The term "*pExampleTableRow" is a pointer into the ExampleTableData described above. The "GetNumberofRows()" method returns an integer value specifying the number of rows in a table. The code also shows the "iRowsRead" variable being set to zero. The code also refers to an unsigned character "cTag", which is a character string describing the contents of a portion of the test data file 24. The remaining declared variables in this program segment are used to implement the bulk copy "bcp" command. These variables are used because an array from the test data file 24 cannot be bound to a table of the database program 30. Instead, selected variables of an array must be bound to corresponding fields of a table in the database program.
______________________________________
extern DBPROCESS dbproc;
int StuffTable ( )
ExampleTableData *pExampleTableRow;
int iNumberOfRows = GetNumberOfRows ( );
int iRowsRead = 0;
unsigned char
cTag;
int iField1;
int iField2;
char[128]
acField3;
double dField4;
char[64] acField5;
______________________________________
The following "if" statement is used to allocate memory. If memory is not available, then NULL is true and an error message is written to the table using the "ExitAndMarkstatusTable()" method. The "ExitAndMarkstatusTable( )" method can be viewed as a method to implement step 52 of FIG. 3. In this case, steps 42-48 of FIG. 3 are performed, but the file transfer is unsuccessful, so the table, e.g., Table I above is marked (step 52) and processing stops (step 54). File "456" of Table I is an example of this processing. The "while" loop in the following code reads the "cTag" and the number of bytes ("sizeof") associated with the "cTag". Depending upon the identity of the "cTag" character string, a selected set of code is executed. By way of example, the following code shows "EXAMPLE.sub.-- TABLE.sub.-- DATA" as the "cTag" and a set of operations associated with this variable. The operations include reading an "iFileDescriptor" and incrementing the "iRowsRead" variable. The reading of the "iFileDescriptor" operates to load data into the ExampleTableRow so that this data can be subsequently transferred by the bulk copying operation. All rows are read in this manner until the end of file ("EOF") character is read, in which case the loop is exited. At this point, all rows of the test data file 24 have been read. The number of rows is associated with the variable "iRowsRead".
______________________________________
if( ( pExampleTableRow = new ExampleTableData [ iNumberOfRows ])
== NULL )
ExitAndMarkstatusTable ( );
}
while ( 1 )
{
read ( iFileDescriptor, &cTag, sizeof( unsigned char ) );
switch ( cTag )
{
...
case EXAMPLE.sub.-- TABLE.sub.-- DATA:
{
pExampleTableRow [ iRowsRead ] . Read ( iFileDescriptor );
iRowsRead ++;
}
...
case EOF:
break;
}
}
______________________________________
As indicated above, at this processing point it is known how many rows will have to be written to "ExampleTable". The "dbfreebuf(dbproc)" statement is used to initialize the bulk copy "bcp" command. The first "if" command performs an initialization operation. If the operation fails, an error message field of the file table 34 (Table I above) is marked to indicate "Can not do bcp.sub.-- int". The second "if" clause is used to bind all local variables to corresponding column names in the database 30 that will be written to. This is done by writing the variable "iField1" to "dbproc", which allows the bulk copy "bcp" command to write the information to the database program 30. If this operation cannot be performed, an error message field of the file table 34 is marked to indicate "Can not bind TABLE.sub.-- NAME (Field1)".
______________________________________
...
// Initialize BCP
dbfreebuf( dbproc );
if( bcp.sub.-- init ( dbproc,
"DB.sub.-- NAME..TABLE.sub.-- NAME", NULL, NULL, DB.sub.-- TN )
== FAIL )
ExitAndMarkstatusTable ( "Can not do bcp.sub.-- init[ );
if( bcp.sub.-- bind( dbproc,
( unsigned char * )&iField1,
0,
-1,
NULL,
0,
SYBINT2,
1) ==FAIL )
ExitAndMarkstatusTable ( "Can not bind TABLE.sub.-- NAME ( Field1 )"
______________________________________
);
The following code is used to send data to the database program 30. The "for" loop circulates until the "iRowsRead" integer value is reached. The first statement in the "for" loop is to assign previously bound variables to fields in a row of a database table. The first "if" clause in the "for" loop is used to send an array row to the database program 30 using the bulk copy "bcp" command. If the operation fails, an error message field of the file table 34 is marked "Can not send row to TABLE1". The subsequent program statement actually writes the data to the database program 30 and returns an integer number "i" indicating the number of rows successfully written. If the correct number of rows was written, as checked with the "if (i!=iRowsRead)" statement, then processing continues. Otherwise, the file table 34 is marked to indicate "BCP to TABLE1 failed". Processing continues until all rows have been processed. If all rows are successfully processed, then the "return SUCCESS" command is executed. This operation calls a method to mark the file table 34 to indicate that the transfer was successful. This operation can be viewed as step 56 of FIG. 3. At this point, a new file in the table 34 is selected (step 44) and processing continues in the manner previously described.
______________________________________
for ( int = 0; i < iRowsRead, i++ )
iField1 = pExampleTableRow[i].iField1
if( bcp#sendrow( dbproc ) --=FAIL)
{
ExitAndMarkstatusTable ( "Can not send row to TABLE1");
}
}
i = (int) bcp done ( dbproc );
if( i!=iRowsRead )
{
ExitAndMarkstatusTable ( "BCP to TABLE1 failed" );
}
return SUCCESS;
}
______________________________________
The file table 34 may be implemented in any number of ways. By way of example, the file table has been implemented with the following fields: TesterName (i.e., tester 22), ProductName (i.e., the product being tested), LotName (i.e., the product lot being tested), BeginProgramTime (i.e., the time the test equipment 22 began to execute the testing run), LDBFileSize (i.e., the size of the test data file), EndProgramTime, BashfulName (i.e., the file name associated with the run), PCT22NETTime (i.e., the time that data was transferred from the test equipment 22 to the file server 21), PEDBTransferTime (i.e., the start time of transfer from file server 21 to database 30), StufferSuccessCode (e.g., file transfer status, such as 0=file not seen, 1=transfer started, 2=transfer successful, 3=transfer failed, 4=corrupted file, etc.), StufferDetailCode (i.e., variables characterizing corrupted files), DetailText (i.e., a detailed error message), AlteraLotStepDoneSeqNum (i.e., a pointer to a row in the table), and LastStuffTime (i.e., the time the last file was stuffed). Many of these fields are loaded by the previously described "ExitAndMarkStatusTable" and "SUCCESS" methods. In sum, the bulk copy tracking routine 32 of the invention operates on top of a standard bulk copying protocol. In particular, it generates a table to record file transfer status information. The table can then be used in the case of a database failure or other event which prevents the completion of a bulk copying operation. In such an event, it is possible to discern what information has been transferred to the database. Prior art bulk copy processes cannot make this determination. The invention exploits the speed advantage of bulk copy operations, while providing recoverable information that is not otherwise available with bulk copy operations. The foregoing description, for purposes of explanation, used specific nomenclature to provide a thorough understanding of the invention. However, it will be apparent to one skilled in the art that the specific details are not required in order to practice the invention. In other instances, well known computing devices are shown in block diagram form in order to avoid unnecessary distraction from the underlying invention. Thus, the foregoing descriptions of specific embodiments of the present invention are presented for purposes of illustration and description. They are not intended to be exhaustive or to limit the invention to the precise forms disclosed, obviously many modifications and variations are possible in view of the above teachings. The embodiments were chosen and described in order to best explain the principles of the invention and its practical applications, to thereby enable others skilled in the art to best utilize the invention and various embodiments with various modifications as are suited to the particular use contemplated. It is intended that the scope of the invention be defined by the following claims and their equivalents.
|
Same subclass Same class Consider this |
||||||||||