Interface for providing access to external data sources/sinks

What is claimed is:

1. A computer-implemented method for enabling an application program to connect to a selected one or more of a plurality of external data sources/sinks, said application program running on a computer having active memory, the method comprising:

providing a plurality of driver means, each of said driver means corresponding to a different subgroup of said plurality of external data sources/sinks;

in response to an inquiry from said application program, reporting to the application program the identity of each of the driver means of said plurality of driver means;

in response to the application program, selecting one of the plurality of driver means previously identified to the application program;

loading the selected driver means into active memory; and

reporting to the application program a first plurality of entry points in said loaded driver means for a first plurality of function calls that said application program can make to said loaded driver means, said first plurality of entry points for use by said application program to make said first plurality of function calls directly to said loaded driver means, said first plurality of function calls including function calls for establishing and/or terminating connectivity to said loaded driver means.

2. The computer-implemented method of claim 1 wherein said first plurality of function calls includes browsing function calls which enable the application program to discover the external data sources/sinks.

3. The computer-implemented method of claim 1 wherein said first plurality of function calls includes function calls for establishing and/or terminating connectivity to a selected one of the external data sources/sinks associated with said loaded driver means.

4. The computer-implemented method of claim 3 further comprising reporting to said application program a second plurality of entry points in said loaded driver means for a second plurality of function calls that said application program can make to said loaded driver means, said second plurality of entry points for use by said application program to make said second plurality of function calls directly to said loaded driver means, said second plurality of function calls relating to accessing data in said selected external data source/sink.

5. The computer-implemented method of claim 4 wherein said second plurality of function calls includes catalog browsing function calls which enable the application program to discover tables of data that are available through said selected external data source/sink.

6. The computer-implemented method of claim 5 wherein said catalog browsing function calls are also for enabling the application program to discover columns within said tables.

7. The computer-implemented method of claim 6 wherein said second plurality of function calls includes a function call for returning a capability array for said data source/sink to the application program, said capability array identifying the capabilities of other of said second plurality of function calls.

8. The computer-implemented method of claim 7 wherein said capability array comprises a plurality of masks, each of said masks associated with a different logical group of capabilities.

9. The computer-implemented method of claim 8 wherein said capability array comprises a summary mask, said summary mask including an entry corresponding to each of the other masks of said plurality of masks, each entry indicating whether any of the capabilities of the corresponding logical group of capabilities is present.

10. The computer-implemented method of claim 1 wherein the step of reporting to the application program the identify of each of the driver means of said plurality of driver means comprises creating and establishing in memory a registration data structure that identifies to said application program the plurality of drivers and the plurality of external data sources/sinks available to said application program.

11. The computer-implemented method of claim 10 wherein the step of reporting to the application program the identity of each of the driver means of said plurality of driver means further comprises searching through said registration data structure so as to identify to the application program the drivers of said plurality of drivers.

12. A computer-implemented method for enabling an application program to access functionality of a selected external data source/sink, the method comprising:

in response to a connectivity request from said application program, establishing connectivity of the application program to said selected external data source/sink; and

after said application program establishes connectivity with said external data source/sink, causing said selected external data source/sink to make available to said application program a browsing function for use by said application program to discover information about capabilities of said external data source/sink;

in response to use of said browsing function by the application program, reporting to the application program an array of capabilities that are supported by said external data source/sink, said capabilities being available to be directly utilized by said application program.

13. The computer-implemented method of claim 12 further comprising reporting to said application program a plurality of entry points to a plurality of function calls that said application program may make to said selected external data source/sink, said plurality of function calls including a function call for returning the capability array for said data source/sink, said capability array identifying the capabilities of other of said plurality of function calls.

14. The computer-implemented method of claim 13 wherein said capability array comprises a plurality of masks, each of said masks associated with a different logical group of capabilities.

15. The computer-implemented method of claim 14 wherein said capability array comprises a summary mask, said summary mask including an entry corresponding to each of the other masks of said plurality of masks, each entry indicating whether any of the capabilities of the corresponding logical group of capabilities is present.

