Stored procedure interface language and tools

The present invention relates to systems and methods to generate stored procedure calling code. The invention includes system and methods that utilize annotation(s) included in a stored procedure. The annotations comprise attributes that indicate the intent to have stored procedure calling code generated. After the annotated stored procedure becomes available, the code is compiled and, optionally, checked for errors, and an intermediate definition with metadata is created. The definition is a stored procedure interface language (SPIL) file. The SPIL definition is then utilized to generate high-level, stored procedure calling code, or class libraries. The high level code can then be employed within an application to invoke the stored procedure.

TECHNICAL FIELD

The present invention relates generally to databases, and more particularly to systems and methods for generation of executable instruction(s) employed to invoke a stored procedure associated with a database.

BACKGROUND OF THE INVENTION

As computing and networking technologies become robust, secure and reliable, more consumers, wholesalers, retailers, and information providers are employing the Internet to conduct business. For example, many wholesalers and information providers are providing web sites and/or on-line services for purchasing products and searching for information, respectively.

Typically, business over the Internet includes a user interfacing with a client application (e.g., a web page) to interact with a server that stores information in database(s) that are accessible to the client application. For example, a stock market web site can provide the user with tool(s) for retrieving stock quotes and purchasing stocks. The user can type in a stock symbol, and then request a stock quote. The client application queries database table(s) of stocks, for example, and returns a stock quote. The user can submit a request to purchase the stock through the client application. The actual stock purchase is dependent on the provider's policy, and the date and time of the request.

In general, the server can be associated with one or more databases and can comprise a complex and large volume of data and executable code. The typical business (wholesaler, retailer, etc.) offering the service is not staffed with employees with the technical background and experience to construct the databases, write the executable code and/or build the client application. Generally, the business out-sources the effort to a third party vendor, for example, for a fee. The vendor can then employ various programming philosophies to develop and efficiently provide the business with a reliable and cost effective system.

SUMMARY OF THE INVENTION

The present invention relates to systems and methods to utilize an annotated stored procedure to generate executable code (e.g., automatically) and associated runtime libraries to call the stored procedure without having to program additional code to call the stored procedure.

