JP7484033B1 - Method, program and information processing device for changing a database management system - Google Patents

Abstract

[Problem] To provide a method, program, and information processing device for changing an existing database management system (DBMS) having a function of providing a user interface for parameter acquisition by writing an SQL statement to another DBMS without such a function. [Solution] A step (ST110) of creating a second SQL statement for the destination DBMS includes identifying parameters used in a first SQL statement of the source DBMS, creating a second SQL statement using the identified parameters, and creating a module to be executed on a terminal device operated by a user. The module includes a first program code that provides a user interface for inputting parameters, and a second program code that instructs the destination DBMS to execute the second SQL statement created for the first SQL statement using parameters input by the user interface. [Selected Figure] Figure 2

Description

The present invention relates to a method, program, and information processing device for changing a system that manages a database.

There are various types of database management systems (DBMS), and the appropriate DBMS is selected depending on the size of the database and the usage status. For example, ACCESS (registered trademark) from Microsoft Corporation in the United States is widely used as a DBMS for relatively small businesses because even beginners with no programming skills can easily build business databases. On the other hand, as the business scale expands, a larger database becomes necessary, and simultaneous execution control for multiple people becomes necessary, and DBMSs for small businesses such as ACCESS cannot meet these demands. In such cases, a technology is known that changes the DBMS of an existing database to a different DBMS. The following cited document discloses a data conversion method that converts queries used in the source DBMS into queries that fit the format of the destination DBMS.

JP 2011-248507 A

The SQL (structured query language) used to write ACCESS queries has a simpler syntax with fewer restrictions than general SQL, so that even beginners can easily create queries. For example, with ACCESS SQL, you can simply add a simple statement to the WHERE clause, which defines the conditions for the records that are the target of operations in the SELECT or UPDATE clause, and the parameters included in the conditions can be entered from an input dialog.

If the narrowing condition is that the field value of [Product Code] matches the value entered in the input dialog, the WHERE clause in the ACCESS SQL can be written as follows:
WHERE
Product code = [Please enter the product code]
Here, [Please enter the product code] is a parameter, and this parameter is displayed in the title of the input dialog.

However, typical SQL used in DBMSs that handle relatively large databases, such as Microsoft's Azure SQL Database (registered trademark), does not have a function to provide a user interface (such as an input dialog) for acquiring parameters. Therefore, in the case of a DBMS that has a function that allows a user interface for acquiring parameters to be easily provided simply by writing SQL statements, there is a problem in that it is difficult to change to a DBMS that does not have such a function.

The present invention has been made in consideration of the above circumstances, and its purpose is to provide a method, program, and information processing device that can change an existing DBMS that has the function of providing a user interface for obtaining parameters by writing SQL statements to another DBMS that does not have such a function.

A first aspect of the present invention is a method for changing a system that manages a database from a first management system to a second management system, the method comprising: a database managed by the first management system is called the first database; a database managed by the second management system is called the second database; an SQL statement executed by the first management system to operate the first database is called the first SQL statement; and an SQL statement executed by the second management system to operate the second database is called the second SQL statement; the method comprises a database creation step in which an information processing device creates a second database corresponding to the first database; and an information processing device analyzes one or more first SQL statements created for the first database and, based on the results of the analysis, creates one or more second SQL statements that cause the second management system to operate the second database corresponding to the operation of the first database by the one or more first SQL statements. The method includes a step of creating an SQL statement, the step of creating an SQL statement includes the steps of: identifying one or more parameters to be used in a first SQL statement and input by a user; creating a second SQL statement using the identified one or more parameters as a second SQL statement for operating a second database corresponding to the operation of the first database by the first SQL statement; and creating a module to be executed on a terminal device operated by a user, the module including one or more first program codes each providing a user interface for inputting the one or more parameters identified for the first SQL statement, and a second program code for instructing a second management system to execute the second SQL statement created for the first SQL statement using one or more parameters input by the user interface.

A second aspect of the present invention is a program including instructions for causing an information processing device to perform a process of changing a system that manages a database from a first management system to a second management system, the database managed by the first management system being called the first database, the database managed by the second management system being called the second database, an SQL statement executed by the first management system to operate the first database being called the first SQL statement, and an SQL statement executed by the second management system to operate the second database being called the second SQL statement, and the process performed by the information processing device in accordance with the instructions includes a database creation process for creating a second database corresponding to the first database, and one or more first SQL statements for analyzing one or more first SQL statements created for the first database and, based on the results of the analysis, causing the second management system to operate the second database corresponding to the operation of the first database by the one or more first SQL statements. The program includes an SQL statement creation step of creating a second SQL statement, the SQL statement creation step being a step of identifying one or more parameters that can be input by a user as parameters used in a first SQL statement, creating a second SQL statement using the identified one or more parameters as a second SQL statement that operates a second database corresponding to the operation of the first database by the first SQL statement, and creating a module that is executed on a terminal device operated by a user and includes one or more first program codes that each provide a user interface for inputting the one or more parameters identified for the first SQL statement, and a second program code that instructs the second management system to execute the second SQL statement created for the first SQL statement using one or more parameters input by the user interface.

A third aspect of the present invention is an information processing device that performs processing to change a system that manages a database from a first management system to a second management system, the information processing device having a processing unit and a memory unit that stores instructions to be executed by the processing unit, the database managed by the first management system is called the first database, the database managed by the second management system is called the second database, an SQL statement executed by the first management system to operate the first database is called the first SQL statement, and an SQL statement executed by the second management system to operate the second database is called the second SQL statement, and the processing performed by the processing unit in accordance with the instructions includes a database creation process that creates a second database corresponding to the first database, and analyzing one or more first SQL statements created for the first database and, based on the results of the analysis, causing the second management system to perform an operation of the second database corresponding to the operation of the first database by the one or more first SQL statements. The information processing device has an SQL statement creation process for creating one or more second SQL statements for executing the first SQL statement, the SQL statement creation process comprising: identifying one or more parameters that can be input by a user as parameters used in the first SQL statement; creating a second SQL statement using the identified one or more parameters as a second SQL statement for operating a second database corresponding to the operation of the first database by the first SQL statement; and creating a module that is executed on a terminal device operated by a user and includes one or more first program codes that each provide a user interface for inputting the one or more parameters identified for the first SQL statement, and a second program code that instructs the second management system to execute the second SQL statement created for the first SQL statement using one or more parameters input by the user interface.

A fourth aspect of the present invention is an information processing device that performs processing to change a system that manages a database from a first management system to a second management system, the database managed by the first management system being called the first database, the database managed by the second management system being called the second database, an SQL statement executed by the first management system to operate the first database being called the first SQL statement, and an SQL statement executed by the second management system to operate the second database being called the second SQL statement, the information processing device including: a database creation means that creates a second database corresponding to the first database; and an SQL statement creation means that analyzes one or more first SQL statements created for the first database and, based on the results of the analysis, creates one or more second SQL statements that cause the second management system to operate the second database corresponding to the operation of the first database by the one or more first SQL statements. The information processing device has a means for creating a SQL statement, the SQL statement creation means having a means for identifying one or more parameters used in a first SQL statement that can be input by a user, a means for creating a second SQL statement using the identified one or more parameters as a second SQL statement that operates a second database corresponding to the operation of the first database by the first SQL statement, and a means for creating a module that is executed on a terminal device operated by a user and includes one or more first program codes that each provide a user interface for inputting the one or more parameters identified for the first SQL statement, and a second program code that instructs the second management system to execute the second SQL statement created for the first SQL statement using one or more parameters input by the user interface.

The present invention provides a method, program, and information processing device that can change an existing DBMS that has a function of providing a user interface for obtaining parameters by writing SQL statements to another DBMS that does not have such a function.

