Apparatus and method for accessing foreign databases in a heterogeneous database system

• US 6,236,997 B1
• Filed: 06/23/1997
• Issued: 05/22/2001
• Est. Priority Date: 06/23/1997
• Status: Expired due to Term

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle Corporation
• Inventors: Draaijer, Jacco, Voss, Eric, Bodamer, Roger
• Primary Examiner(s): Breene, John E.
• Assistant Examiner(s): ROBINSON, GRETA LEE
• Application Number:
  US08/880,333
• Time in Patent Office: 1,429 Days
• Field of Search: 707/102, 707/10, 707/103, 709/201
• US Class Current: 1/1
• CPC Class Codes: G06F 16/258 Data format conversion from or to a database

Abstract

An apparatus and method for accessing foreign processes in a heterogeneous database environment includes a local database server having a heterogeneous services module to selectively send requests to the foreign processes based on their respective capabilities. A client application sending a statement to the local database server is checked by the local server process to determine if the statement includes a reference to a foreign database system, or whether the statement is registered as an external routine. The heterogeneous services module selectively outputs a request to an agent process executing in an address space separate from the local server process and in communication with the foreign database. The agent process performs all necessary interaction with the foreign database, including data type translation. The agent includes a conversion module that includes data type conversion routines, and which may obtain additional conversion routines via an Application Programming Interface (API) from a foreign database driver corresponding to the foreign database. The converted request is output from the agent process to the foreign database. Hence, the heterogeneous services module manages client statements involving foreign database systems having limited capabilities, as well as client statements having expressions unrecognizable by the local database server. Use of the agent process ensures that the integrity of the local server process is protected.

Claims

1. A method for processing a statement from a client, comprising the steps of:

receiving the statement at a local database server specifying an operation to be performed at a foreign database server;

determining whether the operation specified to be performed at the foreign database server is not implemented by the foreign database server; and

if the operation is determined to be not implemented by the foreign database server, then causing the local database server to perform the steps of;

determining one or more first operations to be performed by the local database server and one or more second operations to be performed by the foreign database server to execute the statement;

sending a request to the foreign database server to perform said one or more second operations and to retrieve data stored in the foreign database server and needed to process said one or more first operations; and

completing execution of the statement by performing said one or more first operations in the local database server based on retrieval of the data stored in the foreign database server and needed to process said one or more first operations.

2. The method of claim 1, further comprising the steps of:

accessing a first table in response to reception of the statement by the local database server, said first table specifying operations that can be performed by the foreign database server; and

generating the request based on the operations that can performed by the foreign database server.

3. The method of claim 2, further comprising the steps of:

identifying said foreign database server based on the statement; and

identifying the data stored in the foreign database server and needed to process one or more first operations.

4. The method of claim 3, further comprising the step of including in the request a query to retrieve the data stored in the foreign database server needed to process said one or more first operations.

5. The method of claim 1, wherein the step of sending the request to the foreign database server to perform said one or more second operations includes the steps of sending the request to an agent process in communication with the foreign database server.

6. The method of claim 5, wherein the step of completing execution of the statement includes the step of receiving from the agent process a response to the request from the foreign database server, the response including the data stored in the foreign database server and needed to process said one or more first operations.

7. The method of claim 5, further comprising:

generating the request in a first format readable by the local database server process;

converting the request in the agent process to a second format readable by the foreign database server; and

sending the converted request having the second format from the agent process to the foreign database server.

8. The method of claim 5, further comprising the step of initiating execution of the agent process by the local database server.

9. The method of claim 5, wherein the step of causing the local database server process to determine at least one of said operations that can be performed by a foreign database server includes the step of causing the local database server process to determine at least one of said operations that can be performed by a foreign database server based on stored data describing capabilities of the foreign database server.

10. A distributed database system configured to respond to a statement, comprising:

a first computer system executing a local server process; and

a second computer system, in communication with the first computer system, executing a foreign server process;

wherein the local server process is configured for;

receiving the statement, said statement specifying an operation to be performed by the foreign server process;

determining whether the operation specified to be performed at the foreign server process is not implemented by the foreign server process; and

if the operation is determined to be not implemented by the foreign server process, then performing the steps of;

determining one or more first operations to be performed by the local server process and one or more second operations to be performed by the foreign server process;

sending a request to the foreign server process to perform said one or more second operations and to retrieve data stored in the second computer system and needed to process said one or more first operations; and

