Various embodiments of systems and methods for validating Structured Query Language (SQL) queries in a database-accessing software application during application development are described herein. In some embodiments, an SQL query can be copied, during debugging of the software application, from a program editor used to define the software application into an SQL console that facilitates modifying and executing the query and displays data resulting from the execution of the query. Upon developer validation of the SQL query, the validated query may be copied back into the software application to substitute the original query. The SQL query may include one or more unresolved parameters that can be resolved by the SQL console via access to memory in which the software application is executed during debugging.
Legal claims defining the scope of protection, as filed with the USPTO.
1. A computer-implemented method for testing Structured Query Language (SQL) queries in a software application during application development, the method comprising: executing, in a debug mode, a database-accessing software application including a main program written in a non-SQL programming language and, embedded in the main program, an SQL query, the debug mode specifying a breakpoint in the main program that is associated with the SQL query, the SQL query comprising at least one unresolved parameter having a value that is unresolved in the SQL query itself and is determinable during execution of the main program; copying the SQL query associated with the breakpoint, in response to user selection of the SQL query in a program editor upon reaching the breakpoint, from the program editor into an SQL console; and by the SQL console, resolving the at least one unresolved parameter based on the execution of the main program; by the SQL console, parsing the SQL query to extract a source part and a result part from the SQL query, and automatically determining a data type definition and data declaration based on the extracted source part and result part and identifying one or more tables accessible by the SQL query based on the source part; determining that a user has authority to access the one or more tables and, following the determination, causing execution of the SQL query, separately from the execution of the main program, by generating and causing execution of a dynamic subroutine with the determined data type definition and data declaration to thereby retrieve data from a database in accordance with the SQL query; and displaying the retrieved data to the user.
2. The method of claim 1 , wherein the SQL query comprises at least one of a select statement, a create statement, a delete statement, or an update statement.
3. The method of claim 1 , further comprising modifying the SQL query in response to user input, executing the modified SQL query to thereby retrieve data from the database in accordance with the modified SQL query, and displaying the data retrieved in accordance with the modified SLQ query to the user, wherein the value of the at least one parameter resolved based on the execution of the main program remains frozen during the modification and execution of the SQL query.
4. The method of claim 3 , further comprising, upon validation of the modified SQL query by the user, substituting the modified SQL query for the SQL query associated with the breakpoint.
5. The method of claim 1 , wherein executing the software application in debug mode comprises computing, during execution of the main program, one or more values of one or more parameters including the at least one unresolved parameter of the SQL query, and storing the computed one or more parameter values in memory allocated to execution of the main program, and wherein resolving the at least one unresolved parameter comprises accessing, by the SQL console, the memory allocated to execution of the main program to retrieve the value of the at least one unresolved parameter therefrom.
6. The method of claim 5 , further comprising, by the SQL console, allocating memory to execution of the SQL query and storing the retrieved value of the at least one unresolved parameter therein, the memory allocated to execution of the SQL query being separate from the memory allocated to execution of the main program.
7. The method of claim 1 , wherein the breakpoint is placed in the main program or immediately preceding the SQL query.
8. A system for testing Structured Query Language (SQL) queries in a software application during application development, the system comprising: one or more computer processors; and one or more machine-readable storage media storing a plurality of instructions for execution by the one or more computer processors, the instructions comprising: a program editor for defining, via user input, a database-accessing software application including a main program written in a non-SQL programming language and, embedded in the main program, an SQL query comprising at least one unresolved parameter having a value that is unresolved in the SQL query itself and is determinable during execution of the main program; a program executor to execute the software application in a debug mode specifying a breakpoint in the main program that is associated with the SQL query; and an SQL console to receive a copy of the SQL query upon user selection of the query in the program editor during execution of the software application in the debug mode, resolve the at least one unresolved parameter based on the execution of the main program, parse the SQL query to extract a source part and a result part from the SQL query, and automatically determine a data type definition and a data declaration based on the extracted source part and result part and identify one or more tables accessible by the SQL query based on the source part; determine that a user has authority to access the one or more tables and, following the determination, cause execution of the SQL query, separately from the execution of the main program, by generating and causing execution of a dynamic subroutine with the determined data type definition and data declaration to thereby retrieve data from a database in accordance with the SQL query, and display the retrieved data to the user.
9. The system of claim 8 , wherein the SQL console comprises a query editor for modifying, in response to user input, the SQL query.
10. The system of claim 8 , wherein the SQL console comprises a data previewer for displaying the retrieved data.
11. The system of claim 8 , wherein the SQL console comprises a query executor configured to allocate memory to the execution of the SQL query, and to access memory allocated to the execution of the main program to copy therefrom the value of the at least one parameter resulting from the execution of the main program into the memory allocated to the execution of the SQL query, the memory allocated to execution of the SOL query being separate from the memory allocated to execution of the main program.
12. The system of claim 11 , wherein the SQL console is further configured to cause execution of the SQL query by the program executor.
13. The system of claim 8 , wherein the SQL console is configured, upon validation of the SQL query by the user, to copy the validated SQL query from the SQL console into the software application defined in the program editor.
14. The system of claim 9 , wherein the program editor and the program executor are implemented in ASAP in Eclipse.
15. A non-transitory computer-readable medium storing instructions for testing, during execution, in debug mode, of a database-accessing software application comprising a main program written in a non-SQL programming language, a Structured Query Language (SQL) query embedded in the main program and including an unresolved parameter having a value that is unresolved in the SQL query itself and is determinable during execution of the main program, the instructions, when executed by at least one processor, causing the at least one processor to: copy the SQL query, upon user selection thereof when a debugging breakpoint in the main program that is associated with the SQL query is reached, from a program editor used to define the software application into a query editor; resolve the unresolved-parameter based on the execution of the main program; parse the SQL query to extract a source part and a result part from the SQL query, automatically determine a data type definition and data declaration based on the extracted source part and result part and identify one or more tables accessible by the SQL query based on the source part; determine that a user has authority to access the one or more tables and, following the determination, cause execution of the SQL query, separately from the execution of the main program, by generating and causing execution of a dynamic subroutine with the determined data type definition and data declaration to retrieve data from a database in accordance with the SQL query; and display the retrieved data.
16. The computer-readable medium of claim 15 , wherein resolving the parameter comprises copying a value of the parameter from memory allocated to the execution of the main program to separate memory allocated to the execution of the SQL query.
Cooperative Patent Classification codes for this invention. Click any code to explore related patents in that topic.
November 25, 2014
October 3, 2017
Browse 5M+ US patents with plain-English claim translations and AI-generated analysis.