Transparent parameter marker support for a relational database over a network

• US 6,735,594 B1
• Filed: 06/23/2000
• Issued: 05/11/2004
• Est. Priority Date: 06/23/2000
• Status: Active Grant

• Current Assignee: Google LLC (Alphabet Inc.)
• Original Assignee: International Business Machines Corporation
• Inventors: Hall, Harold Hershey Jr., Nguyen, Lynh, Runge, Norbert, Zimowski, Melvin Richard
• Primary Examiner(s): Pardo, Thuy N.
• Application Number:
  US09/602,277
• Time in Patent Office: 1,418 Days
• Field of Search: 707/1, 707/2, 707/3, 707/4, 707/104, 707/513, 707/10, 707/9, 707/102, 717/127, 709/330
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2438 Embedded query languages
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99934 Query formulation, input preparation, or translation
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface

Abstract

A method and system for retrieving data from a database over a network includes: generating an executable statement at a gateway in accordance with a macro language file, where the executable statement comprises one or more a parameter markers; passing the executable statement from the gateway to database software; and passing a value for each parameter marker from the gateway to the database software. The present invention allows the Net.Data gateway to support parameter markers in place of Net.Data variables. This allows the Net.Data gateway to take advantage of the dynamic Structured Query Language (SQL) statement caching feature available with conventional database systems. With the use of this invention, the execution of the SQL statements occurs more rapidly, saving resources and improving performance. Additionally, the Net.Data variables may be automatically replaced with parameter markers, with the Net.Data variables and their values maintained. This provides transparent support for parameter markers.

Claims

1. A method for retrieving data from a database over a network, comprising the steps of:

(a) generating an executable statement at a gateway in accordance with a macro language file, wherein the executable statement comprises one or more parameter markers;

(b) passing the executable statement comprising the parameter markers from the gateway to database software, wherein one or more variables can be found to each parameter marker;

(c) passing a value for each parameter marker from the gateway to the database software; and

(d) sending a request from the gateway to the database software to execute the executable statement, wherein each parameter marker is substituted for its value when the executable statement is executed.

2. The method of claim 1, wherein the executable statement comprises a Structured Query Language (SQL) statement.

3. The method of claim 1, wherein the gateway comprises a Net.Data gateway.

4. The method of claim 1, wherein the parameter marker is a syntactical element, wherein one or more variables can be found to the parameter marker, wherein each parameter marker is replaced by a value when the executable statement is executed.

5. The method of claim 1, wherein the passing step (b) occurs in a first function call by the gateway to the database software.

6. The method of claim 1, wherein the passing step (c) occurs in a second function call by the gateway to the database software.

7. The method of claim 1, wherein the database software comprises a relational database management system (RDBMS) software.

8. The method of claim 1, wherein prior to the providing step (a), comprises:

(a1) determining if a status variable for a statement in the macro language file indicates that variable replacement should occur;

(a2) automatically replacing each variable in the statement with a parameter marker if the status variable indicates that variable replacement should occur; and

(a3) maintaining each variable and a value for each variable.

9. The method of claim 8, wherein each variable comprises a Net.Data variable.

10. A system, comprising:

a gateway capable of being coupled to a database server, having database software further comprising a first process for generating an executable statement in accordance with a macro language file wherein the executable statement comprises one or more parameter markers, a second process for passing the executable statement comprising the parameter markers to the database software, wherein one or more variables can be found to each parameter marker and a third process for passing a value for each parameter marker to the database software, wherein each parameter marker is substituted for its value when the executable statement is executed.

11. The system of claim 10, wherein the database server comprises a RDBMS server.

12. The system of claim 10, wherein the database software comprises RDBMS software.

13. The system of claim 10, wherein the gateway comprises a Net.Data gateway.

14. The system of claim 10, wherein the executable statement comprises a SQL statement.

15. The system of claim 10, wherein the parameter marker is a syntactical element, wherein one or more variables can be found to the parameter marker, wherein each parameter marker can be replaced by a value when the executable statement is executed.

16. The system of claim 10, wherein the SQL statement is passed to the database software in a first function call.

