Trying to get my first CLR external function call working, compiled with VS.NET 2005 C++/CLI, @@version = 12.0.1.3406

Useful commands:

grant connect to zookeeper;
alter external environment clr location 'bin32\\dbextclr11';
alter procedure zookeeper.dummy() external name 'zookeeper.dll::dead.dummy( )' language clr;

A "call zookeeper.dummy()" succeeds instantly once the environment is loaded, with no obvious error messages. Neither the class nor the function exists within the dll file. The dll file has been dropped into the BIN32 folder, ProcessMonitor confirms that it is being loaded.

Debugging the dbextclr12.exe process (confirmed as 32bit on a 64bit server) shows no Zookeeper.dll module being loaded, I don't know whether this is expected or not.

The only error I get is from a method call that returns a resultset, the error is "Table 'ExtEnvResultSet23' not found" (the number increases by one on each attempt)

DumpBin does appear to show that this is a valid CLR executable:

Dump of file zookeeper.dll
PE signature found
File Type: DLL

clr Header:
          48 cb
        2.05 runtime version
       12838 [    7B34] RVA [size] of MetaData Directory
          10 flags
        EF20 entry point (1000EF20)
           0 [       0] RVA [size] of Resources Directory
           0 [       0] RVA [size] of StrongNameSignature Directory
           0 [       0] RVA [size] of CodeManagerTable Directory
       1A36C [      10] RVA [size] of VTableFixups Directory
           0 [       0] RVA [size] of ExportAddressTableJumps Directory
           0 [       0] RVA [size] of ManagedNativeHeader Directory

Section contains the following imports:
    KERNEL32.dll
    MSVCR80.dll
    WS2_32.dll
    msvcm80.dll
    MSVCP80.dll
    mscoree.dll

