KR20040009536A - Method and system of managing sql query using metasql component - Google Patents

Abstract

PURPOSE: A method and a system for managing an SQL(Structured Query Language) query using a metaSQL component are provided to enhance efficiency for the system development by managing the SQL query at the outside in order not to execute recompile again even if the SQL query of a program source is changed. CONSTITUTION: An SQL query file manager manages the SQL query as a file form having a constant extension. A memory stores an SQL query file managed by the SQL query manager in a constant directory. A program source part(10) is inserted by the information of the SQL query file. The metaSQL component(20) brings a query execution result by reading the SQL query file information from the program source part and accessing a database(40), and returns the execution result to the program as vector.

Description

METHOD AND SYSTEM OF MANAGING SQL QUERY USING METASQL COMPONENT}

The present invention relates to a method and system for efficiently managing SQL queries, and more particularly, to a method and system for managing SQL queries using MetaSQL components.

In general, when a program execution unit for executing a specific function is called an executable file, the executable file is variously represented in the form of .exe, .com, etc. according to the program language used. Executable files are also commonly referred to as classes, which are generated by compiling a program source written by a programmer through a compiler, as shown in FIG.

Class files are machine code that can be understood by Machine, and are units of execution that can perform tasks required by real users.

2A and 2B conceptually represent a process in which a SQL source is inserted into a program source to create a program source, and the program source is composed of machine code after being compiled through a compiler.

Here, the implementation language of the program source may be configured in various ways such as Java, C, C ++. Executable files compiled by the compiler cannot be interpreted by the human eye and consist of the letters 0, 1, A through F.

Now, when changing the SQL source inserted in the program source, once composed class has to change the program source and recompile. Recompiling creates a new executable file with the changed SQL source.

In other words, a class created by compiling cannot change the class itself, and a programmer must make a newly changed class file by modifying the source program source and recompiling when he / she wants to change the class.

As can be seen in Figure 2, generally, when writing a program source for system implementation, a database query language (SQL), which is a database query language, is often inserted into the program source, and in this case, the SQL query is changed. There was a problem that you always had to compile again to change the class file.

The present invention is to solve the problems of the prior art as described above, an object of the present invention by separately managing the SQL query used in the conventional program source to prevent re-compiling even if the SQL query of the program source is changed again This is to improve the efficiency of system development by managing queries externally.

1 is a conceptual diagram of generating a general executable file.

2A and 2B are conceptual diagrams of executable file generation of a program source including an SQL source as a related art.

3 is a conceptual diagram of a preferred embodiment of an efficient system for managing SQL queries in accordance with the present invention.

4A and 4B are conceptual diagrams showing the association between an extension of a file and a program.

5 is a conceptual diagram of a method for efficiently managing SQL queries according to the present invention.

In order to achieve the above object, the SQL query management system according to the present invention, SQL query file management unit for managing the SQL query in the form of a file having a specific extension; A memory unit for storing the SQL query file managed by the SQL query file manager in a specific directory; A program source unit into which the information of the SQL query file is inserted; A database unit; And a MetaSQL component that reads SQL query file information from the program source unit, accesses the database unit to obtain the query execution result, and returns the execution result to the program as a vector.

In addition, the SQL query management method according to the present invention comprises the steps of creating an SQL query through a JSP page and storing the file in a specific directory of the server; Inserting the file name, file path, and parameters of the SQL query into the program source; Reading, by a MetaSQL component, the file name, file path, and parameter value of an SQL query from the program source; Reading an SQL query file stored in the path; And accessing a database with the information read from the above, and transmitting the query execution result as a vector to a program source.

In addition, according to the present invention, the SQL query management method for changing the query content while maintaining the file name, file path, and parameter value information of the SQL query file inserted into the program source is modified by using the editor means of the JSP page. Deleting and storing; Reading, by a MetaSQL component, the file name, file path, and parameter value of an SQL query from the program source; Reading, by the MetaSQL component, an SQL query file stored in the path; And MetaSQL component characterized in that it comprises the step of accessing the database with the information read in the above to deliver the query execution result as a vector to the program source.

Hereinafter, a SQL query management system and a management method according to the present invention will be described in detail with reference to the accompanying drawings.

3 is a block diagram of a SQL query management system according to a preferred embodiment of the present invention.

As shown in FIG. 3, the SQL query management system according to the present invention includes a MetaSQL component 20, a program source unit 10, a JSP page 50, and a database 40.

If a programmer needs an SQL query within the program source, instead of inserting the SQL query directly into the program source, as in the prior art (see the conceptual diagram in FIG. 2), the programmer can direct the SQL query to a .sdt file via a JSP page 50 provided by the server. Create it and save it in a specific directory on the server.

.sdt files specify specific file types to implement the present invention. Commonly used .txt or .hwp (Korean application), .pds (Photoshop application), .ppt (powerpoint application), .xls (Excel application) It may be referred to as a file extension used in a specific application such as (see FIG. 4A).

4B is a conceptual diagram illustrating a relationship between JSP pages and .sdt files provided in the present invention. In the present invention, a specific file extension capable of storing SQL queries is defined as .sdt, and these .sdt files are shown and stored. It provides a JSP page 50 that functions as an editor that can be edited.

