Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

SAServerSideConnection.Connection doesn't detect it's called from a server side assembly

3

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:

alter external environment clr location 'dbextclr16_v4.0';

For framework 4.5, reference to iAnywhere.Data.SQLAnywhere.v4.5.dll and use this version of the external environment:

alter external environment clr location 'dbextclr16_v4.5';

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:

Could not execute statement.
Procedure 'CLRGetResultSet1' terminated with unhandled exception 
'Server  side connection cannot be used outside of a server side assembly.'
SQLCODE=-91, ODBC 3 State="HY000"

Here's what I do. Versions are SA 16.0.0.1824 and Visual Studio 2012 Premium Update 4.

  • Create a new C# project for a class library targeting framework 2
  • Delete Class1.cs
  • Add Existing Item and select the DataTypeTest.cs from the examples
  • In the project properties set the assembly name to clrtest
  • I didn't change the "Platform target" default value which is "Any CPU"
  • Now build the release configuration
  • For the sake of simplicity, I just copied clrtest.dll to C:\Program Files\SQL Anywhere 16\Bin64 (by executing CMD.EXE as administrator and then copying the dll)

Now execute the example statements from datatype.sql in ISQL. Works like a charm.

OK, now for the data stuff:

  • In VS, add a reference to C:\Program Files\SQL Anywhere 16\Assembly\V2\iAnywhere.Data.SQLAnywhere.dll
  • Add Existing Item and select ResultSet.cs
  • Build the solution again
  • List item
  • Now copy the the dll to the SA bin directory again

Before you copy the dll again, close the CLR in ISQL

STOP EXTERNAL ENVIRONMENT CLR;

When I try to run the test statements from resultset.sql, the following fails

call CLRGetResultSet1()

with the mentioned exception from SAServerSideConnection.Connection. Poor thing thinks its out although it isn't.

If i change the GetConnection() to

private static void GetConnection() {
    if( _conn == null ) {
        // _conn = SAServerSideConnection.Connection;
        _conn = new SAConnection("eng=demo16;uid=dba;pwd=sql");
        _conn.Open();
    }
}

it works. But that's just a bad workaround, for obvious reasons.

Any idea?

TIA,

Michael

asked 15 May '14, 13:16

Michael%20Fischer's gravatar image

Michael Fischer
670●11●15●27
accept rate: 25%

edited 17 Jun '15, 13:58

1

IMHO, here's the link to the according CR #767053, fixed with 12.0.1.4196 and 16.0.2050, starting with...

Applications that attempted to make server side calls in a CLR External Environment would only have worked with .NET 2.0 or 3.5. Applications could use the CLR External Environment with assemblies targeted at .NET 4.0 or 4.5, provided those assemblies did not make server side calls back to the SQL Anywhere server. This problem has now been fixed, and new CLR External Environment executables have now been included for use with .NET 4.0 or 4.5

Note, according to that, it's recommended to omit the actual major version name and replace that by '[VER_MAJOR]', such as

ALTER EXTERNAL ENVIRONMENT CLR LOCATION 'dbextclr[VER_MAJOR]_v4.0';

and the like to smoothen the upgrade to a future major version.

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

Excellent hint, Volker, thank you!

(18 Jun '15, 04:43) Michael Fischer
3 Answers:
active answersoldest answersnewest answerspopular answers
1

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)

permanent link

answered 15 May '14, 13:28

Mikel%20Rychliski's gravatar image

Mikel Rychliski
2.1k●16●41
accept rate: 32%

1

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:

Could not execute statement.
Procedure 'CLRGetResultSet1' terminated with unhandled exception 
Die Datei oder Assembly "System.Data, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089" oder eine Abhängigkeit davon wurde
nicht gefunden. Das System kann die angegebene D
SQLCODE=-91, ODBC 3 State="HY000"
Line 1, column 1

CALL CLRGetResultSet1()

It seems like it doesn't find the System.Data.dll although it is present:

gacutil /l System.Data

The Global Assembly Cache contains the following assemblies:
  system.data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=AMD64
  system.data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=x86
  system.data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=AMD64
  system.data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=x86