FIG. 1 is a diagram showing an example of the configuration of a system according to the present embodiment. FIG. 2 is a flowchart for explaining an example of processing by the information processing device according to the present embodiment. FIG. 3A is a flowchart illustrating an example of a process for creating a second SQL statement, and FIG. 3B is a flowchart illustrating an example of a process for specifying parameters of a first SQL statement. Fig. 4A is a diagram showing an example of the structure of a first table, Fig. 4B is a diagram showing an example of a first SQL statement, and Fig. 4C and Fig. 4D are diagrams showing examples of an input dialog displayed by executing the first SQL statement. Figures 5A and 5B are diagrams showing parameters and their types specified in the first SQL statement shown in Figure 4B, and Figure 5C is a diagram showing an example of a second SQL statement corresponding to the first SQL statement shown in Figure 4B. FIG. 6 is a first diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. FIG. 7 is a second diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. FIG. 8 is a third diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. FIG. 9 is a fourth diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. FIG. 10 is a flowchart for explaining the processing when the modules shown in FIGS. 6 to 9 are executed in the terminal device. FIG. 11 is a flowchart illustrating an example of a process for inputting a first type parameter. FIG. 12 is a flowchart illustrating an example of a process for inputting a second type parameter. FIG. 13A is a diagram showing an example of the structure of a first table, FIG. 13B is a diagram showing an example of a first SQL statement, and FIG. 13C is a diagram showing an example of a second SQL statement. FIG. 14A is a diagram showing an example of the structure of a first table, FIG. 14B is a diagram showing an example of a first SQL statement, and FIG. 14C is a diagram showing an example of a second SQL statement. FIG. 15A is a diagram showing an example of the structure of the first table, FIG. 15B is a diagram showing an example of data contained in the first table, FIG. 15C is a diagram showing an example of a first SQL statement, FIG. 15D is a diagram showing an example of the results of a query indicated by the first SQL statement, and FIG. 15E is a diagram showing an example of a second SQL statement. FIG. 16A is a diagram showing an example of the structure of the first table, FIG. 16B is a diagram showing an example of data contained in the first table, FIG. 16C is a diagram showing an example of a first SQL statement, FIG. 16D is a diagram showing an example of the results of a query indicated by the first SQL statement, and FIG. 16E is a diagram showing an example of a second SQL statement. FIG. 17A is a diagram showing an example of the structure of the first table, FIG. 17B is a diagram showing an example of data contained in the first table, FIG. 17C is a diagram showing an example of a first SQL statement, FIG. 17D is a diagram showing an example of the results of a query indicated by the first SQL statement, and FIG. 17E is a diagram showing an example of a second SQL statement. FIG. 18A is a diagram showing an example of the structure of the first table, FIG. 18B is a diagram showing an example of data contained in the first table, FIG. 18C is a diagram showing an example of a first SQL statement, FIG. 18D is a diagram showing an example of a query result indicated by the first SQL statement, FIG. 18E is a diagram showing an SQL statement of a comparative example, and FIG. 18F is a diagram showing an example of a second SQL statement. FIG. 19A is a diagram showing an example of the structure of a first table, FIG. 19B is a diagram showing an example of a first SQL statement, and FIG. 19C is a diagram showing an example of a second SQL statement. FIG. 20A is a diagram showing an example of the structure of a first table, FIG. 20B is a diagram showing an example of a first SQL statement, and FIG. 20C is a diagram showing an example of a second SQL statement. FIG. 21A is a diagram showing an example of the structure of the first table, FIG. 21B is a diagram showing an example of the first SQL statement, FIG. 21C is a diagram showing a field section and its alias included in the first SQL statement, and FIG. 21D is a diagram showing an example of the second SQL statement. FIG. 22A is a diagram showing an example of the structure of a first table, FIG. 22B is a diagram showing an example of a first SQL statement, and FIG. 22C is a diagram showing an example of a second SQL statement.

Figure 1 is a diagram showing an example of a system according to this embodiment. The system shown in Figure 1 has an information processing device 1 and a second management system 3 that can communicate with each other via a communication network 9 such as the Internet. In the system shown in Figure 1, the information processing device 1 performs processing to change the system that manages the database from an existing DBMS to another DBMS. The information processing device 1 operates as the existing DBMS that is the source of the change, and also performs processing related to the change of the DBMS. The second management system 3 operates as the other DBMS that is the destination of the change. After the DBMS is changed, the information processing device 1 also operates as a terminal device that accesses the second management system 3.

[Information processing device 1]
The information processing device 1 is a device such as a personal computer that performs various information processing by installed programs (such as application software). The information processing device 1 manages a first database 200 as an existing DBMS (hereinafter, sometimes referred to as a "first management system"). For example, the information processing device 1 operates as the first management system by executing ACCESS (registered trademark) application software.

The information processing device 1 also creates a second database 400 in the second management system 3 in which the same data as that in the first database 200 is registered, and performs a process (hereinafter sometimes referred to as "DBMS change process") that enables queries similar to the queries prepared for the first management system for the first database 200 to be executed on the second database 400 in the second management system 3.

Furthermore, the information processing device 1 accesses the second management system 3 as a terminal device operated by a user who uses the second database 400 created by the DBMS change process. In this case, the information processing device 1 (terminal device) performs processes such as instructing the second management system 3 to execute a query of an SQL statement created in the DBMS change process, and obtaining and displaying the results of the query executed by the second management system 3. If the first management system is ACCESS, the information processing device 1 (terminal device) may operate as a front end of the second management system 3 by using some of the functions of ACCESS (forms, reports, macros, modules, etc.).

The information processing device 1 shown in the example of FIG. 1 has a communication unit 11, an input unit 12, a display unit 13, a memory unit 14, and a processing unit 15.

The communication unit 11 communicates with other devices (such as the second management system 3) via the communication network 9. The communication unit 11 includes a device (such as a network interface card) that communicates in accordance with a specific communication standard, such as Ethernet (registered trademark) or wireless LAN.

The input unit 12 inputs instructions and other information according to user operations to the processing unit 15. For example, the input unit 12 includes at least one device equipped with an input function such as a keyboard, a mouse, a touch panel, a touch pad, a button, a switch, a microphone, or a camera.

The display unit 13 is a device that displays an image corresponding to the video signal generated by the processing unit 15, and includes display devices such as a liquid crystal display, an organic EL display, and a projector.

The storage unit 14 stores one or more programs 141 including instructions executable by the processing unit 15, data temporarily stored during processing by the processing unit 15, data used in the processing of the processing unit 15, data obtained as a result of the processing of the processing unit 15, and the like. The storage unit 14 may include, for example, a main storage device (RAM, ROM, etc.) and an auxiliary storage device (flash memory, SSD, hard disk, memory card, optical disk, etc.). The storage unit 14 may be composed of one storage device or multiple storage devices. When the storage unit 14 is composed of multiple storage devices, each storage device is connected to the processing unit 15 via a computer bus or any other communication means.

The processing unit 15 is responsible for overall operation of the information processing device 1 and executes predetermined information processing. The processing unit 15 includes, for example, one or more processors (such as a central processing unit (CPU), micro-processing unit (MPU), digital signal processor (DSP)) that execute processing in accordance with instructions of one or more programs 141 stored in the memory unit 14. The processing unit 15 operates as a computer by the one or more processors executing instructions of one or more programs 141 stored in the memory unit 14. The information processing device 1 may include multiple such computers, and these computers may communicate via any communication network to cooperate and execute processing.

The processing unit 15 may include one or more dedicated hardware (such as an application specific integrated circuit (ASIC), field programmable gate array (FPGA)) configured to realize a specific function. In this case, the processing unit 15 may execute all of the processing described in this embodiment in a computer, or may execute at least some of the processing in dedicated hardware.

The program 141 may be recorded, for example, on a computer-readable recording medium (optical disk, memory card, USB memory, or other non-transitory tangible medium). The processing unit 15 may read at least a portion of the one or more programs 141 recorded on such a recording medium using a recording medium reading device (optical disk device, etc.) or an interface device (USB interface, etc.) not shown, and write the program to the storage unit 14. Alternatively, the processing unit 15 may download at least a portion of the one or more programs 141 from another device connected to the communication network 9 using the communication unit 11, and write the program to the storage unit 14. The program 141 may include an instruction that causes the processing unit 15 to perform at least a portion of the processing according to this embodiment described below.

[Storage device 2]
The storage device 2 stores various information used in the processing of the information processing device 1. The information processing device 1 and the storage device 2 can communicate with each other via any communication path (such as a LAN, a dedicated line network, or the Internet). For example, the storage device 2 may be included in a file server or a database server that accepts access from multiple devices, or may be a dedicated storage device that is accessible only to the information processing device 1.

In the example of FIG. 1, the storage device 2 stores information (information for the first database) used when the information processing device 1 operates as a first management system, and information (information for the second database) used when the information processing device 1 operates as a terminal device of the second management system 3.