17. The system of claim 10, wherein the values are passed to the database software in a second function call.

18. The system of claim 10, wherein the gateway is capable of being coupled to a web server coupled to a plurality of web clients.

19. A method for retrieving data from a database over a network, comprising the steps of:

(a) determining if a status variable for a statement in a macro language file in a gateway indicates that variable replacement should occur;

(b) automatically replacing each variable in the statement with a parameter marker if the status variable indicates that variable replacement should occur;

(c) maintaining each variable and a value for each variable;

(d) generating an executable statement comprising the parameter marker for each variable at the gateway in accordance with the statement in the macro language file;

(e) passing the executable statement comprising the parameter marker for each variable to database software wherein one or more variables can he found to the parameter marker;

(f) passing a value for each parameter marker to the database software; and

(g) sending a request from the gateway to the database software to execute the executable statement, wherein each parameter marker is substituted for its value when the executable statement is executed.

20. The method of claim 19, wherein the executable statement comprises a SQL statement.

21. The method of claim 19, wherein the gateway comprises a Net.Data gateway.

22. The method of claim 19, wherein the parameter marker is a syntactical element, wherein one or more variables can be found to the parameter marker, wherein each parameter marker is replaced by a value when the executable statement is executed.

23. The method of claim 19, the database software comprises RDBMS software.

24. A computer readable medium with program instructions for retrieving data from a database over a network, the instructions for:

(a) generating an executable statement at a gateway in accordance with a macro language file, wherein the executable statement comprises one or more parameter markers;

(b) passing the executable statement comprising the parameter markers from the gateway to database software, wherein one or more variables can be found to each parameter marker;

(c) passing a value for each parameter marker from the gateway to the database software; and

(d) sending a request from the gateway to the database software to execute the executable statement, wherein each parameter marker is substituted for its value by the database software when the executable statement is executed.

25. A computer readable medium with program instructions for retrieving data from a database over a network, the instructions for:

(a) determining if a status variable for a statement in a macro language file in a gateway indicates that variable replacement should occur;

(b) automatically replacing each variable in the statement with a parameter marker if the status variable indicates that variable replacement should occur;

(c) maintaining each variable and a value for each variable;

(d) generating an executable statement comprising the parameter marker for each variable at the gateway in accordance with the macro language file;

(e) passing the executable statement comprising the parameter marker for each variable to a database software, wherein one or more variables can be found to the parameter marker;

(f) passing a value for each parameter marker to the database software; and

(g) sending a request from the gateway to the database software to execute the executable statement, wherein each parameter marker is substituted for its value by the database software when the executable statement is executed.

26. A system, comprising:

means for generating an executable statement at a gateway in a network in accordance with a macro language file, wherein the executable statement comprises one or more parameter markers;

means for passing the executable statement comprising the parameter markers to database software, wherein one or more variables can be found to each parameter marker;

means for passing a value for each parameter marker to the database software; and

means for sending a request from the gateway to the database software to execute the executable statement, wherein each parameter marker is substituted for its value by the database software when the executable statement is executed.

27. A system, comprising:

means for determining if a status variable for a statement in a macro language file in a gateway indicates that variable replacement should occur;

means for automatically replacing each variable in the statement with a parameter marker if the status variable indicates that variable replacement should occur;

means for maintaining each variable and a value for each variable;

means for generating an executable statement comprising the parameter marker for each variable at the gateway in accordance with the macro language file;

means for passing the executable statement comprising the parameter marker for each variable to a database software, wherein one or more variables can be found to the parameter marker;

means for passing a value for each parameter marker to the database software; and

means for sending a request from the gateway to the database software to execute the executable statement, wherein each parameter marker is substituted for its value by the database software when the executable statement is executed.

28. A method for retrieving data from a database over a network, comprising the steps of:

(a) generating a dynamic Structured Query Language (SQL) statement at a gateway in accordance with a macro language file, wherein the dynamic SQL statement comprises one or more parameter markers;

(b) passing the dynamic SQL statement comprising the parameter markers from the gateway to database software;

