KR20170077935A

KR20170077935A - An update system for the database by using spreadsheet interface

Info

Publication number: KR20170077935A
Application number: KR1020150187691A
Authority: KR
Inventors: 배영근; 강주희
Original assignee: (주)비아이매트릭스
Priority date: 2015-12-28
Filing date: 2015-12-28
Publication date: 2017-07-07
Also published as: JP2017120610A; KR101769857B1; JP6199439B2

Abstract

The present invention relates to a database updating system using a spreadsheet interface that fetches data from a database and displays it on a worksheet and reflects the updated data on the displayed worksheet to the database. An incoming data requesting unit; A worksheet display unit that provides a spreadsheet interface, generates a worksheet, and displays the structure of the target table and record data on the worksheet; A worksheet updating unit for updating the record data on the worksheet according to a user's input through the spreadsheet interface; An update definition unit that detects an updated record on the worksheet and defines a table structure and record data of the updated record; A query generating unit for generating a query statement for reflecting the table structure of the defined record and the record updated from the record data to the database; And a DB update unit for transmitting the generated query statement to the database to update the target table.
By fetching data in the form of a spreadsheet and directly modifying the table by the above method, the table of the corresponding database is updated, so that the user can update the database without knowledge of the database processing method such as the query statement.

Description

An update system for a database using a spreadsheet interface

The present invention relates to a database update system using a spreadsheet interface that fetches a data set from a database and displays it on a spreadsheet interface system such as Excel and reflects the data modified by the user on the spreadsheet interface to update the database .

Generally, in order to update a database on the Web, a web program for processing the database and a query statement for instructing it are needed. That is, the update operation is performed by the SQL level in the database management system [Patent Document 1].

Specifically, in order to input, modify, or delete data recorded in a specific table in the database, it is updated through a server-side web program such as JavaServer Pages (JSP), Active Server Pages (ASP), or Hypertext Preprocessor (PHP). Specifically, the server-side Web program must connect to a database and create a query statement such as an SQL statement for processing data of the target table. If you send the created query statement to the database and request to process the query, the database processes the received query to update the tables in the database.

Therefore, in order for the web program to process the query of the database, the web screen for each table or item should be configured. In addition, there is a problem that an input screen must be separately developed for the number of target tables. Therefore, each query must be written by the developer himself, so that it takes a lot of time and lots of work. In addition, it is possible to handle only single data or a small amount of data in the created screen, and it is inconvenient for mass data processing and it takes much time to process.

To this end, various techniques for locally modifying a remote database on a network basis have been proposed to more effectively modify the database. As an example, a technology for modifying a database by caching and modifying a part of a multidimensional database on a user device and reflecting the same on a multidimensional database has been proposed (Patent Document 2). In addition, a technique of updating a periodic update using a specific file format such as a sand file has been proposed (Patent Document 3). Further, a technology for processing update of a subset database in a partial database is proposed (Patent Document 4).

However, all of the above prior arts are techniques for modifying another database, which is a part of the database, and reflecting it in the parent database. Therefore, there is a problem in that a database must be configured and processed locally. That is, the local user has to have knowledge to deal with the database.

In addition, a technique for integrating a large amount of data into a database has been proposed [Patent Document 5]. That is, it is a technique of performing a processing sequence including a plurality of consecutive jobs for converting a data set into converted data, and changing the database based on the converted data. However, the prior art is a technique of converting new data to a specific format and then converting the data into an original database and storing the original data. Therefore, there is a problem that a general user must configure a large amount of data in a specific format and can not process the data intuitively.

[Patent Document 1] Korean Laid-open Patent No. 10-1999-0038698 (published May 5, 1999) [Patent Document 2] Korean Patent Laid-Open No. 10-2008-0031202 (published on April 4, 2008) [Patent Document 3] Korean Published Patent Application No. 10-2004-0053254 (published on June 23, 2004) [Patent Document 4] Korean Published Patent Application No. 10-2012-0052301 (Published May 23, 2012) [Patent Document 5] Korean Published Patent Application No. 10-2014-0027128 (published on Mar. 03, 2014)