As shown in FIG. 1, the information for the first database includes, for example, a first database 200 that is a database managed by a first management system (information processing device 1), a first SQL statement 202 that is an SQL statement executed by the first management system to operate the first database 200, query information 203 related to the contents of the query in the first SQL statement 202, a module 204 that includes program code for various processes executed by the first management system, and form/report information 205 related to user interfaces such as forms and reports. The first database 200 includes one or more first tables 201 in which data is registered.

1, the second database information includes a second SQL statement 212 which is an SQL statement executed by the second management system 3 to operate the second database 400, a module 214 including program code for various processes (such as a process for providing a user interface and a process for causing the second management system 3 to execute the second SQL statement) executed in a terminal device (information processing device 1) that accesses the second management system 3, and form/report information 215 relating to user interfaces such as forms and reports. The module 214 executed when a specific object (such as a form or report) is displayed on the screen of the terminal device (information processing device 1) may be referred to below as an "object module."

[Second Management System 3]
The second management system 3 is a DBMS that manages the second database 400. The second management system 3 executes a query of the second SQL statement (212, 412) in response to an instruction from the terminal device of the information processing device 1, and performs an operation on the second database 400. The second management system 3 also provides the query execution result (such as data extracted from the first database 200) to the terminal device.

The second management system 3 may be, for example, a cloud-based database system such as Azure SQL Database (registered trademark), and may include one or more computers equipped with a communication unit, a memory unit, and a processing unit similar to those of the information processing device 1 described above.

[Storage device 4]
The storage device 4 stores various information used in the processing of the second management system 3. The second management system 3 and the storage device 4 can communicate with each other via any communication path (such as a LAN, a dedicated line network, or the Internet). For example, the storage device 4 may be included in a file server or a database server that accepts access from multiple devices, or may be a dedicated storage device that is accessible only to the storage device 4.

In the example of FIG. 1, the storage device 4 stores a second database 400, which is a database managed by the second management system 3. The second database 400 includes one or more second tables 401 in which data is registered. The storage device 4 also stores a stored procedure 402, a function 403, and a view 404 as a second SQL statement 412 that the second management system 3 executes to operate the second database 400. The second management system 3 can execute the SQL statements (stored procedure 402, function 403, view 404) stored in the storage device 4 in response to an instruction from the terminal device (information processing device 1).

Next, we will explain the DBMS change processing performed by the information processing device 1 in the system having the above-mentioned configuration. Figure 2 is a flowchart for explaining an example of the DBMS change processing performed by the information processing device 1.

First, the information processing device 1 creates a second database 400 corresponding to the first database 200 in the second management system 3 (ST100). For example, the information processing device 1 creates a second table 401 in the second management system 3 that has fields similar to those of the first table 201 in the first database 200.

Next, the information processing device 1 selects one of the first SQL statements 202 created for the first database 200 (ST105). The information processing device 1 analyzes the selected first SQL statement 202, and based on the analysis result, creates a second SQL statement 212 or 412 that causes the second management system 3 to perform an operation on the second database 400 corresponding to the operation on the first database 200 by the first SQL statement 202 (ST110). For example, the information processing device 1 performs syntax analysis on the first SQL statement 202, and creates a second SQL statement (212, 412) that has a syntax equivalent to the analyzed syntax.

When the information processing device 1 creates the second SQL statement 212 or 412 for one first SQL statement 202, if there is a first SQL statement 202 that has not yet been analyzed (Yes in ST115), it selects that first SQL statement 202 (ST120) and performs the processing of step ST110 described above. As a result, the information processing device 1 creates second SQL statements (212, 412) for each of all first SQL statements 202 created for the first database 200.

The information processing device 1 converts user interface objects (forms, reports, etc.) prepared for the first database 200 in the first management system into objects usable in the terminal device of the second management system 3, and stores them in the storage device 2 as second database information (module 214, form/report information 215) (ST120). When the first management system is ACCESS and the information processing device 1 uses the user interface functions (forms, reports, etc.) of ACCESS as a terminal device, at least some of the objects (forms, reports, etc.) prepared for the first database 200 may be used as the second database information as is.

Next, the details of the process of step ST110 (FIG. 2) in which the second SQL statement (212, 412) is created will be described. In step ST110, the information processing device 1 performs various processes according to the contents of the first SQL statement 202 to be processed. Here, the processes of step ST110 according to the contents of the first SQL statement 202 will be described. In the following description, as an example, the first management system is ACCESS and the second management system 3 is Azure SQL Database.

<1. Query that accepts parameter input by the user>
FIG. 3A is a flowchart illustrating an example of a process for creating a second SQL statement (212, 412) corresponding to the first SQL statement 202, and illustrates the process when the query of the first SQL statement 202 is a query that accepts parameter input by the user.

In this case, the information processing device 1 identifies one or more parameters used in the first SQL statement 202 that can be input by the user (ST200). Next, the information processing device 1 creates a second SQL statement 412 that uses the identified one or more parameters as a second SQL statement 412 that operates the second database 400 corresponding to the operation of the first database 200 by the first SQL statement 202 (ST205). If the second management system 3 is Azure SQL Database (registered trademark), the information processing device 1 creates a stored procedure 402 or function 403 that can pass the parameter value as an argument as the second SQL statement 412.

The information processing device 1 also creates a module 214 that is executed when accessing the second management system 3 as a terminal device (ST210). This module 214 includes one or more first program codes that provide a user interface for inputting one or more parameters identified in step ST200, and a second program code that instructs the second management system 3 to execute the second SQL statement 412 created for the first SQL statement 202 using one or more parameters inputted by the user interface. When the first management system is ACCESS, the module 214 includes program code written in VBA (Visual Basic for Applications). By the terminal device (information processing device 1) executing this module 214, it becomes possible to input necessary parameters in the user interface provided by the first program code, and to set the input parameters as arguments of the second SQL statement 412, thereby making it possible to have the second management system 3 execute the second SQL statement 412.

FIG. 3B is a flowchart for explaining an example of the process of identifying parameters of the first SQL statement 202 (ST200: FIG. 3A).
In this example, first, the information processing device 1 acquires parameter information included in the query information 203 of the first SQL statement 202 (ST300). The parameter information is information about one or more parameters used in the query of the first SQL statement 202, and when the first management system is ACCESS, the parameter information can be acquired from an array (QueryDef.Parameters) that holds parameters in a QueryDef object that holds definition information of the query.

The information processing device 1 sequentially selects one token from among multiple tokens (character strings representing meaningful words in SQL) constituting the first SQL statement 202 (ST305), and determines whether the selected token is a token representing a parameter indicated by the parameter information (hereinafter, may be referred to as a "parameter token") (ST310). For example, the information processing device 1 compares the name of the parameter indicated by the parameter information with the character string of the token, and determines whether the token is a parameter token based on the comparison result.

If the selected token is a parameter token (Yes in ST310), the information processing device 1 determines the type of parameter corresponding to the parameter token (ST315). In this embodiment, there are multiple types of parameters that differ in the way the user inputs the parameter. The type of parameter can be identified, for example, based on the relationship between the parameter token and other tokens in the first SQL statement 202, the character string contained in the parameter token, etc. The information processing device 1 determines the type of parameter corresponding to the parameter token based on criteria such as the parameter token having a specified relationship with other tokens and the parameter token containing a specified character string.

When the information processing device 1 performs the processes of steps ST310 and ST315 for one token, if there is a token for which these processes have not yet been performed (Yes in ST320), the information processing device 1 selects that token (ST325) and performs the processes of steps ST310 and ST315 described above. As a result, the information processing device 1 performs the processes of steps ST310 and ST315 for all tokens included in the first SQL statement 202.

When the information processing device 1 creates the module 204 in the process of step ST210 (FIG. 3A) described above, it creates a first program that provides a user interface that is compatible with the input method for the type of parameter determined in step ST215. That is, a first program created for a first SQL statement 202 in step ST210 (FIG. 3A) is a user interface for inputting a parameter used in the first SQL statement 202, and provides a user interface that is compatible with the input method for the type determined for the parameter. As a result, when a query of a second SQL statement 412 that uses a parameter is executed, it becomes possible to input the parameter of the second SQL statement 412 through an appropriate user interface that is compatible with the type (input method) of the parameter of the first SQL statement 202.

In this embodiment, there are, for example, two types of parameters (first type and second type) of the first SQL statement 202. The first type parameters are input by an input object (such as an input dialog) displayed on the screen of the first management system according to the first SQL statement 202. The second type parameters are obtained based on data input or presented in a specific object (such as a form or report) displayed on the screen of the first management system.

