Update: The issue has been solved! For SQLA 16, reference to the .NET framework 3.5 version of iAnywhere.Data.SQLAnywhere when you use the default CLR environment dbextclr16.exe. When you want to use framework 4.0, reference to iAnywhere.Data.SQLAnywhere.v4.0.dll and execute the following statement before you use the external method the first time:
For framework 4.5, reference to iAnywhere.Data.SQLAnywhere.v4.5.dll and use this version of the external environment:
Original post: I'd like to use the CLR external environment to retrieve data from various sources using the .NET framework and then represent it to the database as a result set. The examples provided with SA show how to access the database from .NET code by using an internal connection accessable through SAServerSideConnection.Connection. And this is exactly where the samples throw an error:
Here's what I do. Versions are SA 16.0.0.1824 and Visual Studio 2012 Premium Update 4.
Now execute the example statements from datatype.sql in ISQL. Works like a charm. OK, now for the data stuff:
Before you copy the dll again, close the CLR in ISQL
When I try to run the test statements from resultset.sql, the following fails
with the mentioned exception from SAServerSideConnection.Connection. Poor thing thinks its out although it isn't. If i change the GetConnection() to
it works. But that's just a bad workaround, for obvious reasons. Any idea? TIA, Michael |
Hi Michael, This is a documentation bug, .NET 2.0 is not supported by the CLR external environment. .NET 3.5 or later is required to use SAServerSideConnection. This page documents the support correctly. Other references will be updated accordingly. Thanks, Mikel (CR#744053) |
Hi Mike, thanks for the ultrafast reply. Yep, the CHM-version of the SA 16 docs still says "Only .NET version 2.0 is supported." under SQL Anywhere Server - Programming » SQL Anywhere external environment support » The CLR external environment Unfortunately, framework4 doesn't work for me, either. Same setup as in my initial question, this time targeting framework 4 and referencing "C:\Program Files\SQL Anywhere 16\Assembly\V4\iAnywhere.Data.SQLAnywhere.v4.0.dll" This time, it doesn't even start the dll:
It seems like it doesn't find the System.Data.dll although it is present:
German Windows 8.1 with VS 2012 Premium. Framework 4 is there, no doubt. Same happens when I try Framework 3.5, btw. You don't have a working version as a VS Project, do you? Michael Oops, I meant same happens with Framework 4.5
(15 May '14, 14:23)
Michael Fischer
I was able to get it to work using Disclaimer, the link includes a batch file that will modify your system. It has not been tested and is not official code, read every line
(15 May '14, 15:07)
Mikel Rychliski
|
Mikel, drives me crazy. Nope, doesn't work. Here is what I do: I copy your three files to a directory c:\clrtestbuild and run repro.bat. The dbisql call to repro.sql tells me, it doesn't find iAnywhere.Data.SQLAnywhere.v4.0
Right, it's not in my path. But it's in the GAC where the SA installation routine puts it.
I always thought .NET hosts would pull missing assemblies from there. Whatever, I change the repro.bat and add the missing path:
To my surpise: no change! It is still missing the iAnywhere data dll. What the heck, I copy the iAnywhere DLL to the destination directory c:\clrtest. Arrrgh, it's still missing. Maybe if I copy the iAnywhere dll to the c:\clrtestbuild directory? Nope. For some reason, I get an idea: I copy your three files to the c:\clrtest directory and run repro.bat from there. I even remove the command that extends the path to c:\clrtest from repro.bat. And ahhh, we are one step further: it doesn't complain about the missing dll. Unfortunately, now I get another error (see below). Now I start the DB from a different location. In the command prompt, I move to the root directory and
Bam, iAnywhere dll missing. Here is my theory: "When the external dll loaded into the SA CLR host is not located in the path from where the DB server was STARTED, links to other DLLs are not properly resolved." Haven't tried it with a SA db running as a service yet. I can reproduce the problem on a clean Win7 virtual machine with just SA 16 and framework 4 installed. Back to the subsequent error. When the db and the dll are in the same directory and I start the db from there, I get the following error from your sample:
telling me in German that an object reference doesn't lead to an object instance. Here is some more information from the server log:
I'm still stuck. Kind regards, Michael Hi Michael, Looks like there may be a bug here. I fixed a configuration problem and now server side connections aren't functioning in .NET 4.0/4.5 for me either. We'll look into this. In the meantime, would you be able to use 3.5?
(16 May '14, 14:41)
Mikel Rychliski
Replies hidden
Mikel, Don't know. The reason for us to use the CLR host was a project where we have to read from a JSON service and put the data into a SA database. I want to do this from within the database without having to write a Service. The library we want to use is JSON.NET and there is still an older, downloadable version of this lib that supports 3.5. I have to check wether this version has everything we need. I'll get back to you. In the meantime, when you fix the issue with the server side connection, would you please test the configuration, too? I had so much trouble with assemblies not found, as you can see in this thread. What I would love to do is just throw my DLL and the referenced iAnywhere DLLs into the same Directory as the database. The .NET runtime could pull the rest (the Framework dlls) from the GAC or another path. Regards, Michael
(19 May '14, 04:22)
Michael Fischer
|
IMHO, here's the link to the according CR #767053, fixed with 12.0.1.4196 and 16.0.2050, starting with...
Note, according to that, it's recommended to omit the actual major version name and replace that by '[VER_MAJOR]', such as
and the like to smoothen the upgrade to a future major version.
Excellent hint, Volker, thank you!