VALIDATING SQL QUERIES IN A REPORT

• US 20150261507A1
• Filed: 03/14/2014
• Published: 09/17/2015
• Est. Priority Date: 03/14/2014
• Status: Abandoned Application

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Bhagavan, Raghuvira, Chattopadhyay, Subhankar, P K, Pramod, Thengdi, Supriya
• Application Number:
  US14/210,443
• Publication Number:
  US 20150261507A1
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2428 Query predicate definition using graphical user interfaces, including menus and forms G06F16/2423 takes precedence
• CPC Class Codes: G06F 8/33 Intelligent editors

Abstract

Various embodiments of systems and methods for validating Structured Query Language (SQL) queries in a software programming language report during application development are described herein. The method involves receiving a selection of a specific SQL query in the report and automatically invoking a data preview editor interface. The selected query is rendered on the interface for execution. In an aspect, the selected query is parsed and a source part and a result part are extracted. Based on the extracted source part and result part, a data type definition and data declaration are automatically determined for the accessed data. Further, a dynamic subroutine is generated with the determined data type definition, data declaration, and the selected query and executed. A preview of the result of executing the dynamic subroutine is rendered in the data preview editor for validating the query.

Claims

1. A computer-implemented method for validating Structured Query Language (SQL) queries in a software programming language program during application development, comprising:

receiving a selection of a specific SQL query in the program;

automatically invoking a data preview editor interface and rendering the selected query on the interface;

in response to receiving an execute command, parsing the selected query;

extracting a source part and a result part from the selected query;

automatically determining a data type definition and data declaration based on the extracted source part and result part;

generating a dynamic subroutine with the determined data type definition, data declaration, and the selected query;

executing the dynamic subroutine to render a preview of query result in the data preview editor interface; and

validating the selected query based on the preview of the query result.

2. The method of claim 1 further comprising:

validating the selected query by assessing the query result rendered in the preview;

upon determining that the rendered query result is inaccurate, modifying the selected query;

executing the modified query for validating the modified query; and

iteratively modifying the selected query until a result of executing the modified query, rendered in the preview, is accurate.

3. The method of claim 2, further wherein,

upon determining that the modified query is valid, copying the modified query back into the program;

and

automatically provisioning data type definitions and data declarations for the modified query in the program for execution.

4. The method of claim 1, wherein automatically generating the data type definition and the data declaration further comprises:

identifying one or more tables accessible by the selected query based on the source part; and

determining that a user has authority to access the one or more tables prior to generating the dynamic subroutine.

5. The method of claim 1, further comprising:

determining whether the selected query includes an ‘

INTO’

part; and

upon determining that the selected query includes the ‘

INTO’

part, replacing the ‘

INTO’

part in the query with the generated data declaration.

6. The method of claim 1, further comprising:

determining whether the selected query includes a ‘

TOP’

part;

upon determining that the selected query includes the ‘

TOP’

part, replacing the ‘

TOP’

part in the query with a predefined row limit;

upon determining that the selected query does not include the ‘

TOP’

part, appending the ‘

TOP’

part to the selected query with the predefined row limit.

7. A computer program product comprising a non-transitory machine-readable medium storing instructions that, when executed by at least one programmable processor, cause the at least one programmable processor to perform operations comprising:

receiving a selection of a specific SQL query in a software programming language program;

automatically invoking a data preview editor interface and rendering the selected query on the interface;

in response to receiving an execute command, parsing the selected query;

extracting a source part and a result part from the selected query;

automatically determining a data type definition and data declaration based on the extracted source part and result part;

generating a dynamic subroutine with the determined data type definition, data declaration, and the selected query;

executing the dynamic subroutine to render a preview of query result in the data preview editor interface; and

validating the selected query based on the preview of the query result.

8. The computer program product of claim 7, wherein the operations further comprise:

validating the selected query by assessing the query result rendered in the preview;

upon determining that the rendered query result is inaccurate, modifying the selected query;

executing the modified query for validating the modified query; and

iteratively modifying the selected query until a result of executing the modified query, rendered in the preview, is accurate.

9. The computer program product of claim 8, wherein the operations further comprise:

determining that the modified query is valid;

copying the modified query back into the program; and

automatically provisioning data type definitions and data declarations for the modified query in the program for execution.

10. The computer program product of claim 7, wherein automatically generating the data type definition and the data declaration further comprises:

identifying one or more tables accessible by the selected query based on the source part; and

determining that a user has authority to access the one or more tables prior to generating the dynamic subroutine.

11. The computer program product of claim 7, wherein the operations further comprise:

determining whether the selected query includes an ‘

INTO’

part; and

upon determining that the selected query includes the ‘

INTO’

part, replacing the ‘

INTO’

part in the query with the generated data declaration.

12. A computer program product as in claim 7, wherein the operations further comprise:

determining whether the selected query includes a ‘

TOP’

part;

upon determining that the selected query includes the ‘

TOP’

part, replacing the ‘

TOP’

part in the query with a predefined row limit;

upon determining that the selected query does not include the ‘

TOP’

part, appending the ‘

TOP’

part to the selected query with the predefined row limit.

13. A system comprising:

at least one programmable processor; and

a machine-readable medium storing instructions that, when executed by the at least one programmable processor, cause the at least one programmable processor to perform operations comprising;

receiving a selection of a specific SQL query in a software programming language program;

automatically invoking a data preview editor interface and rendering the selected query on the interface;

in response to receiving an execute command, parsing the selected query;

extracting a source part and a result part from the selected query;

automatically determining a data type definition and data declaration based on the extracted source part and result part;

generating a dynamic subroutine with the determined data type definition, data declaration, and the selected query;

executing the dynamic subroutine to render a preview of query result in the data preview editor interface; and

validating the selected query based on the preview of the query result.

14. The system of claim 13 wherein the data preview editor interface comprises a text area for receiving SQL queries and a preview area for rendering query results.

15. The system of claim 13, wherein the software programming language comprises an advanced business application programming (ABAP) language.

16. The system of claim 13, wherein the selected SQL query is an Open SQL query.

17. The system of claim 13, wherein the data preview editor interface further comprises a user-selectable option for executing the selected query.

18. The system of claim 13, wherein the data preview editor interface further comprises a filter for filtering the query result.