Using the CLR with AbaPerls

This page discusses how to work with CLR assemblies in AbaPerls.

Contents:
   Defining the Asssembly
   Files for CLR Objects
   Loading Assemblies
   Assemblies and DBBUILD
   Assemblies and Update Scripts

Defining the Asssembly

Assemblies have their own place in the AbaPerls subsystem structure, the directory ASSEMBLIES. In this directory, you put files of the types .DLL and .ASSEM. The .DLL file is the assembly itself, and you compile and link the DLL outside AbaPerls. (With Visual Studio, or using the command-line compiler). The .ASSEM file holds the CREATE ASSEMBLY statement for the assembly. Assume that your DLL is called MyAssembly.dll. Then MyAssembly.assem should look like this:

   CREATE ASSEMBLY MyAssembly AS
   $DLLINCLUDE
   WITH PERMISSION_SET = SAFE

The directive $DLLINCLUDE will cause Preppis to read the DLL file, and insert the contents as a hex-string, and send it to SQL Server. While AbaPerls will let you get away with specifying a filename or your own hexstring instead of $DLLINCLUDE, AbaPerls will always look for the DLL file, and you will get an error, if it is missing.

If MyAssembly.dll depends on another assembly, say HerAssembly.dll, then you should add this line to MyAssembly.dll:

   $REQUIRE MyAssembly.assem

And this line to HerAssembly.dll:

   $USEDBY MyAsssembly.assem

This ensures that DBBUILD and the scripts generated by DBUPDGEN load the assemblies in the correct order.

It is not unlikely that you will want to use namespaces for your assemblies. This could give you a DLL with a name like: OurCompany.OurBestAssembly.dll. You can use this name perfectly well with AbaPerls, that is, the .ASSEM file can be named OurCompany.OurBestAssembly.assem. You need to use brackets in the CREATE ASSEMBLY statement:

   CREATE ASSEMBLY [OurCompany.OurBestAssembly] AS
   $DLLINCLUDE

However, you should not use brackets in the $REQUIRE and $USEDBY directives, but simply say:

   $REQUIRE OurCompany.OurBestAssembly.assem

Files for CLR Objects

SQL 2005 permits you to create these types of objects with the CLR:

Stored procedures.
Scalar-valued functions.
Table-valued functions.
User-defined aggregates.
Triggers.
User-defined types.

Currently, with AbaPerls you can only create object of the first three types.

You put the definition of your CLR object in a file just like it was regular T-SQL object. So if MyAssembly.dll implements the stored procedure MyCLRsp and the table-valued function MyCLRTable, you put the definition of these in MyCLRsp.sp and MyCLRTable.sqlfun respectively. For instance, this is how MyCLRsp.sp could look like:

   CREATE PROCEDURE MyCLRsp @param1 int
   AS EXTERNAL NAME MyAssembly.StoredProcedures.the_actual_csharp_proc

The thing to note here is that AS EXTERNAL NAME must be on one single line. Else AbaPerls would insert a SET NOCOUNT ON after the AS (this is one of the standard changes performed by the AbaPerls file-loading process). If you would mistakenly split up AS and EXTERNAL NAME on two lines, AbaPerls will give you an explicit error message.

Before you can create MyCLRsp and MyCLRTable, you need to add these two lines to MyAssembly.assem:

   $USEDBY MyCLRsp.sp
   $USEDBY MyCLRtable.sqlfun

Preppis handles EXTERNAL NAME as a directive and looks up the .ASSEM file for the assembly, to check whether the $USEDBY for the current file is there, and will produce an error message if it is missing. The $USEDBY is needed, because if an assembly is included in an update script generated by DBUPDGEN, the CLR objects defined from it must also be included for reason that will prevail in the following sections.

Since the assembly name in the EXTERNAL NAME clause must map to an .ASSEM file, you cannot define a CLR object from an assembly in a different subsystem, nor from an assembly in a sub-directory to ASSEMBLIES.

Loading Assemblies

You load an assembly loading the .ASSEM file. You cannot give the DLL as input.

If the assembly is new, there is not much to say. Loading an .ASSEM file is just just like loading any other file.

For an existing assembly, there are some possible complications, though. As for other objects, the AbaPerls file-loading process, changes CREATE to ALTER if the assembly already present in the database. However there are a number of legit situations where ALTER ASSEMBLY results in an error:

The exactly same DLL already exists in the database. This includes the case when you perform ALTER ASSEMBLY without having recompiled the DLL since last time you loaded the assembly.
The definition of the assembly is not compatible with dependent assemblies.
You have changed the definition of a function that maps to a CLR object. For instance, say that you add a second parameter to the_actual_csharp_proc above. That renders your definition of MyCLRsp invalid.
The CLR version string is higher in the version of the DLL that is in the database, than in the one you are trying to load.

AbaPerls is aware of that these errors may occur, and acts accordingly. The first error is handled different from the rest: In this case, AbaPerls ignores the error, and considers the load successful. (If the error message indicates that your DLL is identical to the DLL for another assembly, then AbaPerls considers this to be an error.)

For all the other errors, AbaPerls falls back to DROP + CREATE. To inform you that this happens, AbaPerls emits an informational message:

   ALTER ASSEMBLY of 'assemblyname' failed with error errno. Will DROP/CREATE instead.

If this was unexpected and you want to know what the error means, you can look it up in sys.messages. Also, pay attention to the assembly name: the assembly that could not be altered, may be an assembly that you are dependent of and not the assembly you gave as input.

To be able to drop an assembly, AbaPerls, must first drop dependent objects, that is referencing assembles, and stored procedures and functions created from the assembly, directly or indirectly. For every object AbaPerls drops, you will get this warning:

   Object 'MyCLRsp' dropped because of dependency. Make sure that is is reloaded.

This message has number -1000. Normally, AbaPerls own messages has number 0, but this message has -1000, so that LISTERRS can act on it, see below.

When you load an assembly with ABASQL, you will need to do exactly what the messsage says. As for DBBUILD -rebuild and update scripts generated by DBUPDGEN, see the sections below.

Assemblies and DBBUILD

When you build an empty database, the ASSEMBLIES directory is loaded directly after the .SQL files in the MESSAGE directory, that is as the second group of files.

When you run with -rebuild, DBBUILD reloads all assemblies. This could lead to error -1000, Object dropped because of dependency. If you read the log with LISTERRS, you would only see this error, if the object was not successfully reloaded after it was dropped.

Assemblies and Update Scripts

DBUPDGEN puts assembly files in a section that comes after MESSAGE and before TYPE.

When DBUPDGEN builds an update scripts, it performs these specific actions for assemblies:

If a .DLL file has changed, the corresponding .ASSEM file is included in the update script. (The .DLL file is also included, but it is commented out, a .DLL file as such cannot be loaded.)
All files that are mentioned in $USEDBY in a .ASSEM file that is included in an update script are also included in the update script. This is applied recursively.

Say that there are two assemblies, root, child1 and child2, and that child1 and child2 depend on root (and this is properly stated through $REQUIRE). root defines no objects, but child1 defines the stored procedure sp1 and child2 defines the stored procedure sp2. Someone checks in a change of root.dll. Then all these files will be included in the update script: root.assem, child1.assem, child2.assem, sp1.sp and sp2.sp, even if all of these files themselves are unchanged. (And the reason they can be included is because of those $USEDBY directives.)

When you run the update script, root.assem will be loaded first, thanks to the $REQUIRE. Since all dependent files are included, it should not be any problem if root.dll has been changed in a way that ALTER ASSEMBLY fails. In this case, AbaPerls drops the other two assemblies and the stored procedures, and there will be four warnings in the error log. If you read the log with LISTERRS, you will not see these warnings, if the files were loaded successfully after they were dropped.

Note that the scheme is not waterproof. Assume that with the files above that the only file that has changed is child1.dll. Then only child1.dll and sp1.sp will be included in the update script. But since child1.assem depends on root.assem, AbaPerls will reload root.assem as well. Now, if for some reason ALTER ASSEMBLY of root.dll is not possible, for instance because there is a newer version of the DLL in the database, AbaPerls will drop all dependent objects. But since child2.assem and sp2.sp are not in the update script, they will not be reloaded, and when you read the log with LISTERRS, you will see the message Object dropped because of dependency.

To add insult to injury, there is a general restriction in AbaPerls, so that the version checks for Test and Production databases are not carried out for files loaded through $REQUIRE. (This is a bug that needs to be fixed.)