Advantage Developer Zone

 
 
 

Advantage Extended Procedures Version 2

Wednesday, April 14, 2004

With the release of Advantage Database Server 7.0, the Advantage Extended Procedures (AEP) format has changed slightly. These changes have made AEPs more efficient and easier to build. Advantage Database Server version 7.0 and newer will support both versions of AEPs.

Use the information in this tech tip to enhance your current AEPs and make them more efficient. You can also use the information to enhance your current databases by using AEPs.

Updated AEP Prototype

When calling an AEP with Advantage version 6.x the following parameters were required: ulConnectionID, pucUserName, pucPassword, pucProcName, ulRecNum, pucTable1, pucTable2. AEP version 2 requires the following parameters: ulConnectionID, ulConnectionHandle, pulNumRowsAffected.

UlConnectionID is a unique identifier that can be used to associate a user/connection with state-specific variables and other connection-specific information. hConnection is the active connection that can be used for all data operations. ulNumRowsAffected is an optional output parameter that can be used to specify how many rows were updated.

Since the active connection handle is passed to your AEP function it is no longer necessary to create a new connection within your AEP. This reduces the amount of code you have to write and makes the AEP more efficient.

Handling Input and Output Parameters

Input and output parameters are passed to an AEP using tables. With AEP version 1 you have to parse the table path from the pucTablex input parameters and make a connection.  Then you have to extract the file name from pucTable1 and pucTable2. These are your input and output tables respectively.

With AEP version 2 these tables are virtual. They can be accessed using the keywords __input and __output. They can be opened directly as a table or via an SQL statement.  The server keeps track of these virtual tables for you, reducing the amount of code you have to write and avoiding path problems on Linux, which is a case sensitive operating system.

A third virtual table __error is available to return custom error information from your procedure. The table contains two columns, ErrNo and Message. Errno is an Integer field and Message is a Memo field. If this table contains a record then Advantage will return the error number and message to the client.

AEPs and Permissions

With version 1 AEPs a new connection was made to Advantage using the passed in username and password. Therefore, access to the dictionary objects was limited to the permissions of the defined user. Version 2 AEPs remove this restriction. AEPs have access to all dictionary objects by default. 

This allows the developer to restrict access to dictionary objects, tables for example, and allow access only through defined AEPs. This provides great flexibility and control of your data since you define how the tables are manipulated within your AEP code.

Other Enhancements

AEPs can now be called from within a transaction. If the active connection handle passed to the AEP is in a transaction all operations performed by the AEP will be included in the context of the transaction.

You can now return the number of rows affected by the AEP using the ulNumRowsAffected parameter. This parameter is set within the code of your AEP and is available to the client after the AEP has executed. This is similar to the functionality available with data manipulation language (DML) SQL statements such as INSERT, UPDATE or DELETE.

A new AEP version 2 template is available for Delphi and ships with the Advantage TDataSet Descendant. Templates are also available for Visual Studio .NET, both C# and VB, and C++ environments.

Additional Information

The Advantage Help File includes tutorials for Delphi and Visual Basic programmers. These tutorials provide step-by-step instructions on creating a simple AEP. 

Advantage Database Server: The Official Guide also contains an entire chapter on AEPs.  The book includes detailed descriptions of extended procedures as well as sample code written in Delphi and C#.

An online webinar presentation discussing the usage and functionality of AEPs was presented on Thursday, April 22, 2004. See first hand, how easy it is to create AEPs and use them in new and existing applications. Examples are provided in Delphi and Visual Studio .NET.  To view the archived seminar, visit: http://www.extendedsystems.com/web/download.aspx?key=6A6CFC2624E2D9E886E285B610F940C1.