The first program, which provides a user interface for inputting a first type of parameter, causes a terminal device (information processing device 1) to execute a process of displaying an input object (such as an input dialog) on the screen of the display unit 13, and a process of acquiring data input in the input object as the first type of parameter.

The first program, which provides a user interface for inputting a second type parameter, executes module 214 (object module) in the terminal device (information processing device 1) to determine whether the second type parameter can be acquired based on data input or presented in a specific object (form, report, etc.) displayed on the screen of the display unit 13, and if it is determined that the second type parameter cannot be acquired, executes a process of displaying an input object (input dialog, etc.) for inputting the second type parameter on the screen of the display unit 13, and a process of acquiring the data input in the input object as the second type parameter.

Below, we will explain a specific example of the processing of step ST110 (Figure 2) when the query of the first SQL statement 202 accepts parameter input.

Figure 4B shows an example of the first SQL statement 202 to be changed, and Figure 4A shows an example of the structure of the first table 201 to be operated on by the first SQL statement 202. In the query of the first SQL statement 202 shown in Figure 4B, records are extracted from the table "T_details", which is the first table 201, that satisfy the conditions that the field "Product CD" is equal to the parameter "[prm Product CD]", the field "Customer CD" is equal to the parameter "[Forms!F_Discount bulk setting!cbo Customer", and the field "Unit price" is equal to or greater than the parameter "[Enter the minimum unit price]", and the data value of the field "Discount amount" in the extracted records is updated to "150".

When the first management system (information processing device 1) executes the first SQL statement 202 shown in FIG. 4B, it displays input dialogs such as those shown in FIG. 4C and FIG. 4D on the screen of the display unit 13, and the parameters "[prm product CD]" and "[Please enter the minimum unit price]" are entered in these input dialogs. The first management system (information processing device 1) also obtains the value set in the control "cbo customer" in the form "F_bulk discount setting" (not shown) as the value of the parameter "Forms!F_bulk discount setting!cbo customer".

When analyzing the first SQL statement 202 shown in FIG. 4B in step ST110 (FIG. 2), the information processing device 1 performs a process of identifying parameters in the first SQL statement 202 (ST200: FIG. 3A). First, the information processing device 1 generates a list of parameters in the first SQL statement 202 from the parameter object (QueryDef.Parameters) included in the QueryDef object. FIG. 5A shows an example of the parameter list. In this list, the type of each parameter is still unknown.

Next, the information processing device 1 identifies parameter tokens included in the first SQL statement 202 by comparing each token included in the first SQL statement 202 with the parameters included in the list (FIG. 5A) (ST310: FIG. 3B). When the information processing device 1 identifies a parameter token, it determines the type of the parameter token (ST315: FIG. 3B).

For example, the information processing device 1 determines that a parameter explicitly defined by a PARAMETERS phrase is the first type. Also, if the first character string of a parameter token contains "Forms" or "Reports", the information processing device 1 determines that the parameter is the second type. If the parameter token does not meet any of these conditions, the information processing device 1 determines that the parameter is the first type.
According to this judgment criterion, as shown in FIG. 5B, the parameter "[prm Product CD]" is judged to be the first type, the parameter "[Forms!F_Discount Bulk Setting!cbo Customer" is judged to be the second type, and the parameter "[Please enter the minimum unit price]" is judged to be the first type.

Figure 5C shows an example of a second SQL statement 412 created by the information processing device 1 for the first SQL statement 202 shown in Figure 4B. In the example of Figure 5C, the second SQL statement 412 is a stored procedure 402. This stored procedure 402 has three arguments "@p_1", "@p_2", and "@p_3" corresponding to the three parameters "[prm product CD]", "[enter minimum unit price]", and "Forms!F_discount lump sum setting!cbo customer" identified in step ST200.

Figures 6 to 9 are diagrams showing an example of a module 214 created by the information processing device 1 for the stored procedure 402 (second SQL statement 412) shown in Figure 5C, and show a function procedure written in VBA.

The module 214 shown in the examples of Figures 6 to 9 includes first program codes P1 and P2 and second program code P3. The first program code P1 (Figure 7) provides a user interface for inputting the first type parameter "prm product CD", and the first program code P2 (Figure 8) provides a user interface for inputting the second type parameter "Forms!F_discount lump sum setting!cbo customer". The first program code that provides a user interface for inputting the first type parameter "[Enter minimum unit price]" is generally the same as the first program code P1 shown in Figure 7, so it is not shown. The second program code P3 (Figure 9) instructs the second management system 3 to execute the stored procedure 402 (second SQL statement 412) shown in Figure 5C using three parameters input by the user interface.

Figure 10 is a flowchart for explaining the processing when module 214 shown in Figures 6 to 9 is executed on a terminal device (information processing device 1).

The terminal device (information processing device 1) displays a confirmation message "Executing an update query will change the data in the table. Are you sure you want to execute this action query?" in a message box on the screen of the display unit 13 before executing the stored procedure 402 (second SQL statement 412) shown in FIG. 5C (ST400). If the "No" button is pressed in this message box (No in ST405), the terminal device ends the processing. If the "Yes" button is pressed in the message box (Yes in ST405), the terminal device performs processing to obtain each of the three parameters (ST410).

Figure 11 is a flowchart for explaining an example of the process of inputting the first type of parameter ("[prm product CD]", "[Please enter the minimum unit price]") in step ST410 (Figure 10). In this case, the terminal device (information processing device 1) displays an input dialog as shown in Figure 4C on the screen of the display unit 13, and allows the user to input a value (ST510). If a value is not input in the input dialog (No in ST515), the terminal device ends the process. If a value is input in the input dialog (Yes in ST515), the terminal device sets the input value to the parameter of the stored procedure 402 (ST520), and then reads the value of the parameter set in the stored procedure 402 and determines whether a value is set in the parameter (ST525). If the parameter value is set correctly (Yes in ST525), the terminal device ends the process. If a value is not set in the parameter (No in ST525), the terminal device displays an error message "The value entered in this field is incorrect. For example, a character string was entered in a numeric field" in a message box (ST530). When the "Yes" button is pressed in the message box, the terminal device returns to step ST510 and repeats the process from step ST510 onwards.

Figure 12 is a flowchart for explaining an example of the process of inputting a second type parameter ("Forms! F_discount bulk setting! cbo customer") in step ST410 (Figure 10). The flowchart shown in Figure 12 is the flowchart shown in Figure 11 with steps ST500 and ST505 added, and the other processes are the same as those in the flowchart shown in Figure 11. In the process shown in Figure 12, the terminal device (information processing device 1) first acquires the value set in the object indicated by the second type parameter (control "cbo customer" in form "F_discount bulk setting") (ST500). If the value of this object can be acquired normally (Yes in ST505), the terminal device proceeds to the above-mentioned step ST520 and determines whether the acquired value can be correctly set as a parameter of the stored procedure 402 (ST525). If the value of the object cannot be acquired normally (No in ST505), the terminal device proceeds to step ST510 and causes the user to input the value of the parameter from the input dialog.

Return to Figure 10.
If all the three parameter values cannot be obtained in step ST410 (No in ST415), the terminal device (information processing device 1) ends the process. If the three parameter values can be obtained in step ST410 (Yes in ST415), the terminal device tentatively executes the stored procedure 402 (FIG. 5C) in the second management system 3, and obtains tentative execution information regarding the data of the second database 400 that has been changed to be rolled back by the tentative execution (ST420). For example, the terminal device obtains the number of records in the second database 400 that have been changed to be rolled back by the tentative execution as tentative execution information. Then, the terminal device displays a final confirmation message including the tentative execution information, "xx records will be updated. If you click [Yes], the records will be updated and cannot be restored," in a message box (ST425). If the "No" button is pressed in this confirmation message box (No in ST430), the terminal device cancels the tentative execution (ST440) and ends the process. When the "Yes" button in the message box is pressed (Yes in ST430), the terminal device confirms the changes to the data in the second database 400 made by the provisional execution of the stored procedure 402 (ST420) in the second management system 3 (ST435).

