Automatically load 32 or 64 Bit Advantage Extended Procedures

With the release of Advantage Database Server 10 back in 2010 the API for 64Bit clients was added. The latest patches (10.10.0.28) now also supports Delphi XE2 and with that Delphi developers can write Advantage Extended Procedures (AEP) for 64Bit Servers aswell.
As my colleague Chris Franz mentioned in his blog back in April 2010 (blog.advantageevangelist.com) it is not possible to load 32Bit DLLs from a 64Bit service (and vice versa):

64bit Servers and External Libraries
If you have built external libraries which contain either Triggers or Stored Procedures these libraries may cause errors with a 64-bit Advantage server. Since these external libraries are loaded into the Advantage Server process space they must be built as 64-bit libraries. A 64-bit process cannot load a 32-bit DLL.

This causes some headaches for the deployment. You need to know the exact architecture of the ADS service currently running on the server in order to create the AEP in the data dictionary.
Let’s start with a convention: 32Bit AEPs are copied to the Win32 subfolder of the database, 64Bit AEPs to Win64 instead.

CREATE PROCEDURE myAep(i INTEGER, o CHAR(30) OUTPUT)  
  FUNCTION myProcedure  
  IN LIBRARY [.\Win32\myAEP];  

When running on 64Bit Servers you have to change the path to the DLL:

ALTER PROCEDURE myAep(i INTEGER, o CHAR(30) OUTPUT)  
  FUNCTION myProcedure  
  IN LIBRARY [.\Win64\myAEP];  

Changing the AEP at runtime is a very handy feature of ADS. But you still need to know all of your stored procedures, the parameters and the function names. And this is just for changing one property of the AEP, the DLL name.
An easier approach is to change just this property using the system procedure sp_ModifyProcedureProperty:

EXECUTE PROCEDURE sp_ModifyProcedureProperty('myAep','PROC_DLL_NAME','.\Win64\myAEP'); 

The DLL name is a string property which makes it easier to use parameters or variables. So you can easily automate the process and set the path according to Advantage’s architecture which can be retrieved by executing the sp_MgGetServerType system procedure:

EXECUTE PROCEDURE sp_mgGetServerType(); 

The result set of that system procedure contains a column of type INTEGER named ServerTypeValue. The possible return values of this column are described in the help file:

• NetWare 4 or earlier (1)
• Windows Server (2)
• Local Server (3)
• Windows 9x (4)
• NetWare 5 or greater (5)
• Linux (6)
• Windows 64-bit (7)
• Linux 64-bit (8)

For simplicity we now concentrate on Windows Servers only (ServerTypes 2 and 7).

DECLARE @Path STRING;  
DECLARE @ServerType INTEGER;  
@ServerType=(SELECT ServerTypeValue FROM (EXECUTE PROCEDURE sp_mgGetServerType()) a);  
IF @ServerType=2 THEN  
  @Path='.\Win32';  
ELSE IF @ServerType=7 THEN  
  @Path='.\Win64';  
ELSE   
  RAISE SCRIPT_EXCEPTION(@ServerType,'Unsupported ServerType found');  
ENDIF; ENDIF;  

EXECUTE PROCEDURE sp_ModifyProcedureProperty('myAep','PROC_DLL_NAME',TRIM(@Path)+'\myAEP');  

A collection of all stored procedures of the data dictionary can be retrieved by querying the system.ansi_storedprocedures view (we don’t need unicode in this case):

SELECT * FROM system.ansi_storedprocedures 

This view contains a column named Proc_Invoke_Option (type INTEGER) which can be used to get only stored procedures that are implemented as AEP (in a DLL). The return values are:

• ADS_STORED_PROC (1)
• ADS_COMSTORED_PROC (2)
• ADS_SCRIPT_PROC (4)

Let’s put it all together into one script:

DECLARE @Path STRING;  
DECLARE @Procs CURSOR AS   
  SELECT * FROM system.ansi_storedprocedures   
    WHERE (NOT Proc_DLL_Name LIKE @Path+'%')   
    AND (Proc_Invoke_Option=1);  
DECLARE @ServerType INTEGER;  
DECLARE @Name STRING;  
@ServerType=(SELECT ServerTypeValue FROM (EXECUTE PROCEDURE sp_mgGetServerType()) a);  
IF @ServerType=2 THEN  
  @Path='.\Win32';  
ELSE IF @ServerType=7 THEN  
  @Path='.\Win64';  
ELSE   
  RAISE SCRIPT_EXCEPTION(@ServerType,'Unsupported ServerType found');  
ENDIF; ENDIF;  

OPEN @Procs;  
WHILE FETCH @Procs DO  
  @Name=trim(@Procs.Proc_DLL_Name);  
  @Name=REPLACE(@Name,'.\Win64',@Path);  
  @Name=REPLACE(@Name,'.\Win32',@Path);  
  EXECUTE PROCEDURE sp_ModifyProcedureProperty(@Procs.Name,'PROC_DLL_NAME',@Name);  
ENDWHILE;  
CLOSE @Procs;