Method for changing database management system, program, and information processing device

Abstract

To provide a method for changing an existing database management system (DBMS) that has a function of providing a user interface for acquiring a parameter by writing an SQL sentence, to another DBMS that does not have such a function, a program, and an information processing device.SOLUTION: A step (ST110) of creating a second SQL sentence of a change destination DBMS includes identifying a parameter that is used in a first SQL sentence of a change origin DBMS, creating the second SQL sentence using the identified parameter, and creating a module that is executed in a terminal device operated by a user. The module includes a first program code for providing a user interface for inputting a parameter and a second program code for giving a command to cause the change destination DBMS to execute the second SQL sentence created for the first SQL sentence using a parameter inputted through the user interface.SELECTED DRAWING: Figure 2

Description

[Technical field]

[0001] The present invention relates to a method, a program, and an information processing device for changing a system that manages a database. [Background technology]

[0002] There are various types of database management systems (DBMS), and an appropriate DBMS is selected depending on the size of the database and the usage status. For example, ACCESS (registered trademark) by Microsoft Corporation in the United States is widely used as a DBMS for relatively small-scale businesses because even beginners without programming skills can easily build a business database. On the other hand, as the scale of business expands, a larger database becomes necessary, or concurrent execution control by many people becomes necessary, and DBMSs for small businesses such as ACCESS cannot meet these demands. In such cases, a technique is known for changing the DBMS of an existing database to another DBMS. The following cited document discloses a data conversion method for converting a query used in a source DBMS into a query compatible with the format of a destination DBMS. [Prior art documents] [Patent documents]

[0003] [Patent Document 1] JP 2011-248507 A Summary of the Invention [Problem to be solved by the invention]

[0004] By the way, the SQL (structured query language) used to write queries in ACCESS has a simpler syntax with fewer restrictions than general SQL, so that even beginners can easily create queries. For example, in ACCESS SQL, you can enter parameters included in the conditions from an input dialog just by adding a simple description to the WHERE clause that defines the conditions for the records that are the target of the SELECT or UPDATE clause.

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

[0006] However, general 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 by simply writing an SQL statement, there is a problem that it is difficult to change to a DBMS that does not have such a function.

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

[0008] 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; and a database creation step in which an information processing device creates a second database corresponding to the first database; and the 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. and an SQL statement creating step, the SQL statement creating step comprising the steps of: identifying one or more parameters used in a first SQL statement and which can be input by a user; creating a second SQL statement using the identified one or more parameters as a second SQL statement that causes an operation of 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 that instructs a second management system to execute the second SQL statement created for the first SQL statement using the one or more parameters input by the user interface.

[0009] 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, wherein a database managed by the first management system is referred to as a first database, a database managed by the second management system is referred to as a second database, an SQL statement executed by the first management system to operate the first database is referred to as a first SQL statement, and an SQL statement executed by the second management system to operate the second database is referred to as a second SQL statement, and the processes performed by the information processing device in accordance with the instructions include a database creation process of creating a second database corresponding to the first database, and one or more first SQL statements of 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. and an SQL statement creating step of creating a second SQL statement of the first SQL statement, the SQL statement creating step comprising the steps of: identifying one or more parameters used in the first SQL statement and which can be input by a user; creating a second SQL statement using the identified one or more parameters as a second SQL statement that causes an operation of 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 that instructs a second management system to execute the second SQL statement created for the first SQL statement using the one or more parameters input by the user interface.

[0010] 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 processing performed by the processing unit in accordance with the instructions includes a database creation step of creating 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. and an SQL statement creating step of creating one or more second SQL statements that cause a user to operate the first database corresponding to the operation of the first database by the first SQL statement, the SQL statement creating step comprising the steps of: identifying one or more parameters that are used in the first SQL statement and that can be input by a user; creating a second SQL statement that uses the identified one or more parameters as a second SQL statement that causes an operation of 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, the module including one or more first program codes that respectively provide a user interface for inputting the one or more parameters identified for the first SQL statement, and a second program code that instructs a second management system to execute the second SQL statement created for the first SQL statement using the one or more parameters input by the user interface.