SUMMARY OF THE INVENTION The object of the present invention is to solve the above-mentioned problems, and it is an object of the present invention to provide a method and system for retrieving a data set from a database, displaying it on a spreadsheet interface system such as Excel, And a database updating system using the spreadsheet interface.

In order to achieve the above object, the present invention relates to a database update system using a spreadsheet interface, which is connected to a database via a network, comprising: a data request unit for requesting and fetching a data set of a target table from the database; A worksheet display unit that provides a spreadsheet interface, creates a worksheet and displays the structure of the data set and record data on the worksheet; A worksheet updating unit for updating the record data on the worksheet according to a user's input through the spreadsheet interface and displaying the update status of each record data; An update definition unit for defining a data set structure and record data of the updated record for the record updated on the worksheet; A query generator for generating a query statement for reflecting a data set structure of the defined record and a record updated from the record data to the database; And a DB updating unit for transmitting the created query statement to the database to update the target table.

According to another aspect of the present invention, there is provided a database updating system using a spreadsheet interface, wherein the data requesting unit gives a result obtained as a query for the entirety of the target table or the target table fetched from the database to the worksheet display unit as a data set, Extracts a query result using a query editor that performs a query on the table, and gives the extracted query result to the worksheet display unit as a dataset, and displays the result on the worksheet.

According to another aspect of the present invention, there is provided a database updating system using a spreadsheet interface, wherein the worksheet display unit displays the structure of the data set in a row on the worksheet, And records the data of the records.

In the database update system using the spreadsheet interface, the structure of the target table includes a field name of each data field, and further includes at least one of a display name, a data type, a condition, and a key field .

According to another aspect of the present invention, there is provided a database updating system using a spreadsheet interface, wherein the worksheet display unit displays, for each data field of the data set structure, a cell format or a column format for a column on a worksheet of the data field, And the data type is set to a data type of

According to another aspect of the present invention, there is provided a database updating system using a spreadsheet interface, wherein the worksheet display unit further generates a record field by adding a first update status field for indicating a record update state on the worksheet, The sheet update unit displays the update type in the first update status field of the record when the cell contents of one record on the worksheet is updated and the update definition unit refers to the first update status field on the worksheet, And the type of the update is determined.

Further, the present invention provides a database updating system using a spreadsheet interface, wherein, when the worksheet display unit generates the worksheet, an event is generated when the contents of the records on the worksheet are updated in an execution module linked with the worksheet And a script function that is called and executed by the event, and the script function automatically displays the update type in the first update status field when the record is updated.

According to another aspect of the present invention, there is provided a database updating system using a spreadsheet interface, wherein the update definition unit defines a data set structure as an XML document and defines updated data as a flat file, And the field value of each record is distinguished by the row delimiter.

According to another aspect of the present invention, there is provided a database updating system using a spreadsheet interface, wherein the worksheet display unit further generates a record constituting a second update state field for displaying a field update state on the worksheet, The worksheet update unit displays the update type of the change in the second update status field of the corresponding record field when the cell contents of one record on the worksheet is updated, and the update definition unit updates the key field of the record field and the second update And a structure of the dataset to be updated is generated by constructing only the updated field in the status field.

As described above, according to the database updating system using the spreadsheet interface according to the present invention, when data is fetched in the form of spreadsheet such as Excel, and the data is directly modified, the table of the corresponding database is updated. Therefore, It is possible to update the database without knowledge of the database.

In addition, according to the database updating system using the spreadsheet interface according to the present invention, the database is updated by using a spreadsheet, such as Excel, which is often used for office work, so that the data can be intuitively updated and the database can be updated .

In addition, according to the database updating system using the spreadsheet interface according to the present invention, a user can easily process a large amount of data, thereby creating and analyzing a desired data analysis report, Processing, and the like.

