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

asked 17 Jun '15, 06:47

Michael%20Fischer's gravatar image

Michael Fischer
645111527
accept rate: 12%

edited 18 Jun '15, 04:47

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...)

(17 Jun '15, 09:37) Volker Barth
Replies hidden

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.

(17 Jun '15, 13:40) Michael Fischer

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

(19 Jun '15, 06:13) Martin
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×92
×19

question asked: 17 Jun '15, 06:47

question was seen: 2,870 times

last updated: 19 Jun '15, 06:13