I've got plenty of debugging to do (i.e. write a different client), but this feels like I'm not even getting my foot in the door. I had the same symptoms with the 64bit version of dbextclr11 (which probably shouldn't have successfully loaded the DLL)

asked 09 Sep '11, 13:43

Erik%20Anderson's gravatar image

Erik Anderson
43681323
accept rate: 15%

edited 12 Sep '11, 12:54

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

... so what exactly is your question?

(09 Sep '11, 17:05) Mark Culp
Replies hidden

I guess the question is how I get ASA to actually call into the CLR code rather than just returning "Procedure completed" without even checking to see that the procedure even exists inside the DLL.

At the minimum I'd appreciate some kind of error message at least saying "nothing's working, can't load the DLL, can't find the function, function threw an exception, external environment crashed", etc. The utter silence means I have to stare at ProcessMonitor traces and see if I can divine what it's doing (or not doing)

(09 Sep '11, 17:07) Erik Anderson
1

I agree that the errors you are getting seem not helpful. Unfortunalety, the calling details are way beyond my knowledge.

Nevertheless, in my book it would be much more reasonable to try to call an existing function/method from a DLL and check whether that does its work - than to check what happens when a not existing function is called.

Wouldn't it be more helpful to write a small DLL with a function/method that does something simple but "noticeable" (showing a message box or writing to a file) and call that from SQL?

At least that's how I did my first steps with external functions, as far as I remember.

(10 Sep '11, 17:10) Volker Barth
1

Yah, already gone through that, ended up with the call to the nonexistent function. There's no difference between a function that exists and one that doesn't, no difference between a 64bit host (which shouldn't have successfully loaded the DLL) and a 32bit version. ProcessMonitor shows it opening the DLL on every request, VC.NET in CLR debugging mode shows no activity.

I'm thinking there's just something fundamentally wrong or unexpected with the DLL that I'm throwing at it in a way that ASA doesn't expect or know how to handle. I have not had time to write a different client to call the functions and make sure there's nothing Bad happening, so it's possible this thing may be throwing an exception on load or someting. If I run out of ideas I may just give up on this external environment stuff and go back to writing C++ extnapi plugins, I've at least gotten those to work. Running inside of the database process is a bit scary though, you can never make mistakes.

(12 Sep '11, 12:42) Erik Anderson

FWIW, starting with v11, you can also let your external C/C++ procs run in external environments - cf. this FAQ.

Basically, you might just have to change the procedure definition.

(12 Sep '11, 12:54) Volker Barth

Okay, first of my answers that might actually be an answer. If this seems... umm... normal then this might be the answer for this question.

(1) external environment clr is having problems loading anything other than compiled bare files (directly compiled using the document csc.exe calls).

Solution: create a single-file c# wrapper that calls the library function. Compile the c# wrapper using "csc.exe /reference:..libraryDebuglibrary.dll" so it can find the destination. Distribute both DLLs together.

(2) external environment clr is having problems returning error messages

Solution: every call the wrapper implements will have a "catch(Exception e)", translate as much of the exception as we can (into xml), then return to a supporting SP to decode and rethrow the error message (using OPENXML). As long as the wrapper doesn't throw any errors the response looks good.

permanent link

answered 15 Sep '11, 20:56

Erik%20Anderson's gravatar image

Erik Anderson
43681323
accept rate: 15%

Great analysis, maybe someone from Sybase can confirm or identify a bug?

(16 Sep '11, 03:10) Martin

If it really matters anymore, a looking-back addendum to this issue. When I completed testing and deployed our code to the production server I did start getting error messages (this time about DLLs not in the wrong place) and it started to become clear to me that whatever issues I was having here were specific to my development machine. It wouldn't be the first time I ran into "me only" errors, but it's still frustrating to run into them. I'm still using the workarounds I developed to get around this issue, mostly because I don't really have any means of developing or testing without them.

permanent link

answered 04 Jan '12, 15:44

Erik%20Anderson's gravatar image

Erik Anderson
43681323
accept rate: 15%

In your script, you are refering to

alter external environment clr location 'bin32\\dbextclr11';

Shouldn't be that dbextclr12 when using v12?

I'm no .NET expert at all but I guess you might call a v11 external environment, and that might not work with v12...

permanent link

answered 12 Sep '11, 13:01

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819
accept rate: 34%

Yah, I noticed that after I posted it, lol. I'm guessing it was set by the first version of ASA that supported external environments? I'm assuming this location setting is preserved through unloads.

I did verify that it was loading dbextclr12.exe, changing it to bin32\dbextclr12 had no effect on the issue. I'm guessing there's some special-case logic inside the engine for detecting "olde" locations.

Thank you for noticing though :-) I'm still half expecting this to be my fault somewhere, this feature has been likely working for many people for many years and two major versions.

(12 Sep '11, 13:07) Erik Anderson

Not sure what the protocol is here in giving answers that aren't answers to your own problem. But I did get around to writing a one-line C# client and doing test calls to the same functions I'm trying to call from ASA.

If I compile the C# application as "AnyCPU" then I get a BadImageFormatException (as expected, the DLL is explicitly 32bit). If I compile it as "x86" then everything seems to work as intended.

I did make extra sure that I'm running the 32bit version of dbextclr, both with the ALTER described above as well as ProcessExplorer making sure it's running the version in the BIN32 folder. Next step is prob to apply EBFs, maybe strip out all code so there's nothing but stubs left in my code, etc.

So no real progress yet, other than eliminating potential causes.

permanent link

answered 13 Sep '11, 18:02

Erik%20Anderson's gravatar image

Erik Anderson
43681323
accept rate: 15%

I can't claim whether the compilation as "x86" is considered a solution or a workaround...

Some more info on 32/64-bit may be found in this FAQ. And clearly Karim or others will have much more insight than me.

(14 Sep '11, 03:20) Volker Barth
1

So with a C# wrapper around your managed C++ DLL you say it works now?

(14 Sep '11, 05:04) Martin
1

The "Any CPU" setting determines the "launch bitness" of the CLR - dbextclr really just "launches" the CLR and passes everything back and forth from the database server.

On 64-bit machines, the .NET Common Language Runtime (CLR) has the option to run as a 32-bit environment or as a 64-bit environment. The way this is determined is through the use of the "/platform" Visual Studio project switch ( http://msdn.microsoft.com/en-us/library/zekwfyz4.aspx ) , which was added to Visual Studio as part of its 64-bit support. This option is usually configured through the project's "Build Target" menu in Visual Studio and is set for "Any CPU" by default. (See the article here for a longer explanation).


This means that if you're running the 32-bit database server, with a 32-bit dbextclr, on a 64-bit OS, you'll need to set the switch to "/platform:x86" on csc.

(14 Sep '11, 09:18) Jeff Albion
Replies hidden

That C# wrapper is a wonderful idea for isolating this issue here, especially if dbextclr has a nonstandard loader on it. I'll try to put together a sample proxy and see if i can trap whatever error is being thrown (if there is an error and it's just not the dll prejudiced against loading a C++ dll.

I am running a 64-bit database server with a 32-bit dbextclr with this (which is why I used the ALTER to try to force 32bits).

The AnyCPU test I ran was a limitation on what the client needed to be compiled with. I'm guessing that dbextclr running as 32bit means it was compiled with /platform:x86 on it, but things are getting nuanced enough that I'm starting to not know my left from my right.

(14 Sep '11, 12:19) Erik Anderson
2

From Karim's above cited answer:

Note that this problem is resolved in SQL Anywhere 12 since the dbextclr12.exe is now built specifically with /platform:x86 (for the bin32 one) and /platform:x64 (for the bin64 one).

(14 Sep '11, 13:06) Volker Barth
Replies hidden

Yah, think I saw that before. Prob means I should apply the latest EBF before going much further with this (may not have been changed in the first 12 release). Also means things can get even weirder, trying to second-guess what's going on inside of dbextclr. I'll know that I've officially gone crazy once I start randomly changing the case of my DLL filenames.

(14 Sep '11, 13:26) Erik Anderson

"I'll know that I've officially gone crazy..." - can't confirm that:)

FWIW, 12.0.1.3406 is a quite fresh EBF, and the mentioned "dll" case problem should be fixed there, cf. this.

(14 Sep '11, 13:32) Volker Barth
showing 4 of 7 show all flat view

More answers that aren't answers to my own question...

This is now an environmental issue.

I have compiled the following .cs file using the "don't compile it as part of a project" instructions in the help file and am getting the exact same symptoms.

So this is no longer a "I'm doing something weird" issue, it's more of a "it's broken for me (and works for everyone else) issue". Unless the 32bit dbextclr12.exe instance is still an issue...

using System;

namespace zootest
{
    class Program
    {
        static String echoTest()
        {
            return "hello";
        }

        static string exceptionTest()
        {
            throw new OperationCanceledException("uh-oh");
        }
/*
        static void zooTest()
        {
            AsaZooClient.Delete(""); // should throw a "function sequence error" exception
        }*/
    }
}

alter external environment clr location 'bin32\\dbextclr12';
grant connect to zookeeper;
create function zookeeper.test1() returns long varchar
    external name 'zootest.dll::Program.echoTest( ) string' language clr;
create procedure zookeeper.test2()
    external name 'zootest.dll::Program.exceptionTest( )' language clr;
//create procedure zookeeper.test3()
//  external name 'zootest.dll::Program.zooTest( )' language clr;
create procedure zookeeper.test4()
    external name 'zootest.dll::Program.nothingHere( )' language clr;

E:\modules\zootest>csc /target:library /out:zootest.dll Program.cs
Microsoft (R) Visual C# 2005 Compiler version 8.00.50727.4927
for Microsoft (R) Windows (R) 2005 Framework version 2.0.50727
Copyright (C) Microsoft Corporation 2001-2005. All rights reserved.

Time to check for EBFs I think...

permanent link

answered 14 Sep '11, 19:15

Erik%20Anderson's gravatar image

Erik Anderson
43681323
accept rate: 15%

1

From another example it seems to me, that you need to add the namespace to your function declaration, like:

external name 'zootest.dll::zootest.Program.echoTest( ) string' language clr;

(15 Sep '11, 03:00) Martin
Replies hidden

Good idea, makes sense, no effect

select zookeeper.test1() => NULL
call zookeeper.test2() => Procedure Completed
call zookeeper.test4() => Procedure Completed


EDIT:

Ooh, much better, after comparing to the samples. Still not there yet though. Dropped the namespace declaration (didn't know you could do that) and added a bunch of missing PUBLIC keywords. Then when compiling the single file I get the following:

select zookeeper.test1() => 'hello'
call zookeeper.test2() => Procedure Completed
call zookeeper.test4() => Procedure Completed

If I compile it as a module then nothing works (again), and the original library I was trying to use did not include any namespace declarations or missing PUBLIC keywords.

As this could technically be considered "working" (the instructions seem to stress use of csc.exe quite heavily, which does not permit the compilation of entire programs), I'm not sure how much farther I can push. I'm also not sure how much I can leverage this without trying to use proxy C# and reflection to try to forge a reference and force-load the real library I want to run.

All in all there's progress here, and a path to a solution, but not one that I like. If things go really bad I might discover that you aren't allowed to return resultsets that weren't originally created by the database engine...

(15 Sep '11, 13:02) Erik Anderson
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

Question tags:

×76
×24
×23
×19

question asked: 09 Sep '11, 13:43

question was seen: 5,639 times

last updated: 04 Jan '12, 15:44