completing execution of the statement by performing said one or more first operations based on retrieval of data stored in the foreign server process and needed to process said one or more first operations;

wherein the foreign server process is configured for receiving the request, performing said one or more second operations, and returning the results based on said performing said one or more second operations.

11. The system of claim 10, further comprising a computer-readable medium storing a capabilities table specifying operations that are executable by the foreign server process.

12. The system of claim 11, wherein the local server process is configured for sending the statement as the request to the foreign server process based on accessing the capabilities table.

13. The system of claim 11, wherein the local server process is configured for determining said one or more second statements including one operation specified in the capabilities table and sending the request for the one operation to the foreign server process based on accessing the capabilities table.

14. The system of claim 11, wherein the local server process is configured for generating a transaction message to the foreign server process based on corresponding transaction capabilities identified in the capabilities table.

15. The system of claim 14, wherein the transactions coordinated between the local server process and the foreign server process include a distributed transaction between the local server process and the foreign server process.

16. The system of claim 15, wherein the distributed transaction is a two-phase commit distributed transaction.

17. The system of claim 10, wherein:

the local server process executes in a first address space and the foreign server process is executed by an agent for performing an external routine in a second address space separate from the first address space;

and said first computer system further comprises a heterogeneous services module that includes an external procedures module configured to initiate a connection with, and send the request to, the agent executing the external routine.

18. The system of claim 17, wherein the heterogeneous services module includes foundation services configured to establish and maintain a connection with the agent process in communication with the foreign server process during execution of the statement.

19. The system of claim 17, wherein the external procedures module is configured to pass arguments corresponding to the request to the agent for processing by the agent.

20. The system of claim 17, wherein the local server process is configured for suspending execution of the statement in response to a query from the external routine and corresponding to the request.

21. The system of claim 20, wherein:

the external procedures module is configured to receive a plurality of said queries from the external routine during processing of the request, and

the local server process recursively processes the queries within a singular instance.

22. The system of claim 10, further comprising a computer-readable medium storing a data dictionary translation table specifying foreign data objects accessible by the foreign server process,

wherein the heterogeneous services module is configured for translating an object in the statement to a corresponding one of the foreign data objects in response to accessing the data dictionary translation table, the request including the one foreign data object.

23. The system of claim 10, wherein the local server process is configured for sending the request to the foreign server process to perform said one or more second operations by sending the request to an agent process in communication with the foreign server process.

24. The system of claim 23, wherein the agent process is configured to control translation of the request from a first format readable by the local server process to a second format readable by the foreign server process.

25. The system of claim 24, wherein the agent process includes a foreign database driver configured for providing the query services module with mapping information for the corresponding foreign server process for translating the request.

26. The system of claim 25, wherein:

the local server process is configured for mapping the request onto a format specified by an Application Programming Interface (API), and

the foreign database driver is configured for translating the request from the format specified API to the second format.

27. The system of claim 26, wherein foreign database driver is configured for translating the results received from the foreign server process from the second format in accordance with a format specified by the API, the query services module translating the results from the format specified by the API to the first format for the local server completing execution of the statement.

28. The system of claim 26, wherein:

the query services module is configured for communicating with a second foreign database driver providing mapping information for a second corresponding foreign server process and mapping a second portion of the statement from the first format to a format specified by the API, and

the second foreign database driver is configured for translating the second portion of the statement from the format specified by the API to a third format readable by the second foreign server process.

29. The system of claim 25, further comprising a plurality of agent processes having a plurality of said foreign database drivers providing mapping information for foreign server processes having prescribed formats, respectively, the query services module configured to map between the first format onto a format specified by an Application Programming Interface (API), each of the foreign database drivers configured to map between the format specified by the API and the format readable by the corresponding foreign server process.

30. The system of claim 25, wherein:

the agent process includes a second transaction module, complementary to the first transaction module, configured for mapping a transaction message from a first format readable by the local server process onto a format specified by an Application Programming Interface (API) for translation by a foreign database driver, and

the foreign database driver is configured for translating the transaction message from the format specified by the API to a second format readable by the foreign server process for executing the transaction message.

31. The system of claim 30, wherein:

the second transaction module is configured for mapping a transaction response, generated by the foreign server process in response to executing the transaction message, from the format specified by the API to the first format, and

the foreign database driver is configured for converting the transaction response from the second format to the format specified by the API.

32. The system of claim 23, wherein the local server process is configured for initiating execution of the agent process by the local server process.