16. The computer-implemented method of claim 14 wherein at least one of said masks corresponds to data definition capabilities that are supported by said plurality of function calls.

17. A computer-implemented method for enabling an application program to connect to an external data source/sink, said application program supporting a first plurality of data types, said external data source/sink supporting a second plurality of data types, the method comprising:

establishing connectivity of said application program to said data source/sink through a driver;

once connectivity between said application program and said data source/sink is established, negotiating a mutually supported data type for transferring data between said application and said external data source/sink, said negotiating taking place between the application program and said driver; and

adopting the mutually supported data type for transferring data between said application and said external data source/sink.

18. The computer-implemented method of claim 17 wherein the step of negotiating comprises reporting to said application program which of said second plurality of data types said external data source/sink proposes to use to transfer data to said application program, and wherein said adopting step comprises changing from said proposed data type to said mutually supported data type.

19. The computer-implemented method of claim 18 wherein said negotiating step further comprises reporting to the application program the data types included among said second plurality of data types.

20. The computer-implemented method of claim 19 wherein said changing step comprises selecting one of said second plurality of data types as said mutually supported data type.

Description Submit all comments and votes

The invention relates to an interface for enabling a computer application program to communicate with a data source/sink, such as a database engine.

Spreadsheet programs provide a powerful mechanism for analyzing large amounts of complex data such as is typically found, for example, in financial reports, stock quotations, business income and expense statements, sales and product inventories, etc. Sometimes a significant portion of the data that is processed by a spreadsheet program is available through computerized data sources, such as, for example, a database program. It is desirable, therefore, to have a mechanism which enables the spreadsheet program to directly access such data. A problem, however, is that there is a large number of commercially available databases and no standard according to which they all operate. Some databases, being more sophisticated than others, have far greater capabilities associated with them. Other databases possess only rudimentary capabilities for organizing data. In addition, there is no common language that is understood by all databases. Some databases use SQL (also know as SEQUEL for the Structured English QUEry Language that was designed and implemented by International Business Machines), some databases use QBE (Query By Example) and other databases use yet other languages. Any interface to a database must take into account that database's unique capabilities and requirements. Such differences among databases (and the even greater differences among all possible data sources) create significant barriers to developing a common interface useable with a wide variety of databases.

SUMMARY OF THE INVENTION

In general, in one aspect, the invention is an interface for enabling an application program to connect to a selected one or more of a plurality of external data sources/sinks, said application program running on a computer having active memory. The interface includes a plurality of driver means, each of which corresponds to a different subgroup of the external data sources/sinks; a name manager for identifying the drivers to the application; means for selecting one of the, identified-external data sources/sinks; means for loading the driver means corresponding to the selected external data source/sink into active memory; and means for identifying a first plurality of entry points for a first plurality of function calls that the application can make to the loaded driver means, the plurality of function calls including function calls for establishing and/or terminating connectivity to the loaded driver means.

Preferred embodiments include the following features. The first plurality of function calls includes: browsing function calls for identifying the external data sources/sinks to the application, and function calls for establishing and/or terminating connectivity to a selected one of the external data sources/sinks associated with said loaded driver means. The interface also includes means for identifying a second plurality of entry points for a second plurality of function calls that said application can make to said loaded driver means, said second plurality of function calls relating to accessing data in said selected external data source/sink. The second plurality of function calls includes catalog browsing function calls for identifying tables of data that are available through the external data source/sink and for identifying columns within the tables. There is also a function call for returning a capability array for the data source/sink which identifies the capabilities of other function calls of the second plurality of function calls. The capability array includes a plurality of masks, each of the masks associated with a different logical group of capabilities. One of the masks is a summary mask including an entry corresponding to each of the other masks, each entry indicating whether any of the capabilities of the corresponding logical group of capabilities is present.

Also in preferred embodiments, the name manager includes means for establishing a registration data structure that identifies the plurality of drivers and the plurality of external data sources/sinks available to the application program. Also, the interface includes browsing means for searching through the registration data structure so as to identify to the application program the available drivers.