Number of items = 4

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

permanent link

answered 15 May '14, 14:22

Michael%20Fischer's gravatar image

Michael Fischer
670●11●15●27
accept rate: 25%

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 csc.exe. This works for me from a VS command prompt. Do the projects app.config or Resources.resx files contain .NET 2.0 specific references? You may have to modify these as described here.

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
0

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

Could not execute statement.
Procedure 'test' terminated with unhandled exception 'Die Datei oder
Assembly "iAnywhere.Data.SQLAnywhere.v4.0, Version=16.0.0.18244,
Culture=neutral, PublicKeyToken=f222fc4333e0d400" oder eine Abhängigkeit
davon wurde nicht gefunden. Das System kann die
SQLCODE=-91, ODBC 3 State="HY000"
File: "repro.sql" on line 4, column 1
call test()

Right, it's not in my path. But it's in the GAC where the SA installation routine puts it.

C:\CLRTestBuild>gacutil /l iAnywhere.Data.SQLAnywhere.v4.0
Microsoft (R) .NET Global Assembly Cache Utility.  Version 4.0.30319.17929
Copyright (c) Microsoft Corporation.  All rights reserved.

The Global Assembly Cache contains the following assemblies:
  iAnywhere.Data.SQLAnywhere.v4.0, Version=16.0.0.13244, Culture=neutral, PublicKeyToken=f222fc4333e0d400, processorArchitecture=MSIL
  iAnywhere.Data.SQLAnywhere.v4.0, Version=16.0.0.17614, Culture=neutral, PublicKeyToken=f222fc4333e0d400, processorArchitecture=MSIL
  iAnywhere.Data.SQLAnywhere.v4.0, Version=16.0.0.18244, Culture=neutral, PublicKeyToken=f222fc4333e0d400, processorArchitecture=MSIL

Number of items = 3

I always thought .NET hosts would pull missing assemblies from there. Whatever, I change the repro.bat and add the missing path:

set PATH=%PATH%;C:\CLRTest;C:\Program Files\SQL Anywhere 16\Assembly\V4

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 

"%SQLANY16%\Bin64\dbspawn" "%SQLANY16%\Bin64\dbsrv16" clrtest\CLRRepro.db
"%SQLANY16%\Bin64\dbisql.com" -onerror continue -c "Server=CLRRepro;UID=dba;PWD=sql" "call test();"

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:

Could not execute statement.
Procedure 'test' terminated with unhandled exception 'Der Objektverweis
wurde nicht auf eine Objektinstanz festgelegt.'
SQLCODE=-91, ODBC 3 State="HY000"

telling me in German that an object reference doesn't lead to an object instance. Here is some more information from the server log:

bei iAnywhere.MarshalToAppDomain.ExecuteMethod(String str, String className, TextWriter textWriter, String methodName, Object[]& args)
bei iAnywhere.MarshalToAppDomain.ExecuteMethod(String str, String className, TextWriter textWriter, String methodName, Object[]& args)
bei iAnywhere.SAClrClassLoader.Execute(String methodSig)
Der Objektverweis wurde nicht auf eine Objektinstanz festgelegt.

I'm still stuck.

Kind regards,

Michael

permanent link

answered 16 May '14, 07:24

Michael%20Fischer's gravatar image

Michael Fischer
670●11●15●27
accept rate: 25%

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
Your answer
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

learn more about Markdown

Question tags:

×19

question asked: 15 May '14, 13:16

question was seen: 3,767 times

last updated: 18 Jun '15, 04:43

Related questions

I need a clr function example

external environment clr call succeeds without effort

Start a thread from CLR when called from SQL Anywhere

CLR with depencies, does not work

Sybase 17 call External DLL, Could not find --'MyMethodTEst' in dynamic library..

Do I really need hardcoded assembly pathes for the CLR?

CLR external environment .Net 4.5 support

When to call STOP EXTERNAL ENVIRONMENT

query syntax problem

enable clr integration for stored proc and functions