Do I really need hardcoded assembly pathes for the CLR?

[Michael_Fischer]

Hello,

It seems like dbextclr16 can only locate my CLR extension dlls when I hardcode their path in the CREATE PROCEDURE statement. Using dbextclr16_v4.0 or dbextclr16_v4.5 for frameworks 4 and 4.5 doesn't make a difference. Which is not a good idea, IMHO.

The following code was tested with 16.0.0.2127.

There is no reproducable example of a CLR call shipping with SQL Anywhere, so here is my code:

SaClrTest.cs

using System;
using System.Data;

public class TestClass
{
    public static void GetTest(IDataReader[] readers)
    {
        DataTable table = new DataTable();

        table.Columns.Add("ID", typeof(Int32));
        table.Columns.Add("Name", typeof(String));

        DataRow row = table.NewRow();
        row["ID"] = 1;
        row["Name"] = "T-Shirt";
        table.Rows.Add(row);

        row = table.NewRow();
        row["ID"] = 2;
        row["Name"] = "Jeans";
        table.Rows.Add(row);

        IDataReader reader = new DataTableReader(table);

        readers[0] = reader;
    }
}

SaClrTest.sql

alter external environment clr location 'dbextclr[VER_MAJOR]_v4.0';

CREATE OR REPLACE PROCEDURE GetTestData() RESULT(Id integer, Name varchar(20))
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'C:\\CLRTest\\Test2\\SaClrTest.dll::TestClass.GetTest(IDataReader[])'
    LANGUAGE CLR;

/*
CREATE OR REPLACE PROCEDURE GetTestData() RESULT(Id integer, Name varchar(20))
    DYNAMIC RESULT SETS 1
    EXTERNAL NAME 'SaClrTest.dll::TestClass.GetTest(IDataReader[])'
    LANGUAGE CLR;
    */

select * from GetTestData();

And finally SaClrTest.bat

@echo off

set path=%path%;C:\\CLRTest\\Test2\\

rem Determine SA version
set SAbin=bin32
if exist "%sqlany16%\\bin64\\dbisql.com" set SAbin=bin64

echo Stop and delete previous database
"%SQLANY16%\\%SAbin%\\dbstop" -c "eng=SaClrTest;uid=dba;pwd=sql" -y
"%SQLANY16%\\%SAbin%\\dberase" -y SaClrTest.db

echo Rebuild database and start it
"%SQLANY16%\\%SAbin%\\dbinit" SaClrTest.db
"%SQLANY16%\\%SAbin%\\dbspawn" -f dbeng16 SaClrTest -x sharedmemory,tcpip

echo Compile extension assembly
"C:\\Windows\\Microsoft.NET\\Framework64\\v4.0.30319\\csc.exe" /target:library /platform:anycpu /reference:"C:\\Program Files\\SQL Anywhere 16\\Assembly\\V4\\iAnywhere.Data.SQLAnywhere.v4.0.dll" /out:"SaClrTest.dll" SaClrTest.cs

echo Run test
where SaClrTest.dll
"%SQLANY16%\\%SABin%\\dbisql.com" -onerror continue -c "Server=SaClrTest;UID=dba;PWD=sql" SaClrTest.sql

"%SQLANY16%\\%SABin%\\dbstop" -y -c "Server=SaClrTest;UID=dba;PWD=sql"

pause

Running the batch produces the following output:

...
Run test
C:\\CLRTest\\Test2\\SaClrTest.dll
         Id Name
--------------------------------
          1 T-Shirt
          2 Jeans

You must store these three files in C:\\CLRTest\\Test2 on you local computer. Otherwise, it won't run. Please not that you might need to adjust the path to the C# compiler CSC.EXE in the batch file. If you have VS installed, you could also remove the path and call the batch from the VS command prompt (while being in C:\\CLRTest\\Test2).

Now use the version of the CREATE PROCEDURE without the hardcoded path to the DLL instead. Doesn't run - although the WHERE in the batch clearly proofs that the assembly can be found through the path.

What the example shows:

1. The enormous potential of CLR calls. We can gather data from ANYWHERE using the .NET framework and return it to SQLA as a result set.
2. The lousy documentation of the CLR environment. It took me almost 2 days to get here. SAP should provide more and reproducable examples. Other developers are having a hard time to get this to run, too. See this question or this thread. There is not a single line of information in the help file mentioning that dbextclr16.exe is hardwired to version 3.5 of the .NET framework or how you can use ALTER EXTERNAL ENVIRONEMTN to use dbextclr16_v4.0.exe and dbextclr16_v4.5 instead.
3. Finding dlls. I don't like the hardcoded pathes at all. At least, I didn't find any other way to make it work. The CLR loaders should use the path or even better: have an option to load assemblies from the folder where the .db database file is!

Kind regards,

Michael

[VolkerBarth]

Does it work when you do not supply a path in the CREATE PROC clause and put the CLR DLL in the according SA bin32/bin64 sub directory? (Note: I don't claim that will suffice your requirements, it might just be a way to prevent hardcoding the path...)

[Michael_Fischer]

Thank's for the idea, Volker, but in the days of UAC, VirtualStore and other policies, I can't easily copy a dll to the program directory of another software or change it whenever there's an update.

[MCMartin]

Have you tried storing the .net assembly in the global assembly cache?