Writing AEPs

You can write AEPs using any language that supports the creation of the types of executable files supported by ADS. To simplify the task of creating AEPs, Advantage provides templates for the most popular development environments. One of these templates is used for Delphi, Kylix, and C++Builder. There is another template for Visual Basic. Additional templates are used for VB.NET and Microsoft C#. There is also a template for Borland’s C#Builder.

You do not need to begin with one of these templates, but doing so is highly recommended. These templates are well designed and can significantly reduce the amount of time it takes for you to create AEPs. If you decide instead to write your own AEPs from scratch, you should examine the AEP templates both for the signatures of the included functions, as well as for the helpful comments that appear throughout the code.

The following sections in this chapter show you how to create AEPs using Delphi, C#, and VB.NET. The descriptions in these sections assume that you are already familiar with using the development environment being described. Consequently, only high-level steps are provided, such as “create a new project from the AEP template.”

The primary focus of these sections is the code that produces the AEP. If you are new to one or more of these development environments, and want to follow along with the examples, you may want to refer to the development environment’s documentation, or get a good introductory book on the tool.

The AEP container that is created in the following sections contains only a single stored procedure function. This function is named Get10Percent, and it will return every tenth invoice number for a given customer. If a customer has fewer than ten invoices overall, Get10Percent returns an error.

A function like Get10Percent might be used to select records for auditing purposes. If this were a real function, you would probably want to select ten percent of the records by randomly selecting the records to return. Implementing Get10Percent using random selection would require much more code, which is why this example simply returns every tenth record.

Nevertheless, this stored procedure demonstrates the value of server-side processing provided when you use AEPs with ADS. Specifically, if you were to write a function like Get10Percent in a client application, all of a customer’s records need to be transferred across the network before the ten percent of records can be selected. Implementing Get10Percent as an AEP permits the selection to be performed on the server, and only the ten percent of records is transferred to the client.

Get10Percent takes a single integer input parameter that will identify the customer whose records need to be processed. This stored procedure has a single output parameter, which holds the invoice numbers that are returned. In this case, zero or more records are returned by the function.

From within the function, the input parameter is read from the _ _input table and is used to select all of that customer’s records. Next, the invoice number for every tenth record is written to the _ _output table. Once this processing is complete, if the output table contains no records because there were less than ten records for that customer, an error code and a message is written to the _ _error table.

The final step is optional in your stored procedures. If your stored procedure affected one or more records, the number of affected records can be assigned to the third parameter of the stored procedure, a parameter provided for just this purpose.

But before we get to the examples, there are a couple of warnings about AEPs to consider. These are related to the multithreaded nature of AEP execution, as well as exception handling in your AEPs.

ADS is a multithreaded server. When a client invokes an individual stored procedure in an AEP container, that invocation is performed by one of the server’s threads.

Most AEPs can be invoked simultaneously from different threads on the ADS server. (Only COM servers created in Visual Basic 6 cannot be invoked concurrently. More about that is said in the section “Creating AEPs Using Visual Basic 6.”) As a result, it is extremely important that you use good multithreading programming techniques.

In short, you should not refer to global variables or other resources that are not thread-safe from within your stored procedures without using a synchronization object such as a critical section. The state maintenance object, however, is thread- safe (at least as it is employed in the code generated by AEP templates).

Note

Two or more stored procedure calls from a given client’s connection will not necessarily be made using the same thread. In other words, do not use the thread ID of the thread from which a stored procedure is invoked to identify a client. Only use the connection ID parameter passed to the stored procedure to identify which client connection the stored procedure is being invoked from. Also, for the same reason, thread local storage cannot be used for state maintenance. If you need to maintain state, add that feature to your state maintenance object that is created in the startup function.

Another concern for AEP developers is exceptions. It is considered bad form to permit an unhandled exception to escape your AEP container. Consequently, your AEP code should always be placed inside an exception trapping block. If an exception occurs, your code should insert an error into the _ _error table.

Creating AEPs Using Delphi