1 is a block diagram of a configuration of an overall system for implementing a database update system using a spreadsheet interface according to the present invention;
2 is a block diagram of an overall system for implementing a database update system using a spreadsheet interface according to the present invention;
3 is a block diagram of a configuration of a database update system using a spreadsheet interface in accordance with an embodiment of the present invention.
4 is an example of a screen for selecting a target table of an object database according to an embodiment of the present invention.
5 is an example of a screen displaying a schema of a table on a worksheet according to an embodiment of the present invention;
6 is an example of a screen displaying data of a table on a worksheet according to an embodiment of the present invention;
7 is an example of a screen displaying a state in which data of a table is updated on a worksheet according to the first embodiment of the present invention;
8 is an example of table information displayed in XML according to the first embodiment of the present invention.
9 is an example of a flat file that defines data to be updated according to the first embodiment of the present invention.
10 is an example of a screen displaying a state in which data of a table is updated on a worksheet according to the second embodiment of the present invention;
11 is an example of table information displayed in XML according to the second embodiment of the present invention.
12 is an example of a flat file that defines data to be updated according to the second embodiment of the present invention;

DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS Hereinafter, the present invention will be described in detail with reference to the drawings.

In the description of the present invention, the same parts are denoted by the same reference numerals, and repetitive description thereof will be omitted.

First, an overall system for implementing a database updating system using a spreadsheet interface according to the present invention will be described with reference to FIG.

1, the overall system for implementing the present invention includes a client 20 installed in a user terminal 10, a renewal server 30 connected to the client 20 via a network, And a database 40 for storing the data. FIG. 2 is a configuration diagram showing the entire system of FIG. Quot; user "shown in Fig. 2 is driven by the client 20 in the configuration in the user terminal 10. [

The client 20 is a client program system installed in the user terminal 10 and has a user interface through a web browser. That is, the user performs processing such as updating data on-line through a screen interface such as a web browser or a web browser. At this time, the user terminal 10 receives a command of the user, executes the command, and displays the processing result on the screen or the web browser.

Meanwhile, the user terminal 10 is a computer terminal having a computing function such as a personal computer (PC), a notebook, a tablet PC, a tablet, a PDA, and a smart phone. The user terminal 10 and the update server 30 are connected via a network so that the client 20 on the user terminal 10 can perform a data processing operation on-line.

