Calling Mathematica from SQL via Stored Procedure
- To: mathgroup at smc.vnet.net
- Subject: [mg123643] Calling Mathematica from SQL via Stored Procedure
- From: ndrs <andreas.kohlmajer at gmx.de>
- Date: Wed, 14 Dec 2011 06:00:47 -0500 (EST)
- Delivered-to: l-mathgroup@mail-archive0.wolfram.com
After struggling for a long time, I would like to give some
information back to the community. I'm used to Mathematica and SQL
programming, but I was not familiar with .NET. I had difficulties to
create "something" to call the MathKernel from SQL via a stored
production and trigger this process from within SQL.
This is how I got it working.
Step 1: compiling the C# script to a DLL
Create a folder C:\Temp\Mathematica and copy Wolfram.NETLink.dll to
it.
Script source code of MathLinkCLR.cs
/* code of MathLinkCLR.cs */
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
using Wolfram.NETLink;
public class MathLinkCLR
{
[SqlProcedure]
public static int Call(SqlString MLScript, out SqlString MLReturn)
{
string MLScriptString = (string)MLScript;
int iresult = -1;
string MLReturnTemp = null;
MLReturn = null;
if (MLScriptString != null)
{
// Initialize Mathematica link
IKernelLink ml = MathLinkFactory.CreateKernelLink();
// Discard the initial InputNamePacket the kernel will
send when launched.
ml.WaitAndDiscardAnswer();
// Evaluate Mathematica Script
MLReturnTemp = ml.EvaluateToOutputForm(MLScriptString, 0);
if (MLReturnTemp != null && MLReturnTemp.Trim().Length >
0)
{
// return 0 (=ok), if Mathematica output does not
contain "$Aborted" or "$Failed"
if (!MLReturnTemp.Contains("$Aborted") && !
MLReturnTemp.Contains("$Failed"))
{
MLReturn = (SqlString)MLReturnTemp;
iresult = 0;
}
}
// Always close link when done:
ml.Close();
}
return iresult;
}
}
Open a cmd-box:
cd "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727"
csc.exe /target:library /out:c:\temp\mathematica\MathLinkCLR.dll /
reference:c:\temp\mathematica\Wolfram.NETLink.dll c:\temp\mathematica
\MathLinkCLR.cs
Step 2: prepare SQL database and connections; enable CLR and create
assemblies
a) EXEC sp_configure 'clr enabled', 1 ; RECONFIGURE ;
b) ALTER DATABASE [TestDB] SET TRUSTWORTHY ON ;
c) CREATE ASSEMBLY SystemDrawing FROM 'C:\WINDOWS\Microsoft.NET
\Framework\v2.0.50727\System.Drawing.dll' WITH PERMISSION_SET =
UNSAFE ;
d) CREATE ASSEMBLY SystemWindowsForms FROM 'C:\WINDOWS\Microsoft.NET
\Framework\v2.0.50727\System.Windows.Forms.dll' WITH PERMISSION_SET =
UNSAFE ;
e) CREATE ASSEMBLY CallMathematica FROM 'c:\temp\mathematica
\MathLinkCLR.dll' WITH PERMISSION_SET = UNSAFE ;
f) CREATE PROCEDURE CallMathematica @MLScript NVARCHAR(MAX), @MLReturn
NVARCHAR(MAX) OUTPUT AS EXTERNAL NAME
CallMathematica.MathLinkCLR.Call ;
Item c) and d) are necessary, as Wolfram.NETLink.dll needs some
windows forms; they are generating some warnings (not fully tested),
but it still works, as I=92m not using any windows forms from my SQL
environment by calling the MathKernel. Warnings are:
Warning: The Microsoft .NET Framework assembly 'system.drawing,
version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a,
processorarchitecture=msil.' you are registering is not fully tested
in the SQL Server hosted environment and is not supported. In the
future, if you upgrade or service this assembly or the .NET Framework,
your CLR integration routine may stop working. Please refer SQL Server
Books Online for more details.
Warning: The Microsoft .NET Framework assembly 'system.windows.forms,
version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089,
processorarchitecture=msil.' you are registering is not fully tested
in the SQL Server hosted environment and is not supported. In the
future, if you upgrade or service this assembly or the .NET Framework,
your CLR integration routine may stop working. Please refer SQL Server
Books Online for more details.
Warning: The Microsoft .NET Framework assembly 'accessibility,
version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a,
processorarchitecture=msil.' you are registering is not fully tested
in the SQL Server hosted environment and is not supported. In the
future, if you upgrade or service this assembly or the .NET Framework,
your CLR integration routine may stop working. Please refer SQL Server
Books Online for more details.
Warning: The Microsoft .NET Framework assembly
'system.runtime.serialization.formatters.soap, version=2.0.0.0,
culture=neutral, publickeytoken=b03f5f7f11d50a3a,
processorarchitecture=msil.' you are registering is not fully tested
in the SQL Server hosted environment and is not supported. In the
future, if you upgrade or service this assembly or the .NET Framework,
your CLR integration routine may stop working. Please refer SQL Server
Books Online for more details.
Step 3: test call
DECLARE @in nvarchar(max), @out nvarchar(max), @rv int;
SET @in = 'N[Pi,2^16]';
EXEC @rv = [dbo].[CallMathematica] @in, @out OUTPUT;
SELECT @rv as [ReturnValue], @in as [Input], @out as [Output],
LEN(@out) as [OutputLength];
This shows 2^16 digits of pi. If you have multiple returns from your
Mathematica script, @out currently contains only the last recordset. I
think this is related to CR/LF characters created from
EvaluateToOutputForm.
Maybe, someone can comment on this how to fix this. Any further hints,
tips, recommendations or optimizations are greatly appreciated.
NVARCHAR(MAX) can store a huge script. I have my specific scripts
stored in SQL and I=92m now able to execute them as needed. The
communication from Mathematica to SQL can be easily handled with
=93DatabaseLink`=94 from within the Mathematica script.
I hope this will help anybody, who is struggling, too. Cheers!