Using AEPs

Once you have written your AEP, compiled it, and installed it on the machines from which it will be executed, you will need to add one stored procedure object to your data dictionary for each stored procedure function that you want to call. For example, if you have an AEP container with five stored procedure functions, and want to be able to call all of them from your client applications, you will need to create five stored procedure objects. Installing AEPs is discussed in the next section.

In addition to adding stored procedure objects, if your data dictionary is set up to require logins and check user rights, you will need to grant permissions to the groups and/or users who need to execute the stored procedures. Granting permissions to stored procedures is discussed later in this section.

Installing AEPs

Like all other objects in a data dictionary, you can install stored procedures using a number of techniques. For example, they can be installed using Advantage SQL and direct calls to the ACE (Advantage Client Engine) API. Typically, you use these techniques if you need to install an AEP at runtime.

Most of the time, however, installing an AEP is part of the overall design process of your data dictionary, which means that you do it at design time. In these cases, you install an AEP using the Advantage Data Architect.

Use the following steps to install your AEPs:

1. If the DemoDictionary data dictionary is not open in the Advantage Database Manager, open it.

2. From the Advantage Database Manager, right-click the STORED PROCS node in the tree view and select Add. The Advantage Database Manager responds by displaying the Advantage Extended Procedure dialog box, like that shown in Figure 7-2.

   
   Figure 7-2: The Advantage Extended Procedure dialog box

3. Set Object Name to the name you want to use to refer to your AEP. If you are installing one of the AEPs that you created by following the steps provided earlier in this chapter, set Object Name to DelphiAEP, CSNetAEP, or VBNetAEP, depending on which development tool you use to create the AEP.

4. If you are installing the Delphi AEP container (or any AEP container you created as a standard Windows DLL or shared object library), enable the Standard AEP radio button. If you created your AEP container as a COM server or a .NET class library, enable the ActiveX or .NET AEP radio button.

5. If you select Standard AEP, set the Advantage Extended Procedure File field to the AEP container filename. Most ADS developers prefer to store their DLLs (or shared object libraries) in the same directory in which they have stored their data dictionary. While this is the recommended approach, you can actually store your DLLs or shared object libraries in any directory on the same share as the data dictionary. If you do store the DLLs or shared object libraries in a directory other than the one in which your data dictionary is stored, we recommend that you refer to the libraries using a UNC (universal naming convention) path, rather than a DOS or Linux path.

   If you select the ActiveX or .NET AEP radio button, set Advantage AEP Program ID (ProgID) to the ProgID of the COM server or managed assembly. The ProgID is a value registered under the HKEY_CLASSES_ROOT key of the Windows registry. So long as the COM server or .NET class library has been registered on the machine on which the Advantage Data Architect is running, you can click the ellipsis button to the right of the Advantage AEP Program ID (ProgID) field to see a list of the registered ProgIDs. Normally, the ProgID for a registered .NET class library is the combination of the .NET project name plus the AEP class name, separated by a period. For example, for the AEPDemoCS project, the ProgID is likely to be AEPDemoCS.aep_ procedures.

   

6. Set the Stored Procedure Name field to the name of the stored procedure function that you are creating this stored procedure object for. If you are installing an AEP based on the steps provided earlier in this chapter, this name will be Get10Percent.

7. Use the Input Parameters section to define the optional input parameters for your stored procedure. For the Get10Percent function, set Name to CustID, and set Type to INTEGER. Click Add after defining the name and type to add the parameter to the input parameter list.

8. Use the Output Parameters section to define the optional output parameters of your AEP. For the Get10Percent function, set Name to InvoiceNo, set Type to CHAR, and set Size to 12.

9. When you are done, click OK to save the stored procedure object.

10. If your data dictionary requires login and checks user rights, you need to grant execute privileges to each of the groups and users that need to be able to execute the stored procedure. For groups, right-click the node for the group in the Advantage Database Manager tree view and select Properties. Click the Procedure Rights button, and then enable the checkbox for each stored procedure that the group’s members need to execute. Click OK to close the Permissions dialog box, and then click Save to save the new permissions.

    For each user who does not belong to a group from which they will inherit the rights to execute your stored procedures, right-click the user’s node and select Properties. Click Procedure Rights and then check the checkbox next to each procedure that this user needs to be able to execute. Click OK to close the Permissions dialog box, and click Save to save your changes.

    Caution

    You cannot include spaces in either input parameter or output parameter names. If you attempt to define a parameter whose name includes at least one space, the stored procedure object cannot be created.

