JPH07160558A - Data base retrieval sentence preparing device - Google Patents

Abstract

PURPOSE:To provide the device for improving the productivity of a program by reducing the burden of a program preparing person. CONSTITUTION:This device is provided with a table name segmenting means 10 for segmenting the describing part of a table name from a source program, column name specifying means 12 for segmenting the describing part of a host variable from the source program and specifying a column name shown by the host variable, work area name generating means 14 for generating the name of a work area required for referring to the data of the specified column name, and data base retrieval sentence generating means 16 for generating a data base retrieval sentence for data retrieval while using the segmented describing part of the table name, specified column name and the generated name of the work area.

Description

Detailed Description of the Invention

[0001]

BACKGROUND OF THE INVENTION 1. Field of the Invention The present invention relates to an apparatus for creating a database search statement from a source program.

In many relational database management systems, Structured Quer
y Language (SQL structured query language) is supported, and the application program that accesses the relational database is C
It can be obtained by compiling a source program written in a programming language such as OBOL or C or a dedicated simple language.

[0003]

2. Description of the Related Art As shown in FIG. 3A, a database table name (table 1: view table may be used) and a column name (column 1, column 2) are described. A user creates a source program (for example, described in 4GL) and passes the file to a dedicated compiler.

In this case, the compiler is "SELECT"
Column 1, column 2 FROM Table 1 INTO Work area of column 1, column 2 Generates an application program including an SQL statement having the content ".

The database user activates the generated application program, and the activated application program accesses the table 1 in the database to retrieve the data necessary for the user's work from the columns 1 and 2 and Output (usually displayed on the screen of the database user's network terminal).

After that, when the output of the column 3 is required in addition to the columns 1 and 2, the source program is modified as shown in FIG. 3B to add a portion for instructing the output of the column 3. Also, a part for instructing the search in column 3 is added.

[0007]

When only the table name is described in the search instruction part to simplify the source program, the work area is automatically prepared for each column, and therefore the columns not used by the application program are not used. Work area is wasted.

Further, not all data formats supported by the database management system can be handled by the application program.

For this reason, the creator of the source program assigns the names of the required columns among the columns defined in the data format (attribute) that can be used by the application program to both the output instruction part and the search instruction part of the source program as shown in FIG. Describe all as in A) or define a view table formed with necessary columns.

Further, column names are assigned to both the output instruction portion and the search instruction portion in response to a request from the database user.
As shown in (B), the description is deleted from both when it is no longer used, or the view table is redefined.

However, the work of describing all the columns described in the output instruction part in the source program in the search instruction part and defining the view table is a heavy burden on the creator of the source program.

The present invention has been made in view of the above conventional circumstances, and an object thereof is to provide an apparatus capable of reducing the burden on a program creator and improving the productivity of the program. .

[0013]

In FIG. 1, a device according to the first invention is a table name cutout means 10 for cutting out a description part of a table name from a source program, and a cutout part for describing a host variable from a source program, A column name specifying means 12 for specifying a column name indicated by the host variable, a work area name generating means 14 for generating a work area name required for data reference of the specified column name, and a cut out table name. Description part of
Database search statement generation means 16 for generating a database search statement for data search using the specified column name and the generated work area name.

In FIG. 2, the apparatus according to the second invention cuts out the description portion of the table name from the source program,
An available column determining means 20 for determining a column name of a usable data format by referring to a table definition of a relational database using the description part, and a description part of a host variable is cut out from a source program, and the host variable is used. A column name specifying means 22 for specifying a column name that has been determined to be usable and a work area name generating means 24 for generating a name of a work area required for data reference of the specified column name. When,
A database search statement generating means 26 for generating a database search statement for data search using the cutout table name description portion, the specified column name, and the generated work area name;
Have.

[0015]

The apparatus of the first aspect of the invention identifies the columns used by the application program (indicated by the host variables embedded in the source program) and automatically prepares the work area only for those columns.

The apparatus of the second invention is a column that can be used by the application program (whether or not it can be determined by referring to the table definition of the database), and the column used by the application program (source program (Indicated by host variables embedded in.) And automatically prepare work areas only for them.

Since the work area is reserved only for the columns used in this way or the columns that can be used and are actually used, the creator of the source program displays the search instruction portion in the portion shown in FIG. 4 (A). Only the name is described, and the view table is not defined.

In order to retrieve data from column 3 in addition to columns 1 and 2 of Table 1 as in the case of FIG. 3, only the output instruction portion of the source program as shown in FIG. Column 3 is described.

As can be understood from the above description, the work (or change work) of creating the source program is extremely easy. Therefore, it is possible to reduce the burden on the program creator and significantly increase the productivity of the source program.

[0020]

[Embodiment] FIG. 5 illustrates the configuration of the embodiment. The apparatus shown in FIG. 5 includes a resource information analysis unit 50 and a program analysis unit 5.
2, a data area generator 54 and an SQL statement generator 56.

The SQL database 500 shown in the figure is pre-built, and when a source program 502 (one example of which is shown in FIG. 6) is created and delivered to the device,
The resource information analysis unit 50 starts the processing of FIG. 7.

In the figure, the resource information analysis unit 50 reads the source program 502 (step 700), checks the resource section (see FIG. 6), and names the table (Table 1: TABLE).
1) is specified (step 702), and S is calculated using the table name.
The table definition of the QL database 500 is referred to (step 704).

