Computer-implemented method and system for handling and transforming database queries in a fourth generation language

• US 8,498,996 B2
• Filed: 11/03/2008
• Issued: 07/30/2013
• Est. Priority Date: 11/03/2008
• Status: Active Grant

• Current Assignee: SAS Institute Incorporated
• Original Assignee: SAS Institute Incorporated
• Inventors: Whitcher, Michael Stephen, Langston, Richard Dean, Plemmons, Howard Jr.
• Primary Examiner(s): LIN, SHEW FEN
• Application Number:
  US12/263,760
• Publication Number:
  US 20100114932A1
• Time in Patent Office: 1,730 Days
• Field of Search: 707/759, 707/769
• US Class Current: 707/759
• 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 processing a query, comprising:

receiving a native syntax query requesting data stored in a non-native database that uses a non-native syntax, wherein the query is received at an application that is separate from the non-native database, wherein the query requests that the data be retrieved from the non-native database, wherein the query requests that a processing operation be performed on the requested data by the application, wherein the query includes one or more expressions, and wherein one or more of the expressions includes one or more functions;

parsing the native syntax query, wherein parsing includes identifying a function within an expression that cannot be processed by the non-native database, wherein the function specifies the processing operation to be performed on the requested data by the application, wherein a plurality of labels are associated with the function and the expression, and wherein labels include constant labels and format labels;

analyzing the function and the expression to determine a context of the function within the expression, wherein the context describes how the function is used within the expression;

generating, using one or more data processors, a final expression query by obtaining a control string from an internal table for each of the plurality of labels associated with the function and the expression, wherein label modifiers are applied to format labels;

transforming the native syntax query into an equivalent non-native syntax query, wherein transforming includes parsing and inserting the final expression query into the equivalent non-native syntax query using the function, the expression, and the context to translate the function and the expression into multiple functions and multiple expressions that are configured for processing by a non-native database system;

transmitting the equivalent non-native syntax query to a non-native database system to generate results and to perform the processing operation on the generated results;

receiving processed results from the non-native database system; and

transmitting the processed results to a client application.

2. The method of claim 1, wherein the native syntax query is generated by the client application, and wherein the client application is a native system application.

3. The method of claim 2, wherein the application is a non-Structured Query Language (SQL) application, and wherein the non-native syntax is an SQL query syntax.

4. The method of claim 3, wherein the function and expression are not recognized by the SQL query syntax.

5. The method of claim 1, wherein the equivalent non-native syntax query is generated in an iterative manner using a list of formats.

6. The method of claim 1, wherein the internal table maps native syntax to non-native syntax.

7. The method of claim 1, wherein the native syntax and the non-native syntax are each associated with a different fourth generation language.

8. The method of claim 1, wherein generating the final expression query includes using metadata that describes how character or numeric data is transformed.

9. The method of claim 1, wherein generating the final expression query includes referencing a lookup table to determine one or more allowed values in the non-native syntax.

10. The method of claim 1, wherein the native syntax is pre-defined.

11. The method of claim 1, wherein generating the final expression query includes transforming numeric data into character data.

12. The method of claim 1, wherein generating the final expression query is based upon structure of data in the non-native database.

13. The method of claim 12, wherein generating the final expression query is based upon a data type of a field in the non-native database.

14. The method of claim 1, wherein an argument in the function or the expression is in a form unrecognized by the non-native syntax, and wherein the argument is translated without using a pre-defined list of substitute values.

15. The method of claim 1, wherein generating the final expression query includes transforming an upper case string to a numeric value.

16. The method of claim 1, wherein generating the final expression query includes transforming a stripped or trimmed string into a numeric value.

17. The method of claim 1, wherein generating the final expression query includes dividing the function and the expression into subexpressions, wherein each subexpression is transformed based upon a relationship of that subexpression to other subexpressions.

18. The method of claim 1, wherein determining the context of the function within the expression includes considering an argument of the function.

19. A computer implemented system for processing a query, comprising:

one or more physical data processors;

one or more non-transitory computer-readable storage mediums containing instructions configured to cause the one or more processors to perform operations including;

receiving a native syntax query requesting data stored in a non-native database that uses a non-native syntax, wherein the query is received at an application that is separate from the non-native database, wherein the query requests that the data be retrieved from the non-native database, wherein the query requests that a processing operation be performed on the requested data by the application, wherein the query includes one or more expressions, and wherein one or more of the expressions includes one or more functions;

parsing the native syntax query, wherein parsing includes identifying a function within an expression that cannot be processed by the non-native database, wherein the function specifies the processing operation to be performed on the requested data by the application, wherein a plurality of labels are associated with the function and the expression, and wherein labels include constant labels and format labels;

analyzing the function and the expression to determine a context of the function within the expression, wherein the context describes how the function is used within the expression;

generating a final expression query by obtaining a control string from an internal table for each of the plurality of labels associated with the function and the expression, wherein label modifiers are applied to format labels;

transforming the native syntax query into an equivalent non-native syntax query, wherein transforming includes parsing and inserting the final expression query into the equivalent non-native syntax query using the function, the expression, and the context to translate the function and the expression into multiple functions and multiple expressions that are configured for processing by a non-native database system;

transmitting the equivalent non-native syntax query to a non-native database system to generate results and to perform the processing operation on the generated results;

