JP2002108886A - Database management system - Google Patents

Abstract

PROBLEM TO BE SOLVED: To enable an application to access an RDB via an RDBMS without preparing an SQL statement. SOLUTION: A template having its name 'SELECT-1' is taken out of a template storing part 141 and outputted to an SQL statement production part 144. Meanwhile, an instruction receiving part 142 outputs an instruction number '20000614', i.e., an argument included in the data on the received instruction to the part 144. Receiving the template and argument, the part 144 combines the argument with the template to produce an SQL statement.

Description

DETAILED DESCRIPTION OF THE INVENTION

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to a database management system for accessing a database based on an SQL statement.

[0002]

2. Description of the Related Art Relational Database (RDB)
In a relational database management system (RDBMS), which is software that implements the above model, data is represented in a table format, and a standardized database access language SQL (Structured Query Language) is used. To update or delete data,
When using DBMS, RDBMS using SQL statement
To access. When an SQL statement is input, the RDBMS executes a function corresponding to the SQL statement and outputs an RDB execution result. Since SQL is established by the International Organization for Standardization and Japanese Industrial Standards, it is basically possible to execute any RDBMS as long as it is a SQL statement conforming to the plan. However, in reality, the description format of the SQL statement is slightly different depending on the type of the RDBMS, although it conforms to the above-mentioned standard.

For example, in a database system described in Japanese Patent Application Laid-Open No. 11-25116, an SQL statement described in a program of an application 501 stored in a storage device of a computer (not shown) as shown in FIG. By executing 501a, the RDBMS 50
2 accesses the RDB 503 and returns a predetermined result. In this case, the SQL statement 501a is the RDBM
Since the description format differs depending on the type of S502, the application 501 needs to prepare an SQL statement 501a suitable for the RDBMS 502 used in the program.

[0004]

Therefore, in the above database system, when the type of the RDBMS 502 is changed, it is necessary to change the SQL statement 501a in the program of the application 501.
Every time the MS 502 is changed, the work of changing the application 501 is required, and there is a problem that the work load is large. On the other hand, in the database system described in JP-A-7-65032, as shown in FIG. 6, the database server 602 is provided with an SQL statement conversion unit 602a, and even if the RDBMS 603a is changed, the SQL in the application 501 is changed. The sentence 501a does not need to be changed.

However, even in the database system shown in FIG. 6, since the description of the SQL statement is necessary,
To create a program for the application 501, use SQL
Knowledge for sentence description is required. When the access performance of the database is poor, the structure of the database may be changed. For example, the access performance may be improved by unifying a plurality of tables including a plurality of records into one table. is there. In such a case,
As shown in FIG. 6, even if the SQL statement conversion unit 602a is provided,
The L sentence 501a needs to be changed. Specifically, SQL
It is necessary to change the table name and the like defined in the statement 501a.

As described above, conventionally, when the type or configuration of the RDB is changed, it is necessary to correct or change the SQL statement prepared for the application, and the work load accompanying the change of the RDB is reduced. There was a problem of being big. The present invention has been made to solve the above problems, and has as its object to enable an RDBMS to access an RDB without preparing an SQL statement on the application side.

[0007]

According to the present invention, there is provided a database management system comprising: a database including a plurality of tables each including a plurality of records including a plurality of items and data corresponding to the items; and an access to the database. A database access control unit controlled by an SQL statement, specification of any table in the database,
An item to be accessed in a specified table is described, and a template storage unit storing a plurality of templates composed of SQL statements provided as an input part capable of changing information of the specific item to be accessed is provided. A template corresponding to this access is retrieved from the template storage unit based on the specified access instruction to the database, and information on the specific item designated by the access instruction is added to the input unit of the retrieved template and an SQL statement corresponding to the access instruction Is created, and the database access control unit attempts to access the database using the created SQL statement. According to the present invention, an SQL sentence for accessing a database is created from a template.

In the above invention, there is provided an application program having a function composed of designation of a template stored in a template storage section and information of a specific item added to an input portion of the template. A function may be executed by inputting an access instruction, and an SQL statement corresponding to the access instruction may be created by executing this function. Further, a template creation / change unit that edits a template stored in the template storage unit may be provided.

[0009]

Embodiments of the present invention will be described below with reference to the drawings. <First Embodiment> First, a first embodiment of the present invention will be described. FIG. 1 is a configuration diagram illustrating a configuration of a database management system according to an embodiment of the present invention. First, in the present system, the network 101
Are provided, a plurality of client computers 102 are connected to each other,
For example, an application program 112 such as a factory management program for managing a scheduled completion date of a certain process is provided.

