Using the CLR with AbaPerls

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

   Defining the Assembly
   Files for Objects Defined from Assemblies
   Loading Assemblies
   Assemblies and DBBUILD
   Assemblies and Update Scripts
   Building Assemblies from Source Code
   Privileged Assemblies

Defining the Assembly

Assemblies have their own place in the AbaPerls SQL directory structure, the directory ASSEMBLIES. AbaPerls offers two possibilities to load an assembly to SQL Server. You can build a DLL outside AbaPerls, for instance in Visual Studio, and provide the DLL as input to AbaPerls. This is likely to be your choice for a larger assembly that consists of multiple source files which themselves would be under version control elsewhere, out of reach for AbaPerls. You can also serve AbaPerls a source file written in C# or Visual Basic .Net and AbaPerls will compile the file. (Other languages are currently not supported.) In this case, each source file results in it own assembly. This route is intended for simple CLR functions and stored procedures, maybe just some hundred lines of code.

No matter of which these roads you take, the main file for as assembly as far as AbaPerls is concerned is the .assem file which includes the CREATE ASSEMBLY statement extended with Preppis directives.

Assemblies with a DLL Built Outside AbaPerls

The $DLLINCLUDE directive is mandatory, and Preppis expands this directive to a hex-string of the DLL. Say that you have an assembly called MyAssembly.dll. You should create the file MyAssembly.assem with this content:


When Preppis reads the .assem file, it finds MyAssembly.dll, using the AbaPerls file-lookup order, reads it and pastes the contents into the CREATE ASSEMBLY statement as a hex-string:

CREATE ASSEMBLY MyAssembly FROM 0x4D5A90000300000004000000FFFF0000B800000000...

The pattern shown here applies to safe assemblies. For assemblies that need EXTERNAL_ACCESS or UNSAFE permission, there are a few more steps you need take, see the last section Privileged Assemblies.

Assemblies where AbaPerls Compile the Source Code

When you want AbaPerls to compile your source code, you use the $COMPILE directive to instruct Preppis to compile the source. For instance, say that you written MySimple.cs. You create MySimple.assem with this contents:


The $COMPILE directory instructs Preppis to invoke the C# compiler to compile the file to create MySimple.dll. This is followed by $DLLINCLUDE to insert the hex-string to the CREATE ASSEMBLY command.

Note that when you use $ÇOMPILE, that you must make sure that you have the correct version of the compiler in your path; AbaPerls will not look it up, as AbaPerls cannot know which compiler that is appropriate.

What You Check In

You check in files related to assemblies in the ASSEMBLIES directory in your version control system. You should always check in the .assem file. If you build the assembly outside AbaPerls, you should check in the DLL to the ASSEMBLIES directory. If you let AbaPerls compile your source code, you should check in your .cs or .vb file. You should not check in the DLL.

Cross-assembly References

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

$REQUIRE HerAssembly.assem

And this line to HerAssembly.assem:

$USEDBY MyAsssembly.assem

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

Dealing with Namespaces

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

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

$REQUIRE OurCompany.OurBestAssembly.assem

Files for Objects Defined from Assemblies

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

Currently, with AbaPerls you can only create objects of the first four types.

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

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 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. You also get this error message if you leave out AS entirely, which is permitted for CREATE FUNCTION. The one case you should not have AS before EXTERNAL NAME is CREATE AGGREGATESQL Server does not permit this.

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

$USEDBY MyCLRTableFun.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 produces 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 reasons that will prevail in the following section.

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 by loading the .assem file. You can also give a .cs or .vb file as input to AbaPerls, but AbaPerls changes the extension to .assem first thing, and all messages AbaPerls produces will relate to the .assem file. You cannot specify a .dll file as input to AbaPerls.

Loading a new .assem file is no different from loading any other type of file. AbaPerls will store a row in abasysobjects for the assembly, and one row for the file that is the direct input to AbaPerls. That is, if you provide a DLL only, AbaPerls will store a row for the DLL. If you provide a source file with the $COMPILE directive, AbaPerls stores a row about the source file, but it does not store any row for the DLL. If the database is a test or a production database, AbaPerls will perform version checks for both the .assem file and the other file on subsequent loads.