receiving processed results from a non-native database system; and

transmitting the processed results to a client application.

20. The system of claim 19, wherein the native syntax query is generated by the client application, and wherein the client application is a native system application.

21. The system of claim 20, wherein the application is a non-Structured Query Language (SQL) application, and wherein the non-native syntax is an SQL query syntax.

22. The system of claim 21, wherein the function and expression are not recognized by the SQL query syntax.

23. The system of claim 19, wherein the equivalent non-native syntax query is generated in an iterative manner using a list of formats.

24. The system of claim 19, wherein the internal table maps native syntax to non-native syntax.

25. The system of claim 19, wherein the native syntax and the non-native syntax are each associated with a different fourth generation language.

26. The system of claim 19, wherein generating the final expression query includes using metadata that describes how character or numeric data is transformed.

27. The system of claim 19, wherein generating the final expression query includes referencing a lookup table to determine one or more allowed values in the non-native syntax.

28. The system of claim 19, wherein the native syntax is pre-defined.

29. The system of claim 19, wherein generating the final expression query includes transforming numeric data into character data.

30. The system of claim 19, wherein generating the final expression query is based upon structure of data in the non-native database.

31. The system of claim 30, wherein generating the final expression query is based upon a data type of a field in the non-native database.

32. The system of claim 19, wherein an argument in the function or the expression is in a form unrecognized by the non-native syntax, and wherein the argument is translated without using a pre-defined list of substitute values.

33. The system of claim 19, wherein generating the final expression query includes transforming an upper case string to a numeric value.

34. The system of claim 19, wherein generating the final expression query includes transforming a stripped or trimmed string into a numeric value.

35. The system of claim 19, wherein generating the final expression query includes dividing the function and the expression into subexpressions, wherein each subexpression is transformed based upon a relationship of that subexpression to other subexpressions.

36. The system of claim 19, wherein determining the context of the function within the expression includes considering an argument of the function.

37. A computer-program product for processing a query, tangibly embodied in a machine-readable non-transitory storage medium, including instructions configured to cause a data processing apparatus to:

receive a native syntax query requesting data stored in a non-native database that uses a non-native syntax, wherein the query is received at an application that is separate from the non-native database, wherein the query requests that the data be retrieved from the non-native database, wherein the query requests that a processing operation be performed on the requested data by the application, wherein the query includes one or more expressions, and wherein one or more of the expressions includes one or more functions;

parse the native syntax query, wherein parsing includes identifying a function within an expression that cannot be processed by the non-native database, wherein the function specifies the processing operation to be performed on the requested data by the application, wherein a plurality of labels are associated with the function and the expression, and wherein labels include constant labels and format labels;

analyze the function and the expression to determine a context of the function within the expression, wherein the context describes how the function is used within the expression;

generate a final expression query by obtaining a control string from an internal table for each of the plurality of labels associated with the function and the expression, wherein label modifiers are applied to format labels;

transform the native syntax query into an equivalent non-native syntax query, wherein transforming includes parsing and inserting the final expression query into the equivalent non-native syntax query using the function, the expression, and the context to translate the function and the expression into multiple functions and multiple expressions that are configured for processing by a non-native database system;

transmit the equivalent non-native syntax query to a non-native database system to generate results and to perform the processing operation on the generated results;

receive processed results from the non-native database system; and

transmit the processed results to a client application.

38. The computer-program product of claim 37, wherein the native syntax query is generated by the client application, and wherein the client application is a native system application.

39. The computer-program product of claim 38, wherein the application is a non-Structured Query Language (SQL) application, and wherein the non-native syntax is an SQL query syntax.

40. The computer-program product of claim 39, wherein the function and expression are not recognized by the SQL query syntax.

41. The computer-program product of claim 37, wherein the equivalent non-native syntax query is generated in an iterative manner using a list of formats.

42. The computer-program product of claim 37, wherein the internal table maps native syntax to non-native syntax.

43. The computer-program product of claim 37, wherein the native syntax and the non-native syntax are each associated with a different fourth generation language.

44. The computer-program product of claim 37, wherein generating the final expression query includes using metadata that describes how character or numeric data is transformed.

45. The computer-program product of claim 37, wherein generating the final expression query includes referencing a lookup table to determine one or more allowed values in the non-native syntax.

46. The computer-program product of claim 37, wherein the native syntax is pre-defined.

47. The computer-program product of claim 37, wherein generating the final expression query includes transforming numeric data into character data.

48. The computer-program product of claim 37, wherein generating the final expression query is based upon structure of data in the non-native database.

49. The computer-program product of claim 48, wherein generating the final expression query is based upon a data type of a field in the non-native database.

50. The computer-program product of claim 37, wherein an argument in the function or the expression is in a form unrecognized by the non-native syntax, and wherein the argument is translated without using a pre-defined list of substitute values.

51. The computer-program product of claim 37, wherein generating the final expression query includes transforming an upper case string to a numeric value.

52. The computer-program product of claim 37, wherein generating the final expression query includes transforming a stripped or trimmed string into a numeric value.

53. The computer-program product of claim 37, wherein generating the final expression query includes dividing the function and the expression into subexpressions, wherein each subexpression is transformed based upon a relationship of that subexpression to other subexpressions.

54. The computer-program product of claim 37, wherein determining the context of the function within the expression includes considering an argument of the function.