System and method for virtualization of relational stored procedures in non-native relational database systems

• US 20080016080A1
• Filed: 07/12/2006
• Published: 01/17/2008
• Est. Priority Date: 07/12/2006
• Status: Active Grant

• Current Assignee: X Corp. (f/k/a Twitter, Inc.) (X Holdings Corp.)
• Original Assignee: International Business Machines Corporation
• Inventors: Lin, Eileen Tien, Labrie, Jacques Joseph, Korn, Stefan
• Granted Patent: US 7,739,296 B2
• Time in Patent Office: Days
• US Class Current: 707/10
• CPC Class Codes: G06F 16/2443 Stored procedures

Abstract

A system, method, and program product are provided that identifies a remote stored procedure stored in a remote database management system, and automatically generates a local stored procedure stored in a local database management system. To automatically generate the local stored procedure, local and remote metadata are gathered corresponding, respectively, to a local database management system and a remote database management system. The remote metadata is used to create a call statement to the remote stored procedure. The created invocation method maps input values, input to the local stored procedure, to input parameters of the remote stored procedure. Results in the local stored procedure are set by mapping the data returned from the remote stored procedure to the corresponding return values (e.g., parameters and result sets) in the local stored procedure values.

Claims

1. A computer-implemented method comprising:

identifying a remote stored procedure stored in a remote database management system; and

automatically generating a local stored procedure stored in a local database management system, the automatic generating including;

gathering remote metadata corresponding to the remote database management system and the remote stored procedure;

gathering local metadata corresponding to the local database management system;

creating a call statement to the remote stored procedure based on the gathered remote metadata, the call statement mapping one or more input values to one or more input parameters corresponding to the remote stored procedure; and

setting one or more local stored procedure values, the setting including mapping one or more remote stored procedure results to one or more local stored procedure.

2. The method of claim 1 further comprising:

prior to the generating, searching a database catalog corresponding to the remote database management system;

receiving a search result from the searching; and

generating a call signature corresponding to the remote stored procedure based on the search result.

3. The method of claim 1 wherein the generating further comprises:

generating a connection string corresponding to the remote database management system; and

generating data handling code corresponding to the remote stored procedure results.

4. The method of claim 3 further comprising:

including data transforming code in the generated data handling code that, when executed, transforms a value included in the remote stored procedure results from a first data type used by the remote database management system to a second data type that is used by the local database management system.

5. The method of claim 1 further comprising:

executing the generated local stored procedure, the executing including;

calling the remote stored procedure using the created call statement;

receiving the remote stored procedure results in response to the calling; and

retrieving the local stored procedure values resulting from the setting.

6. The method of claim 5 wherein the executing further comprises:

connecting to the remote database management system using a connection string generated from the gathered remote metadata; and

transforming one or more values included in the remote stored procedure results from a first data type to a second data type, wherein the first data type is used by the remote database management system and the second data type is used by the local database management system.

7. The method of claim 6 further comprising:

displaying at least one of the transformed values to a user of the generated local stored procedure.

8. A information handling system comprising:

at least one processor;

at least one memory associated with the at least one processor;

a nonvolatile storage area associated with the at least one processor; and

a set of instructions contained within the at least one memory, wherein the at least one processor executes the set of instructions in order to perform actions of;

identifying a remote stored procedure stored in a remote database management system; and

automatically generating a local stored procedure stored in a local database management system, the automatic generating including;

gathering remote metadata corresponding to the remote database management system and the remote stored procedure;

gathering local metadata corresponding to the local database management system;

creating a call statement to the remote stored procedure based on the gathered remote metadata, the call statement mapping one or more input values to one or more input parameters corresponding to the remote stored procedure; and

setting one or more local stored procedure values, the setting including mapping one or more remote stored procedure results to one or more local stored procedure results.

9. The information handling system of claim 8 wherein the set of instructions further comprises instructions to perform actions of:

prior to the generating, searching a database catalog corresponding to the remote database management system;

receiving a search result from the searching; and

generating a call signature corresponding to the remote stored procedure based on the search result.

10. The information handling system of claim 8 wherein the instructions to perform the generating further comprises instructions to perform actions of:

generating a connection string corresponding to the remote database management system; and

generating data handling code corresponding to the remote stored procedure results.

11. The information handling system of claim 10 wherein the set of instructions further comprises instructions to perform actions of:

including data transforming code in the generated data handling code that, when executed, transforms a value included in the remote stored procedure results from a first data type used by the remote database management system to a second data type that is used by the local database management system.

12. The information handling system of claim 8 wherein the set of instructions further comprises instructions to perform actions of:

executing the generated local stored procedure, the executing including;

calling the remote stored procedure using the created call statement;

receiving the remote stored procedure results in response to the calling; and

retrieving the local stored procedure values resulting from the setting.

13. The information handling system of claim 12 wherein the instructions to perform the executing further comprises instructions to perform actions of:

connecting to the remote database management system using a connection string generated from the gathered remote metadata; and

transforming one or more values included in the remote stored procedure results from a first data type to a second data type, wherein the first data type is used by the remote database management system and the second data type is used by the local database management system.

14. A computer program product in a computer readable medium, comprising functional descriptive material that, when executed by a data processing system, causes the data processing system to perform actions that include:

identifying a remote stored procedure stored in a remote database management system; and

automatically generating a local stored procedure stored in a local database management system, the automatic generating including;

gathering remote metadata corresponding to the remote database management system and the remote stored procedure;

gathering local metadata corresponding to the local database management system;

creating a call statement to the remote stored procedure based on the gathered remote metadata, the call statement mapping one or more input values to one or more input parameters corresponding to the remote stored procedure; and

setting one or more local stored procedure values, the setting including mapping one or more remote stored procedure results to one or more local stored procedure results.

15. The computer program product of claim 14 further comprising additional functional descriptive material that, when executed by the data processing system, causes the data processing system to perform actions that include:

prior to the generating, searching a database catalog corresponding to the remote database management system;

receiving a search result from the searching; and

generating a call signature corresponding to the remote stored procedure based on the search result.

16. The computer program product of claim 14 wherein the generating further comprises additional functional descriptive material that, when executed by the data processing system, causes the data processing system to perform actions that include:

generating a connection string corresponding to the remote database management system; and

generating data handling code corresponding to the remote stored procedure results.

17. The computer program product of claim 16 further comprising:

including data transforming code in the generated data handling code that, when executed, transforms a value included in the remote stored procedure results from a first data type used by the remote database management system to a second data type that is used by the local database management system.

18. The computer program product of claim 14 further comprising additional functional descriptive material that, when executed by the data processing system, causes the data processing system to perform actions that include:

executing the generated local stored procedure, the executing including;

calling the remote stored procedure using the created call statement;

receiving the remote stored procedure results in response to the calling; and

retrieving the local stored procedure values resulting from the setting.

19. The computer program product of claim 18 wherein the executing further comprises additional functional descriptive material that, when executed by the data processing system, causes the data processing system to perform actions that include:

connecting to the remote database management system using a connection string generated from the gathered remote metadata; and

transforming one or more values included in the remote stored procedure results from a first data type to a second data type, wherein the first data type is used by the remote database management system and the second data type is used by the local database management system.

20. The computer program product of claim 19 further comprising additional functional descriptive material that, when executed by the data processing system, causes the data processing system to perform actions that include:

displaying at least one of the transformed values to a user of the generated local stored procedure.