You create a Windows DLL when you create an AEP in Delphi using the template provided. This template is installed when you install the Advantage TDataSet Descendant. This template is also available when you install the TDataSet Descendant for C++Builder or the TDataSet Descendant for Kylix. With Kylix, however, the template will create a shared object library.

Note

The extension of this DLL or shared object library will be .aep.

Creating the AEP Project

You can use the following steps to create an AEP using Delphi:

1. Select File | New | Other to display the Object Repository.

2. Click Projects to view the Project templates page of the Object Repository.

   

3. Select the Advantage Extended Procedure, and click OK. When prompted, use the displayed browser to select a directory in which this project will be stored.

The copied template is now your current project in Delphi.

Note

The steps given here for creating AEPs using Delphi are also applicable to Kylix. For C++Builder, the steps are similar, and the object properties and methods are the same, but you need to write the code in C++ instead of the Delphi language.

Saving the Project Using a New Name

When you use a project template, you are using an exact copy of a project that was installed in your Object Repository. If you were to create another AEP, it would have exactly the same project name, and this could lead to confusion. Consequently, one of your first tasks should be to rename the project. To do this:

1. Select File | Save Project As from the main menu.

2. Save the project using the name AEPDemoD.dpr.

Changing Your Stored Procedure Function Name

Initially the AEP template includes one stored procedure named MyProcedure. You should change the name of this procedure to something meaningful. In this case, the new procedure name will be Get10Percent. This must be done in two places in the .DPR file as described here:

1. Search the AEPDemoD.dpr file for the line that begins function MyProcedure.

2. Change the name of the function from MyProcedure to Get10Percent.

3. Locate the exports clause at the end of this unit. Change the entry for MyProcedure to Get10Percent.

Writing Your Stored Procedure

You are now ready to update the Get10Percent function and compile your project. Use the following steps:

1. Locate the beginning of the Get10Percent function in AEPDemoD.dpr.

2. Modify this procedure to look like that shown in Listing 7-1.

3. When you are done updating the procedure, select Project | Compile AEPDemoD.dpr from the main menu.

You are now done. Your compiled DLL, named AEPDemoD.aep, will be located in the directory where you saved your AEP project. While it is not required that you do so, we recommend that you set your output directory to the same directory where your DemoDictionary data dictionary is stored. That way, a new copy of the DLL will be written to this directory each time you compile, and it will also make debugging easier, if necessary.

ON THE CD

This listing is also located in listing7-1.txt located on this book’s CD-ROM (see Appendix B).

Listing 7-1

