JP2004126680A - Sql masking type database access method and computer program - Google Patents

Abstract

<P>PROBLEM TO BE SOLVED: To provide an SQL masking type access method for allowing even a person who does not know SQL at all to easily perform access to a relational database. <P>SOLUTION: An SQL masking type access method comprises a step for inputting a request action from an input screen 26, a step for inputting an access condition from the input screen 26, a step for generating a management information table 22 according to an inputted access condition, a step for automatically generating a SQL sentence based on the inputted request action and data read from the management information table 22, a step for issuing the SQL sentence to a relational data base, and for acquiring the retrieval result, a step for developing the acquired retrieval result to a memory 25, and a step for list-displaying the retrieval results read from the memory on a display screen 27. <P>COPYRIGHT: (C)2004,JPO

Description

[0001]
TECHNICAL FIELD OF THE INVENTION
The present invention relates to a relational database access method, that is, a method of referring, updating, adding and deleting data in a relational database management system (RDBMS).
[0002]
[Prior art]
When accessing a relational database such as referring to, updating, adding, and deleting data, a language for querying and operating the relational database, usually called SQL, is used. SQL (Structured Query Language) is a structured query language developed by IBM Corporation in the United States.
[0003]
However, in order to make full use of SQL, it is necessary to learn and understand SQL, and not everyone can freely write an SQL sentence. Therefore, a tool and a simple language for automatically creating an SQL sentence have been developed (for example, see Japanese Patent Application Laid-Open No. 5-2611). However, their use also requires some knowledge and understanding of SQL. Also, such tools and simple languages are often developed for specific databases and are not universal.
[0004]
FIG. 17 is a conceptual view showing a conventional database table access method. Conventionally, when a database table access process is described in an SQL statement and incorporated in a high-level language, an API (application program interface) is created for each table as shown in FIG. There is a need to do.
[0005]
[Problems to be solved by the invention]
SUMMARY OF THE INVENTION The present invention has been made in view of the above-described problems, and even a person who has no knowledge of SQL can easily access a relational database, and it is necessary to create an access program for each table constituting the database. It is an object of the present invention to provide a SQL concealment type database access method and a computer program without any.
[0006]
[Means for Solving the Problems]
An SQL concealed database access method according to the present invention is a database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database, and a step of inputting an access condition from an input screen. Generating a management information table according to the input access condition; inputting a request action from an input screen; automatically generating an SQL statement based on the input request action and data read from the management information table. Generating, issuing the SQL statement to a relational database and obtaining a search result, expanding the obtained search result in a memory, and displaying a list of the search results read from the memory on a display screen To do And having a flop.
[0007]
According to such a method, even a person who has no knowledge of SQL can easily access the relational database, and it is not necessary to create an access program for each table constituting the database. If predetermined information is registered in the management information table, it can be used for a plurality of types of SQL statements. Also, when issuing a plurality of SQL statements to an RDBMS (relational database management system), the RDBMS only needs to return search results collectively by batch processing, so that overhead can be reduced.
[0008]
Preferably, a table name, search column information, setting data information, narrowing key information, and sort column information are registered in the management information table. Some or all of this information is used depending on the type of the SQL sentence.
[0009]
Further, a computer program according to the present invention is a computer program executed by a computer to realize an SQL concealed database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database. Inputting an access condition from an input screen, generating a management information table in accordance with the input access condition, inputting a requested action from the input screen, inputting the requested action and the management information Automatically generating an SQL statement based on the data read from the table, issuing the SQL statement to a relational database and obtaining a search result, and expanding the obtained search result in a memory. When, characterized in that to execute a process including the step of displaying a list of search results read from the memory on the display screen to the computer.
[0010]
By installing such a computer program in a computer system such as a personal computer and executing the same, the effects of the above SQL concealed database access method can be achieved.
[0011]
Such a computer program is supplied in a state of being recorded on a computer-readable recording medium such as a CD-ROM, and is installed on a computer from the recording medium and executed. Alternatively, it can be downloaded from another computer such as a server connected to a network, installed, and executed sequentially.
[0012]
BEST MODE FOR CARRYING OUT THE INVENTION
Hereinafter, embodiments of the present invention will be described with reference to the drawings.
[0013]
FIG. 1 is a block diagram showing a configuration example of a computer system for realizing a database access method according to an embodiment of the present invention. The computer system 1 has a connection function to a network 4 and can access an RDBMS (relational database management system) 2 via the network 4. A computer program for accessing an RDBMS (hereinafter, sometimes simply referred to as a database) 2 to perform reference, update, addition and deletion of data together with an OS (operating system) and various application software is installed in the computer system 1. Is done. This computer program is supplied in a state recorded on a recording medium 6 such as a CD-ROM (optical disk), and is installed in an auxiliary storage device (HDD) 13 of a computer main body 10 via a reading device 7.
[0014]
The computer program installed in the auxiliary storage device 13 is loaded into the main memory 12 and executed by the processing device 14. The computer main body 10 includes an input processing unit 15a, a display processing unit 16a, and a communication unit 17 in addition to the main memory 12, the auxiliary storage device 13, and the processing device 14. An input device 15 such as a keyboard and a mouse is connected to the input processing unit 15a, and a display device 16 such as a CRT (cathode ray tube) and an LCD (liquid crystal display device) is connected to the display processing unit 16a. The communication unit 17 is connected to the network 4, and the computer system 1 is connected to the database 2 and the like via the network 4.
[0015]
FIG. 2 is a conceptual diagram showing a database access method according to the embodiment of the present invention, and corresponds to the conventional conceptual diagram shown in FIG. In the configuration of this embodiment, the table access processing is generalized, and it is not necessary to create an API (application program interface) for each table 21 of the database.
[0016]
In FIG. 2, the SQL statement automatic generation unit 23 and the SQL statement issuance and search result acquisition unit 24 are configured by programs. Access conditions such as a search table name, a search item, and a search condition input from an input screen 26 displayed on the display device 16 are registered in a general-purpose table (also referred to as a management information table) 22 stored in the auxiliary storage device 13. Is done. The input reference, update, addition and deletion request actions are directly given to the SQL sentence automatic generation unit 23.
[0017]
The SQL sentence automatic generation unit 23 automatically generates an SQL sentence according to the data read from the general-purpose table 22 and the input requested action, and gives the SQL sentence and the search result acquisition unit 24. The SQL statement issuance and search result acquisition unit 24 issues an SQL statement to each table 21 of the relational database and acquires a search result. The result data is temporarily stored in the memory 25 (main memory 12), then displayed and output collectively, and displayed on the display screen 27 of the display device 16.
[0018]
FIG. 3 is a diagram showing a configuration example of the general-purpose table (management information table) 22. In the SQL concealment type database access method of the present embodiment, information obtained by various procedures from the user is managed by a general-purpose table 22 as shown in FIG. In FIG. 3, the table name is only one information, but other information (search column information, setting data information, etc.) is a plurality of information. The contents of each management information are as follows.
[0019]
The table name is the name of the table to be accessed by the database. The column name described in the search column information is the column name of the search target. In the setting data information, a column name, a column type, and column setting data are described. The column name is the column name of the data to be updated or added. The column type is the column type of the data you want to update or add. The column setting data is the data value of the data to be updated or the data to be added.
[0020]
The narrowing key information includes a column name, a column type, column setting data, a judgment condition, and a logical condition. The column name is a column name to be narrowed down at the time of search, update or deletion. The column type is a column type to be narrowed down when searching, updating, or deleting. The column setting data is the value of the data to be narrowed down at the time of search, update or deletion. The determination condition is a condition such as equality (=), large and small (>, <,> =, <=) for narrowing down data. The logical condition is a condition of a logical product (AND) or a logical sum (OR) of a plurality of determination conditions.
[0021]
A column name and a sort type are described in the sort column information. The column name is the column name to be sorted. The sort type is an ascending order or a descending order.
[0022]
These pieces of information can be used in common among a plurality of DML (database operation language) statements when performing automatic generation of SQL statements (SELECT, INSERT, UPDATE, and DELETE DML statements) and unique to each DML statement. Can be divided into things. The following list shows which information is necessary for automatically generating which DML sentence.
[0023]
FIG. 4 is a chart showing a list of information necessary for automatically generating each DML sentence. In FIG. 4, a mark “「 ”indicates necessary information, and a mark“ − ”indicates unnecessary information. FIG. 5 is a diagram exemplifying an action selection screen. On the action selection screen of the input screen 26, as shown in FIG. 5, four buttons indicating reference, update, addition and deletion request actions are displayed. The user selects the requested action by pressing any of these buttons. Pressing a reference button generates a SELECT statement, pressing an update button generates an UPDATE statement, pressing an add button generates an INSERT statement, and pressing a delete button generates a DELETE statement.
[0024]
FIG. 6 is a diagram showing a data flow in the database access method of the present embodiment. FIG. 7 shows a flowchart of the corresponding process. In step # 101, when information to be inquired is input from the input screen (including standard input), the input information is set in the general-purpose table 22 in the next step # 102.
[0025]
In step # 103, a DML sentence corresponding to the requested action is selected. That is, the DML sentence corresponding to the requested action selected by the user as described above is selected. The timing at which the user selects the requested action from the action selection screen as shown in FIG. 5 is performed after the information is set in the general-purpose table 22 by the processing of step # 101 and step # 103.
[0026]
In the next step # 104, information is read from the general-purpose table 22, the DML sentence is edited, and an SQL sentence is generated. In the subsequent step # 105, an SQL statement is issued to the database 2, and the search results are returned from the database 2 in a lump. The search results obtained collectively in step # 106 are temporarily expanded (stored) in the memory 25 in step # 107. Then, in step # 108, the search results read from the memory 25 are collectively displayed and output, and displayed on the display screen 27 of the display device 16.
[0027]
Hereinafter, the database access method of the present embodiment will be described with a specific example. FIG. 8 is a chart showing a product table as an example of a database. Reference (SELECT), addition (INSERT), update (UPDATE), and deletion (DELETE) of the database (product table) illustrated in FIG. 8 will be sequentially described as an example.
[0028]
First, in order to automatically generate a SELECT statement for referring to a database, management information of “table name”, “search column information”, “narrowing key information”, and “sort column information” is used. For example, an SQL statement to be created when referring to the product code, product name, and price of a pencil with a cost of 7 yen or more from the product table in ascending order of the product code is “SELECT product code, product name, price FROM Product WHERE Cost> 7 AND Product name = “Pencil” ORDER BY Product code ASC ”.
[0029]
FIG. 9 is a diagram illustrating an example of input information for referring to a product table in a database. FIG. 10 is a diagram showing the relationship between the management information table (general-purpose table) 22 registered in accordance with the input information and the automatically generated SELECT statement 31. In this example, by inputting the information in FIG. 9 from the input screen 26, the management information table 22 as shown in FIG. 10 is registered, and the SELECT statement 31 is automatically generated.
[0030]
Next, to automatically generate an INSERT statement for adding data to the database, management information of “table name” and “setting data information” is used. For example, when adding product information of product code: 21, product name: pencil 2B, price: 10 yen, cost: 8 yen to the product table of the database, the SQL statement to be created is “INSERT INTO product (product code , Product name, price, cost) VALUES (21, 'pencil 2B', 10, 8) ".
[0031]
FIG. 11 is a diagram illustrating an example of input information for adding data to the product table of the database. FIG. 12 is a diagram showing the relationship between the management information table (general-purpose table) 22 registered in accordance with the input information and the automatically generated INSERT statement 32. In this example, by inputting the information of FIG. 11 from the input screen 26, the management information table 22 as shown in FIG. 12 is registered, and the INSERT statement 32 is automatically generated.
[0032]
Next, in order to automatically generate an UPDATE statement for updating data in the database, management information of “table name”, “setting data information”, and “narrowing key information” is used. For example, when the price of the pencil HB becomes 9 yen and the cost becomes 5 yen, the SQL statement to be created for updating the data in the database is “UPDATE product SET price = 9, cost = 5 WHERE product name = 'Pencil HB'.
[0033]
FIG. 13 is a diagram illustrating an example of input information for updating the product table of the database. FIG. 14 is a diagram showing the relationship between the management information table (general-purpose table) 22 registered in accordance with the input information and the UPDATE statement 33 automatically generated. In this example, by inputting the information of FIG. 13 from the input screen 26, the management information table 22 as shown in FIG. 14 is registered, and the UPDATE statement 33 is automatically generated.
[0034]
Next, in order to automatically generate a DELETE statement for deleting data in the database, management information of “table name” and “narrowing key information” is used. For example, an SQL statement to be created when data of a pencil with a cost of 8 yen or more is to be deleted is “DELETE FROM product WHERE cost> = 8 AND product name =“ pencil ””.
[0035]
FIG. 15 is a diagram illustrating an example of input information for deleting data from the product table of the database. FIG. 16 is a diagram showing the relationship between the management information table (general-purpose table) 22 registered according to the input information and the DELETE statement 34 automatically generated. In this example, by inputting the information in FIG. 15 from the input screen 26, the management information table 22 as shown in FIG. 16 is registered, and the DELETE statement 34 is automatically generated.
[0036]
In the above example, the data registered in the management information table (general-purpose table) can be used in common even if the SQL statement changes as long as there is no change. When a part of data is to be changed, only the data of the part to be changed need be input. Thus, once information is registered in the management information table (general-purpose table), it can be used for a plurality of types of SQL statements.
[0037]
In addition, the specific processing contents and processing procedures described in the above embodiment are merely examples. The present invention is not limited to the above embodiments, but can be implemented in various forms.
[0038]
(Supplementary Note 1) An SQL concealed database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database,
Inputting access conditions from an input screen;
Generating a management information table according to the input access condition;
Inputting a request action from an input screen;
Automatically generating an SQL statement based on the input requested action and data read from the management information table;
Issuing the SQL statement to a relational database and obtaining a search result;
Expanding the obtained search results in a memory;
Displaying a list of search results read from the memory on a display screen.
[0039]
(Supplementary note 2) The SQL concealment type database access method according to supplementary note 1, wherein a table name, search column information, setting data information, narrowing key information, and sort column information are registered in the management information table.
[0040]
(Supplementary Note 3) A computer program executed by a computer to implement an SQL concealed database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database,
Inputting access conditions from an input screen;
Generating a management information table according to the input access condition;
Inputting a request action from an input screen;
Automatically generating an SQL statement based on the input requested action and data read from the management information table;
Issuing the SQL statement to a relational database and obtaining a search result;
Expanding the obtained search results in a memory;
Causing a computer to execute a process including a step of displaying a list of search results read from the memory on a display screen.
[0041]
(Supplementary note 4) The computer program according to supplementary note 2, wherein a table name, search column information, setting data information, narrowing key information, and sort column information are registered in the management information table.
[0042]
(Supplementary Note 5) Computer readable recording a computer program to be executed by a computer to realize an SQL concealed database access method for easily referencing data, updating data, inserting data, and deleting data in a table of a relational database Recording medium, wherein the computer program inputs an access condition from an input screen,
Generating a management information table according to the input access condition;
Inputting a request action from an input screen;
Automatically generating an SQL statement based on the input requested action and data read from the management information table;
Issuing the SQL statement to a relational database and obtaining a search result;
Expanding the obtained search results in a memory;
Displaying a list of search results read from the memory on a display screen.
[0043]
(Supplementary Note 6) Computer-readable recording a computer program to be executed by a computer to realize an SQL concealed database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database Recording medium, wherein the computer program inputs an access condition from an input screen,
Generating a management information table according to the input access condition;
Inputting a request action from an input screen;
Automatically generating an SQL statement based on the input requested action and data read from the management information table;
Issuing the SQL statement to a relational database and obtaining a search result;
Expanding the obtained search results in a memory;
Outputting a search result read from the memory to a storage device;
Communicating between the memories;
Displaying a list on a display screen.
[0044]
【The invention's effect】
As described above, according to the SQL concealment type database access method and the computer program of the present invention, even a person who has no knowledge of SQL can easily access the relational database, and furthermore, each table constituting the database has There is no need to create an access program. If predetermined information is registered in the management information table, it can be used for a plurality of types of SQL statements. Further, when issuing a plurality of SQL statements to the RDBMS, the RDBMS only needs to return the search results collectively by batch processing, so that the overhead can be reduced.
[Brief description of the drawings]
FIG. 1 is a block diagram showing a configuration example of a computer system for realizing a database access method according to an embodiment of the present invention.
FIG. 2 is a conceptual diagram illustrating a database access method according to an embodiment of the present invention.
FIG. 3 is a diagram illustrating a configuration example of a general-purpose table (management information table).
FIG. 4 is a table showing a list of information necessary for automatically generating each DML sentence.
FIG. 5 is a diagram illustrating an action selection screen.
FIG. 6 is a diagram showing a data flow in the database access method of the embodiment.
FIG. 7 is a flowchart of a process corresponding to the data flow of FIG. 6;
FIG. 8 is a chart showing a product table as an example of a database.
FIG. 9 is a diagram showing an example of input information for referring to a product table in a database.
FIG. 10 is a diagram showing a relationship between a management information table (general-purpose table) registered in accordance with input information and an automatically generated SELECT statement.
FIG. 11 is a diagram showing an example of input information for adding data to a product table of a database.
FIG. 12 is a diagram showing a relationship between a management information table (general-purpose table) registered in accordance with input information and an automatically generated INSERT statement.
FIG. 13 is a diagram showing an example of input information for updating a product table in a database.
FIG. 14 is a diagram illustrating a relationship between a management information table (general-purpose table) registered in accordance with input information and an automatically generated UPDATE statement.
FIG. 15 is a diagram showing an example of input information for deleting data from a product table of a database.
FIG. 16 is a diagram showing a relationship between a management information table (general-purpose table) registered according to input information and a DELETE statement automatically generated.
FIG. 17 is a conceptual view showing a conventional database table access method.
[Explanation of symbols]
1 computer system 2 RDBMS (database)
6 Recording medium 7 Reader 22 General-purpose table (management information table)
23 SQL sentence automatic generation unit 24 SQL sentence issuance and retrieval result acquisition unit 25 Memory

