The Structure of AEPs

Most database servers that support stored procedures limit how you can create them. For example, most database servers support only one, or maybe two, languages to write stored procedures. In most cases, these languages are a mixture of SQL and rudimentary control structures. These stored procedures are often written using tools provided by the database vendor, which in some cases have to be purchased separately. In addition, typically only the database administrator can create and install stored procedures.

ADS provides you with great flexibility when it comes to creating and installing stored procedures. As mentioned earlier in this chapter, AEPs can be written as Windows DLLs, in-process COM objects, .NET class libraries, or Linux shared object libraries. It does not matter which language you use to create these AEPs.

Because you can use your language of choice to create your stored procedures, AEPs can be far more sophisticated than those used by most other database servers. Instead of being restricted to conditional SQL, your stored procedures can use the full breadth of capabilities of the development tool you choose. You can even perform tasks that have little or nothing to do with your database, such as sending an automated email message, if your development environment supports that.

For example, a Delphi developer can use practically any VCL (visual component library) class or RTL (runtime library) routine. Similarly, developers using a .NET language, such as C#, VB.NET, C#Builder, or Delphi for .NET can use any of the classes in the FCL (framework class library), as well as any invokable managed or unmanaged code.

Being able to use your development language of choice provides you with additional benefits. First, you do not have to learn another language before you can write stored procedures. Second, you get to use the tools with which you are already comfortable.

While ADS supports four distinct types of libraries for creating AEP containers, as described earlier in this chapter, all have similar characteristics. Each library type must export at least four functions: Startup, Shutdown, GetInterfaceVersion, and one or more stored procedure functions. The names used by the stored procedure functions are arbitrary.

Note

ADS 7.0 introduced version 2 AEPs, which greatly improved the performance of stored procedures. AEPs prior to ADS 7.0 are now called version 1 AEPs, and both their functions and the function parameters are significantly different from version 2 AEPs. Since version 1 AEPs have been deprecated, they are not discussed further in this book. For information on version 1 AEPs, refer to the Advantage documentation.

In addition to these functions, all AEP containers have an object that is used to manage one or more client connections. This object is created when the AEP container is first loaded, and destroyed when the AEP container is released. The name of this object depends on which AEP template you use to create your AEP container. For example, with .NET languages, this object is a HashTable named colClientInfo. For the convenience of the following discussion, we will refer to this object as the connection manager.

The following sections describe the four types of functions in greater detail.

The Startup Function

The startup function is called the first time a given client application calls one of the stored procedure functions in a particular AEP container using a specific connection. If a client application has two connections to ADS, and calls a particular AEP from each connection, the startup function will be called twice, once for each connection. The purpose of the startup function is to initialize an object that is used to maintain client state information about the connection, and to save this object to the connection manager.

The startup function is passed two parameters. The first parameter is a unique integer that is generated by the server prior to the invocation of the startup function. This value uniquely identifies the client’s connection that is invoking the AEP. This same value is passed as the first parameter in the shutdown function and as the first parameter in the stored procedure functions for all subsequent calls from the same client over the same connection. Consequently, this value is used to store the state maintenance object to, and retrieve the state maintenance object from, the connection manager.

The second parameter is a handle to an active ADS connection. This ADS connection has a one-to-one mapping to the connection on which the client is communicating with the AEP. As a result, if the client’s connection has an active transaction, any data manipulation performed with the connection whose handle is passed in the second parameter will be performed in that same transaction.

In most AEP containers, the startup function will create an instance of a state maintenance object, initialize a connection using the connection handle passed in the second parameter, and then save the state maintenance object so that it can be retrieved within the stored procedure functions and the shutdown function.

The startup function returns a 32-bit integer. With ADS 7.0, this value is not actually used, but should be set to the value 0 (zero) in order to maintain compatibility with potential changes in later releases of ADS.

The Shutdown Function

The shutdown function is called when a client that had invoked at least one of the stored procedure functions in the AEP container is closing its connection. The shutdown function is used to perform any cleanup operations on connection-specific objects that were created during the client’s use of the AEP.

Like the startup function, the shutdown function is passed two parameters. The first parameter is the unique connection ID for this client connection. The second parameter is a handle of an active connection. The shutdown function uses the connection ID to remove the state maintenance object for this connection from the connection manager, and to destroy it, if necessary. With automatic garbage collecting languages, such as .NET languages, the state maintenance object is destroyed automatically once there are no remaining references to it. With languages like these, you do not have to explicitly destroy the state maintenance object.

The shutdown function returns a 32-bit integer value. As with the startup function, this value should be 0 (zero).

The GetInterfaceVersion Function

This simple function returns a 32-bit integer, and is used to inform ADS of the AEP version of the library. With AEPs created for ADS 7.0, this function should return a value of 2. If this function is absent from the AEP, ADS will assume that the AEP version is 1.

The GetInterfaceVersion function takes no parameters.

Note

If you are using a version of ADS later than version 7.0, and Extended Systems has updated its AEP support, it is possible for GetInterfaceVersion to return a value other than 1 or 2.

Stored Procedure Functions

Each AEP container can have one or more stored procedure functions. These functions contain the code that performs the operations that you want to execute on the server when the stored procedure is called by the client application.

Stored procedure functions are passed three parameters. The first and second parameters contain the connection ID and the associated connection handle, respectively. The connection ID is typically used to retrieve the state maintenance object for the connection that is calling this procedure. In most cases, this state maintenance object holds an active connection, so usually you do not need to use the second parameter of your stored procedure object.

The third parameter is a 32-bit integer that serves as an optional output parameter. If your stored procedure can modify one or more records, you can use this parameter to return the number of records affected by the stored procedure’s execution.

As you learned earlier, stored procedures can be passed parameters and can return one or more rows of data. Both input parameters and results sets returned by stored procedures are passed between the AEP and ADS using ADT tables. The table containing the input parameters is named _ _input, and the table you use to return a result set is named _ _output. (Both of these table names are preceded by two underscore characters.)

These tables can be accessed from within your stored procedure function through the connection handle. Recall that this same connection handle is passed to the startup and shutdown functions, as well as to the stored procedure functions. For efficiency, most developers initialize this connection in the state maintenance object that is saved in the startup function. From within a given stored procedure, the state maintenance object for this connection ID is retrieved, and the connection is then used to read the input parameters from the _ _input table, if necessary. Not all stored procedures use input parameters.

As the stored procedure executes, any data that it needs to return to the invoking client is inserted into the _ _output table. As must be obvious, the __output table is always an empty table when the stored procedure function begins executing.

The structures of the _ _input and _ _output tables are defined when you register your stored procedure in a data dictionary. When registering a stored procedure in a data dictionary, you provide names and data types for all of your input and output parameters. The _ _input table will have one field for each input parameter, and the _ _output table will have one field for each output parameter.

There is a third temporary table accessible through the connection handle. This table, named _ _error, is an in-memory table. Unlike the _ _input and _ _output tables, the _ _error table is available to the startup and shutdown functions as well.

The _ _error table has two fields. The first field is an integer field named ERRORNO, and the second is a memo field named MESSAGE. If you want to return a custom error message, you add a single record to this table, setting ERRORNO to your custom error number and setting MESSAGE to your error message. If you want to return a custom message, but want to use ADS’s standard AEP error code, leave ERRORNO empty.