The systems and methods generally include component(s) (associated with stored procedure interface language and tools) interfaced with a database, for example a SQL-based database, to access an annotated stored procedure. In general, a stored procedure is a precompiled collection of statements stored under a name on server and processed as a unit. The annotation comprises attributes, or metadata, to allow a developer to indicate whether to export and/or bind a method, for example. The system and methods utilize the annotation to generate an intermediate stored procedure interface language (SPIL) definition, and subsequently generate high-level code and libraries (e.g., C, C++, C#, Jscript.net, VB.net, Perl.net) associated with the stored procedure. The high-level code can be accessed via an application, wherein a call to the generated code calls the stored procedure in the database.

Conventionally, a developer write a stored procedure(s), and transfers (e.g., upload) the code to a database. Typically, the code comprises one or more files that are individually and/or batch uploaded to the database. The developer then develops the infrastructure (e.g., code) to call the stored procedure(s). Writing the code to call the stored procedure is generally time intensive, and includes development, debugging and testing efforts.

The developer enters a cycle of writing the stored procedure, saving it to a database, writing a stored procedure call, and debugging and testing the call to provide additional behaviors. Presently, a typical platform can include more than five hundred stored procedures. Hence, about five hundred or more individual cycle iterations may have to occur to provide stored procedure calling code. Thus, manually developing the calling code for the stored procedures can consume considerable research and development resources and cost, with minimal relative return (e.g., reusability).

The present invention mitigates developing, debugging and testing code to call a stored procedure via generating (e.g., automatically) the stored procedure calling code. The developer, while writing the stored procedure, includes in-line annotations that facilitate code generation. Then, a tool and/or method in accordance with an aspect of the invention can be employed to utilize the annotations from the stored procedure and generate stored procedure calling code. Thus, the present invention reduces development time and improves development efficiency.

Furthermore, the present invention provides compile-time error checking, which generally is not employed. Conventionally, the stored procedure is merely referenced during compilation. Parameter and/or partitioning mismatch, for example, are discovered during runtime. Thus, the present invention mitigates relying on anticipating the possible runtime errors and testing substantially all combinations, and then detecting unforeseen errors during runtime, which can be resource and time expensive.

In addition, the present invention affords consistent and clean stored procedure calling code since the code is generated and not generated manually by various developers who can impose coding styles and nuances. Thus, the present invention implicitly provides the opportunity to develop a coding standard that promotes efficiency, ease of debugging, and good manufacturing practices.

DETAILED DESCRIPTION OF THE INVENTION

The present invention relates to systems and methods to generate (e.g., automatically) code that executes a stored procedure within a database (e.g., SQL). The invention includes components to extract annotations from a stored procedure, utilize (e.g., compile with or without error checking) the annotations to create an intermediate file (e.g., XML based) that defines rights, bindings, etc., and generates high level code and associated libraries (e.g., C, C++, C#, Jscript.net, VB.net, Perl.net) from the intermediate file. The high level code and associated libraries can then be employed to invoke a stored procedure via calling the generated code from a user application. Those skilled in the art will recognize that that any suitable high level code can be employed in accordance with the present invention. It is to be appreciated any type of high level code suitable for carrying out the present invention can be employed and all such types of high level code are intended to fall within the scope of the hereto appended claims.

A developer inserts the annotation in the stored procedure while writing the stored procedure. The annotation can includes metadata indicating a desire to generate a method for the stored procedure, an algorithm to generate a function to execute on a logical partition, a partition and a class where the function resides, for example. The present invention is then employed to generate stored procedure calling code for the stored procedure via utilizing the annotations within the stored procedure. Thus, the present invention mitigates developing, debugging and testing stored procedure calling code via generating, with compile-time error checking, the stored procedure calling code; thereby reducing R&D resources, time and costs, and improving coding efficiency and consistency.

Referring toFIG. 1, a stored procedure interface language (SPIL) compiler system100in accordance with an aspect of the present invention is illustrated. The system100comprises an annotation extractor110, and a SPIL definition constructor120. Optionally, the system100can include a SPIL definition store130and/or a compile-time checker140.

The SPIL compiler system100receives an annotated stored procedure, extracts annotation(s) and provides a SPIL definition of the annotated stored procedure as an output. For example, SPIL definition of the annotated stored procedure can be stored in the SPIL definition store130. In one example, the SPIL definition store130stores SPIL definitions associated with a plurality of annotated stored procedures.

The system100utilizes the annotations within a stored procedure to facilitate generation of code (e.g., by a code generator (not shown)). In accordance with an aspect of the present invention, a developer writing a stored procedure includes annotation(s) in the stored procedure that define the developer's intent to generate stored procedure calling code. Exemplary annotations in accordance with an aspect of the present invention are described in greater detail below. After the developer has written the stored procedure, the stored procedure is placed in a database (not shown) and/or server (not shown). For example, the stored procedure can encompass a plurality of files that are uploaded, serially and/or concurrently (e.g., batch), to a SQL server and/or database. In another example, a database with annotated stored procedures can be generated through a build, and a binary (file) of the database can be created. The binary can then be mounted on a server during deployment instead of uploading files.

The annotation extractor110extracts the annotation(s) within a stored procedure (e.g., without altering the annotated stored procedure) and provides information associated with the extracted annotation(s) to the SPIL definition constructor120. The SPIL definition constructor120utilizes the extracted annotation(s) to facilitate constructing a SPIL definition of the annotated stored procedure. For example, the SPIL definition can be XML-based. The SPIL definition thus functions as an intermediate language between an annotated stored procedure and generated code for invoking the stored procedure. The SPIL definition can further provide the stored procedure logical groupings.

In one example, the SPIL definition declaratively states a set of stored procedures and the stored procedure logical grouping(s). For example, a developer can define an abstraction (e.g., grouping) of a set of methods. The SPIL definition can include metadata to identify application grouping (e.g., an abstraction), a database connection string and a list of stored procedures that belong to the grouping. Additionally, for each stored procedure defined, the SPIL definition can include a list of parameters and type information.

Optionally, the system100can include a compile-time checker140. The compile-time checker140can perform compile-time error checking of the annotated stored procedure. As noted above, conventionally, a stored procedure is simply referenced at compile time. Error(s) are typically resolved by testing foreseen permutation(s), and then relying on runtime to find unaccounted for error(s). Intensive testing and relying on runtime can introduce inefficiencies and consume time and resource(s). The compile-time checker140can include, for example, enable and/or disable option(s) that can be set through command line switch(es), bit setting and/or any known technique. Compile-time error checking can mitigate reliance on anticipation of potential runtime error(s) (e.g., parameter and/or partitioning mismatch), testing of substantially all combinations, and then detecting unforeseen errors during runtime, which can be resource and time expensive.

WhileFIG. 1is a block diagram illustrating components for the SPIL compiler system100, it is to be appreciated that the SPIL compiler system100, the annotation extractor110, the SPIL definition constructor120, the SPIL definition store130and/or the compile-time checker140can be implemented as one or more computer components, as that term is defined herein. Thus, it is to be appreciated that computer executable components operable to implement the SPIL compiler system100, the annotation extractor110, the SPIL definition constructor120, the SPIL definition store130and/or the compile-time checker140can be stored on computer readable media including, but not limited to, an ASIC (application specific integrated circuit), CD (compact disc), DVD (digital video disk), ROM (read only memory), floppy disk, hard disk, EEPROM (electrically erasable programmable read only memory) and memory stick in accordance with the present invention.

Turning toFIG. 2, a SPIL generator system200in accordance with an aspect of the present invention is illustrated. The system200comprises a code generator210and a library generator220. Optionally, the system200can include a stored procedure generated code store230and/or a stored procedure library240.

The system200receives a SPIL definition (e.g., from a SPIL compiler system100and/or SPIL definition store130). The SPIL definition functions as an intermediate between a stored procedure and the generated code for invoking the stored procedure, and can further provide the set of stored procedures and the stored procedure logical groupings. The system200produces code and libraries associated with the SPIL definition to call a stored procedure.

The code generator210receives the SPIL definition (e.g., from the compiler system100and/or SPIL definition store130) and produces code (e.g., object code) with the SPIL definition to call a stored procedure. The code can be generated employing various techniques. For example, in one aspect of the present invention, the code can be organized within namespaces. As known, a namespace can be utilized as a scoping means, for example within the context of a class library. Then, the stored procedures can be grouped to define a class. A stored procedure will typically map to a method within the class. The generated method can expose the parameters as defined in the stored procedure, and can pack the parameters and call the stored procedure employing a shared SPIL runtime. In another aspect, a predefined hierarchy organizing by database can be employed. The code generator210can translate the SPIL definition (e.g., based on the annotation(s) of the annotated stored procedure), and produce verified and usable logic, or high-level code that can be compiled into application code.

The library generator receives the SPIL definition (e.g., from the compiler system100and/or SPIL definition store130) and produces library(ies) associated with the SPIL definition to call the stored procedure.

It is to be appreciated that the SPIL generator system200, the code generator210, the library generator220, the stored procedure generated code store230and/or the stored procedure library240can be computer components as that term is defined herein.

FIGS. 3 and 4provide exemplary stored procedure annotation syntax in accordance with an aspect of the present invention. It is to be appreciated that the following examples do not limit the invention, and are provided to facilitate in understanding of the invention. Thus, in accordance with an aspect of the present invention, additional and/or other attribute(s) can be employed. Further, various ordering of the attributes relative to one another can be employed in accordance with an aspect of the present invention.

Next, referring toFIG. 3, an exemplary annotation syntax300in accordance with an aspect of the present invention is illustrated. Stored procedure annotation syntax300includes one or more attributes, wherein an attribute is a metadata set associated with a stored procedure. The attribute(s) are utilized alongside stored procedure definition. The attribute(s) are accessible, and can be utilized for various purposes. For example, an attribute can identify method(s) to be exported. The two dashes (--) preceding the attribute is an exemplary syntax (e.g., T-SQL comment syntax) in acc. Those skilled in the art will recognize that that any suitable attribute can be employed in accordance with the present invention. It is to be appreciated any type of attribute syntax suitable for carrying out the present invention can be employed and all such types of attribute syntax are intended to fall within the scope of the hereto appended claims.

The stored procedure annotation syntax300provides for an attribute310to include an attribute name320and, optionally, associated attribute parameter(s)330. The attribute parameter(s)330comprise N parameters, where N is an integer equal to or greater than zero. In one example, N equals three wherein the stored procedure annotation syntax340includes three parameters: parameter1, parameter2, and, parameter3as arguments. In another example, N equals zero wherein parameter arguments are not employed, as depicted in stored procedure annotation syntax350. The stored procedure annotation syntax350illustrates an example in which a set of empty parentheses is employed when no parameter argument is passed. Additionally and/or alternatively, the attribute name without parentheses can be utilized when no parameter argument is passed. Generally, an annotation is included, for example, by a developer prior to creation of the SPIL definition associated with the annotated stored procedure, and one or more annotations can be employed.

Turning next toFIG. 4, an exemplary list400of attribute names and parameters in accordance with an aspect of the present invention are illustrated. The list400is for purposes of illustration and is not intended to limit the present invention. Those skilled in the art will recognize that any suitable attribute name and/or parameter can be employed in accordance with the present invention. It is to be appreciated any type of attribute name and/or parameter suitable for carrying out the present invention can be employed and all such types of attribute name(s) and/or parameter(s) are intended to fall within the scope of the hereto appended claims. The list400includes an attribute name field410, an attribute parameter(s) field420and a description field430.

A method attribute440can be employed to request a SPIL tool (e.g., a SPIL compiler system100and/or a SPIL generator system200)) to generate a method for the annotated stored procedure, and, to indicate that the method can be exposed to a high-level language through binding. The method attribute440does not have an associated attribute parameter.

A class attribute450can be employed to notify the SPIL tool to generate a function inside a custom class indicated through the class name parameter. The class attribute450has a class name parameter associated with it. The class specified in the class name parameter can be, for example, a logical unit within the high-level language that represents a logical entity. The class attribute450affords grouping of a set of methods within a class for high-level language accessibility. For example, if multiple stored procedures are annotated with similar class names, then they can be grouped together. A rights attribute460can be employed to assign read and/or write access.

Data can be partitioned, for example, logically, physically, temporally and/or spatially. A PartitionBy attribute470can be employed to request the SPIL tool generate a function to execute on a specific logical partition. The PartitionBy attribute470can have an algorithm and/or a partition identifier as a parameter. The algorithm parameter can be used to indicate a name of a hash function. The partition identifier parameter can be used to specify the parameter(s) utilized in the stored procedure to calculate a logical partition identification. The SPIL tool can utilize the parameter(s) to ensure the partition algorithm is meaningful.

A PartitionAll attribute480requests the SPIL tool to generate a function to execute on substantially all physical partitions. The PartitionAll attribute480does not have an associated attribute parameter.

A PartitionAny attribute490requests the SPIL tool to generate a function to execute on any physical instance identification. The PartitionAny attribute490does not have an associated attribute parameter.

The call by identification attribute494requests the SPIL tool to generate a function to execute on a specific physical instance identification. The call by identification attribute494does not have an associated attribute parameter.

In one example, the PartitionBy attribute470, the PartitionAll attribute480, the PartitionAny attribute490and the call by identification attribute494are mutually exclusive, that is, no more than one of them can be employed within a particular annotated stored procedure.

For example, an exemplary portion of an annotated stored procedure in accordance with an aspect of the present invention provides:

TABLE 1--[Method]--[class(x)]--[rights(read)]--[PartitionBy(y, z)]CREATE PROCEDURE Example_1(code associated with stored procedure Example_1)

Table 1 includes annotations as described above. In particular, the portion of the stored procedure comprises a method attribute, a class attribute with a parameter “x”, a rights attribute with a “read” parameter, and, a PartitionBy attribute470with parameters “y” and “z”. The attributes indicate that this method should be exported, that language binding should be created in the class x, that read privileges are available, and that the database instance is resolved using the hash function y and partition identification z.

Next, another exemplary portion of an annotated stored procedure in accordance with an aspect of the present invention provides:

TABLE 2--[Method]--[class(a)]--[PartitionAny]CREATE PROCEDURE Example_2(code associated with stored procedure Example_2

Table 2 includes annotations as set forth previously. In particular, the portion of the stored procedure illustrated includes annotations comprising a method attribute, a class attribute with a parameter “a”, and a partition any attribute. The attributes indicate that this method should be exported, that language binding should be created in the class a, and that any database instance can be utilized.

The example of Table 2 does not include a rights attribute; however, in one example, a default attribute can be employed when an attribute is not included in the annotation. For example, default rights can be read-only wherein not including the rights attribute indicates read-only privileges. Likewise, default attributes can be employed with one or more other annotation attributes. Further, it is to be appreciated that the system and method of the present invention can be employed with partitioned and/or non-partitioned system(s).

Turning toFIG. 5, a stored procedure code generation system500in accordance with an aspect of the present invention is illustrated. The system500comprises a SPIL tool set510and, optionally, a server520. The SPIL tool set510includes a SPIL compiler system100and a SPIL generator system200. The server520includes an annotated stored procedure store530.

The server520can be an SQL server with one or more SQL databases, or any server and/or database wherein a stored procedure can be saved and invoked. The server520is operative to the SPIL tool set510to provide the SPIL tool set510with access to the annotated stored procedures store530. The server520can additionally provides a transfer interface540for transferring annotated stored procedures to the server520, and/or a call interface550for invoking a stored procedure residing in the server520.

The transfer interface540can be utilized, for example, by a developer writing annotated stored procedure(s). The annotation in a stored procedure can include the annotation and associated attributes described above. The annotation included in a stored procedure declaratively indicates the developer's intent to bind the stored procedure to high-level code (e.g., a method(s)). The annotation can further indicate the method, a class where the method resides, read/write privileges, and partition information, for example.

The call interface550can be employed to call an annotated stored procedure via employing generated high-level code that was generated by the SPIL tool set510from the annotated stored procedures store530. Generally, the generated code is complied to form application code, wherein a client application can call the stored procedure via the application code to invoke the stored procedure. Conventionally, the developer can write a call to a stored procedure after writing the stored procedure. The present invention mitigates consuming developer time and effort by generating (e.g., automatically) the call to the stored procedure.

As noted above, the SPIL tool set510comprises the SPIL compiler system100and a SPIL generator system200. The SPIL compiler system100can receive annotated stored procedure(s) from the annotated stored procedure store530. In one example, an external trigger (not shown) from a developer notifies the SPIL tool set510that an annotated stored procedure is available to utilize to generate stored procedure calling code. In another example, the server520notifies the SPIL tool set510that an annotated stored procedure is available. In yet a third example, the SPIL tool set510periodically polls the server520to determine if an annotated stored procedure is available. It is to be appreciated that the aforementioned techniques do not limit the invention and that any known notification technique can be employed in accordance with the present invention.

After receiving an annotated stored procedure, the SPIL tool set510utilizes annotation(s) to facilitate generating stored procedure calling code. The SPIL compiler system100compiles code to generate intermediate code in stored procedure interface language (SPIL) definition (e.g., XML-based language). The SPIL compiler system100can optionally further employ error checking during compilation via the compile-time checker140.

The SPIL definition is then utilized by the SPIL generation system200to generate stored procedure calling code, for example, stored in the stored procedure generated code store230and/or associated stored procedure library(ies)240(e.g., C, C++, C#, Jscript.net, VB.net and/or Perl.net). The generated stored procedure calling code and the associated libraries can be accessible to application code, and can be employed to invoke the stored procedure within the server520(e.g., runtime library(ies) for calling the stored procedure(s)). Thus, the present invention mitigates developing, debugging and testing code to call a stored procedure, thereby reducing development time and improving development efficiency. In addition, consistent and clean stored procedure calling code will be generated, and can be employed as a coding standard.

Next, referring toFIG. 6, a stored procedure invocation system600in accordance with an aspect of the present invention is illustrated. The system600includes an interface component610, a database620having stored procedure(s)630. The system600further includes a stored procedure generated code store230and/or a stored procedure library240. The system600facilitates access to generated stored procedure calling code (e.g., to client applications).

Generally, stored procedure(s) are saved in a server (or database) wherein the stored procedures can be called from an application. Conventionally, a developer writes a stored procedure, and then writes the code to call the stored procedure. In accordance with an aspect of the present invention, a developer can include annotation(s) as set forth supra within the stored procedure to mitigate writing corresponding stored procedure calling code, wherein the annotations are utilized to generate (e.g., automatically) stored procedure calling code.

The annotated stored procedure can then be transferred to a server, and a stored procedure interface language tool set510(SPILT), in accordance with the invention, can be employed to generate the stored procedure calling code (e.g., in the stored interface language (SPIL) or other suitable XML-based language) utilizing the annotation(s) within the stored procedure. The generated stored procedure calling code can then be made accessible to application(s), and employed to invoke a stored procedure within the server.

The interface component610can be utilized for uploading a stored procedure to the database620(or server) and downloading a stored procedure from the database620. Typically, after an annotated stored procedure is written, tested and debugged, it is transferred (e.g., uploaded), employing interface component610, to the database620wherein it can be stored in the stored procedure(s)630and to generate stored procedure calling code and/or be called.

The interface component610can further be employed to interface application(s) with (1) generated stored procedure calling code stored in the stored procedure generated code store230and/or (2) the stored procedure library240. Typically, the application code interacts with the generated code to instantiate a generated class, and then the application code invokes (e.g., via a calling function) a stored procedure in the server (not shown). The interface component610facilitates connection management between the calling function and the stored procedure in the server to provide a physical connection, to execute the stored procedure, and optionally to provide enumeration of physical connection(s). Thus, the interface component610mitigates the application code from having knowledge regarding the physical connection. In addition, a layer of security can be provided to shield the application code from obtaining the actual physical identifier.

It is to be appreciated that various connection management model(s) can be employed in accordance with the present invention. For example, connection management models (e.g., off-the-shelf and proprietary) to connect to one or more database servers with one or more partitions on the one or more database servers can be employed. In one aspect of the present invention, the connection management employed by the interface component610provides connections for supplied parameter (e.g., store name and logical hash value) combinations. Generally, a look-up table or logical calculations can be employed to perform a virtual-to-physical connection mapping. Then a connection string can be formed to establish the connection for the supplied parameter combination. Various techniques can be employed to mitigate the cost associated with establishing the connection when substantially similar parameters are subsequently supplied. For example, caching object instances can be employed to reduce the cycles required to create and destroy similar objects. In another aspect of the present invention, enumeration of all physical instances for a given logical database can be employed. For example, enumeration keys for physical instances can be provided. The enumeration keys can be accessible to a stored procedure call that is tagged with physical calling attributes.

The stored procedure library240can provide classes that can be instantiated and employed to invoke stored procedures within the database620.

Turning briefly toFIGS. 7,8,9and10that may be implemented in accordance with the present invention are illustrated. While, for purposes of simplicity of explanation, the methodologies are shown and described as a series of blocks, it is to be understood and appreciated that the present invention is not limited by the order of the blocks, as some blocks may, in accordance with the present invention, occur in different orders and/or concurrently with other blocks from that shown and described herein. Moreover, not all illustrated blocks may be required to implement the methodologies in accordance with the present invention.

The invention may be described in the general context of computer-executable instructions, such as program modules, executed by one or more components. Generally, program modules include routines, programs, objects, data structures, etc. that perform particular tasks or implement particular abstract data types. Typically the functionality of the program modules may be combined or distributed as desired in various embodiments.

Referring toFIG. 7, a method of providing a SPIL compilation of an annotated stored procedure700in accordance with an aspect of the present invention is illustrated. At710, an annotation is extracted from the annotated stored procedure. The annotation can include an attribute name, for example, a method attribute, a class attribute, a rights attribute, a partition by attribute, a partition all attribute, a partition any attribute and/or a call by identification attribute. In one example, substantially all annotations are extracted from the annotated stored procedure.

At720, the extracted annotation is utilized to construct a SPIL definition of the annotated stored procedure. For example, the SPIL definition can declaratively identify a set of annotated stored procedures and/or a logical grouping of a set of annotated stored procedures. The SPIL definition can be XML-based.

At730, the SPIL definition is stored, for example, in a SPIL definition store. At740, compile-time error checking of the annotated stored procedure is performed. For example, the compile-time error checking can be performed when a client calls the stored procedure.

Turning toFIG. 8, a method of producing code to call an annotated stored procedure in accordance with an aspect of the present invention is illustrated. At810, code to call the annotated stored procedure is generated produced based, at least in part, upon a SPIL definition of the annotated stored procedure. At820, the produced code is stored, for example, in a stored procedure generated code store.

At830, a library to call the annotated stored procedure is generated based, at least in part, upon the SPIL definition. At840, the library is stored, for example, in a stored procedure library. For example, the library can be associated with the C, C++, C#, Jscript.net, VB.net and/or Perl.net programming languages (e.g., runtime library(ies) for calling the stored procedure(s)).

Next, referring toFIG. 9, a method to generate stored procedure calling code900in accordance with an aspect of the present invention. At910, an annotated stored procedure is created (e.g., by a programmer writing a stored procedure and including annotation(s). The annotation(s) declare the programmer's intent to have stored procedure calling code generated. For example, the programmer can transfer the annotated stored procedure to a server and/or database.

At920, the annotated stored procedure is compiled to create a SPIL definition (e.g., by a SPIL compiler system100). At930, code to call the annotated stored is generated based, at least in part, upon the SPIL definition (e.g., by a SPIL code generation system200). At940, a class library accessible to application(s) is generated based, at least in part, upon the SPIL definition.

Referring toFIG. 10, a method of utilizing generated stored procedure calling code1000in accordance with an aspect of the present invention is illustrated. At1010, application code is created that includes employing an annotated stored procedure. For example, the annotated stored procedure can be called utilizing a class library that was generated from annotations included in the annotated stored procedure (e.g., by a SPIL tool set510).

At1020, an object that utilizes a library associated with the annotated stored procedure is instantiated. At1030, a method associated with the object instance is executed. At1040, the executed method calls the annotated stored procedure.

In order to provide additional context for various aspects of the present invention,FIG. 11and the following discussion are intended to provide a brief, general description of a suitable operating environment1110in which various aspects of the present invention may be implemented. While the invention is described in the general context of computer-executable instructions, such as program modules, executed by one or more computers or other devices, those skilled in the art will recognize that the invention can also be implemented in combination with other program modules and/or as a combination of hardware and software. Generally, however, program modules include routines, programs, objects, components, data structures, etc. that perform particular tasks or implement particular data types. The operating environment1110is only one example of a suitable operating environment and is not intended to suggest any limitation as to the scope of use or functionality of the invention. Other well known computer systems, environments, and/or configurations that may be suitable for use with the invention include but are not limited to, personal computers, hand-held or laptop devices, multiprocessor systems, microprocessor-based systems, programmable consumer electronics, network PCs, minicomputers, mainframe computers, distributed computing environments that include the above systems or devices, and the like.

With reference toFIG. 11, an exemplary environment1110for implementing various aspects of the invention includes a computer1112. The computer1112includes a processing unit1114, a system memory1116, and a system bus1118. The system bus1118couples system components including, but not limited to, the system memory1116to the processing unit1114. The processing unit1114can be any of various available processors. Dual microprocessors and other multiprocessor architectures also can be employed as the processing unit1114.

The system memory1116includes volatile memory1120and nonvolatile memory1122. The basic input/output system (BIOS), containing the basic routines to transfer information between elements within the computer1112, such as during start-up, is stored in nonvolatile memory1122. By way of illustration, and not limitation, nonvolatile memory1122can include read only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable ROM (EEPROM), or flash memory. Volatile memory1120includes random access memory (RAM), which acts as external cache memory. By way of illustration and not limitation, RAM is available in many forms such as synchronous RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), Synchlink DRAM (SLDRAM), and direct Rambus RAM (DRRAM).

It is to be appreciated thatFIG. 11describes software that acts as an intermediary between users and the basic computer resources described in suitable operating environment1110. Such software includes an operating system1128. Operating system1128, which can be stored on disk storage1124, acts to control and allocate resources of the computer system1112. System applications1130take advantage of the management of resources by operating system1128through program modules1132and program data1134stored either in system memory1116or on disk storage1124. It is to be appreciated that the present invention can be implemented with various operating systems or combinations of operating systems.

A user enters commands or information into the computer1112through input device(s)1136. Input devices1136include, but are not limited to, a pointing device such as a mouse, trackball, stylus, touch pad, keyboard, microphone, joystick, game pad, satellite dish, scanner, TV tuner card, digital camera, digital video camera, web camera, and the like. These and other input devices connect to the processing unit1114through the system bus1118via interface port(s)1138. Interface port(s)1138include, for example, a serial port, a parallel port, a game port, and a universal serial bus (USB). Output device(s)1140use some of the same type of ports as input device(s)1136. Thus, for example, a USB port may be used to provide input to computer1112, and to output information from computer1112to an output device1140. Output adapter1142is provided to illustrate that there are some output devices1140like monitors, speakers, and printers among other output devices1140that require special adapters. The output adapters1142include, by way of illustration and not limitation, video and sound cards that provide a means of connection between the output device1140and the system bus1118. It should be noted that other devices and/or systems of devices provide both input and output capabilities such as remote computer(s)1144.

Computer1112can operate in a networked environment using logical connections to one or more remote computers, such as remote computer(s)1144. The remote computer(s)1144can be a personal computer, a server, a router, a network PC, a workstation, a microprocessor based appliance, a peer device or other common network node and the like, and typically includes many or all of the elements described relative to computer1112. For purposes of brevity, only a memory storage device1146is illustrated with remote computer(s)1144. Remote computer(s)1144is logically connected to computer1112through a network interface1148and then physically connected via communication connection1150. Network interface1148encompasses communication networks such as local-area networks (LAN) and wide-area networks (WAN). LAN technologies include Fiber Distributed Data Interface (FDDI), Copper Distributed Data Interface (CDDI), Ethernet/IEEE 802.3, Token Ring/IEEE 802.5 and the like. WAN technologies include, but are not limited to, point-to-point links, circuit switching networks like Integrated Services Digital Networks (ISDN) and variations thereon, packet switching networks, and Digital Subscriber Lines (DSL).

Communication connection(s)1150refers to the hardware/software employed to connect the network interface1148to the bus1118. While communication connection1150is shown for illustrative clarity inside computer1112, it can also be external to computer1112. The hardware/software necessary for connection to the network interface1148includes, for exemplary purposes only, internal and external technologies such as, modems including regular telephone grade modems, cable modems and DSL modems, ISDN adapters, and Ethernet cards.