In addition, the client 20 requests the update server 30 to process data on-line, such as data request and data update, and obtains the result from the server 30 and displays the result on the web browser. Or transmits the processed data to the update server 30. In particular, the client 20 displays data obtained from the database 40 through an interface of a spreadsheet program such as Excel (a trademark of Microsoft Corporation's spreadsheet software) or receives commands / data. Further, the client 20 transmits the update result of the processed data through the spreadsheet interface to the update server 30. [

Next, the update server 30 fetches the data set from the database 40 and transmits it to the client 20, or updates the database 40 by returning the updated data set from the client 20. In particular, the update server 30 detects newly added, deleted, or modified data in the client 20 and automatically creates a database query statement for processing the changed data. The update server 30 requests the created query statement to the database 40 and requests the result to be reflected in the database 40. [

Meanwhile, the functions of the client 20 and the update server 30 described above are merely examples, and may be variously implemented according to the implementation techniques of the server and the client. That is, the client 20 and the update server 30 are one update system, and their functions can be distributed to each other according to performance.

As another example, the client 20 simply has a web browser function, and all the functions can be built in the update server 30. [ That is, the update server 30 can have both the interface function of the database 40 and the query statement creation function for the modified data, as well as the function of modifying data through a spreadsheet-based interface.

As another example, the update server 30 merely has an interface function with the database 40, and the client 20 can perform not only the data modification function through the spreadsheet-based interface but also the automatic generation of the query statement for the modified data Function.

As another example, without the update server 30, the client 20 directly accesses the database 40 and accesses the above-described database connection, the data modification function through the spreadsheet interface, and the automatic query statement generation function .

Next, the database 60 is a normal database (DB) for storing data, and has a DBMS for managing data, and performs operations such as data storage, deletion, and retrieval through queries. In particular, the database 40 is a commercially available database, and performs a data query service using a general query function for processing a data set.

In particular, the database 40 is a database for storing big data. Also, preferably, the database 40 comprises a relational database (RDB).

Next, a database updating system using a spreadsheet interface according to a first embodiment of the present invention will be described in more detail with reference to FIG. As described above, the automatic database update system is a server-client system implemented by the client 20 of the user terminal 10 and the server 30 connected to the network. Hereinafter, the system implemented in the server 30 will be described.

3, a database updating system using a spreadsheet interface according to an embodiment of the present invention includes a data request unit 31 for requesting a data set of a database, a worksheet 31 for displaying a requested data set on a spreadsheet interface, A worksheet updating unit 33 for updating some data in the data set on the worksheet, an update defining unit 34 for defining the updated contents of the updated data, a query statement for updating the updated data, And a DB updating unit 36 for updating the database with the generated query statement.

First, the data requesting unit 31 requests the database 40 to fetch the data set from the object database. A data set refers to a set of data consisting of a plurality of records composed of the same fields. That is, one record is composed of a plurality of field values, and the fields (columns) of all records have the same field (or column).

Preferably, the dataset is data from one DB table.

Specifically, the data requesting unit 31 requests the database 40 to inquire the table list of the object database, and displays the inquired table list on the screen. The user can select a desired table from the displayed table list on the screen. 4 shows an example of a screen for selecting a table to be imported from the table list.

When one of the tables displayed on the user screen is selected, schema information for the corresponding table is retrieved. The schema of the destination database or target table is a detailed description of how the data in the table is organized. That is, the schema indicates the structure of the table data such as the name of each data field, the display name, the type (kind) of the data, and the condition.

As another example, the data requesting unit 31 may fetch the table data of the object database through the query statement. Preferably, the query at this time is a query for one table, and a key field of the corresponding table must exist in the query result. In this case, when a plurality of fields form a key, all corresponding fields must be included. The data retrieved through the query form a single dataset.

That is, the data request unit 31 fetches the data set from the object database. If you select a table earlier, the dataset that you import is all the data in the table, and if you import the target table into the target table, it is part of the filtered table by the query statement. At this time, preferably, the data of the object database is fetched through a Java DB connection driver (JDBC) or the like. The data schema is also retrieved using the Java DB connection driver.

In another embodiment, the data request unit 31 extracts the query result using a query editor that imports the target table of the target database into the data set and performs a query on the imported data set (or target table) can do. You can then use the extracted query results as a single data set. That is, only the data set of the query result extracted from the worksheet display unit 32 to be described below can be displayed as data on the worksheet. This makes it possible to display a table composed of only fields desired by the user on the worksheet without displaying the entirety of the target table on the worksheet. In addition, by not displaying an unnecessary data field, it is possible to enhance convenience of editing data of a user.

As described above, the data set of the table obtained from the object database, the data set obtained by the query statement, or the data set filtered by the query statement are all the data sets to be updated. This will be hereinafter referred to as an initial data set (first data set).

Next, the worksheet display section 32 provides a spreadsheet interface, creates a worksheet (hereinafter referred to as a worksheet) on a spreadsheet, such as Excel, and displays the data set on the created worksheet. Specifically, a data set retrieved from the destination database is displayed on a worksheet in a spreadsheet. A spreadsheet is a work that is displayed in a tabular form in rows and columns, such as Excel (trademark of Microsoft Corporation), Lotus (Lotus 1-2-3, trademark of Lotus Software Corporation), Hansel And a spreadsheet software program configured to process data in each cell.

First, the worksheet display unit 32 analyzes the schema information (or the structure of the data set) of the target data set and writes the detailed information on each of the fields to the worksheet. To do this, create a new worksheet.

As an example, as shown in Fig. 5, a detailed structure of table data such as a name, a display name, a data type (kind), a condition, and a key field of each data field is displayed according to a column of the worksheet. Further, the type of data corresponding to each column is analyzed, and the display attributes (in particular, the display format for numbers) of each column are corrected to match the type of data (format). For example, if the name of the column is "Birthday" and the format of the field is a date format, set all the columns to date format. If the call is displayed, the data format is displayed accordingly.

In addition, the worksheet display section 32 also generates an execution module linked to a worksheet provided in the spreadsheet when creating the worksheet of the spreadsheet. The execution module is a module (or code) written in a scripting program language such as VBA (Visual Basic for application). The execution module is a module that, when a specific event occurs in a worksheet, executes a function corresponding to each event. Accordingly, when the user performs a specific operation such as deletion, modification, or insertion on the worksheet by the worksheet update unit 33, the function in the execution module is executed according to the job or operation event to perform the related operation.

The worksheet display unit 32 displays the data set to be updated (or the initial data set) on the worksheet of the spreadsheet. As shown in FIG. 6, the detail structure such as the field names of the respective fields of the data is described in columns on the worksheet, and the corresponding field values (or table data ). That is, as shown in FIG. 6, in the data on the spreadsheet (or worksheet), data is displayed in each cell by rows and columns. In this case, each column in the upper row includes a field name (field name) And arranges the records to fit in each field.

That is, the initial data set consists of a number of records, each of which is displayed in a row on the worksheet. Cells corresponding to each column of the record show the field values of the corresponding record field. Particularly, since the data types of the columns of each field are analyzed in advance and all the columns are set in the corresponding format, all the columns of the corresponding records are displayed on the worksheet of the spreadsheet according to the data format.

In the example of FIG. 6, in the case of the EMPLOYEES table, this column (or column) does not allow duplication since EMPLOYEE_ID is the primary key.

In addition, the worksheet display section 32 sets in advance the maximum number of records that can be displayed on one worksheet. Then, when the maximum number is exceeded, the remaining records are created as a new worksheet and displayed on the created worksheet. As an example, millions of data can be defined and entered into the database through the spreadsheet screen, so that the same sheet can contain millions of sheets and multiple sheets.

On the other hand, the worksheet display unit 32 embeds only the main functions of a spreadsheet program such as Excel. That is, the spreadsheet processing operation is performed by the core function of the spreadsheet, and the entire interface is processed by the worksheet display section 32. [ The worksheet updating unit 33 to be described below is also the same.

Further, the worksheet display section 32 additionally displays a column for displaying the update status. Hereinafter, the column is referred to as an update status field or an update status column. Preferably, the first column on the worksheet is set as the update status field. In the example of Fig. 5, the column described as "Check" is set as the update status field.

In addition, the worksheet display section 32 can display a command button for executing a command on the worksheet. Preferably, the command button includes an inquiry button for inquiring a table or a query result, an OK button for updating the database by transmitting the updated data to the server, and the like.

Next, the worksheet updating unit 33 performs an update operation on the data displayed on the worksheet. In particular, the worksheet updating unit 33 performs the data updating operation by the user through the spreadsheet interface screen provided in the worksheet display unit 32 in advance.

That is, the worksheet updater 33 displays the initial data set on the worksheet of the spreadsheet and processes the works on the worksheet of the spreadsheet being processed by the user. For example, a user may modify data in a particular cell, insert a particular row, or delete a specific row on a spreadsheet. At this time, the interface is processed in the same manner as a normal spreadsheet interface.

Each function of the worksheet updating unit 33 is implemented by an execution module (a program module including a script language such as VBA) linked to a worksheet. That is, when the user performs a specific operation such as modification, insertion, deletion, etc. on the worksheet, the operation is judged as one event and the function of the execution module corresponding to the operation event is executed. This functionality is provided by the core functionality of the spreadsheet. Hereinafter, for the sake of convenience of explanation, all changes in the contents of data due to modification, deletion, insertion (new addition), etc. are described as being "updated".

The worksheet updating unit 33 displays the type of update in the cell of the update status field according to the type of editing or the type of update when an editing job is generated on the worksheet. Preferably, the update type is indicated by a symbol or an update type symbol. The update type symbol is a preset symbol. The update type indicates the update status or update type of the corresponding record or the corresponding row, and indicates the update status of each record such as modification, insertion, deletion, and the like. For example, when a cell of a data is modified, 'U' is displayed in the update status column (column) of the corresponding row, and 'C' is displayed when the row is added.

In addition, the worksheet update unit 33 provides the update status field so that the user directly displays the update status or update type symbol. As an example, the user may enter 'D' in the update status column of the row (or record) to be deleted. At this time, a combo box is formed in each cell of the update status column (column) so that the update status can be directly set for each record (or row).

Preferably, no value or symbol is displayed in the update status field in a record (or row) in which no item is updated.

FIG. 7 shows a state in which a part of the record data in FIG. 6 is updated. That is, the record of the key 104 is deleted, the records 105, 106 and 108 are respectively changed, and the changed data are the salary and the phone number (phone_number). Therefore, the update field (check 1) displays D, U, U, and U, respectively.

Next, the update definition unit 34 detects the updated data on the worksheet or the worksheet, and defines the update contents with respect to the detected data (or record). Preferably, it is defined as an XML document, and the data is composed of a flat file.

The worksheet updating unit 33 can previously modify the data set on the worksheet in units of cells, ranges, rows, or columns. That is, it is possible to modify data in one cell, delete a whole row (or record), etc. (that is, display the deletion symbol (D) in the update status field of a record to be deleted). Alternatively, a new data record can be inserted and values can be entered in each field.

Further, the update definition unit 34 defines update contents for the detected update records according to the defined definition rules. At this time, the contents to be defined are the table information indicating the structure or schema of the data or the data set to be updated, and the data to be updated. That is, the update definition unit 34 records (or defines) the update data only for the updated data or the updated record. In other words, data not updated at all is not recorded.

First, the update definition unit 34 defines a data set (or update data structure) by a predetermined format (or definition rule). Preferably, the structure (or update data structure) of the dataset is displayed as an XML document. An example of displaying the structure of a dataset as an XML document is shown in FIG.

The table information or the information of the data set as shown in FIG. 8 includes data on the following entity and its attributes. Preferably, the information on the structure of the table information or the data set includes a table name (automatically designated when a table is selected on the user screen), a DB connection code (automatically specified when a DB is connected on the user screen) Data type, whether to allow null values, and so on.

(1) ExecuteInfo: Database information to be executed

- ConnectionCode: Database code defined on the server.

(2) DataTable: table information to be updated, multiple tables can be processed in the same transaction

- Name: table name

- Caption: Display name

- TargetTable: the name of the table to be updated

(3) Column: Column information of the table (or data set)

- Name: Column name

- Caption: Column display name

- Constraint: key information P: Primary key V: not null N: nullable

- DataType: Data type C: String N: Numeric type, D: Data type

- UpdateFlag: Whether to include the column when generating the Update query

(4) Rows: Define data to process

- RowIndexCol: the column where the data row serial number will be located

- RowStateCol: Column to place the separator code to process the corresponding item in data handling

- TextType: The location of the data to be processed. FILE: Sent as an attachment. XML: Internal xml

The column information or the field information of the data set may represent the entire field information of the target table or may represent a part thereof. That is, if the target table is partially retrieved through a query, the field structure is generated for only some of the fields of the table.

In addition, the update definition unit 34 records the data to be updated in a predetermined format and generates the file. Preferably, the update definition unit 34 also generates updated data on the worksheet as a flat file. A flat file is a normal file that consists of a series of strings of data.

Fig. 9 shows an example of a flat file. Preferably, the data defined in the spreadsheet screen is created as a text file of the form shown in FIG. 9 by the server, the column and the row delimiter defined in advance, and transmitted to the server. Here, as an example, column delimiters are denoted by " | ", and row delimiters are denoted by an "enter (or new line)"

In particular, in FIG. 9, the type of the update is displayed at the top of each line of how the corresponding record (corresponding row) is to be processed. Each update status symbol is as follows.

C: New input U: Data update D: Data deletion

Next, the query generation unit 35 receives the table information and the update document in which the data to be updated is defined, and generates a query statement for the updated data.

That is, the query generation unit 35 analyzes the contents of the update document that defines the table information, and extracts database, table, and column information to be updated. The extracted information is used to generate an input query, an update query, a query for a delete query, and the like.

In addition, the query generator 35 reads the data for each record, and generates an actual query for each record. At this time, a query type of a corresponding kind (for example, additional query statement, update query statement, delete query statement, etc.) is generated according to the update type of each record. In particular, the query generation unit 35 can receive the data to be updated as a stream, and generates the query statement in real time based on the contents of the record to be updated in the order received in the stream.

Next, the DB updating unit 36 requests the database 40 to generate the created (created) query, and requests the database 40 to update the data of the target table.

The DB updating unit 36 can transmit the query statements generated by the above-described actual query statements to the database 40 to request update. However, if the database 40 requests a query statement for each record individually, the entire updating time and cost are considerably large. This is because the database processes a single request in one transaction, because there are many tasks to perform the transaction.

Therefore, preferably, the DB updating unit 36 batches and batches the actual queries for each of a plurality of records. That is, the query statement is created only once, and the transmission data is processed by mapping the corresponding column value to the variable of the query, adding it to the batch, and finally requesting execution to the database only once. can do.

Next, a database updating system using a spreadsheet interface according to a second embodiment of the present invention will be described in detail with reference to FIG. The second embodiment of the present invention is the same as the first embodiment, except that the update status field is displayed on one row in addition to the columns on the worksheet.

As shown in Fig. 10, the worksheet display section 32 additionally displays a row indicating the update status. In Fig. 10, a row indicated by "check 2" is a record indicating the update status. That is, one row is added on the worksheet, and the corresponding row is used as a record indicating the update status. This is referred to as a second update status field (or a second update status record). As in the first embodiment, the update status field of the column that displays the update status will be referred to as a first update status field.

Next, when one data field value (or cell value) is changed (updated) on the worksheet, the worksheet updating unit 33 displays the update state in both the first update field and the second update field corresponding to the corresponding field do. At this time, the second update field is displayed when the data value (or the cell value) is changed. Further, when one record is added and inserted on the worksheet (when a new record is added), it is indicated that the second update field corresponding to the cell into which the new value is input is updated to "C" Therefore, the update state is not displayed in the corresponding second update field with respect to a field that is left empty and no value is described. In addition, when the record is deleted, the contents of the second update field are updated with the indication of "U" or "C" That is, the value of the second update field is not changed.

In the example of FIG. 10, the data value is changed is a field of salary and phone number (number) in the records of the keys 105, 106, and 108. Therefore, only the second update field corresponding to these two fields is updated and displayed as "U ".

Next, the update definition unit 34 creates a structure of a dataset or a structure of a schema by adding only the key field and a field (column) indicated by "U" in the second update field on the worksheet.

In the example of FIG. 10, the key field is "EMPLOYEE ID ", and the changed fields are salary and phone number. Therefore, only those fields (or columns) are used to create the structure of the dataset or the structure of the schema. The XML document of the data set structure created according to the above example is shown in FIG.

As shown in FIG. 11, the column field only shows fields for employee ID, telephone number, and salary for xml documents.

In addition, the update definition unit 34 displays only the structure of the updated data set when generating the updated data. That is, when data to be updated is recorded in a predetermined format and is generated as a file, only the field value (column value) corresponding to the key field and the updated field is displayed and configured as a flat file. An example of this is shown in Fig.

Next, the effect of the present invention will be described in more detail.

First, the number of executions of the present invention is not limited.

Since the data registered / modified / deleted by the user is transmitted to the server through the upload stream, it is possible to freely handle the single data to millions of large data. The data information is converted into a flat file separated by a predetermined row and column delimiter and uploaded to the server. The server reads the data from the upload stream, parses it according to the predetermined row and column delimiter, and transmits And binds it to a pre-generated query from the obtained xml information.

Also, mass data can be processed quickly.

The basic format for processing data in the update server is to access the database through the jdbc dirver and then parse the xml information sent from the client to generate a query statement corresponding to input, The client reads the data from the upload stream transmitted from the client and binds the variable to the pre-generated query.

At this time, among the connection objects, PreparedStatement is used to specify the size (Fetch size), and the bound data is added using the PreparedStatement's addBatch method. When the entire data is added, do. The advantage of this method is that it does not execute every time, but executes at once, reducing the load on the database and speeding up the speed.

In addition, DB function (DB function) is provided as a function.

It provides the option to process database function, user creation function, etc. as well as data processed by the user, so that the server provides the function to process the target column processing instead of data binding function.

You can also include queries such as Oracle functions, time functions such as Oracle sequence, sysdate, and DB functions such as rownum.

The invention made by the present inventors has been described concretely with reference to the embodiments. However, it is needless to say that the present invention is not limited to the embodiments, and that various changes can be made without departing from the gist of the present invention.

10: user terminal 20: client
30: update server 31: data request unit
32: worksheet display section 33: worksheet update section
34: update definition unit 35: query generation unit
36: DB updating unit 37:
40: Database

Claims

A system for updating a database using a spreadsheet interface, the system being networked with a database,
A data request unit for requesting and fetching a data set of the target table from the database;
A worksheet display unit that provides a spreadsheet interface, creates a worksheet and displays the structure of the data set and record data on the worksheet;
A worksheet updating unit for updating the record data on the worksheet according to a user's input through the spreadsheet interface and displaying the update status of each record data;
An update definition unit for defining a data set structure and record data of the updated record for the record updated on the worksheet;
A query generator for generating a query statement for reflecting a data set structure of the defined record and a record updated from the record data to the database; And
And a DB updating unit for transmitting the generated query statement to the database to update the target table.

The method according to claim 1,
The data request unit may extract a query result using a query editor that gives a result obtained as a query on the entirety of the target table or the target table obtained from the database to the worksheet display unit as a data set or queries the imported target table And displays the extracted query result on the worksheet as the worksheet display unit as a data set.

The method according to claim 1,
Wherein the worksheet display unit displays the structure of the data set in a row on the worksheet and displays data of the records from a row below the row in which the structure of the data set is displayed. Update system.

The method of claim 3,
Wherein the worksheet display unit sets a cell format or column format for each data field of the data set structure as a data type of the corresponding data field with respect to a column on a worksheet of the data field. Update system.

The method according to claim 1,
Wherein the worksheet display unit further generates a record field by adding a first update state field for indicating an update state of the record on the worksheet,
The worksheet update unit displays the update type in the first update status field of the record when the cell contents of one record on the worksheet is updated,
Wherein the update definition unit refers to the first update status field on the worksheet to determine whether or not to update each record and the update type.

6. The method of claim 5,
Wherein the worksheet display unit includes a script function for generating an event when the contents of the record on the worksheet are updated by an execution module linked with the worksheet when the worksheet is created and called and executed by an event, Function automatically displays the update type in the first update status field when the corresponding record is updated.

6. The method of claim 5,
The update definition unit defines the data set structure as an XML document and defines the updated data as a flat file, wherein the field definition of each record is defined by a column and a row delimiter defined in the flat file in advance A database update system using a spreadsheet interface.

6. The method of claim 5,
Wherein the worksheet display unit further generates a record constituting a second update status field for displaying an updated state of the field on the worksheet,
The worksheet update unit displays the update type in the second update status field of the corresponding record field when the cell contents of one record on the worksheet is updated or updated,
Wherein the update definition unit generates a structure of a dataset to be updated by configuring only the fields updated or updated in the key field and the second update status field of the record field.