When you load an assembly that already is in the database, there are some possible complications. 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 situations where ALTER ASSEMBLY results in an error:

AbaPerls understands these errors, and acts accordingly. The first error is ignored by AbaPerls and considers the load successful unless the error message indicates that your DLL is identical to another assembly.

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; it 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 assemblies and SQL objects 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 it 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 message 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 files in the ASSEMBLIES directory are loaded after the .sql and .syno files in the MESSAGE directory, that is, as the third group of files. DBBUILD only looks for the .assem files, and ignores all other files types in ASSEMBLIES.

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 script, it performs these specific actions for assemblies:

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, root.dll (commented), child1.assem, child.dll (commented), child2.assem, child2.dll (commented), sp1.sp and sp2.sp. (This is possible thanks to those $USEDBY directives.)

When you run the update script, root.assem will be loaded first, thanks to the $REQUIRE directive. 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, because LISTERRS is able to match the error with the successful load of the other files.

Note that the scheme is not waterproof. Assume 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.

Building Assemblies from Source Code

When you work with a source file written in C# or VB .Net, you would typically edit it in TextPad (or whichever editor you use), and load it that way. Even if you provide the source file as input to AbaPerls, you must also have the .assem file available, since this file is the starting point for AbaPerls. (As mentioned above, when you feed AbaPerls a .cs or .vb file, AbaPerls will change the extension to .assem.) If you want to be able to double-click on the error message from the C# or VB compiler, you will need to set up a specific tool in TextPad with different rules for error messages.

The $COMPILE directive takes one mandatory parameter which specifies the language. Currently the only accepted values are C# and VB. Any subsequent arguments are assumed to be compiler switches and are passed to compiler command without alteration. Beware that AbaPerls always provides these switches: /target, /out and /nologo. AbaPerls may also provide /keyfile, see below about privileged assemblies. You should not put any of these switches as argument to $COMPILE.

Beware that AbaPerls will not go looking for the C# or VB compiler; you need to set up your PATH so that you have a compiler in your path. Tip: even if you do not have Visual Studio installed, there are compilers in C:\Windows\Microsoft.NET\Framework\v2.0.50727 .

When you have a source file written C# or VB, you should check in this file to version control in the ASSEMBLIES directory. You should not check in any DLL. In fact, normally Preppis deletes the DLL file once it has read it.

When you use ‑noexec ‑get to build a kit for installation at a customer site, Preppis will still compile the source file, but in this situation Preppis will not delete the DLL, but the DLL will be included in the installation kit. When you run the installation at the customer site, Preppis will bypass compilation, and load the DLL directly. That is, you will never have any compiler dependency when installing at a customer site.

Privileged Assemblies

If you have an assembly that requires EXTERNAL_ACCESS or UNSAFE permission, there are two ways to go. The simple way is to say WITH PERMISSION_SET = EXTERNAL_ACCESS/UNSAFE in the .assem file. This requires that the database is set to be trustworthy, and that you have the required server-level permissions to load a privileged assembly. Once this is fulfilled you are all set, and do you not need to read the rest of this section.

However, customers may not agree to set the database to trustworthy. In this case, SQL Server requires that the assembly is signed with a certificate or an asymmetric key, and there is a login associated with that key/certificate and this login has been granted the appropriate server-level permission, one of EXTERNAL ACCESS ASSEMBLY and UNSAFE ASSEMBLY. AbaPerls takes care of these steps, and if you don't have the required server-level permission yourself, you can load the assembly and the DBA can complete the installation later. To achieve this, there are a few extra steps you need to take, as detailed in the following sections.

Create a Key File

To create a privileged assembly you need to create a key. You create this file with the sn command (in the Visual Studio Command Prompt).

sn -k keypair.snk

The same key can be used for several assemblies, or you can use one key per assembly as you like. (The name of the key file is according to your own choice; it does not have to match the assembly.)

The Safe Dummy Assembly

Next step is to develop a safe dummy version of your assembly that can be loaded with PERMISSION_SET = SAFE. The safe dummy should have the same interface as the real assembly, but there does not have to be any real code in it. When you build this assembly, you need to make some extra precautions. Assume that the source file for the dummy is dummyassem.cs and the real code is in unsafeassem.cs. Then you need to build the dummy assembly in this way:

CSC /target:library /keyfile:keypair.snk /out:unsafeassem.dll dummyassem.cs
RENAME unsafeassem.dll unsafeassem.safedll

If you use Visual Studio to build the assembly, you may do it in a different way, but the important part is that the DLL for the dummy assembly is created with the same file name as the real assembly, and then you rename it to have the .safedll extension. (If you create it as .safedll directly, SQL Server will not accept the assembly.)

Note: in some cases, SQL Server does not detect until run-time that the assembly need a certain permission. In this case, you can just use a copy your regular DLL as the .safedll file.

You should check in the .safedll file to version control in the ASSEMBLIES directory. This applies no matter whether you supply the real assembly as a DLL, or through a source file in C# or VB. That is, AbaPerls does not supply a way to create a safe DLL directly from a source file.

AbaPerls stores information about the .safedll file in abasysobjects, and the file is subject to the same version checks as other files.

Building the Real Assembly

If you supply the assembly as a DLL, you build the real assembly as you would normally do, but you need to add the /keyfile option and you must use the same key as you used to sign the dummy assembly.

If your assembly is based on a source file in C# or VB .Net, you need to specify the name of the key file with the $KEYFILE directive in your .assem file. This directive causes AbaPerls to add the /keyfile switch to the command line for the compiler. You need to check in the key file into the ASSEMBLIES directory. (When you supply an assembly as a DLL, there is no need to check in the key file into ASSEMBLIES.)

If you check in a new version of the key file, DBUPDGEN will include the file in update scripts, but it will be commented out, as you cannot load an .snk file directly. Normally, there is little reason to change the key once you have created it. Beware that if you change the key, this will cause ALTER ASSEMBLY to fail, and AbaPerls must take the DROP/CREATE route. AbaPerls does not load information about the key file in abasysobjects, and the file is not subject to version checks.

The .assem File

In the .assem file, you specify the desired permission as an argument to the $DLLINCLUDE directive:


 Or for an assembly based on a source file:

$KEYFILE mykey.snk

Loading the Assembly

You load a privileged assembly like you load a regular assembly. These are the steps that AbaPerls performs when you load a privileged assembly .

  1. Creates the assembly from the .safedll file.
  2. Saves the contents of the .dll and .safedll files to one of AbaPerls system tables.
  3. If you do not have CONTROL SERVER permission, AbaPerls prints an informational message, and quits processing.
  4. If you have CONTROL SERVER, AbaPerls continues processing, by creating the safe assembly in the master database.
  5. In master, AbaPerls creates an asymmetric key by extracting the key from the assembly.
  6. AbaPerls drops the assembly in master.
  7. AbaPerls creates a login from the key. (This is not a real login that can connect to SQL Server.)
  8. AbaPerls grants the login the required permission.
  9. AbaPerls supersedes the safe dummy assembly with the real assembly, giving it the required permission set. (If the dummy is a copy of the real version, AbaPerls only alters the permission set.)

If you do not have CONTROL SERVER, you can still define objects from the assembly so that an update script or DBBUILD can complete successfully. In this case, the DBA (or someone that has CONTROL SERVER permission) needs to run a stored procedure that performs steps 4 to 9. This is detailed in the section Information to the DBA in the topic for Modules with Special Permissions.

If you load a second assembly which is signed with the same key, AbaPerls will skip steps 4 to 8. The same is true if you load the assembly in a second database on the same server. That is, the same key and login is shared by all assemblies signed with the same key. An implication of this is that if you change the assembly to be a safe assembly, AbaPerls will not delete any key or login from master. The only way to delete obsolete keys and logins is to run the procedure ap_spp_reload_server_sp, see the section Information to the DBA in the page Modules with Special Permissions.

If you do not have CONTROL SERVER and reload the assembly, AbaPerls will not be able perform ALTER ASSEMBLY since SQL Server does not permit you to alter an unsafe assembly without UNSAFE ASSEMBLY permission, not even with a safe assembly. Instead AbaPerls will use the same approach as when you have changed the interface of the assembly and drop the assembly and all dependent objects, and then create the assembly as a new object.