[0011] A fourth aspect of the present invention is an information processing device which performs processing to change a system which manages a database from a first management system to a second management system, the information processing device comprising: a database which the first management system manages is called a first database; a database which the second management system manages is called a second database; an SQL statement which the first management system executes to operate the first database is called a first SQL statement; and an SQL statement which the second management system executes to operate the second database is called a second SQL statement; a database creation means which creates a second database corresponding to the first database; and an SQL statement creation means which analyzes one or more first SQL statements created for the first database and, based on a result of the analysis, creates one or more second SQL statements which 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. and a generating means for generating a module 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 specified 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 the one or more parameters inputted via the user interface. Effect of the Invention

[0012] According to the present invention, a method, a program, and an information processing device can be provided that can change an existing DBMS that has a function of providing a user interface for obtaining parameters by writing an SQL statement to another DBMS that does not have such a function. [Brief description of the drawings]

[0013] [Figure 1] FIG. 1 is a diagram showing an example of the configuration of a system according to the present embodiment. [Diagram 2] FIG. 2 is a flowchart illustrating an example of processing by the information processing device according to the present embodiment. [Diagram 3] 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 identifying parameters of a first SQL statement. [Figure 4] 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, Fig. 4C and Fig. 4D are diagrams showing examples of an input dialog displayed by executing the first SQL statement. [Diagram 5] 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. [Figure 6] FIG. 6 is a first diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. [Figure 7] FIG. 7 is a second diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. [Figure 8] FIG. 8 is a third diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. [Figure 9] FIG. 9 is a fourth diagram illustrating an example of a module for obtaining parameters and executing a second SQL statement. [Figure 10] FIG. 10 is a flowchart for explaining the processing when the modules shown in FIGS. 6 to 9 are executed in the terminal device. [Figure 11] FIG. 11 is a flowchart illustrating an example of a process for inputting a first type parameter. [Figure 12] FIG. 12 is a flowchart illustrating an example of a process for inputting a parameter of the second type. [Figure 13] 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. [Figure 14] 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. [Figure 15] 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 a query result indicated by the first SQL statement, and FIG. 15E is a diagram showing an example of a second SQL statement. [Figure 16] 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 a query result indicated by the first SQL statement, and FIG. 16E is a diagram showing an example of a second SQL statement. [Figure 17] 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 a query result indicated by the first SQL statement, and FIG. 17E is a diagram showing an example of a second SQL statement. [Figure 18] 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 the first SQL statement, FIG. 18D is a diagram showing an example of the 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 the second SQL statement. [Figure 19] 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. [Figure 20]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. [Figure 21] 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. [Figure 22] 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. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0014] FIG. 1 is a diagram showing an example of a system according to this embodiment. The system shown in FIG. 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 FIG. 1, the information processing device 1 performs a process of changing a system that manages a 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 a process 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.

[0015] [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, may be referred to as a "first management system"). For example, the information processing device 1 operates as the first management system by executing application software such as ACCESS (registered trademark).

[0016] 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 a "DBMS change process") that enables queries similar to the queries for the first management system prepared for the first database 200 to be executed on the second database 400 in the second management system 3.

[0017] 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 a process of instructing the second management system 3 to execute a query of an SQL statement created in the DBMS change process, and a process of acquiring and displaying the results of the query executed by the second management system 3. When 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.).

[0018] The information processing device 1 shown in the example of FIG. 1 includes a communication unit 11, an input unit 12, a display unit 13, a storage unit 14, and a processing unit 15.

[0019] 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 predetermined communication standard such as Ethernet (registered trademark) or wireless LAN.

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

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

[0022] 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 processing by the processing unit 15, data obtained as a result of processing by 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 may be composed of 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.

[0023] The processing unit 15 generally controls the overall operation of the information processing device 1 and executes a predetermined information processing. The processing unit 15 includes, for example, one or more processors (such as a central processing unit (CPU), a micro-processing unit (MPU), or a digital signal processor (DSP)) that executes processing in accordance with instructions of one or more programs 141 stored in the storage 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 storage unit 14. The information processing device 1 may include a plurality of such computers, and these computers may execute processing in cooperation with each other by communicating with each other via an arbitrary communication network.

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

[0025] The program 141 may be recorded, for example, in 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 part of the one or more programs 141 recorded in 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 14 in the storage unit 14. Alternatively, the processing unit 15 may download at least a part 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 14 in the storage unit 14. The program 141 may include an instruction to cause the processing unit 15 to perform at least a part of the processing according to this embodiment described later.

[0026] [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.

[0027] In the example of Figure 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.

[0028] 1, the first database information includes a first database 200 which is a database managed by a first management system (information processing device 1), a first SQL statement 202 which is an SQL statement executed by the first management system to operate the first database 200, query information 203 related to the content of the query in the first SQL statement 202, a module 204 including program codes of 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.

[0029] 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 codes for various processes (such as a process for providing a user interface and a process for making the second management system 3 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 as an "object module" below.

[0030] [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 a second SQL statement (212, 412) in response to an instruction from a terminal device of the information processing device 1, and performs operations on the second database 400. The second management system 3 also provides the query execution results (such as data extracted from the first database 200) to the terminal device.

[0031] The second management system 3 may be 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.

[0032] [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.

[0033] 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 which the second management system 3 executes to operate the second database 400. The second management system 3 can execute the SQL statements (the stored procedure 402, the function 403, and the view 404) stored in the storage device 4 in response to an instruction from a terminal device (the information processing device 1).

[0034] Next, a description will be given of a DBMS change process performed by the information processing device 1 in the system having the above-mentioned configuration.

[0035] 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 having similar fields to the first table 201 of the first database 200 in the second management system 3.

[0036] 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) having a syntax equivalent to the analyzed syntax.

[0037] 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 process of step ST110 described above. In this way, the information processing device 1 creates second SQL statements (212, 412) for all first SQL statements 202 created for the first database 200.

[0038] 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 a part of the objects (forms, reports, etc.) prepared for the first database 200 may be used as the second database information as is.

[0039] Next, the details of the processing of step ST110 (FIG. 2) for creating the second SQL statement (212, 412) 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 processing of step ST110 according to the contents of the first SQL statement 202 will be described. In the following description, as an example, it is assumed that the first management system is ACCESS and the second management system 3 is Azure SQL Database.

[0040] <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.

[0041] In this case, the information processing device 1 identifies one or more parameters that are used in the first SQL statement 202 and 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). When the second management system 3 is Azure SQL Database (registered trademark), the information processing device 1 creates a stored procedure 402 or a function 403 that can pass a parameter value as an argument as the second SQL statement 412.

[0042] 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 through the user interface. When the first management system is ACCESS, the module 214 includes a 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 and cause the second management system 3 to execute the second SQL statement 412.

[0043] FIG. 3B is a flowchart for explaining an example of the process of identifying the 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 a query.

[0044] The information processing device 1 selects one token in order from a plurality of 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.

[0045] If the selected token is a parameter token (Yes in ST310), the information processing device 1 determines the type of the 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 the parameter can be identified, for example, based on the relationship between the parameter token and other tokens in the first SQL statement 202, a character string included in the parameter token, etc. The information processing device 1 determines the type of the parameter corresponding to the parameter token based on the determination criteria that the parameter token has a predetermined relationship with other tokens, that the parameter token contains a predetermined character string, etc.

[0046] 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 for all tokens included in the first SQL statement 202.

[0047] 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 in the type of the parameter determined in step ST215. That is, the first program created for the 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 in the type determined for the parameter. As a result, when a query of the second SQL statement 412 using 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.

[0048] In this embodiment, there are, for example, two types (first type and second type) of parameters 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 acquired 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.

[0049] A first program that 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 a display unit 13, and a process of acquiring data entered in the input object as the first type of parameter.

[0050] In addition, the first program providing a user interface for inputting a second type parameter executes, in the terminal device (information processing device 1), a process of determining 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 by executing module 214 (object module), and if it is determined that the second type parameter cannot be acquired, 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.

[0051] A specific example of the process of step ST110 (FIG. 2) in the case where the query of the first SQL statement 202 accepts input of a parameter will be described below.

[0052] Fig. 4B shows an example of the first SQL statement 202 to be changed, and Fig. 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 Fig. 4B, records are extracted from the table "T_details" which is the first table 201, where the field "Product CD" is equal to the parameter "[prmProduct CD]", the field "Customer CD" is equal to the parameter "Forms!F_Bulk Discount Setting!cboCustomer", and the field "Unit Price" is equal to or greater than the parameter "[Please enter the minimum unit price]", and the data value of the field "Discount Amount" in the extracted records is updated to "150".

[0053] 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 4D on the screen of the display unit 13, and inputs the parameters "[prm product CD]" and "[Please enter the minimum unit price]" in these input dialogs. In addition, the first management system (information processing device 1) obtains the value set in the control "cbo customer" in the form "F_discount bulk setting" (not shown) as the value of the parameter "Forms!F_discount bulk setting!cbo customer".

[0054] When analyzing the first SQL statement 202 shown in FIG. 4B in step ST110 (FIG. 2), the information processing device 1 performs processing to identify 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.

[0055] 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). After identifying a parameter token, the information processing device 1 determines the type of the parameter token (ST315: FIG. 3B).

[0056] For example, the information processing device 1 determines that a parameter explicitly defined by a PARAMETERS clause 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_Bulk Discount 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.

[0057] Fig. 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 Fig. 4B. In the example of Fig. 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 bulk setting!cbo customer" identified in step ST200.

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

[0059] The module 214 shown in the examples of FIGS. 6 to 9 includes first program codes P1 and P2 and a second program code P3. The first program code P1 (FIG. 7) provides a user interface for inputting a first type parameter “[prm product CD]”, and the first program code P2 (FIG. 8) provides a user interface for inputting a second type parameter “Forms!F_discount lump sum setting!cbo customer”. The first program code providing a user interface for inputting a first type parameter “[Enter the minimum unit price]” is almost the same as the first program code P1 shown in FIG. 7, and is therefore omitted from the illustration. The second program code P3 (FIG. 9) instructs the second management system 3 to execute the stored procedure 402 (second SQL statement 412) shown in FIG. 5C using three parameters input by the user interface.

[0060] FIG. 10 is a flowchart for explaining a process when the module 214 shown in FIGS. 6 to 9 is executed in the terminal device (information processing device 1).

[0061] The terminal device (information processing device 1) displays a confirmation message "If you execute an update query, the data in the table will be changed. 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 in this message box is pressed (No in ST405), the terminal device ends the processing. If the "Yes" button in the message box is pressed (Yes in ST405), the terminal device performs processing to obtain each of the three parameters (ST410).

[0062] FIG. 11 is a flowchart for explaining an example of the process of inputting the first type of parameters ("[prm product CD]", "[Please enter the minimum unit price]") in step ST410 (FIG. 10). In this case, the terminal device (information processing device 1) displays an input dialog as shown in FIG. 4C on the screen of the display unit 13, and has the user 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 out 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 processing from step ST510 onward.

[0063] FIG. 12 is a flowchart for explaining an example of the process of inputting the second type parameter ("Forms!F_Discount Bulk Setting!cboCustomer") in step ST410 (FIG. 10). The flowchart shown in FIG. 12 is obtained by adding steps ST500 and ST505 to the flowchart shown in FIG. 11, and other processes are the same as those in the flowchart shown in FIG. 11. In the process shown in FIG. 12, the terminal device (information processing device 1) first acquires a value set in the object indicated by the second type parameter (control "cboCustomer" 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 in the 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.

[0064] Return to Figure 10. If all the three parameter values ​​cannot be acquired in step ST410 (No in ST415), the terminal device (information processing device 1) ends the process. If the three parameter values ​​can be acquired in step ST410 (Yes in ST415), the terminal device tentatively executes the stored procedure 402 (FIG. 5C) in the second management system 3, and acquires tentative execution information regarding the data in the second database 400 that has been changed to be rolled back by the tentative execution (ST420). For example, the terminal device acquires 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 will not be able to be undone," 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).

[0065] Thus, according to this embodiment, the parameters used in the original first SQL statement 202 are specified (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 specified for the first SQL statement 202 are used. In addition, when the second SQL statement (212, 412) using the parameters is created, a module 204 to be executed in the terminal device is created (ST210: FIG. 3A). This module 204 includes one or more first program codes that respectively provide a user interface for inputting one or more parameters specified for the original first SQL statement 202, and a second program code that instructs the second management system 3 to execute the second SQL statement (212, 412) created for the first SQL statement 202 using one or more parameters inputted by 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 input via a user interface, and the query of the second SQL statement (212, 412) is executed using the input parameters. Therefore, it is possible to change an existing DBMS such as ACCESS, which has a function of providing a user interface for obtaining parameters by writing an SQL statement, to another DBMS that does not have such a function.

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

[0067] Furthermore, according to this embodiment, when the first SQL statement 202 that is the change source is an action type SQL statement (such as an action query in ACCESS) that changes the first database 200, in the process by the second program code that instructs the execution of an action type second SQL statement (such as the stored procedure 402) created for the action type SQL statement (the first SQL statement 202), the second SQL statement is tentatively executed (ST420: FIG. 10), and when an instruction is given 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 the stored procedure 402) to be properly confirmed.

[0068] <2. Queries that change data in multiple tables> In ACCESS SQL, action queries that make changes to a database are allowed to include commands to change (update, delete) data in multiple tables in a single 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 statement.

[0069] 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 an action-type second SQL statement (e.g., a stored procedure 402) corresponding to the first SQL statement, which 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 the 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, and that individually changes multiple second tables 401 corresponding to the identified multiple first tables 201.

[0070] 13A to 13C show an example of a first SQL statement 202 that updates data in multiple tables with a single UPDATE statement. Fig. 13B shows an example of an action-type first SQL statement 202, and Fig. 13A shows the structures of two first tables 201 that are targets of updating in this first SQL statement 202. In a query of the action-type first SQL statement 202 shown in Fig. 13B, two first tables 201 ("Table_1", "Table_2") are concatenated in a single UPDATE statement, and predetermined fields ("Field_12", "Field_12") in the two concatenated first tables 201 are updated to predetermined values ​​("aaa", "bbb").

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

[0072] 14A to 14C show an example of a first SQL statement 202 that deletes data from multiple tables with one DELETE statement. Fig. 14B shows an example of an action-type first SQL statement 202, and Fig. 14A shows the structures 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 Fig. 14B, two first tables 201 ("Table_1", "Table_2") are concatenated in one DELETE statement, and predetermined fields ("Field_12", "Field_12") in the two concatenated first tables 201 are deleted.

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

[0074] 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.

[0075] <3. When an alias is automatically assigned to the field part> ACCESS SQL allows fields with the same name in different tables to be used without specifying an alias, whereas general SQL requires that fields with the same name in different tables be given an alias. In addition, ACCESS's SQL allows the same field in the same table to be used multiple times without specifying an alias, but in general SQL, using the same field without specifying an alias will result in an error. Furthermore, ACCESS's SQL allows the use of calculation formulas that use fields without specifying an alias, but in general SQL, an error occurs if an alias is not specified for a calculation formula that uses a field. In the case of ACCESS, an alias is automatically assigned to any field section (field or calculation expression containing a field) that does not have an alias specified, and that alias is stored in the query definition object (QueryDef.Fields), so SQL statements that omit explicit alias specification for field sections are permitted. However, general SQL does not have such functionality, so an error occurs if the alias specification is omitted.

[0076] 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.

[0077] 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. Also, 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 related to the field included in the query information 203 (for example, QueryDef.Fields in ACCESS) is referred to as field information.

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

[0079] 15A to 15E show an example of the 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. FIG. 15C shows an example of the first SQL statement 202, FIG. 15A shows the structure of two first tables 201 that are the operation targets of the first SQL statement 202, and FIG. 15B shows an example of data stored in the two first tables 201. In the first SQL statement 202 shown in FIG. 15C, first fields with the same name that belong to different first tables 201 are used without specifying an alias in the SELECT clause. When the query of the first SQL statement 202 shown in FIG. 15C is executed on the first table shown in FIG. 15B, a query result as shown in FIG. 15D is obtained. "Table_1.Field_A" and "Table_1.Field_A" in the query result shown in FIG. 15D are aliases automatically given by the first management system, and are stored in the field information (QueryDef.Fields, etc.).

[0080] Fig. 15E shows an example of a second SQL statement (212, 412) created by the information processing device 1 for the first SQL statement 202 in Fig. 15C. In the second SQL statement (212, 412) shown in Fig. 15E, an alias obtained from field information (such as QueryDef.Fields) is specified for each second field described in the SELECT clause.

[0081] 16A to 16E show an example of the first SQL statement 202 in which the same first field is used multiple times without specifying an alias. FIG. 16C shows an example of the first SQL statement 202, FIG. 16A shows the structure of the first table 201 that is the operation target of the first SQL statement 202, and FIG. 16B shows an example of data stored in the first table 201. In the first SQL statement 202 shown in FIG. 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 FIG. 16C is executed on the first table shown in FIG. 16B, the query result shown in FIG. 16D is obtained. "Expr1001" and "Expr1002" in the query result shown in FIG. 16D are aliases automatically assigned by the first management system, and are stored in the field information (QueryDef.Fields, etc.).

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

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

[0084] Fig. 17E shows an example of a second SQL statement (212, 412) created by the information processing device 1 for the first SQL statement 202 in Fig. 17C. In the second SQL statement (212, 412) shown in Fig. 17E, an alias acquired from field information (such as QueryDef.Fields) is specified for the second field part including the formula written in the SELECT clause.

[0085] Thus, 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 (such as QueryDef.Fields) generated by the first management system, thereby avoiding errors in the SQL statement in the second management system 3.

[0086] <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 will occur 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 will occur in such a case.

[0087] 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.

[0088] 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 (e.g., IIF function in ACCESS), the information processing device 1 replaces it with another syntax or another function (e.g., CASE WHEN-THEN-ELSE-END) in the second SQL statement (212, 412). Here, when the data type of an 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 an 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.

[0089] 18A to 18F show examples of the 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. FIG. 18C shows an example of the first SQL statement 202, FIG. 18A shows the structure of the first table 201 that is the operation target of the first SQL statement 202, and FIG. 18B shows an example of data stored in the first table 201. In the first SQL statement 202 shown in FIG. 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 “the addition result is false” when the value of the first argument is zero, and is “the 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 FIG. 18C is executed on the first table shown in FIG. 18B, the query result shown in FIG. 18D is obtained.

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

[0091] Fig. 18F shows an example of the second SQL statement (212, 412) created by the information processing device 1. In the second SQL statement (212, 412) shown in Fig. 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" including 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.

[0092] Thus, 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 the argument in the first SQL statement 202, a second argument of a boolean expression 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.

[0093] <5. Syntax where the field section is omitted> In ACCESS SQL, in certain syntax that expresses field-related operations (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 cases.

[0094] Therefore, the information processing device 1 complements the description of the field omitted in the first SQL statement 202 before the change in the second SQL statement (212, 412) after the change.

[0095] For example, the information processing device 1 identifies a predetermined first syntax (INSERT~INTO~, etc.) representing an operation related to a first field portion in the original first SQL statement 202. Next, the information processing device 1 creates a test first SQL statement 202 including the identified first syntax, causes the first management system to process it, and acquires test field information (QueryDef.Fields, etc.) generated by the first management system for the test first SQL statement 202. Then, when creating a second syntax of a second SQL statement (212, 412) corresponding to the first syntax of the first SQL statement 202, the information processing device 1 sets the name of the second field portion to be included in the second syntax based on the test field information.

[0096] 19A to 19C show an example of the first SQL statement 202 in which the description of the field is omitted in the INSERT statement. FIG. 19B shows an example of the first SQL statement 202 before the change, and FIG. 19A shows the structure of two first tables 201 to be operated on in the first SQL statement 202. In the first SQL statement 202 shown in FIG. 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 FIG. 19C. In this second SQL statement (212, 412), the description of the destination field ("Field_A", "Field_B") is added after INSERT. Thereby, the destination field following INSERT and the source field following SELECT correspond one-to-one.

[0097] 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 supplemented in the second SQL statement (212, 412), which is the destination of the change, thereby avoiding errors caused by the omission of a field description.

[0098] <6. When the alias of the subquery is omitted> In ACCESS SQL, it is permissible to omit specifying an alias for a subquery, but in general SQL, omitting to specify an alias for a subquery results in an error.

[0099] Therefore, when the pre-change first SQL statement 202 contains a subquery for which no alias is specified, the information processing device 1 complements the description of the alias specification for the subquery in the post-change second SQL statement (212, 412).

[0100] For example, the information processing device 1 identifies a subquery (first subquery) for which an alias is not 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 an explicitly specified alias used in the original first SQL statement 202 by syntax analysis, and acquires them as used names. 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.

[0101] 20A to 20C show an example of the first SQL statement 202 in which the specification of an alias for the subquery is omitted. FIG. 20B shows an example of the original first SQL statement 202, and FIG. 20A shows the structure of the first table 201 to be operated on in the first SQL statement 202. In the first SQL statement 202 shown in FIG. 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 FIG. 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.

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

[0103] <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 (for example, a SELECT statement), it is permitted to use that alias for another field part in that specific syntax (such as a field calculation formula); however, in general SQL, using such an alias will result in an error.

[0104] 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 prevents the alias specified in the second field section from being used in another second field section when creating a syntax corresponding to the specific syntax in the second SQL statement (212, 412) to be changed (the original name of the second field section is used).

[0105] In the following, a syntax (e.g., a SELECT statement) that represents a data extraction operation related to a field part in an SQL statement is called a data extraction syntax. Also, the data extraction syntax in the first SQL statement 202 is called a first data extraction syntax, and the data extraction syntax in the second SQL statement (212, 412) is called a second data extraction syntax.

[0106] For example, the information processing device 1 identifies a first field extraction syntax (e.g., a SELECT statement) representing a data extraction operation related to a plurality of first field parts in the first SQL statement 202 before change. Next, the information processing device 1 acquires, as a field list, a list including a plurality of first field parts included in the identified first field extraction syntax and aliases specified for the first field parts. The information processing device 1 that has acquired the field list identifies a first field part (alias-containing field part) that includes a character string identical to an alias included in the field list, among the plurality of first field parts included in this field list. Then, when the information processing device 1 creates a second field extraction syntax corresponding to the identified first field extraction syntax in the second SQL statement (212, 412) after change, it 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 corresponding to the alias. The information processing device 1 sets the created character string as a second field part corresponding to the identified first field part (alias-containing field part).

[0107] 21A to 21D show an example of a first SQL statement 202 in which a field part including an alias for the field part (alias-containing field part) is used in the field extraction syntax. Fig. 21B shows an example of the first SQL statement 202 before modification, and Fig. 21A 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 Fig. 21B, the alias "[amount]" of the first field part "[unit price]*[quantity]" in the SELECT statement is included in another first field part "[cost] / [amount]" in the SELECT statement.

[0108] 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 acquires a field list including a plurality of first field parts included in the SELECT statement and aliases specified for the first field parts. Fig. 21C is a diagram showing the field list.

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

[0110] Fig. 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 Fig. 21B. In the second SQL statement (212, 412) shown in Fig. 21D, a character string is created in which "[amount]" in "[cost] / [amount]" is replaced with "([unit price]*[quantity])" as a second field portion corresponding to the identified alias-containing field portion "[cost] / [amount]".

[0111] Thus, according to this embodiment, even if a first field part (alias-containing field part) containing an alias of 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 that accompany the use of an alias of a field part in the field extraction syntax.

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

[0113] Therefore, the information processing device 1 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.

[0114] Figures 22A to 22C show an example of a first SQL statement 202 including a sort 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 (for example, a TOP clause), an error will occur in general SQL.

[0115] Fig. 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 Fig. 22B. In the second SQL statement (212, 412) shown in Fig. 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 execution result of the query includes all data items that are subject to extraction.

[0116] Thus, according to this embodiment, when a sort order specification clause (e.g., an ORDER BY clause) is included in the first field extraction syntax (e.g., a SELECT statement) in the first SQL statement 202, a data count specification clause (e.g., a TOP clause) is included in the second field extraction syntax in the second SQL statement (212, 412). This makes it possible to avoid errors caused by the absence of a data count specification clause in a field extraction syntax that includes a sort order specification clause.

[0117] It should be noted that the present invention is not limited to the above-described embodiment, but includes various variations.

[0118] For example, in the above-described embodiment, the information processing device 1 that performs the DBMS change process 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 a device (such as a computer) other than the information processing device 1.

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

[0120] 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

1. 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 the one or more first SQL statements created for the first database, and creates, based on a result of the analysis, 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 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.

2. 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.

3. 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.

4. 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.

5. The first SQL statement that modifies the first database is called an action-type SQL statement; The process by the second program code for instructing to execute the second SQL statement created for the action-type SQL statement includes: 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.

6. 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.

7. 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, A portion of the second SQL statement expressing the second field or a 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 field information stored for the first SQL statement; Identifying a first field section for which no alias is specified among one or more first field sections 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.

8. 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.

9. 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, A portion of the second SQL statement expressing the second field or a 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.

10. 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.

11. 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, A portion of the second SQL statement expressing the second field or a 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.

12. 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, A portion of the second SQL statement expressing the second field or a 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 such 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.

13. 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.

14. 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.

Images