[0010] The network 101 includes a database access server 103 and an SQL creation server 104.
And are connected. Database access server 10
3 is provided with a relational database management system (RDBMS) 103a. The database access server 103 includes a relational database (RDB) 103b including a plurality of tables each including a plurality of records including a plurality of items and data corresponding to the items. RDB
The MS 103a and the RDB 103b are, for example, stored in a storage device such as a hard disk (not shown). The RDBMS 103a controls access to the RDB 103b using a predetermined SQL statement.

The SQL creation server 104 stores the RDBMS1
For example, a template storage unit 141 stores a plurality of templates, which are SQL statements for accessing the storage unit 03a, as electronic data in a storage device (not shown), and stores a predetermined argument in the template read from the template storage unit 141. In addition, a desired SQL statement is completed. The template is configured in the form of an SQL statement that includes specification of one of the tables in the RDB 103b, items to be accessed in the specified table, and information of a specific item to be accessed as an input portion that can be changed. Have been. In the template storage unit 141, for example,
RD for search, update, add, delete, etc. as access target
A template for creating an SQL statement to be performed on B103b is stored.

The SQL creation server 104 includes an instruction receiving unit 142 that receives an instruction such as search and update from the client computer 102, a template extracting unit 143 that extracts a template corresponding to the received instruction from the template storage unit 141, An SQL statement creation unit 144 that completes an SQL statement by incorporating an argument specified in an instruction from the client computer 102 into the template created, and an RDBM
And an SQL statement transmitting unit 145 for transmitting to S103a.

[0013] In addition, the SQL creation server 104
A new template is created in the result transfer unit 146 for transferring data from the BMS 103a to the client computer 102, and in the template storage unit 141.
A template creation / change unit 147 for modifying a stored template is provided. On the other hand, RDBMS103
a operates the RDB 103b according to the SQL sentence transmitted from the SQL creation server 104. For example, when an SQL sentence for searching is transmitted, the data access server 103 searches for data to be searched according to the SQL sentence, and stores the searched data in the SQL creation server 10.
4 to the transfer unit 146.

Hereinafter, the database management system according to the present embodiment will be described in more detail. First, as shown in FIG. 2, for example, a template for search is stored in the template storage unit 141 with a template name “SELECT-
1 ", and the update template is the template name" UPDATE-
1 ", the template for addition is the template name" INSERT-
A plurality of templates, such as "1", are stored together with template names. For example, the template name "SE
The LECT-1 template, as shown below,
103b that includes the specification of any one of the tables 103b and the item to be searched in the specified table, and the information of the specific item to be searched is provided as a changeable input part.
It is configured in the form of an L sentence.

[SELECT-1] select instruction number, start date, item name, quantity, unit from production instruction, item master, unit master where production instruction, item code = item master. Item code and production order. Unit code = unit master. Unit code and production instruction. Order number = '% s'

This SQL statement is based on the instruction number specified by '% s', and stores the data of the instruction number, start date, item name, quantity and unit in each table of the production instruction, item master and unit master. Select from production order, item master,
The item name is obtained from the table of the item master using the item code as a key, and the unit is obtained from the table of the unit master using the unit code as a key. " The production order, item master, and unit master are shown in FIG.
(B) and (c), and the R shown in FIG.
It constitutes the DB 103b.

In the present system having the above-described template, the application program 11 of the client computer 102 is used as described below.
From 2, the RDB 103b is accessed. First, at the client computer 102, the instruction number "20
000614 ”, an instruction to display information of“ start date ”,“ item name ”,“ quantity ”, and“ unit ”is input.
The following functions are executed by the operation of the application program 112. By executing this function, the client computer 102 instructs the SQL creation server 104 as the template name “SELECT-1” and the instruction number “2000061” as an argument (parameter for search).
4 ”is transmitted.

[Function] SqlTmplt = 'SELECT-1' Param (0) = '20000614' ISELECT (SqlTmplt, Param)

In the SQL creation server 104, the instruction receiving unit 142 receives the search instruction data from the client computer 102, and firstly, extracts the template name “SELECT-1” from the received search instruction data into the template extracting unit 143. Hand over to Upon receiving the template name, the template extracting unit 143 extracts the template with the template name “SELECT-1” from the template storage unit 141 and outputs the extracted template to the SQL statement creating unit 144. on the other hand,
The instruction receiving unit 142 outputs the instruction number “20000614” which is an argument in the received search instruction data to the SQL sentence creating unit 144.

SQL that receives template and arguments
The sentence creating unit 144 creates an SQL sentence by combining the received arguments with the template, and transfers this to the SQL sentence transmitting unit 145. The created SQL sentence is, for example, the one in which the instruction number “20000614” is inserted in “% s” of the above-mentioned <SELECT-1> template, as shown below.

[SQL statement] select instruction number, start date, item name, quantity, unit from production instruction, item master, unit master where production instruction, item code = item master. Item code and production order. Unit code = unit master. Unit code and production instruction. Order number = '20000614'