There are several points that need to be made concerning stored procedures. First, the input and output parameters that you define using the Advantage Extended Procedure dialog box are used to define the structures of the _ _input and _ _output tables that you work with in your stored procedure implementation. In particular, the order of the parameters defines the order of the resulting fields in the _ _input and _ _output tables. If the access mechanism that you are using references fields by their ordinal position, it is up to you to ensure that the order of the parameters in your stored procedure object definition matches the references you use in your stored procedure code.

Another point is that if you modify a stored procedure definition, all access rights to it are removed from your groups and users. Consequently, any time you update a stored procedure object, ensure that you also re-grant the necessary access rights to it. Of course, this is only necessary if you require login and check user rights for the data dictionary.

Testing AEPs

After you create your stored procedure objects, you will want to test them to make sure that they are doing what you want. One of the easiest ways to test a new stored procedure from the Advantage Data Architect is to execute it using the Native SQL Utility. The following steps show you how:

1. Select Tools | Native SQL Utility from the Advantage Data Architect main menu.

2. Set Connection Type to Alias, and set Alias to DemoDD. Click Connect. When the Native SQL Utility asks for the password, enter password. (If you find that you cannot open the Native SQL Utility, select the Query Options toolbar button on the Native SQL Utility, and verify that Connection Type is set correctly. The Native SQL Utility is discussed further in Chapter 9.)

3. Once connected, enter an EXECUTE PROCEDURE SQL statement in the SQL editor. You follow EXECUTE PROCEDURE with the name of the stored procedure object in your data dictionary, and enclose any input parameters in the parentheses that follow. For example, to execute the DelphiAEP object, enter the following SQL statement:

   EXECUTE PROCEDURE DelphiAEP(12037)

4. After entering the SQL statement, click the Execute SQL button. If the stored procedure returns data, the result set is displayed, as shown in Figure 7-3.

   
   Figure 7-3: The results of a stored procedure shown in the Native SQL Utility

Debugging AEPs

You debug an AEP using the debugging features of your development environment. Most development environments permit you to set breakpoints in your code, and then cause an external host to load your compiled application. In Visual Studio .NET, the external application is referred to as the calling application, whereas in Borland products, it is called the host application.

Note

Most developers debug their AEPs using ALS. However, beginning with ADS 7.0, the Windows NT/2000/2003 and Linux versions of the ADS server have an -exe command-line option, permitting ADS to be used during debugging. See the ADS documentation for more information on this feature.

When the external application calls your AEP and encounters the breakpoint, your application is loaded into the debugger. From that point, you can use the tools of your development environment’s debugger to inspect variables, evaluate expressions, and step into or over individual instructions.

For specific information on debugging DLLs, shared object libraries, COM objects, and .NET class libraries, see the documentation for your particular development environment.

Note

In some cases you may need to adjust one or more properties of the project before it can be loaded by your development environment’s debugger. For example, with a Delphi AEP, you must use the linker page of the Project Options dialog box to instruct the compiler to include TD32 debug info and include remote debug symbols. Once you are through debugging your AEP, be sure to turn off these settings before you deploy your AEP container.

Deploying AEPs

What steps you need to take when deploying an AEP container depend on the type of AEP container it is. The easiest AEP containers to deploy are those that are created as DLLs or shared object libraries, especially if you store them in the same directory as the data dictionary. Simply copy the DLL or shared object library to that directory. If you store your DLL or shared object library in a directory different from where your data dictionary files are, ensure that your stored procedure object definition includes the path to that library.

AEP containers deployed as COM objects or .NET class libraries are somewhat more complicated to deploy. Although your COM object or .NET assembly can be placed in any directory, it must be registered with the Windows registry. As you learned earlier in this chapter, you register it with regasm.exe, which ships with the .NET framework, and is therefore available on any machine on which the .NET framework has been deployed. The .NET framework must appear on any machine to which you deploy an AEP container created as a .NET-managed assembly.

Note

A number of development environments include an installation builder that can take responsibility for registering your COM objects and .NET class libraries.

There are several additional issues that apply to .NET assemblies that you want to deploy. Specifically, you may want to consider providing your deployed AEP assemblies with a strong name. Strongly naming an assembly prevents it from being spoofed, allowing unauthorized access to your database. In addition, you may want to sign your assemblies, which ensures your customers of the source of the executable. Refer to your .NET documentation for additional details about these issues.

Note that if you are deploying updated AEP containers that are either COM or .NET class libraries, it is a good idea to first unregister your older versions before replacing and registering the new versions. Failure to unregister older versions before replacing them may leave unwanted entries in that machine’s Windows registry.

The problem of installation is magnified if you are using ALS instead of ADS. When you use ALS, the COM object or .NET-managed assembly must be installed on all client machines. Furthermore, the .NET framework must be present on all client machines as well.

In the next chapter, you will learn about Advantage triggers.