Claims (5)

An SQL concealed database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database,
Inputting access conditions from an input screen;
Generating a management information table according to the input access condition;
Inputting a request action from an input screen;
Automatically generating an SQL statement based on the input requested action and data read from the management information table;
Issuing the SQL statement to a relational database and obtaining a search result;
Expanding the obtained search results in a memory;
Displaying a list of search results read from the memory on a display screen. 2. The SQL concealed database access method according to claim 1, wherein a table name, search column information, setting data information, narrowing key information, and sort column information are registered in the management information table. A computer program executed by a computer to realize an SQL concealed database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database,
Inputting access conditions from an input screen;
Generating a management information table according to the input access condition;
Inputting a request action from an input screen;
Automatically generating an SQL statement based on the input requested action and data read from the management information table;
Issuing the SQL statement to a relational database and obtaining a search result;
Expanding the obtained search results in a memory;
Causing a computer to execute a process including a step of displaying a list of search results read from the memory on a display screen. 3. The computer program according to claim 2, wherein a table name, search column information, setting data information, narrowing key information, and sort column information are registered in the management information table. A computer-readable recording medium that records a computer program to be executed by a computer to realize an SQL concealed database access method for easily performing data reference, data update, data insertion, and data deletion of a table of a relational database. Wherein the computer program inputs an access condition from an input screen;
Generating a management information table according to the input access condition;
Inputting a request action from an input screen;
Automatically generating an SQL statement based on the input requested action and data read from the management information table;
Issuing the SQL statement to a relational database and obtaining a search result;
Expanding the obtained search results in a memory;
Displaying a list of search results read from the memory on a display screen.

Images