The SQL sentence transmitting unit 145 having received the SQL sentence transmits the SQL sentence to the database access server 103. The RDBMS 103a that has received the SQL sentence transmitted from the SQL sentence transmitting unit 145 accesses the RDB 103b using the received SQL sentence, extracts predetermined data from each table as shown in FIG. Data consisting of “2000-6-4”, item name “product A”, quantity “50”, and unit “Kg” is generated and transmitted to the result transfer unit 146 of the SQL creation server 104.

The result transfer unit 146 receiving the data from the database access server 103 transfers the received data to the client computer 102. Client computer 1 transferred from result transfer unit 146
The data received at 02 is used as a result of executing the above-described function in the application program 112, and is displayed on a display unit (not shown) of the client computer 102, for example. As described above, according to the present embodiment, in the application program 112, the start date “2000-6−6” in the instruction number “20000614” is obtained only by executing the function described above.
4 ", item name" product A ", quantity" 50 ", unit" K "
g ”can be obtained from the RDB 103b.

Second Embodiment Next, another embodiment of the present invention will be described. In the above-described embodiment, the RDB 103b shown in FIG.
Although the table is made up of the table of the item master and the unit master, these may be unified to improve the database accessibility. In this case, RDB
103b is composed of the table of the production order shown in FIG.

As described above, unlike the above-described embodiment, when the table of the item master and the unit master is not used, for example, the template of the template name “SELECT-1” has the following SQL statement format. What is necessary is just to reconfigure. This change is performed by the template creation changing unit 14 shown in FIG.
7 is performed.

[SELECT-1] select instruction number, start date, item name, quantity, unit from production instruction where production instruction. Order number = '% s'

This SQL statement indicates that “data of an instruction number, a start date, an item name, a quantity, and a unit is selected from a table of a production instruction based on the instruction number specified by '% s'”. Is shown. On the other hand, in the application program 112 of the client computer 102, the above-described function may be used without any change. As described above, according to the database management system of FIG. 1, even if the configuration of the RDB 103b or the form of the SQL statement in the RDBMS 103a is changed, the template may be changed in accordance with the change, and the application program 112 No need to change.

[0028]

As described above, according to the present invention,
Since the SQL statement is created and the database access control is performed using the template of the SQL statement in which the data portion of the predetermined item can be changed, for example, in order to realize a desired access from an application program. In this case, since only the specification of the template and the data of the items required for the access need only be specified, an excellent effect that the application can access the database without preparing the SQL statement is obtained.

[Brief description of the drawings]

FIG. 1 is a configuration diagram showing a configuration of a database management system according to an embodiment of the present invention.

FIG. 2 is a configuration diagram illustrating a configuration of a template storage unit in FIG. 1;

FIG. 3 is a configuration diagram showing a configuration of a database of FIG. 1;

FIG. 4 is a configuration diagram showing another configuration of the database of FIG. 1;

FIG. 5 is a configuration diagram showing a configuration of a conventional database management system.

FIG. 6 is a configuration diagram showing a configuration of a conventional database management system.

[Explanation of symbols]

101: network, 102: client computer, 103: database access server, 103a
... Relational database management system (RDBM)
S), 103b ... Relational database (RD)
B), 104: SQL creation server, 112: application program, 141: template storage unit, 14
2: Instruction receiving unit, 143: Template extracting unit,
144 ... SQL sentence creation unit, 145 ... SQL sentence transmission unit, 1
46: Result transfer unit, 147: Template creation / change unit.

 ──────────────────────────────────────────────────続 き The continuation of the front page F term (reference) 5B075 QT06 5B082 GA08 GC04

Claims (3)

[Claims] A database configured from a plurality of tables each including a plurality of records including a plurality of items and data corresponding to the items; a database access control unit configured to control access to the databases by using an SQL statement; A plurality of templates consisting of an SQL statement in which the specification of any table of the database and the item to be accessed in the specified table are described and the information of the specific item to be accessed is provided as a changeable input part A template corresponding to the access from the input instruction for access to the database from the template storage unit, and information of the specific item designated by the access instruction being extracted from the template. In addition to the input section of An SQL sentence corresponding to the access instruction is created, the created SQL sentence is transmitted to the database access control unit, and the database access control unit that has received the created SQL sentence writes the SQL sentence to the database based on the received SQL sentence. A database management system characterized by accessing. 2. The database management system according to claim 1, wherein the application program has a function composed of designation of a template stored in the template storage unit and information of a specific item added to an input part of the template. The function is executed by inputting the access instruction to the application program. By executing the function, a template corresponding to the access is retrieved from the template storage unit and specified by the access instruction. A database management system, wherein information of the specified item is added to an input section of the extracted template, and an SQL statement corresponding to the access instruction is created. 3. The database management system according to claim 1, further comprising a template creation / change unit that edits a template stored in the template storage unit.