In FIG. 8, the definition contents of Table 1 in the SQL database 500 are explained, and the application program (source program 502) has column 1,
Although columns 1, 2, and 3 of 2, 3, 4, and 5 can be used, columns 4 and 5 cannot be used.

From the contents of this table definition, 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 has columns 1, 2, 3, 3.
Of the columns 4 and 5, the names of columns 1, 2 and 3 that can be used by the application program (source program 502) are associated with the state values that indicate ON or OFF whether or not they are used in the source program 502, and all The state value of is set to OFF (steps 706, 708, 7).
10).

When the resource information analysis unit 50 creates the column management table 504, the program analysis unit 52 starts the processing of FIG. 10, and the resource information analysis unit 50 checks the processing unit (see FIG. 6) in the source program 502. All the column names used by the application program (source program 502) are specified, and all the state values corresponding to these column names on the column management table 504 are set to O.
Set to N (steps 1000, 1002, 100
4).

When the source program 502 of FIG. 6 is created and the table definition of FIG.
As shown in (B), the state values of columns 1 and 2 are turned on,
The state value of column 3 remains OFF.

The data area generation unit 54 performs the processing shown in FIG. 11, and the work area 506 (data area: variable) only for the column name whose state value is turned ON by the program analysis unit 52.
Are prepared (steps 1100, 1102, 110
4).

For example, when the source program 502 of FIG. 6 is created, the table definition of FIG. 8 is performed, and the state values of columns 1 and 2 are turned on as shown in FIG. 9B. At times, as shown in FIG. 9C, the names (variables) of the work area (data area) are prepared only for the columns 1 and 2.

Then, the SQL statement generator 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 (step 1200, 1
202, 1204, 1206, 1208, 1210, 1
212, 1214, 1216, 1218), this SQL
An application program (for example, a COBOL language source program or an object program) in which the statement 508 is embedded is generated.

As described above, only the columns which can be used by the application program and which are used by the application program are included in the work area 5.
06 is automatically prepared.

For this reason, the creator of the source program 502 does not define the view table, and describes only the table name in the processing section for instructing the search as shown in FIG. Therefore, the work of creating the source program 502 becomes extremely easy, and the burden on the program creator is reduced, and as a result,
The productivity of the source program 502 can be greatly increased.

Further, as in the case of FIG. 3, in order to retrieve data from column 3 in addition to columns 1 and 2 of Table 1, the description of column 3 is described only for the output instruction portion of the source program 502. The source program 502 can be easily changed by adding as shown in FIG.

[0033]

As described above, according to the present invention, work can be performed only on columns used in an application program, or only columns usable by the application program and used in the application program. Since the area is prepared, the source program can be created by writing only the table name in the search instruction part without defining the view table, which simplifies the work of creating the source program and reduces the burden on the creator. This makes it possible to significantly improve the production efficiency of program development.

[Brief description of 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 view of the operation of the invention.

FIG. 5 is a diagram illustrating the configuration of the embodiment.

FIG. 6 is an explanatory diagram of a source program according to the embodiment.

FIG. 7 is a flowchart illustrating processing contents of a resource information analysis unit.

FIG. 8 is an explanatory diagram of table definition in the database of 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 the processing content of an SQL statement 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)

[Claims] 1. A table name cutout means (10) for cutting out a description part of a table name from a source program, and a column name for cutting out a description part of a host variable from the source program and specifying a column name indicated by the host variable. A specifying means (12), a work area name generating means (14) for generating a work area name required for data reference of the specified column name, a description part of the cut out table name, the specified column name, Database search statement generation means (16) for generating a database search statement for data search using the generated work area name
And a database search statement creation device characterized by comprising: 2. An available column determining means (20) for extracting a description part of a table name from a source program, referring to the table definition of a relational database using the description part, and determining a column name of a usable data format. And a 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, and the specified column A work area name generating means (24) for generating a work area name required for data reference of a name, a description part of a cut table name, a specified column name, and a generated work area name. Database search statement generation means (26) for generating a database search statement for search
And a database search statement creation device characterized by comprising: