mathematica | Mathematica in SQL Server

from a forum or a web site...
sorry for no original URL...

================================copy from some web resource==========
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! 

Comments