Thus, according to this embodiment, the parameters used in the original first SQL statement 202 are identified (ST200: FIG. 3A), and a second SQL statement (212, 412) that operates the second database 400 corresponding to the operation of the first database 200 by the first SQL statement 202 is created (ST205: FIG. 3A). In this second SQL statement (212, 412), one or more parameters identified for the first SQL statement 202 are used. In addition, when the second SQL statement (212, 412) that uses parameters is created, a module 204 that is executed on the terminal device is created (ST210: FIG. 3A). This module 204 includes one or more first program codes each providing a user interface for inputting one or more parameters specified for the original first SQL statement 202, and a second program code for instructing the second management system 3 to execute a second SQL statement (212, 412) created for the first SQL statement 202 using one or more parameters inputted through the user interface. When this module 204 is executed in the terminal device (information processing device 1), parameters required for the query of the second SQL statement (212, 412) are inputted through the user interface, and the query of the second SQL statement (212, 412) is executed using the inputted parameters.
Therefore, it is possible to change an existing DBMS such as ACCESS, which has a function of providing a user interface for acquiring parameters by writing SQL statements, to another DBMS that does not have such a function.

Furthermore, according to this embodiment, for each parameter included in the first SQL statement 202, the type of the parameter related to the input method is determined. In the first program code that provides a user interface for inputting a parameter in the module 204, a user interface that is compatible with the input method of the determined type is provided. This makes it possible to input the parameter of the second SQL statement 412 using an appropriate user interface that is compatible with the type (input method) of the parameter of the first SQL statement 202.

Furthermore, according to this embodiment, when the first SQL statement 202 that is the source of change is an action-type SQL statement (such as an action query in ACCESS) that makes a change to the first database 200, in the processing by the second program code that instructs the execution of an action-type second SQL statement (such as stored procedure 402) created for the action-type SQL statement (first SQL statement 202), the second SQL statement is first tentatively executed (ST420: FIG. 10), and when instructed by the user, the change to the data in the second database 400 by the tentative execution is confirmed (ST435: FIG. 10). This allows the change to the data in the second database 400 by the second SQL statement (such as stored procedure 402) to be properly confirmed.

<2. Queries that change data in multiple tables>
In ACCESS SQL, in an action query that modifies a database, it is permitted to include commands to change (update, delete) data in multiple tables in a single command statement (UPDATE statement, DELETE statement). On the other hand, in general SQL, it is not possible to include commands to update or delete data in multiple tables in a single command statement.

Therefore, in the case of an action-type first SQL statement 202 that makes changes to data in multiple first tables 201, the information processing device 1 creates a corresponding action-type second SQL statement (e.g., a stored procedure 402) that is an SQL statement that makes changes (updates, deletes) to multiple second tables individually.
For example, the information processing device 1 performs a process of identifying the first table 201 that is the operation target in the action type first SQL statement 202. When multiple first tables 201 are identified as operation targets of the action type first SQL statement 202, the information processing device 1 creates a second SQL statement (e.g., stored procedure 402) that changes the second database 400 corresponding to the change in the first database 200 by the action type first SQL statement 202, the second SQL statement making individual changes to multiple second tables 401 corresponding to the identified multiple first tables 201.

Figures 13A to 13C show an example of a first SQL statement 202 that updates data in multiple tables with a single UPDATE statement. Figure 13B shows an example of an action-type first SQL statement 202, and Figure 13A shows the structure of two first tables 201 that are the target of updates in this first SQL statement 202. In the query of the action-type first SQL statement 202 shown in Figure 13B, two first tables 201 ("Table_1", "Table_2") are linked in a single UPDATE statement, and specific fields ("Field_12", "Field_12") in the two linked first tables 201 are updated to specific values ("aaa", "bbb").

Fig. 13C shows an example of a second SQL statement (stored procedure 402) that the information processing device 1 creates for the first SQL statement 202 shown in Fig. 13B. In this stored procedure 402, an update query for one of two linked first tables 201 and an update query for the other first table 201 are each executed by a separate UPDATE statement.

Figures 14A to 14C show an example of a first SQL statement 202 that deletes data from multiple tables with a single DELETE statement. Figure 14B shows an example of an action-type first SQL statement 202, and Figure 14A shows the structure of two first tables 201 that are the targets of deletion in this first SQL statement 202. In the query of the action-type first SQL statement 202 shown in Figure 14B, two first tables 201 ("Table_1", "Table_2") are concatenated in a single DELETE statement, and specific fields ("Field_12", "Field_12") in the two concatenated first tables 201 are deleted.

Fig. 14C shows an example of a second SQL statement (stored procedure 402) that the information processing device 1 creates for the first SQL statement 202 shown in Fig. 14B. In this stored procedure 402, a delete query for one first table 201 and a delete query for the other first table 201 in two linked first tables 201 are each executed by a separate DELETE statement.

In this way, according to this embodiment, a special SQL statement that makes changes to multiple tables at once using a single command statement can be replaced with a general SQL statement that makes changes to multiple tables using individual command statements.

<3. When an alias is automatically assigned to the field>
In ACCESS SQL, it is permitted to use fields with the same name in different tables without specifying an alias, whereas in general SQL, it is required to specify an alias for each field with the same name in different tables.
In addition, in ACCESS SQL, it is permitted to use the same field in the same table multiple times without specifying an alias, but in general SQL, using the same field without specifying an alias results in an error.
Furthermore, in ACCESS SQL, it is permitted to use a formula that uses a field without specifying an alias, but in general SQL, an error occurs if an alias is not specified for a formula that uses a field.
In the case of ACCESS, an alias is automatically assigned to a field part (a field or a calculation formula including a field) that does not have an alias assigned, and the alias is stored in the query definition object (QueryDef.Fields), so SQL statements that omit explicit alias assignment for a field part are permitted. However, since general SQL does not have such a function, an error occurs if the alias assignment is omitted.

Therefore, when a field part without an alias specified is used in the first SQL statement 202, the information processing device 1 creates a corresponding second SQL statement (212, 412) in which the necessary alias is specified for each field part.

In the following, the field in the first table 201 is referred to as the first field, and the part of the first SQL statement 202 that represents the first field or a formula including the first field is referred to as the first field portion. In addition, the field in the second table 401 is referred to as the second field, and the part of the second SQL statement (212, 412) that represents the second field or a formula including the second field is referred to as the second field portion. Furthermore, information about the field included in the query information 203 (for example, QueryDef.Fields in ACCESS) is referred to as field information.

For example, the information processing device 1 identifies, among one or more first field parts in a first SQL statement 202, a first field part for which no alias is specified, by syntax analysis or the like. Next, the information processing device 1 obtains the name of the identified first field part from the field information (QueryDef.Fields, etc.). Then, in a second SQL statement (212, 412) created for the first SQL statement 202, the information processing device 1 specifies the name included in the field information as an alias for the second field part corresponding to the identified first field part.

Figures 15A to 15E show examples of a first SQL statement 202 in which first fields with the same name that belong to different first tables 201 are used without specifying an alias. Figure 15C shows an example of a first SQL statement 202, Figure 15A shows the structures of two first tables 201 that are the objects of operation of this first SQL statement 202, and Figure 15B shows an example of data stored in the two first tables 201. In the first SQL statement 202 shown in Figure 15C, first fields with the same name that belong to different first tables 201 are used in the SELECT clause without specifying an alias. When the query of the first SQL statement 202 shown in Figure 15C is executed on the first table shown in Figure 15B, the query result shown in Figure 15D is obtained. In the query results shown in FIG. 15D, "Table_1.Field_A" and "Table_1.Field_A" are aliases automatically assigned by the first management system and are stored in the field information (such as QueryDef.Fields).

Figure 15E shows an example of a second SQL statement (212, 412) that the information processing device 1 creates for the first SQL statement 202 in Figure 15C. In the second SQL statement (212, 412) shown in Figure 15E, an alias obtained from field information (such as QueryDef.Fields) is specified for each second field listed in the SELECT clause.

Figures 16A to 16E show an example of a first SQL statement 202 in which the same first field is used multiple times without specifying an alias. Figure 16C shows an example of the first SQL statement 202, Figure 16A shows the structure of the first table 201 that is the target of the operation of this first SQL statement 202, and Figure 16B shows an example of data stored in the first table 201. In the first SQL statement 202 shown in Figure 16C, the same first field is used three times in the SELECT clause without specifying an alias. When the query of the first SQL statement 202 shown in Figure 16C is executed on the first table shown in Figure 16B, the query result shown in Figure 16D is obtained. "Expr1001" and "Expr1002" in the query result shown in Figure 16D are aliases automatically assigned by the first management system, and are stored in the field information (QueryDef.Fields, etc.).

Figure 16E shows an example of a second SQL statement (212, 412) that the information processing device 1 creates for the first SQL statement 202 in Figure 16C. In the second SQL statement (212, 412) shown in Figure 16E, an alias obtained from field information (such as QueryDef.Fields) is specified for the same second field that is written multiple times in the SELECT clause.

Figures 17A to 17E show an example of a first SQL statement 202 in which the first field part containing a formula is used without specifying an alias. Figure 17C shows an example of the first SQL statement 202, Figure 17A shows the structure of the first table 201 that is the target of the operation of this first SQL statement 202, and Figure 17B shows an example of data stored in the first table 201. In the first SQL statement 202 shown in Figure 17C, the first field part containing a formula ("Field_A + Field_B") is used in the SELECT clause without specifying an alias. When the query of the first SQL statement 202 shown in Figure 17C is executed on the first table shown in Figure 17B, the query result shown in Figure 17D is obtained. "Expr1001" in the query result shown in Figure 17D is an alias automatically assigned by the first management system and is stored in the field information (QueryDef.Fields, etc.).

Figure 17E shows an example of a second SQL statement (212, 412) that the information processing device 1 creates for the first SQL statement 202 in Figure 17C. In the second SQL statement (212, 412) shown in Figure 17E, an alias obtained from field information (such as QueryDef.Fields) is specified for the second field section that includes the formula written in the SELECT clause.

In this way, according to this embodiment, even if an alias for the first field part is omitted in the first SQL statement 202, an alias can be specified for the second field part written in the second SQL statement (212, 412) based on the name of the first field part contained in the field information (QueryDef.Fields, etc.) generated by the first management system, thereby avoiding errors in the SQL statement in the second management system 3.

<4. When a syntax for passing a numeric argument is converted to a syntax for passing a boolean argument>
In ACCESS SQL, if a numeric argument is passed to a function or syntax that performs processing depending on the truth or falsity of the argument, no error occurs because if the argument value is zero, it is interpreted as false, and if it is non-zero, it is interpreted as true. However, in general SQL, an error occurs in such a case.

Therefore, in the case of a first SQL statement 202 in which a numeric argument is passed to a function or syntax that performs processing according to the truth or falsehood of the argument, the information processing device 1 creates a second SQL statement (212, 412) in which the numeric argument is replaced with a truth or false expression.

For example, in the process of creating a second SQL statement (212, 412) corresponding to the first SQL statement 202, if the first SQL statement 202 contains a predetermined syntax or a predetermined function (such as the IIF function in ACCESS), the information processing device 1 replaces this with another syntax or another function (such as CASE WHEN-THEN-ELSE-END) in the second SQL statement (212, 412). Here, when the data type of the argument (first argument) passed to the predetermined syntax or the predetermined function to be replaced in the first SQL statement 202 is a numeric value, while the data type of the argument (second argument) passed to the other syntax or the other function to be replaced is a data type that takes a true or false value, the information processing device 1 sets as the second argument a boolean expression that takes a false value when the first argument is zero and takes a true value when the first argument is other than zero.

Figures 18A to 18F show examples of a first SQL statement 202 in which a numeric argument is passed to a function or syntax that performs processing according to the truth or falsity of the argument. Figure 18C shows an example of the first SQL statement 202, Figure 18A shows the structure of the first table 201 that is the operation target of this first SQL statement 202, and Figure 18B shows an example of data stored in the first table 201. In the first SQL statement 202 shown in Figure 18C, an IIF function is used in the first field section of the alias "[Decision result]", and a numeric first argument "Field_A + Field_B" is passed to this IIF function. The value of the first field section "[Decision result]" is "Addition result is false" when the value of the first argument is zero, and "Addition result is true" when the value of the first argument is other than zero. When the query of the first SQL statement 202 shown in Figure 18C is executed on the first table shown in Figure 18B, the query result shown in Figure 18D is obtained.

Figure 18E shows an SQL statement in which the IIF function in the first SQL statement 202 in Figure 18C has been replaced with a CASE WHEN-THEN-ELSE-END syntax. In the SQL statement shown in Figure 18E, the argument "Field_A + Field_B" following CASE WHEN is a numeric type, so an error would occur in general SQL.

Figure 18F shows an example of a second SQL statement (212, 412) created by the information processing device 1. In the second SQL statement (212, 412) shown in Figure 18F, the data type of "CAST (Field_A + Field_B AS int)" becomes an integer type by the CAST function, and the second argument "CAST (Field_A + Field_B AS int) <> 0" containing this becomes a boolean expression. The second argument of the boolean expression takes a false value when "Field_A + Field_B" is zero, and takes a true value when "Field_A + Field_B" is other than zero.

In this way, according to this embodiment, even if a first argument of a numeric type is passed to a function or syntax that performs processing depending on the truth or falsity of an argument in the first SQL statement 202, a second argument of a boolean type is passed to a function or syntax that performs processing depending on the truth or falsity of the argument in the second SQL statement (212, 412), so that errors caused by the data type of the argument can be avoided.

<5. Syntax where the field section is omitted>
In ACCESS SQL, in certain syntax that expresses operations related to fields (such as INSERT-INTO-), it is sometimes possible to omit the description of the field to be operated on (for example, the description of the field following INSERT); however, in general SQL, an error will occur in such a case.

Therefore, the information processing device 1 complements the description of the field that was omitted in the first SQL statement 202, which is the source of the change, in the second SQL statement (212, 412), which is the destination of the change.

For example, the information processing device 1 identifies a predetermined first syntax (INSERT-INTO-, etc.) that represents an operation related to the first field section in the original first SQL statement 202. The information processing device 1 then creates a test first SQL statement 202 that includes the identified first syntax, has the first management system process it, and obtains test field information (QueryDef.Fields, etc.) generated by the first management system for the test first SQL statement 202. When creating the second syntax of the second SQL statement (212, 412) that corresponds to the first syntax of the first SQL statement 202, the information processing device 1 sets the name of the second field section to be included in the second syntax based on the test field information.

Figures 19A to 19C show an example of a first SQL statement 202 in which the description of the field is omitted in the INSERT statement. Figure 19B shows an example of the first SQL statement 202 from which the change is to be made, and Figure 19A shows the structure of two first tables 201 that are the target of the operation in the first SQL statement 202. In the first SQL statement 202 shown in Figure 19B, the description of the destination field following INSERT is omitted. For such a first SQL statement 202, the information processing device 1 creates a second SQL statement (212, 412) shown in Figure 19C. In this second SQL statement (212, 412), the description of the destination field ("Field_A", "Field_B") is added after INSERT. This results in a one-to-one correspondence between the destination field following INSERT and the source field following SELECT.

In this way, according to this embodiment, even if a field description is omitted in the first SQL statement 202, which is the source of the change, the field description is completed in the second SQL statement (212, 412), which is the destination of the change, so that errors caused by the omission of the field description can be avoided.

<6. When the alias of the subquery is omitted>
In ACCESS SQL, it is permitted to omit the specification of an alias for a subquery, but in general SQL, an error occurs if the specification of an alias for a subquery is omitted.

Therefore, when there is a subquery for which an alias is not specified in the first SQL statement 202 from which the change is made, the information processing device 1 complements the description of the alias specification for the subquery in the second SQL statement (212, 412) from which the change is made.

For example, the information processing device 1 identifies a subquery (first subquery) that does not have an alias specified in the original first SQL statement 202. The information processing device 1 also extracts the name of the first table 201, the name of the query (subquery), and any explicitly specified aliases used in the original first SQL statement 202 by syntax analysis, and acquires them as used names. Then, when creating a subquery (second subquery) corresponding to the first subquery in the modified second SQL statement (212, 412), the information processing device 1 sets an alias for the second subquery that does not overlap with any names included in the used names.

Figures 20A to 20C show an example of a first SQL statement 202 in which the specification of an alias for the subquery is omitted. Figure 20B shows an example of the original first SQL statement 202, and Figure 20A shows the structure of the first table 201 that is the target of operation in the first SQL statement 202. In the first SQL statement 202 shown in Figure 20B, the specification of an alias for the subquery written in the FROM clause is omitted. For such a first SQL statement 202, the information processing device 1 creates a second SQL statement (212, 412) shown in Figure 20C. In this second SQL statement (212, 412), an alias "TableExpr0001" that does not overlap with the name of the second table 401 used in the FROM clause is set for the subquery.

In this way, according to this embodiment, even if there is a subquery for which an alias is not specified in the first SQL statement 202 from which the change is made, the description of the alias specification for the subquery is completed in the second SQL statement (212, 412) from which the change is made, so that errors caused by omitting the alias of the subquery can be avoided.

<7. When an alias for a field part is used for another field part in a specific syntax>
In ACCESS SQL, if an alias is specified for a field part in a specific syntax (e.g., a SELECT statement), it is permitted to use that alias for another field part in the specific syntax (such as a field calculation formula); however, in general SQL, the use of such an alias results in an error.

Therefore, when an alias is specified in a first field section in a specific syntax (e.g., a SELECT statement) in the first SQL statement 202 to be changed, and the alias is used in another first field section within the specific syntax, the information processing device 1 ensures that when creating a syntax corresponding to the specific syntax in the second SQL statement (212, 412) to be changed, the alias specified in the second field section is not used in the other second field section (the original name of the second field section is used).

In the following, a syntax (e.g., a SELECT statement) that expresses a data extraction operation related to a field portion in an SQL statement is referred to as a data extraction syntax. In addition, the data extraction syntax in the first SQL statement 202 is referred to as a first data extraction syntax, and the data extraction syntax in the second SQL statement (212, 412) is referred to as a second data extraction syntax.

For example, the information processing device 1 identifies a first field extraction syntax (e.g., a SELECT statement) that represents a data extraction operation related to a plurality of first field parts in the first SQL statement 202 that is the source of the change. Next, the information processing device 1 acquires, as a field list, a list that includes a plurality of first field parts included in the identified first field extraction syntax and aliases specified for the first field parts. After acquiring the field list, the information processing device 1 identifies a first field part (alias-containing field part) that includes a character string identical to an alias included in the field list in a plurality of first field parts included in the field list. Then, when creating a second field extraction syntax corresponding to the identified first field extraction syntax in the second SQL statement (212, 412) that is the destination of the change, the information processing device 1 creates a series of character strings obtained by replacing a character string identical to an alias included in the identified first field part (alias-containing field part) with a character string including a first field part that corresponds to the alias. The information processing device 1 sets the created character string as a second field part that corresponds to the identified first field part (alias-containing field part).

Figures 21A to 21D show an example of a first SQL statement 202 in which a field part containing an alias for a field part (alias-containing field part) is used in the field extraction syntax. Figure 21B shows an example of the original first SQL statement 202, and Figure 21A shows the structure of the first table 201 that is the target of the operation in the first SQL statement 202. In the first SQL statement 202 shown in Figure 21B, the alias "[Amount]" for the first field part "[Unit Price] * [Quantity]" in the SELECT statement is included in another first field part "[Cost] / [Amount]" in the SELECT statement.

First, the information processing device 1 identifies a SELECT statement as the first field extraction syntax in the first SQL statement 202 shown in FIG. 21B, and obtains a field list that includes multiple first field parts included in the SELECT statement and aliases specified for the first field parts. FIG. 21C is a diagram showing that field list.

Next, the information processing device 1 identifies, among the multiple first field sections included in the field list shown in FIG. 21C, a first field section (alias-containing field section) that contains the same character string as an alias included in the field list. In the example of FIG. 21C, the first field section "[Cost]/[Amount]" is an alias-containing field section.

Figure 21D shows an example of a second SQL statement (212, 412) generated by the information processing device 1 for the first SQL statement 202 shown in Figure 21B. In the second SQL statement (212, 412) shown in Figure 21D, a character string is created in which "[amount]" in "[cost]/[amount]" is replaced with "([unit price] * [quantity])" as the second field portion corresponding to the identified alias-containing field portion "[cost]/[amount]".

Thus, according to this embodiment, even if a first field part (alias-containing field part) that contains an alias for another first field part exists in the first field extraction syntax (e.g., SELECT statement) of the first SQL statement 202, the second SQL statement (212, 412) is created so that the alias specified in the second field part is not used in another second field part (so that the original name of the second field part is used). This makes it possible to avoid errors associated with the use of an alias for a field part in the field extraction syntax.

<8. Syntax for specifying the sort order of extracted data>
In ACCESS SQL, if a field extraction syntax (e.g., a SELECT statement) contains an order specification clause (e.g., an ORDER BY clause) that specifies the order of data in the data extraction results, the query will be executed normally even if a data count specification clause (e.g., a TOP clause) that specifies the number of data in the data extraction results is not present. However, in general SQL, an error occurs if both the order specification clause and the data count specification clause are not present in the field extraction syntax.

The information processing device 1 then identifies a first field extraction syntax (e.g., a SELECT statement) included in the first SQL statement 202, and determines whether the identified first field extraction syntax includes an order specification clause (e.g., an ORDER BY clause). If the identified first field extraction syntax includes an order specification clause, the information processing device 1 creates a second field extraction syntax corresponding to the identified first field extraction syntax so that the second field extraction syntax includes a data count specification clause (e.g., a TOP clause) in addition to the order specification clause.

Figures 22A to 22C show an example of a first SQL statement 202 that includes an order specification clause in the first field extraction syntax. Figure 22B shows an example of the original first SQL statement 202, and Figure 22A shows the structure of the first table 201 that is the target of operation in the first SQL statement 202. The first SQL statement 202 shown in Figure 22B includes an ORDER BY clause as a sort order specification clause, but since there is no data count specification clause (e.g. a TOP clause), an error will occur in general SQL.

Figure 22C shows an example of a second SQL statement (212, 412) generated by the information processing device 1 for the first SQL statement 202 shown in Figure 22B. In the second SQL statement (212, 412) shown in Figure 22C, a TOP clause is added after the SELECT clause. Since "100 PERCENT" is set as the number of data items in this TOP clause, the query execution result includes all data items that correspond to the extraction target.

Thus, according to this embodiment, when the first field extraction syntax (e.g., SELECT statement) in the first SQL statement 202 includes an order specification clause (e.g., ORDER BY clause), the second field extraction syntax in the second SQL statement (212, 412) includes a data count specification clause (e.g., TOP clause) along with the order specification clause. Therefore, an error caused by the absence of a data count specification clause in a field extraction syntax that includes a sort order specification clause can be avoided.

The present invention is not limited to the above-described embodiment, but includes various variations.

For example, in the embodiment described above, the information processing device 1 that performs the DBMS change processing has a function as the first management system and a function as a terminal device of the second management system 3, but this embodiment is not limited to this example. In another example of this embodiment, at least one of these functions may be provided by another device (such as a computer) other than the information processing device 1.

The information processing device 1 that performs the DBMS change processing may be a computer system including multiple computers connected by any communication means (LAN, WAN, Internet, etc.). In that case, the DBMS change processing may be shared among the multiple computers.

1...information processing device, 11...communication unit, 12...input unit, 13...display unit, 14...storage unit, 141...program, 15...processing unit, 2...storage device, 200...first database, 201...first table, 202...first SQL statement, 203...query information, 204...module, 205...form report information, 212...second SQL statement, 214...module, 215...form report information, 3...second management system, 4...storage device, 400...second database, 401...second table, 402...stored procedure, 403...function, 404...view, 412...second SQL statement

Claims (14)