(c) passing a value for a variable bound to each parameter marker in the dynamic SQL statement from the gateway to the database software; and

(d) sending a request from the gateway to the database software to execute the dynamic SQL statement, wherein each parameter marker is substituted for its value when the dynamic SQL statement is executed.

29. A system, comprising:

a gateway capable of being coupled to a database server, having database software further comprising a first process for generating a dynamic Structure Query Language (SQL) statement in accordance with a macro language file wherein the dynamic SQL statement comprises one or more parameter markers, a second process for passing the dynamic SQL statement comprising the parameter markers to the database software, and a third process for passing a value for a variable bound to each parameter marker in the dynamic SQL statement to the database software, wherein each parameter marker is substituted for its value when the dynamic SQL statement is executed.

30. A method for retrieving data from a database over a network, comprising the steps of:

(a) determining if a status variable for a statement in a macro language file in a gateway indicates that variable replacement should occur;

(b) automatically replacing each variable in the statement with a parameter marker if the status variable indicates that variable replacement should occur;

(c) maintaining each variable and a value for each variable;

(d) generating a dynamic Structure Query Language (SQL) statement comprising the parameter marker for each variable at the gateway in accordance with the statement in the macro language file;

(e) passing the dynamic SQL statement comprising the parameter marker for each variable to database software;

(f) passing a value for each variable for each parameter marker to the database software; and

(g) sending a request from the gateway to the database software to execute the dynamic SQL statement, wherein each parameter marker is substituted for its value when the dynamic SQL statement is executed.

31. A computer readable medium with program instructions for retrieving data from a database over a network, the instructions for:

(a) generating a dynamic Structure Query Language (SQL) statement at a gateway in accordance with a macro language file, wherein the dynamic SQL statement comprises one or more parameter markers;

(b) passing the dynamic SQL statement comprising the parameter markers from the gateway to database software;

(c) passing a value for a variable for each parameter marker from the gateway to the database software; and

(d) sending a request from the gateway to the database software to execute the dynamic SQL statement, wherein each parameter marker is substituted for its value by the database software when the dynamic SQL statement is executed.

32. A computer readable medium with program instructions for retrieving data from a database over a network, comprising the instructions for:

(a) determining if a status variable for a statement in a macro language file in a gateway indicates that variable replacement should occur;

(b) automatically replacing each variable in the statement with a parameter marker if the status variable indicates that variable replacement should occur;

(c) maintaining each variable and a value for each variable;

(d) generating a dynamic Structure Query Language (SQL) statement comprising the parameter marker for each variable at the gateway in accordance with the statement in the macro language file;

(e) passing the dynamic SQL statement comprising the parameter marker for each variable to database software;

(f) passing a value for each variable for each parameter marker to the database software; and

(g) sending a request from the gateway to the database software to execute the dynamic SQL statement, wherein each parameter marker is substituted for its value when the dynamic SQL statement is executed.

33. A system, comprising:

means for generating a dynamic Structured Query Language (SQL) at a gateway in a network in accordance with a macro language file, wherein the dynamic SQL statement comprises one or more parameter markers;

means for passing the dynamic SQL statement comprising the parameter markers to database software;

means for passing a value for a variable for each parameter marker to the database software; and

means for sending a request from the gateway to the database software to execute the dynamic SQL statement, wherein each parameter marker is substituted for its value by the database software when the dynamic SQL statement is executed.

34. A system, comprising:

means for determining if a status variable for a statement in a macro language file in a gateway indicates that variable replacement should occur;

means for automatically replacing each variable in the statement with a parameter marker if the status variable indicates that variable replacement should occur;

means for maintaining each variable and a value for each variable;

means for generating a dynamic Structure Query Language (SQL) statement comprising the parameter marker for each variable at the gateway in accordance with the macro language file;

means for passing the dynamic SQL statement comprising the parameter marker for each variable to a database software;

means for passing a value for a variable for each parameter marker to the database software; and

means for sending a request from the gateway to the database software to execute the dynamic SQL statement, wherein each parameter marker is substituted for its value by the database software when the dynamic SQL statement is executed.