In general, in another aspect, the invention is an interface for enabling an application program to connect to a selected external data source/sink. The interface includes means for establishing connectivity to the selected external data source/sink; and means for identifying to the application an array of capabilities associated with the external data source/sink, the identified capabilities being available to the application.

In preferred embodiments, the interface also includes means for identifying a plurality of function calls that said application can make to the selected external data source/sink, where the plurality of function calls includes the means for returning the capability array for the data source/sink. The capability array includes a plurality of masks, each of which is associated with a different logical group of capabilities. Among the plurality of masks is a summary mask which includes an entry corresponding to each of the other masks, each entry indicating whether any of the capabilities of the corresponding logical group of capabilities is present. The capability array also includes masks which correspond to the following capability groups: data definition capabilities, privileges capabilities, data update capabilities, fetch orientation capabilties, long data capabilities, query capabilities, row identification capabilities, where capabilities, logical operators, arithmetic operators, mathematical function operators, string function operators, data function operators, financial operators, subquery operators capabilities, aggregation function operators, set function operators, prepared statement capabilities, transaction and concurrency control capabilities, and standard system catalog capabilities.

In general, in yet another aspect, the invention is an interface for enabling an application program to connect to an external data source/sink, where the application program supports a first plurality of data types and the external data source/sink supports a second plurality of data types. The interface includes means for establishing connectivity to the data source/sink; and means for negotiating a mutually supported data type for transferring data between the application and the external data source/sink.

Preferred embodiments include the following features. The negotiating means includes means for identifying to the application which of the second of plurality of data types the external data source/sink proposes to use to transfer data to the application; and means for changing from the proposed data type to the mutually supported data type. The negotiating means also includes means for identifying the data types included among the second plurality of data types and the changing means includes means for selecting one of the second plurality of data types as the mutually supported data type.

One advantage of the invention is that applications may be independent of the specific systems that control the data that the application requires. The invention provides a standard interface powerful enough to accommodate demanding application requirements, while allowing drivers to have substantial flexibility in supporting the interface.

Another advantage of an embodiment of the invention is that it implements the semantics of relational operations through an Application Program Interface (API) that is implemented as a set of procedure calls and data structures. By using the API, the applications are not confined to a particular SQL dialect. In addition, drivers that do not support SQL directly do not need an SQL parser. Operations are determined by direct processing of the data structures passed by the procedure calls of the API. The applications are provided transparent access to a diverse group of one or more external databases so that the application can transfer data independently of the source of that data. That is, the invention provides an API that allows the applications to communicate with selected sources of data regardless of the type or location of the data source. The power of the API reflects in part the functional capabilities of the source of data and thus may vary from one data source to the next. In addition, it is a runtime binding system that loads and establishes connectivity to selected sources of data at runtime.

Other advantages and features will become apparent from the following description of the preferred embodiment and from the claims.

BRIEF DESCRIPTION OF THE DRAWINGS

FIG. 1 illustrates a block diagram of a system which embodies the invention;

FIGS. 2A and 2B depict a flow chart of a typical sequence of function calls for the system shown in FIG. 1;

FIG. 3 illustrates the structure of a registration data structure;

FIG. 4 illustrates an environment descriptor block;

FIG. 5 illustrates a driver record and a database record;

FIGS. 6A and 6B illustrate a driver browsing handle and a database browsing handle, respectively;

FIG. 7 illustrates various data structures including a driver connection control block and a database connection control block;

FIGS. 8A through 8E illustrate the function calls that are accessible through DBLINK;

FIGS. 9A through 9N illustrate a capability groups array and associated capability masks;

FIG. 10 illustrates a querytree data structure;

FIG. 11 illustrates a wherenode data structure;

FIG. 12 illustrates a transfer block data structure; and

FIG. 13 illustrates data structures associated with the extension features of the system.

DESCRIPTION OF THE PREFERRED EMBODIMENT

Referring to FIG. 1, a system which embodies the invention includes an interface 2 which may be invoked by one or more application programs 4(1) through 4(N) (generally identified by reference numeral 4). The application programs 4 (which shall be referred to as applications 4) may be, for example, spreadsheet programs, word processing programs or other programs which use or generate data.