A method for changing a system that manages a database from a first management system to a second management system, comprising:
The database managed by the first management system is called a first database,
The database managed by the second management system is called a second database,
An SQL statement executed by the first management system to operate the first database is called a first SQL statement,
The SQL statement executed by the second management system to operate the second database is called a second SQL statement,
a database creation step in which an information processing device creates the second database corresponding to the first database;
an SQL statement creation step in which the information processing device analyzes one or more of the first SQL statements created for the first database, and creates one or more of the second SQL statements based on a result of the analysis, the one or more second SQL statements causing the second management system to operate the second database corresponding to the operation of the first database by the one or more first SQL statements;
The SQL statement creation step includes:
identifying one or more parameters to be used in the first SQL statement, the parameters being input by a user;
creating a second SQL statement using the one or more identified parameters as the second SQL statement for performing an operation on the second database corresponding to the operation on the first database by the first SQL statement;
creating a module to be executed in a terminal device operated by the user, the module including one or more first program codes each providing a user interface for inputting one or more of the parameters specified for the first SQL statement, and a second program code for instructing the second management system to execute the second SQL statement created for the first SQL statement by using the one or more parameters inputted by the user interface;
Method. the first management system stores parameter information regarding one or more of the parameters used in the first SQL statement;
the plurality of tokens constituting the first SQL statement in which the parameter is used include a parameter token which is the token representing the parameter,
There are a plurality of types of the parameters, each of which has a different input method for the parameter,
each said type being identifiable based on a relationship of said parameter token to other said tokens and/or on a character string contained in said parameter token;
Identifying the parameter to be used in the first SQL statement in the SQL statement creation step includes:
Obtaining the parameter information stored for the first SQL statement;
Identifying one or more parameter tokens representing one or more parameters indicated by the parameter information from among the multiple tokens constituting the first SQL statement;
and for each of the one or more identified parameter tokens, determining the type based on a relationship of the parameter token to other tokens and/or a character string included in the parameter token;
the first program code created for the first SQL statement in the SQL statement creation step provides the user interface for inputting the parameter used in the first SQL statement, the user interface being adapted to the input method in the type determined for the parameter;
The method of claim 1. the type of the parameter input by an input object displayed on a screen of the first management system in accordance with the first SQL statement is called a first type;
The process by the first program code for providing the user interface for inputting the parameters of the first type includes:
displaying the input object on a screen of the terminal device;
acquiring data input in the input object as the first type of parameter;
The method of claim 2. The type in which the parameters are acquired based on data input or presented in a predetermined object displayed on a screen of the first management system is called a second type,
A module executed when the predetermined object is displayed on the screen of the terminal device is called an object module,
The process by the first program code for providing the user interface for inputting the parameters of the second type includes:
determining whether the second type parameter can be acquired based on data input or presented in the predetermined object displayed on the screen of the terminal device by the object module;
when it is determined that the second type parameter cannot be acquired, displaying an input object for inputting the second type parameter on a screen of the terminal device;
acquiring data input in the input object as the second type parameter;
The method of claim 2. The first SQL statement that modifies the first database is called an action-type SQL statement;
The process by the second program code instructs to execute the second SQL statement created for the action-type SQL statement,
tentatively executing the second SQL statement in the second management system, and acquiring tentative execution information related to data of the second database that has been changed in a manner that allows rollback by the tentative execution;
displaying, on a screen of the terminal device, a confirmation object that presents the acquired provisional execution information and asks the user for confirmation as to whether or not to finalize the change to the data in the second database by the second SQL statement;
and when an instruction to confirm the change to the second database by the second SQL statement is input to the confirmation object, confirming the change to the data in the second database by the virtual execution in the second management system.
The method of claim 1. A table included in the first database is called a first table,
A table included in the second database is called a second table,
The first SQL statement that modifies the first database is called an action-type SQL statement;
The SQL statement creation step includes:
If the first SQL statement is the action type SQL statement, identifying the first table that is an operation target in the action type SQL statement;
and when a plurality of the first tables are specified as operation targets of the action type SQL statement, creating the second SQL statement for individually modifying the plurality of the second tables corresponding to the specified plurality of the first tables as the second SQL statement for modifying the second database corresponding to the modification of the first database by the action type SQL statement.
The method of claim 1. A table included in the first database is called a first table,
A table included in the second database is called a second table,
A field in the first table is called a first field,
The field in the second table is called the second field,
A portion of the first SQL statement expressing the first field or a calculation formula including the first field is called a first field portion,
In the second SQL statement, a portion expressing the second field or a calculation formula including the second field is called a second field portion,
the first management system stores field information relating to one or more of the first field portions in the first SQL statement;
The SQL statement creating step of creating one of the second SQL statements for one of the first SQL statements includes:
Obtaining the stored field information for the first SQL statement;
Identifying a first field portion for which no alias is specified among one or more first field portions in the first SQL statement;
Obtaining a name of the identified first field portion from the field information;
and specifying, in the one second SQL statement, the name acquired from the field information as an alias of the second field portion corresponding to the identified first field portion.
The method of claim 1. The SQL statement creation step includes:
replacing a given function or a given syntax in the first SQL statement with a different function or a different syntax in the second SQL statement;
and if a data type of a first argument which is an argument passed to the predetermined function or the predetermined syntax is a numeric value, and a data type of a second argument which is an argument passed to the other function or the other syntax is a data type which takes a value of true or false, setting a boolean expression which takes a value of false when the first argument is zero and takes a value of true when the first argument is other than zero as the second argument.
The method of claim 1. A table included in the first database is called a first table,
A table included in the second database is called a second table,
A field in the first table is called a first field,
The field in the second table is called the second field,
A portion of the first SQL statement expressing the first field or a calculation formula including the first field is called a first field portion,
In the second SQL statement, a portion expressing the second field or a calculation formula including the second field is called a second field portion,
the first management system stores field information relating to one or more of the first field portions in the first SQL statement;
The SQL statement creating step of creating one of the second SQL statements for one of the first SQL statements includes:
Identifying a predetermined first syntax in the first SQL statement, the first syntax expressing an operation related to the first field portion;
creating a first SQL statement for testing including the identified first syntax, causing the first management system to process the first SQL statement, and acquiring the field information for testing generated by the first management system for the first SQL statement for testing;
and when creating a second syntax corresponding to the identified first syntax in the second SQL statement, setting a name of the second field portion to be included in the second syntax based on the test field information.
The method of claim 1. The SQL statement creating step of creating one of the second SQL statements for one of the first SQL statements includes:
Identifying a first subquery in the first SQL statement that is a subquery for which no alias is specified;
Acquiring, as used names, a name of a table of the first database used in the one first SQL statement and a name of another of the subqueries used in the one first SQL statement;
and when creating a second subquery corresponding to the first subquery in the second SQL statement, setting an alias name for the second subquery that does not overlap with the used name.
The method of claim 1. A table included in the first database is called a first table,
A table included in the second database is called a second table,
A field in the first table is called a first field,
The field in the second table is called the second field,
A portion of the first SQL statement expressing the first field or a calculation formula including the first field is called a first field portion,
In the second SQL statement, a portion expressing the second field or a calculation formula including the second field is called a second field portion,
A syntax that expresses a data extraction operation related to the first field portion in the first SQL statement is called a first field extraction syntax,
A syntax that expresses a data extraction operation related to the second field portion in the second SQL statement is called a second field extraction syntax,
The SQL statement creating step of creating one of the second SQL statements for one of the first SQL statements includes:
identifying the first field extraction syntax in the first SQL statement, the first field syntax representing a data extraction operation relating to a plurality of the first field portions;
acquiring a field list including a plurality of the first field portions included in the identified first field extraction syntax and aliases specified for the first field portions;
Identifying a first field portion that includes a character string identical to the alias included in the field list from among the plurality of first field portions included in the field list;
and when creating the second field extraction syntax corresponding to the identified first field extraction syntax in the one second SQL statement, a character string identical to the alias included in the identified first field portion is replaced with a character string including the first field portion corresponding to the alias, and the obtained character string is set as the second field portion corresponding to the identified first field portion.
The method of claim 1. A table included in the first database is called a first table,
A table included in the second database is called a second table,
A field in the first table is called a first field,
The field in the second table is called the second field,
A portion of the first SQL statement expressing the first field or a calculation formula including the first field is called a first field portion,
In the second SQL statement, a portion expressing the second field or a calculation formula including the second field is called a second field portion,
A syntax that expresses a data extraction operation related to the first field portion in the first SQL statement is called a first field extraction syntax,
A syntax that expresses a data extraction operation related to the second field portion in the second SQL statement is called a second field extraction syntax,
The SQL statement creating step of creating one of the second SQL statements for one of the first SQL statements includes:
Identifying the first field extraction syntax in the first SQL statement;
and when the identified first field extraction syntax includes an order specification phrase that specifies the order of data in the data extraction result, creating the second field extraction syntax corresponding to the identified first field extraction syntax so that the second field extraction syntax includes, in addition to the order specification phrase, a data number specification phrase that specifies the number of data in the data extraction result.
The method of claim 1. A program including an instruction for causing an information processing device to perform a process of changing a system that manages a database from a first management system to a second management system,
The process performed by the information processing device in accordance with the command includes each step of the method according to any one of claims 1 to 12.
program. An information processing device that performs a process of changing a system that manages a database from a first management system to a second management system,
A processing section;
A storage unit that stores instructions to be executed by the processing unit,
The processing performed by the processing unit in accordance with the instructions includes each step of the method according to any one of claims 1 to 12.
Information processing device.