Computer-Implemented Method and System for Handling and Transforming Database Queries in a Fourth Generation Language

• US 20100114932A1
• Filed: 11/03/2008
• Published: 05/06/2010
• Est. Priority Date: 11/03/2008
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Plemmons, Howard JR., Langston, Richard Dean, Whitcher, Michael Stephen
• Granted Patent: US 8,498,996 B2
• Time in Patent Office: Days
• US Class Current: 707/760
• CPC Class Codes: G06F 16/2452 Query translation

Abstract

A computer-implemented method and system allows generating a query and a first fourth generation language at a native system to a non-native database which is capable of processing queries in a second fourth generation language. A query initiated by a client applicant in a first fourth generation language is analyzed to determine if it can be processed by a non-native system capable of processing queries in a second fourth generation language. Non-standard syntax representative of a function and the query that cannot be evaluated by the non-native database system is identified. If the syntax is discovered, the query is transformed through the use of formats or through an algorithm process into an equivalent query expression that can be processed by the database system.

Claims

1. A computer-implemented method for generating a query in a first fourth generation language at a native system to a non-native database system capable of processing queries in a second fourth generation language, comprising the steps of:

analyzing a query initiated by a client application in a first fourth generation language at a native system to a non-native system database capable of processing queries in a second fourth generation language, to identify specific non-standard syntax representative of a function in the query that cannot be evaluated by the non-native database system;

if said specific syntax is discovered, transforming the query through the use of formats into an equivalent query expression that can be processed by the non-native database system; and

transmitting the equivalent query expression to the non-native database system for having the non-native database system return results responsive to the query, to the client application.

2. The method of claim 1, wherein said query containing specific non-standard syntax is generated by a software program at the native system separate from the non-native database system.

3. The method of claim 2, wherein said formats operate to transform numbers or strings into other strings.

4. The method according to claim 1, wherein the client application is a SAS PROC application, and said specific non-standard syntax query is transformed into an SQL query processable by the non-native database system.

5. The method according to claim 1, wherein said specific non-standard syntax is generated by a client program as a specific function not supported as an SQL query.

6. The method of claim 1, wherein said equivalent query expression is generated in an iterative manner from a list of formats.

7. The method of claim 1, wherein the specific non-standard syntax is extracted from the query to conduct said transformation.

8. The method of claim 1, wherein said formats comprise at least one table which maps queries in the first fourth generation language to equivalent queries in the second fourth generation language.

9. The method of claim 1, wherein said equivalent query expression in the second fourth generation language is an SQL query expression.

10. The method of claim 8, wherein said formats comprise metadata descriptive of how character or numeric data is to be transformed.

11. The method of claim 5, further comprising conducting a lookup of the client program non-standard syntax to find allowed values, and generating a SQL query based on said allowed values.

12. A computer-implemented method for generating a query is a first fourth generation language at a native system to a non-native database system capable of processing queries in a second fourth generation language, comprising the steps of:

analyzing a query indicated by a client application in a first fourth generation language at a native system to a non-native system database capable of processing queries in a second fourth generation language, to identify specific non-standard syntax representative of a function in the query that cannot be processed by the non-native database system;

if said specific syntax in discovered, transforming the query through an algorithmic process into an equivalent query expression that can be processed by the database system; and

transmitting the equivalent query expression to the non-native database system for having the non-native database system return results responsive to the query to the client application.

13. The method of claim 12, wherein said query in said first fourth generation language is user defined.

14. The method of claim 12, wherein said algorithmic process transforms numeric and character data into character data.

15. The method according to claim 12, wherein said specific non-standard syntax is generated by a client program as a specific function not supported as an SQL query.

16. The method of claim 12, wherein the specific non-standard syntax is extracted from the query to conduct said transformation.

17. The method of claim 15, further comprising conducting a lookup of the client program non-standard syntax to find allowed values, and generating a SQL query based on said allowed values.

18. A computer system for generating a query in a first fourth generation language, comprising:

a native system running a program in a first fourth generation language, and having a program module for generating a query to a non-native database system capable of processing queries in a second fourth generation language;

a non-native database system adapted for processing queries in a second fourth generation language; and

a program module in the native system for analyzing a query generated in the native system for identifying specific non-standard syntax representative of a function in the query that cannot be evaluated by the non-native database system, and said program module configured for transforming the query through the use of at least one of the formats and an algorithmic process, into an equivalent query expression that can be processed by the non-native database system and for transmitting the equivalent query expression to the non-native database system.

19. The system of claim 18, wherein said formats operate to transform numbers or strings.

20. The system of claim 18, wherein said algorithmic process transforms numeric and character data into character data

21. The system of claim 20, wherein said query in said first fourth generation language is user-defined.

22. The system of claim 19, wherein said formats comprise at least one table which maps queries in the first fourth generation language to equivalent queries in the second fourth generation language.