function Get10Percent( ulConnectionID: UNSIGNED32;   hConnection: ADSHANDLE;   pulNumRowsAffected: PUNSIGNED32 ): UNSIGNED32;    {$IFDEF WIN32}stdcall;{$ENDIF}   {$IFDEF LINUX}cdecl;{$ENDIF} var  DM1 : TDM1;  InvoiceTable: TAdsTable;  Counter: Integer;  CustID: Integer; begin  Result := AE_SUCCESS;   {* Get this connection's data module from the session manager. *}   DM1 := TDM1( AEPSessionMgr.GetDM( ulConnectionID ) );   try  with DM1 do  begin  InvoiceTable := TAdsTable.Create(nil);  try   //Get customer's invoices  InvoiceTable.DatabaseName := DataConn.Name;   InvoiceTable.TableName := 'invoice';   InvoiceTable.Open;   InvoiceTable.IndexName := 'customer ID';   tblInput.open;   CustID := tblInput.Fields[0].AsInteger;   InvoiceTable.SetRange([CustID], [CustID]);   tblInput.Close;            //Write 10 percent of customer IDs to output   tblOutput.Open;   InvoiceTable.First;   Counter := 0;   repeat   inc(Counter);   if Counter = 10 then   begin      Counter := 0;   tblOutput.Append;   tblOutput.Fields[0].Value :=   InvoiceTable.Fields[0].Value;   tblOutput.Post;   end;   InvoiceTable.Next;   until InvoiceTable.Eof;             //Generate error if no records   if tblOutput.IsEmpty then   begin   DataConn.Execute( 'INSERT INTO __error VALUES ' +   '( 2500, ' +   QuotedStr( 'There are less than 10 records for ' +   IntToStr(CustID) ) + ' )' );   Exit;   end;   pulNumRowsAffected^ := tblOutput.RecordCount;   finally   InvoiceTable.Free;   tblOutput.Close;   end;   end;  {* with DM1 *}     except   on E : EADSDatabaseError do   {* ADS-specific error, use ACE error code *}   DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( ' +   IntToStr( E.ACEErrorCode ) +    ', ' + QuotedStr( E.Message ) + ' )' );   on E : Exception do   {* other error *}   DM1.DataConn.Execute( 'INSERT INTO __error VALUES ( 1, ' +   QuotedStr( E.Message ) + ' )' );   end; end;

If you wanted to create additional stored procedure functions in this AEP container, you would make one or more copies of the original MyProcedure function. You would then change the names of these functions so that they are unique in the project, and then add these names to your exports clause.

Creating AEPs Using C# with Visual Studio .NET

When you create an AEP using C# with Visual Studio .NET, you begin with a template that is installed when you install the Advantage .NET Data Provider. You’ll see how to do this next.

Note

Extended Systems also provides a template for C#Builder developers. Using that template and the code provided in Listing 7-2, you can create this same project using C#Builder.

Creating the AEP Project

Use the following steps to create a new AEP project in Visual Studio using the AEP template:

1. Begin by selecting File | New | Project. Visual Studio responds by displaying the New Project dialog box. In the Project Types tree view, select Visual C# Projects. The available templates are displayed in the Templates pane, which appears to the right of the Project Types tree view, as shown here:

   

2. Scroll the Templates pane until you see the Advantage AEP template. Select this template.

3. Set Project Name to AEPDemoCS.

4. Next, use the Browse button to choose the directory in which you want to save this project.

5. Click OK to continue.

Your new C# AEP project should now be open in Visual Studio, as shown in Figure 7-1.

Figure 7-1: A new C# AEP project opened in Visual Studio .NET 2003

Renaming Your Stored Procedure

Like with the templates used to create AEPs in Delphi, the AEP template for C# creates a project that contains one stored procedure named MyProcedure. The following steps show you how to rename this procedure:

1. Locate the public method MyProcedure in the public aep_procedures class.

2. Change the name of the method from MyProcedure to Get10Percent.

   Note

   It is not necessary to change the name of the aep_procedures class, although you can do so if you like. Since the aep_procedures class is declared in the namespace associated with your project, it will be unique from any other aep_procedures classes that you create for other .NET AEPs, so long as they are defined in different namespaces. In Visual Studio, the project name defines the namespace, which is AEPDemoCS in this case.

Writing Your Stored Procedure

You are now ready to update and compile your AEP container as a .NET class library. Use the following steps:

1. Locate the Get10Percent method in the aep_procedures class.

2. Modify this method to look like that shown in Listing 7-2.

3. Select Build | Build AEPDemoCS.

Once you build, the file AEPDemoCS.dll will appear in the debug or release directory of your project’s directory.

ON THE CD

This listing is also located in listing7-2.txt, located on this book’s CD-ROM (see Appendix B).

Listing 7-2

public Int32 Get10Percent( Int32 ulConnectionID,   Int32 hConnection,   ref Int32 ulNumRowsAffected ) {   StateInfo         oStateInfo;   IDbCommand  oCommand;   IDataReader  oReader;   AdsDataAdapter Adapter;   DataSet DS;   DataTable Table;   Int32  custID;   Int32 counter;   Boolean oneOrMoreRows;   lock( colClientInfo )   oStateInfo = (StateInfo)(colClientInfo[ulConnectionID]);   try   {   oCommand = oStateInfo.DataConn.CreateCommand();   oCommand.CommandText = "SELECT * FROM __input";   oReader = oCommand.ExecuteReader();   oReader.Read();   custID = oReader.GetInt32(0);   //Close DataReader before connection can be reused   oReader.Close();   oCommand = oStateInfo.DataConn.CreateCommand();   DS = new DataSet();   Adapter = new AdsDataAdapter(   "SELECT [Invoice No] FROM INVOICE " +   "WHERE [Customer ID] = " + custID.ToString(),    oStateInfo.DataConn);    Adapter.Fill(DS, "INVOICES");   Table = DS.Tables["INVOICES"];       counter = 0;   oneOrMoreRows = false;   for (int i = 0; i<= (Table.Rows.Count-1)  ; i++)   {   counter++;   if (counter == 10)    {   oCommand.CommandText =    "INSERT INTO __output  VALUES ('"   + Table.Rows[i].ItemArray[0].ToString() + "')";   oCommand.ExecuteNonQuery();   counter = 0;   oneOrMoreRows = true;   }   }   if (! oneOrMoreRows)    {    oCommand.CommandText = "INSERT INTO __error VALUES( 2500, '"+   "Less than 10 records for "+custID.ToString()+"' )";   oCommand.ExecuteNonQuery();   }   }    catch( Exception e )   {   IDbCommand  oErrCommand = oStateInfo.DataConn.CreateCommand();   oErrCommand.CommandText = "INSERT INTO __error VALUES( 1, '" +     e.Message + "' )";   oErrCommand.ExecuteNonQuery();   }   return 0; }  // Get10Percent

Registering Your Stored Procedure

When you compile your project with Visual Studio, it registers your .NET class library in the Windows registry on your development machine. If your data dictionary is on this same machine, you are ready to install and test your AEP.

If you are running ADS as a remote server, and your data dictionary is on a machine other than the one where you compiled your .NET project, you must install the .NET class library before you can register the AEP in the data dictionary. This installation is performed using the regasm.exe utility, which is installed as part of the .NET framework. Since you cannot run the .NET class library without having installed the .NET framework, this utility should be on any machine on which you will run .NET AEPs.

Note

Depending on who is going to use your AEPs, and how you are going to distribute them, you may want to sign and strongly name your .NET assemblies. Refer to the .NET Framework SDK (software development kit) for more information on signing and strong names.

The following steps describe how to install your .NET class library:

1. Copy your .NET class library to the directory in which your data dictionary resides.

2. Open a command (CMD.EXE) window and navigate to the directory into which you copied your class library.

3. If regasm.exe is in a directory on your DOS path, you can register your library using a command similar to the following:

   regasm AEPDemoCS.dll /codebase

4. If regasm is not on your DOS path, you must enter the fully qualified path to regasm. (If you do not know where regasm.exe is stored, use the Windows Explorer’s searching capabilities to find it.) The command might look something like the following:

   c:\Windows\Microsoft.NET\Framework\v1.1.4322\regasm AEPDemoCS.dll /codebase 

   Note

   The preceding command-line entry must be entered as a single command (in one line). It appears on two lines here because of the limited line space in this book.

The use of the /codebase command-line option is required here because you have not stored your AEP class library in the GAC (global assembly cache). If you did register your .NET executable in the GAC, you do not have to perform the preceding steps. While storing your class library in the GAC permits you to share it, sharing is really not an advantage in this case. Placing your AEP in the same directory as your data dictionary ensures that you do not introduce version control problems with AEPs that are used in more than one data dictionary.

Also, when you use the /codebase command-line option, unless you have signed your compiled library, you will see several lines of message warning you that you are registering an assembly that is not signed, and that does not use a strong name. Giving your assembly a strong name and signing it are options that you need to consider. Those topics, however, are beyond the scope of this book. For information on strongly named and signed assemblies, refer to the online documentation in Visual Studio .NET.

Tip

You can unregister a .NET class library using the same command line, replacing /codebase with /unregister as the command-line option. In fact, if you must update or replace an AEP container, it is strongly recommended that you first unregister the old version before registering the updated version. Otherwise, you may get multiple ProgID entries in your Windows registry.

Creating AEPs Using VB.NET

You create an AEP using VB.NET using the same steps as outlined in the previous section “Creating AEPs Using C# with Visual Studio .NET.” There are only two differences:

• When the New Project dialog box is displayed, set Project Name to AEPDemoVB7 instead of AEPDemoCS.

• Your AEP is implemented in VB.NET instead of C#. Implement your Get10Percent method using the code shown in Listing 7-3.

  ON THE CD

  This listing is also located in listing7-3.txt, located on this book’s CD-ROM (see Appendix B).

Listing 7-3

Public Function Get10Percent(ByVal ulConnectionID As Int32, _  ByVal hConnection As Int32, _  ByRef ulNumRowsAffected As Int32) As Int32  Dim oStateInfo As StateInfo  Dim oCommand As IDbCommand  Dim oReader As IDataReader  Dim Adapter As AdsDataAdapter  Dim DS As DataSet  Dim Table As DataTable  Dim custID As Int32  Dim Counter As Int32  Dim oneOrMoreRows As Boolean  Try   ' Get this client's state information before doing anything   SyncLock colClientInfo   oStateInfo = colClientInfo.Item(CStr(ulConnectionID))   End SyncLock   ' Create command object to use   oCommand = oStateInfo.DataConnection.CreateCommand   oCommand.CommandText = "SELECT * FROM __input"   oReader = oCommand.ExecuteReader   oReader.Read()   custID = oReader.GetInt32(0)   'Close DataReader before connection can be reused   oReader.Close()   oCommand = oStateInfo.DataConnection.CreateCommand   DS = New DataSet   Adapter = New AdsDataAdapter( _   "SELECT [Invoice No] FROM invoice " + _   "WHERE [Customer ID] = " + custID.ToString(), _   oStateInfo.DataConnection)   Adapter.Fill(DS, "invoices")   Table = DS.Tables("invoices")   Counter = 0   oneOrMoreRows = False   Dim i As Int32   For i = 0 To (Table.Rows.Count - 1)   Counter = Counter + 1   If Counter = 10 Then   oCommand.CommandText = _   "INSERT INTO __output  VALUES ('" + _   Table.Rows(i).ItemArray(0).ToString() + "')"   oCommand.ExecuteNonQuery()   Counter = 0  oneOrMoreRows = True   End If   Next   If Not oneOrMoreRows Then   oCommand.CommandText = "INSERT INTO __error VALUES( " + _  "2500, 'Less than 10 records for" + custID.ToString + "')"   oCommand.ExecuteNonQuery()   End If  Catch Ex As Exception   Dim oErrCommand As IDbCommand   oErrCommand = oStateInfo.DataConnection.CreateCommand   oErrCommand.CommandText = "INSERT INTO __error VALUES( 1, '" + _   Ex.Message + "' )"   oErrCommand.ExecuteNonQuery()   Finally   Get10Percent = 0   End Try   End Function 'Get10Percent

Creating AEPs Using Visual Basic 6

Advantage provides a template for creating COM objects with Visual Basic 6. In short, the process is very similar to the process you use when you implement a .NET class library in VB.NET.

There is one very important difference between COM objects created with Visual Basic 6 and those created with Visual Studio .NET, C#Builder, Delphi, Kylix, and C++Builder. The COM objects that Visual Basic 6 produces create the COM object in a single threaded apartment (STA). All calls made to stored procedures in an STA are performed using the Windows message queue, which has the effect of serializing these calls.

The fact that calls to Visual Basic 6–created COM objects are serialized dramatically reduces the value of these types of AEPs. Recall that stored procedures in an AEP are called from a thread on the Advantage Database Server. Many threads can be running concurrently, and if two or more threads need to call a stored procedure in a specific AEP at the same time, the execution of these calls normally proceeds concurrently.

This concurrent processing does not happen when the AEP is compiled as a COM object using Visual Basic 6. Instead, the first thread to call a stored procedure will begin executing it, and a second thread must wait until that execution is complete before being able to proceed with the stored procedure execution. As a result, Visual Basic 6 AEP COM objects represent a potentially serious performance bottleneck.

If you are a Visual Basic 6 developer, and your AEP is called only occasionally or by only one client, there is probably little harm in creating your AEP using Visual Basic 6. However, if your AEP is one that is called frequently from several different clients, you will be much better off creating your AEP using one of the other development environments, even if your client applications are written in Visual Basic 6.