An application 4 uses interface 2 to establish connectivity to one or more drivers 6(1) through 6(M) (generally identified by reference numeral 6). Associated with each driver 6 is a set of one or more external databases 8(1) through 8(X+L) (generally identified by reference numeral 8). After establishing connectivity to one of drivers 6, application 4 can, with the assistance of that driver, establish connectivity to one or more of the external databases 8 available to that driver. Typically, each external database 8 manages one or more relations 10 containing stored data. Once application 4 establishes connectivity to a particular one of external databases 8, it can, with the assistance of that database, access selected relations 10 and create other relations.

The capabilities of databases 8 may vary considerably, some only possessing rudimentary functionality While others possess complex powerful functionality. As will be described in greater detail below, applications 4 can discover through drivers 6 the capabilities of the associated databases 8 and through mechanisms provided by drivers 6 can access those capabilities to manipulate data. Thus, to the extent reflected by the capabilities supported by the driver/database connection, an application 4 can transfer certain data manipulation tasks to the driver/database interface thereby relieving itself of having to perform those tasks.

Before describing in detail the underlying data structures and functions which implement interface 2 and drivers 6, a general overview of the operation of the system will first be given.

Upon invoking interface 2, an application 4 gains access to a collection of driver browsing functions which give it the ability to discover the list of available drivers 6 and their attributes. When a driver 6 is identified to which connectivity is desired, application 4 allocates memory for a driver link (DVLINK) data structure (to be described) and invokes a routine to establish connectivity to that driver. If driver 6 has not yet been loaded into active memory, interface 2 first dynamically loads driver 6 into memory. Once it is loaded, driver 6 stores certain driver-specific information in DVLINK. Some of the stored information identifies multiple entry points into driver 6. Each entry point represents a different call that is supported by driver 6 and that may be made to driver 6 by application 4. The complete collection of available calls represents the service and management capabilities of the driver interface to the external databases 8.

Among the functions accessible through the entry points into driver 6 is a collection of database browsing functions which application 4 can use to discover the list of available databases 8. These browsing functions are similar to those available for the driver. Once application 4 identifies a particular one of databases 8 to which connectivity is desired, it allocates another portion of memory for a database link (DBLINK) data structure (to be described) and application 4 invokes one of the routines identified by an entry point in the DVLINK to establish that connectivity. To establish connectivity, driver 6 fills in the DBLINK with database-specific information, including entry points to a group of calls that may be made by application 4 to, among other things, explore the list of relations 10 available to database 8, manipulate those relations 10, and exercise other functionality that is available through database 8.

One of the function calls available through the DBLINK reports to application 4 the capabilities that are supported by that particular driver/database interface. The capabilities include, for example, whether that driver/database combination can perform a delete search or insert a row of values in a specified table of a relation, or grant access privileges, etc. The capabilities are reported as an array of bits, each bit position corresponding to a different capability. Set bits indicate capabilities that are supported and cleared bits indicate capabilities that are not supported.

Another set of functions that is accessible through the DBLINK permit application 4 to browse through and access relations 10 that are available to the database. These functions are collectively referred to as the catalog browser functions and they give driver 6 the ability to supply application 4 with enough information about tables and columns in relations 10 for application 4 to construct and execute data management commands for that database.

As a vehicle for providing details about the data structures and functions which support the system, a typical sequence of function calls (refer to FIGS. 2A and 2B) for establishing connectivity to a relation available to a particular database will be described. As each function call is described, the relevant data structures will be introduced and also described in detail, referring to other figures where appropriate. The following description, however, will repeatedly return to FIGS. 2A and 2B as each new step in the sequence is introduced.

Both the driver and the application allocate data structures. The following description specifies which structures the driver allocates and which structures the application allocates. When the driver allocates structures, it requests the required memory from the application.

It is assumed that the user begins with an application 4 running on a computer and that no applications have yet attempted to access the functionality of interface 2. That is, no drivers 6 have been loaded into active memory (step 100).

