JP3670305B2 - Database search statement creation device - Google Patents

Description

[0001]
[Industrial application fields]
The present invention relates to an apparatus for creating a database search sentence from a source program.
[0002]
Many relational database management systems support Structured Query Language (SQL Structured Query Language), and the application program that accesses the relational database is a source program written in a programming language such as COBOL or C or a dedicated simple language. Is obtained by compiling
[0003]
[Prior art]
As shown in FIG. 3A, a source program (for example, a column 1) may be described (for example, the name of a database table (table 1: may be a view table) and the names of columns (columns) (column 1, column 2). 4GL), and the file is transferred to a dedicated compiler.
[0004]
In this case, the compiler generates an application program including an SQL statement having the contents “SELECT column 1, column 2 FROM Table 1 INTO column 1, column 2 work area”.
[0005]
The database user starts the generated application program, and the started application program accesses Table 1 in the database, extracts data necessary for the user job from its column 1 and column 2, and outputs it to the database user. (Normally displayed on the network user's network terminal).
[0006]
After that, if it is necessary to output the column 3 in addition to the columns 1 and 2, the source program is modified as shown in FIG. A part for instructing a search in column 3 is also added.
[0007]
[Problems to be solved by the invention]
When simplifying the source program by describing only the table name in the search instruction part, a work area is automatically prepared for each column, so the work area of the column that is not used in the application program becomes useless. .
[0008]
Also, not all data formats supported by the database management system can be handled by an application program.
[0009]
For this reason, the creator of the source program transfers the names of necessary columns among the columns defined in the data format (attributes) that can be used by the application program to both the output instruction portion and the search instruction portion of FIG. 3A. Or define a view table formed with necessary columns.
[0010]
Also, column names are added to both the output instruction part and the search instruction part as shown in FIG. 3B in response to a request from the database user, and the description of those that are no longer used is deleted from both. Redefine the view table.
[0011]
However, the work of describing all the columns described in the output instruction part in the source program in the search instruction part or defining the view table is a heavy burden on the creator of the source program.
[0012]
The present invention has been made in view of the above-described conventional circumstances, and an object of the present invention is to provide an apparatus capable of reducing the burden on the program creator and increasing the productivity of the program.
[0013]
[Means for Solving the Problems]
In FIG. 1, the apparatus according to the first invention includes a table name extracting means 10 for extracting a description part of a table name from a source program, and a column name indicated by the host variable by extracting a description part of a host variable from the source program. Column name specifying means 12 for specifying the data, work area name generating means 14 for generating the name of the work area necessary for referring to the data of the specified column name, the description part of the extracted table name, the specified column name And a database search statement generation means 16 for generating a database search statement for data search of “SELECT, column name FROM, table name, INTO , name of the work area ” using the name of the generated work area.
[0014]
In FIG. 2, the apparatus according to the second invention cuts out a table name description part from the source program, uses the description part to refer to the table definition of the relational database, and determines column names of usable data formats. Usable column determining means 20, column name specifying means 22 for cutting out the description part of the host variable from the source program and specifying the column name indicated by the host variable and determined to be usable; Using the work area name generation means 24 for generating the name of the work area necessary for referring to the data of the specified column name, the description part of the extracted table name, the specified column name, and the name of the generated work area having a, a database search sentence generating means 26 for generating a database search sentence "SELECT the column name FROM table name INTO the work area entitled" becomes data for retrieval Te .
[0015]
[Action]
The apparatus of the first invention specifies columns (indicated by host variables embedded in the source program) used in the application program, and automatically prepares a work area only for them.
[0016]
The apparatus of the second invention is a column that can be used by an application program (whether or not it can be used can be determined by referring to the table definition of the database), and a column (embedded in the source program). Specified in the host variable), and automatically prepare a work area only for them.
[0017]
Since the work area is secured only for the column used in this way or the column that can be used and actually used, the creator of the source program specifies only the table name in the search instruction part as shown in FIG. Describe and do not define the view table.
[0018]
Then, in order to extract data from column 3 in addition to columns 1 and 2 in Table 1 as in the case of FIG. 3, only the output instruction portion of the source program as shown in FIG. Make a description.
[0019]
As can be understood from the above description, the source program creation (or modification) becomes extremely easy. Therefore, the burden on the program creator can be reduced and the productivity of the source program can be greatly increased.
[0020]
【Example】
FIG. 5 illustrates the configuration of the embodiment. The apparatus shown in FIG. 5 includes a resource information analysis unit 50, a program analysis unit 52, a data area generation unit 54, and an SQL statement generation unit 56.
[0021]
The SQL database 500 shown in the figure is constructed in advance, and when the source program 502 (an example of which is shown in FIG. 6) is created and delivered to the apparatus, the resource information analysis unit 50 starts the process shown in FIG.
[0022]
In the figure, the resource information analysis unit 50 reads the source program 502 (step 700), checks the resource section (see FIG. 6), identifies the table name (Table 1: TABLE1) (step 702), and the table name. Is used to refer to the table definition of the SQL database 500 (step 704).
[0023]
FIG. 8 illustrates the definition contents of Table 1 in the SQL database 500. Although the application program (source program 502) can use columns 1, 2, 3 among columns 1, 2, 3, 4, 5, 4 and 5 cannot be used.
[0024]
From this table definition content, the resource information analysis unit 50 creates the column management table 504 of FIG. 5 (steps 706, 708, 710). FIG. 9A shows the contents of the column management table 504 created from the definition contents of Table 1, and the resource information analysis unit 50 uses the application program (source program) among the columns 1, 2, 3, 4 and 5. 502), the names of columns 1, 2, and 3 that can be used and the state values indicated by ON and OFF indicating whether or not they are used in the source program 502 are associated with each other, and all the state values are set to OFF (step 706, 708, 710).
[0025]
When the resource information analysis unit 50 creates the column management table 504, the program analysis unit 52 starts the processing in FIG. 10, and the resource information analysis unit 50 examines the processing unit (see FIG. 6) in the source program 502 to check the application program ( All the column names used by the source program 502) are specified, and all the state values corresponding to these column names on the column management table 504 are set to ON (steps 1000, 1002, 1004).
[0026]
When the source program 502 of FIG. 6 is created and the table definition of FIG. 8 is performed, the state values of the columns 1 and 2 are turned ON as shown in FIG. Remains off.
[0027]
Further, the data area generation unit 54 performs the processing of FIG. 11 and prepares a work area 506 (data area: variable) only for the column names for which the program analysis unit 52 has the status value ON (steps 1100, 1102, 1104). .
[0028]
For example, when the source program 502 of FIG. 6 is created and the table definition of FIG. 8 is performed, and the status values of the columns 1 and 2 are turned ON as shown in FIG. As shown in FIG. 9C, the name (variable) of the work area (data area) is prepared only for the columns 1 and 2.
[0029]
Then, the SQL statement generation unit 56 performs the processing shown in FIG. 12, and creates the SQL statement 508 using the name of the work area while referring to the contents of the source program 502 and the column management table 504 (steps 1200, 1202, and 1204). , 1206, 1208, 1210, 1212, 1214, 1216, 1218), and an application program (for example, a COBOL language source program or an object program) in which the SQL statement 508 is embedded is generated.
[0030]
As described above, the work area 506 is automatically prepared only for columns that can be used by the application program and that are used by the application program.
[0031]
For this reason, the creator of the source program 502 does not define the view table, but describes only the table name in the processing unit instructing the search as shown in FIG. Therefore, the creation of the source program 502 becomes very easy, and the burden on the program creator is reduced. As a result, the productivity of the source program 502 can be greatly increased.
[0032]
Similarly to the case of FIG. 3, in order to extract data from the column 3 in addition to the columns 1 and 2 in Table 1, only the output instruction part of the source program 502 is described in the column 3 as shown in FIG. The source program 502 can be easily changed.
[0033]
【The invention's effect】
As described above, according to the present invention, a work area is prepared only for a column used in an application program, or only for a column that can be used by the application program and is used in the application program. Therefore, the source program can be created by describing only the table name in the search instruction part without defining the view table. Therefore, the work of creating the source program is facilitated and the burden on the creator is reduced. The production efficiency of development can be greatly improved.
[Brief description of the drawings]
FIG. 1 is a diagram illustrating the principle of the first invention.
FIG. 2 is a diagram illustrating the principle of the second invention.
FIG. 3 is an explanatory diagram of a conventional technique.
FIG. 4 is an explanatory diagram of the operation of the invention.
FIG. 5 is a diagram illustrating the configuration of an embodiment.
FIG. 6 is an explanatory diagram of a source program of the embodiment.
FIG. 7 is a flowchart illustrating processing contents of a resource information analysis unit.
FIG. 8 is an explanatory diagram of a table definition in the database according to the embodiment.
FIG. 9 is an explanatory diagram of a column management table and a data area (work area) in the embodiment.
FIG. 10 is a flowchart illustrating processing contents of a program analysis unit.
FIG. 11 is a flowchart illustrating processing contents of a data area generation unit.
FIG. 12 is a flowchart illustrating processing contents of an SQL sentence generation unit.
[Explanation of symbols]
50 Resource Information Analysis Unit 52 Program Analysis Unit 54 Data Area Generation Unit 56 SQL Statement Generation Unit 500 SQL Database 502 Source Program 504 Column Management Table 506 Data Area (Work Area)
508 SQL statement

Claims (2)

Table name extraction means (10) for extracting a description part of the table name from the source program;
Column name specifying means (12) for extracting the description part of the host variable from the source program and specifying the column name indicated by the host variable;
A work area name generating means (14) for generating a work area name necessary for referring to the data of the identified column name;
Using the description part of the extracted table name, the specified column name, and the name of the generated work area, a database search statement for data search “SELECT, column name FROM, table name, INTO, name of work area” Database search statement generation means (16) to generate;
Having
A database search sentence creation device characterized by that. Usable column determination means (20) for extracting a description part of a table name from a source program, referring to a table definition of a relational database using the description part, and determining a column name of a usable data format;
Column name specifying means (22) for extracting the description part of the host variable from the source program and specifying the column name indicated by the host variable and determined to be usable;
A work area name generating means (24) for generating a work area name necessary for referring to the data of the identified column name;
Using the description part of the extracted table name, the specified column name, and the name of the generated work area, a database search statement for data search “SELECT, column name FROM, table name, INTO, name of work area” Database search statement generation means (26) to be generated;
Having
A database search sentence creation device characterized by that.

Images