In this case, the JSP page 50 shows the structure of the folder where the server's .sdt file is stored, the name of the saved .sdt file, and the contents of the .sdt file when the specific .sdt file is selected in the Edit window. The contents of the .sdt file are SQL queries. You can register new SQL query as .sdt file through Edit window and edit / delete the registered .sdt file.

The registered .sdt file can be tested in a JSP page. The SQL query in this .sdt file can be connected to the database through the MetaSQL component to get the result, and the result is displayed in a JSP page.

When the .sdt file (SQL query) you want to use through the JSP page is completed, insert the path, file name and related parameters of the saved .sdt file into the program source.

The path of the .sdt file inserted into the program source is programmed to call the .sdt file from the program source.

When calling the .sdt file from the program source, you can pass along the values of the variable parameters required, and the program can receive the result of the query.

This is useful for managing SQL queries as files.

5 is a conceptual diagram for a SQL query management method according to a preferred embodiment of the present invention.

As shown in the figure, when the SQL query is directly coded within the program source, the program source must be recompiled even if only the SQL query is changed. However, when the SQL query is managed as an external file, it is easy to modify and recompile the source program. There is no need to do it.

Furthermore, if a system has a query commonly used by several program sources to retrieve a user's department code and department name, for example, when creating a product or making a payment, the common query retrieves the user's department code and department name. If you need to create a .sdt file, you don't have to code the same query in the program source, just call the MetaSQL component to write the query.

If the query is changed in the same way, the new SQL query will be applied to all programs that use the .sdt file simply by modifying the SQL query in the .sdt file without having to modify each program separately.

If the path and filename of the .sdt file you are using has not changed, the program sources do not need to be recompiled because the program sources read the .sdt file at the moment it is executed.

If you modify the .sdt file through the JSP page, the program source does not need to be recompiled. This is because the path to the .sdt file inserted into the program source has not changed.

The changed .sdt file is applied to the changed program. This is because it is programmed to read the .sdt file from the server at runtime.

3, the SQL query management system according to the present invention is as follows.

The MetaSQL component 20 receives a .sdt file path, a file name, and parameter values from a program source, reads a .sdt file of the corresponding path, and accesses a database to obtain a query execution result. Return the result to the program as a vector.

The program source 10 generally has the following configuration.

MetaSQLComp msc = new MetaSQLComp ();

Vector = msc.excuteFileVar query (sdt file path, .sdt file name, parameters);

Register the query to use as .sdt and call the MetaSQL component with the file path, file name, and parameters. With the above structure statement, the query result can be returned as a vector.

The .sdt files 30 are stored in a specific directory of the server through JSP pages and are read and executed by MetaSQL components.

The JSP page 50 shows the directory structure where the server's .sdt file is registered and provides an editor window that facilitates registration / modification / deletion of the .sdt file.

You can register / modify / delete SQL queries directly on the page and see the results of query execution. Executing SQL queries through JSP pages is the same as accessing the database through MetaSQL components in the program source.

When the developer presses the button to execute the .sdt file, the internal call of the MetaSQL component causes the MetaSQL component to read the .sdt file, access the database to get the result of executing the SQL query, and return the result to the JSP page.

As described above, according to the SQL query management method and system according to the present invention, the program source is changed when the SQL query is changed by managing the SQL query that has been inserted and managed in the program source in the form of a file with a specific extension from the outside. You don't have to rewrite and recompile anything; you only need to modify SQL queries in externally managed .sdt files.

In addition, the present invention has the effect of integrated management by integrated management without distributing and managing SQLs scattered and inserted in the program source.

Claims (8)

In a system for efficiently managing SQL queries, An SQL query file manager for managing the SQL query in a file format having a specific extension; A memory unit for storing the SQL query file managed by the SQL query file manager in a specific directory; A program source unit into which the information of the SQL query file is inserted; A database unit; And And a MetaSQL component that reads the SQL query file information from the program source unit, accesses the database unit, obtains the query execution result, and returns the execution result to the program as a vector. The method of claim 1, The information of the SQL query file inserted into the program source unit is a file name, a file path in which the file is stored, and an associated parameter value. The method of claim 1, The specific file extension that can store the SQL query is .sdt, and the SQL query file management unit comprises a JSP page including an editor means for editing the .sdt file. The method of claim 3, wherein The JSP page is installed on the server, SQL query management system, characterized in that the individual users can share. The method of claim 3, wherein The editor means for the JSP page includes a means for registering, modifying, and deleting SQL queries directly on the page. Writing an SQL query through a JSP page and storing the file in a specific directory of the server; Inserting the file name, file path, and parameters of the SQL query into the program source; Reading, by a MetaSQL component, the file name, file path, and parameter value of an SQL query from the program source; Reading an SQL query file stored in the path; And And accessing a database with the information read from the above information, and transmitting the query execution result as a vector to a program source. In the SQL query management method for changing the query content while maintaining the file name, file path, parameter value information of the SQL query file inserted into the program source, Modifying, deleting and storing the SQL query using the editor means of the JSP page; Reading, by a MetaSQL component, the file name, file path, and parameter value of an SQL query from the program source; Reading, by the MetaSQL component, an SQL query file stored in the path; And MetaSQL component accessing the database with the information read in the above step and passing the query execution result as a vector to the program source. The method according to claim 6 or 7, The JSP page calling a MetaSQL component; Reading, by the MetaSQL component, an SQL query file and accessing a database to obtain a result of performing an SQL query; And transmitting the execution result to the JSP page.