Given this initial condition, application 4 begins by calling an init.sub.-- interface function which initializes the environment for interface 2 and constructs a registration data structure 150 (see FIG. 3) that will be used for browsing through the available drivers 6 and registered databases 8 (step 102). The init.sub.-- interface function also identifies an application allocated buffer for returning an error message in the event that the init.sub.-- interface function fails.

As part of the environment intialization, the init-interface function passes to interface 2 a pointer to a environment descriptor block (ENVBLK 200, see FIG. 4) that is allocated and supplied by application 4. The application uses ENVBLK 200 to store a description of the environment, i.e., to store certain application-specific information required by interface 2 and to identify a small number of call backs that the system can make to the application. More specifically, as shown in FIG. 4, fields 202 through 240 of ENVBLK 200 contain the following information.

Field 202 contains the size of the ENVBLK in total number of bytes stored. Field 204 contains an identifier of the platform on which the application is running such as for example whether the platform is a PC using a DOS or an OS/2 operating system or a workstation or a mainframe operating system. Field 206 contains an identifier of the character set utilized by the application. Field 208 contains a pad that is used to maintain alignment of the data structure. Field 210 contains the maximum number of handles that can be mapped at any one time by the application. Field 212 identifies the type of memory manager that is provided by the application. Field 214 contains the size, in bytes, of the largest contiguous block of memory that can be allocated. Field 216 contains the platform-specific null handle value. Field 218 contains a pointer to a string that identifies the application to the driver.

Fields 220 through 236 identify the calls that can be made back into the application. They relate primarily to mapped memory deallocator functions, respectively. Fields memory management functions. Fields 220 and 222 contain a pointer to the application's mapped memory allocator and 224 and 226 contain pointers to the application's handle mapper and handle unmapper functions, respectively. Fields 228 and 230 contains pointers to the application's fixed (i.e., real) memory allocator and fixed memory deallocator functions, respectively. Fields 232 and 234 contain pointers to the application's loader and unloader functions, respectively. Field 236 contains a pointer to the application's "system" function which calls the operating system command shell.

Finally, field 238 contains a pointer to a location for storing data that is private to the interface. And, field 240 contains a pointer to at least one registration file (to be described below) that identifies the drivers and data bases available to the application.

A driver obtains memory from the application's memory manager, not directly from the operating system. Field 220 of ENVBLK 200 identifies a pointer to a call back function to the application's memory allocator. This allocator returns a memory handle that may have to be mapped, depending on memory type, before the driver can use it. An application supports one of three types of memory managers, namely, NOT.sub.-- MAPPED, MUST.sub.-- MAP, and MUST.sub.-- UNMAP.

If the memory type specified in field 212 of ENVBLK 200 is NOT.sub.-- MAPPED, then all handles passed across the interface are pointers to real memory. In contrast, in a MUST.sub.-- MAP environment, all handles passed between the application and the driver must be mapped in order to obtain pointers to memory. In such an environment, the driver's calls to the application's map function, whose address is in field 224 of ENVBLK 200, must precede all references to mapped memory.

In some environments, such as the DOS environment, only four pointers to mapped memory are available at one time. The application passes the number of map registers it supports in field 210 of ENVBLK 200. Pointers obtained by mapping remain valid until another handle is mapped into the same map register.

Finally, in the third type of memory environment, MUST.sub.-- UNMAP, the driver must make a call to an unmap function to release mapped memory.

To construct registration data structure 150 shown in FIG. 3, the init.sub.-- interface function uses information that is stored in a registration file 20 (shown in FIG. 1). Registration file 20 is an ASCII text file that can contain two types of records, namely, one or more driver records 22 and zero or more database records 24 (shown in FIG. 5).

Driver record 22 consists of two required parameters and four optional parameters. Each driver record 22 must include a driver name parameter (DN) followed by the driver name (i.e., DN="Drivername") (field 26). DN identifies the record as a driver record and must be the first parameter in the record. The driver name is the name returned to application during driver browsing and the application uses it to connect to the driver. Driver record 22 must also include a dri