July 15, 2011 at 8:08 am
My project involves financial calcuations and to gain performance I'm trying to move the calculations (along with much of the lookups required for parameters to the calculations) into the database by using SQLCLR. For many reasons, simple Transact-SQL won't do the job.
Unfortunately, one of the calculations is contained in a C .dll written by a third party and a .Net rewrite is not going to happen. Also, due to licensing costs, the C .dll is only installed for some of our clients. This has been dealt with in the past by using LoadLibrary to determine the existence of the C .dll.
I have been attempting to use LoadLibrary within my SQLCLR code without success. The section I have marked as "For debugging" throws an exception that lets me know that the C .dll has not been found, even though it exists on the database server in the correct location (I've even tried moving everything to the root of C:).
According to the sparse references I can find on this topic, I'm doing this right - but it's not working. I'm hoping that I've done something wrong that one of you more experienced with SQLCLR can point out.
// All variables, classes and file names have been made generic to protect me
public class Calc
{
[DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
internal static extern IntPtr LoadLibrary([In, MarshalAs(UnmanagedType.LPStr)] string lpFileName);
[DllImport("kernel32.dll", SetLastError = true)]
static extern bool FreeLibrary(IntPtr hModule);
[DllImport("kernel32.dll", CharSet=CharSet.Auto, SetLastError=true)]
internal static extern IntPtr GetProcAddress([In] IntPtr hModule,
[In, MarshalAs(UnmanagedType.LPStr)] string lpProcName);
private IntPtr _moduleAddress = IntPtr.Zero;
private IntPtr _calcAddress = IntPtr.Zero;
public bool LoadCalcModule()
{
if (_moduleAddress == IntPtr.Zero)
{
_moduleAddress = LoadLibrary("c:\\Program Files\\Our Company\\calc.dll");
// For debugging, throw an exception so that
// SSMS shows the results of LoadLibrary
if (_moduleAddress == IntPtr.Zero)
throw new Exception("Calc Module NOT Loaded");
else
throw new Exception("Calc Module Loaded");
_calcAddress = _moduleAddress != IntPtr.Zero
? GetProcAddress(_moduleAddress, "DoCalc") : IntPtr.Zero;
}
return (_calcAddress == IntPtr.Zero);
}
}
July 15, 2011 at 8:47 am
John,
I have to preface by saying I have not done what you are attempting, but I have written a fair amount of SQLCLR. I'm not real sure you are going to be able to do this in SQLCLR, while it IS CLR, there are a LOT of restrictions on what you can do and what assemblies you can reference easily. You say you are referencing a C DLL, what is the permission set for your assembly: SAFE, EXTERNAL_ACCESS, or UNSAFE? It will need to be at least EXTERNAL_ACCESS but very likely UNSAFE.
However, if you know the location and name of the library or another way to see if it is licensed by reading a file or the registry I would say that those are better methods. It feels like you want to accomplish this the way you have in the past and aren't looking at other methods. I am suggesting you look at other methods. Mileage may vary..
CEWII
July 15, 2011 at 8:55 am
It's not just a matter of checking licensing. If the file exists, I will need to actually call the calculation it contains as well.
If there's another way to dynamically load a C .dll that makes SQLCLR happier, I'd be glad to explore it.
July 15, 2011 at 8:57 am
The permission set has been set to UNSAFE. I don't think it will install otherwise.
July 15, 2011 at 9:11 am
John,
I don't have any great answers, just links that seem to fit..
http://stackoverflow.com/questions/1791471/sql-clr-stored-procedure-relative-path-for-dllimport
http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/f03d427f-caf8-47f4-9ca9-97d9ec747f7f/[/url]
Those were the two that seemed to fit best. I hope it helps..
CEWII
July 15, 2011 at 9:35 am
I've already been to both of those links, but neither one is quite what I need. The one using SQLCLR is using static loading of the dll, but, since I need to work on different systems where the dll may or may be installed I definitely need to use dynamic loading.
Thank anyway Elliott. Perhaps someone else will have an answer.
I always have my last chance approach - simply don't do those particular calcuations from inside the database. Instead, I'd have to use the old approach of querying all the parameters and taking them back to the workstation to do the calcs. It'll be a shame, since the speed of the database-imbedded calculations is an amazing improvement.
July 15, 2011 at 12:07 pm
We only have one CLR forum on this site so please confirm the version of SQL Server you're on and the version of .NET framework you're targetting.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 15, 2011 at 12:17 pm
That would be SQL Server 2008, targeting .Net Framework 3.0.
July 15, 2011 at 12:30 pm
EDIT: ...woops, the forum got confused about which thread I was posting too...
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 15, 2011 at 12:37 pm
opc.three: That must be the answer to someone else's question. I'm trying to dynamically load an old dll in SQLCLR (ie. use late binding by way of LoadLibrary).
July 15, 2011 at 12:38 pm
john.delahunt (7/15/2011)
opc.three: That must be the answer to someone else's question. I'm trying to dynamically load an old dll in SQLCLR (ie. use late binding by way of LoadLibrary).
Yeah, I was working on another thread...not sure how the forum put my last post on this thread :hehe:
Thanks for confirming your version. I'll take a look.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 15, 2011 at 1:30 pm
Arrgh, I don't have the dev tools here to get into it. I may have some time over the weekend.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 15, 2011 at 1:31 pm
Framework V 3 might be a problem..
CEWII
July 15, 2011 at 1:34 pm
Targeting framework v.2 gets the same results.
July 15, 2011 at 1:37 pm
Elliott Whitlow (7/15/2011)
Framework V 3 might be a problem..CEWII
Isn't that still using the 2.0 runtime?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply