I have used Microsoft Open Database Connectivity (ODBC) with Mathematica 8.0 and MSSQL 2008 on Windows XP, but I can't get it to work on Windows 7 Enterprise (64-bit) with Mathematica 9.0.1.0 and MS SQL server 2012 (64-bit). Has anyone had success in doing this?
$\begingroup$
$\endgroup$
0
Add a comment
|
3 Answers
$\begingroup$
$\endgroup$
I am using Windows 7 Professional, SQL 2008 and Mathematica v9.
I had endless problems connecting and getting things to work, but after a lot of back and forward between Wolfram support and myself I got the following to work:
Needs["DatabaseLink`"]
Connection1=OpenSQLConnection[JDBC["net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://MYSERVERNAMEHERE:MYSERVERPORTHERE/MYDATABASENAMEHERE;instance=MYSQLINSTANCENAMEHERE"], "Username" -> "MYUSERNAMEHERE", "Password" -> "MYPASSWORDHERE"]
For example
Connection1=OpenSQLConnection[JDBC["net.sourceforge.jtds.jdbc.Driver", "jdbc:jtds:sqlserver://Server01:1433/local_customer_data;instance=SQL2008"], "Username" -> "Admin", "Password" -> "123"]
When selecting data from tables I had to specify the table's schema instead of just the table name as it suggests in the built in help.
SQLSelect[CONNECTIONNAMEHERE, "SCHEMANAMEHERE.TABLENAMEHERE"]
for example
SQLSelect[Connection1, "dbo.equipment"]
I made sure NOT to use Windows Authentication credentials. Some other pointers they gave me were:
Enable TCP/IP protocol for both instances: - Open "Sql Server Configuration Manager" in "Start Menu\Programs\Microsoft SQL Server 2012\Configuration Tools\" - "SQL Server Network Configuration" -> "Protocols for SQLEXPRESS" -> Enable TCP/IP
Make sure the "SQL Server Browser" Windows service is activated and running: - Control Panel -> Administrative Tools -> Services - Enable "SQL Server Browser" service (manual or automatic) - Start it - Restart both instances to be safe
This method works well becasue if there is more than one instance of SQL installed on the same server, you can specify which one to connect to. I hope this helps.
$\begingroup$
$\endgroup$
I had nearly the same problem approximately 2 years ago. If you mean the data communication with MS SQL from Mathematica, you just need to use the DatabaseLink package. I'm using MS SQL 2008, Windows 7 64-bit Enterprise and Mathematica 9. Sometimes, you need to add a bufferdir statement in your OpenSQLConnection string. You can find some more information by googling "JDBC bufferdir", etc. You also need to add an instance if you're using SQLEXPRESS.
Needs["DatabaseLink`"];
callsql := Block[{conn,sql,data},
conn = OpenSQLConnection[
JDBC[
"MicrosoftSQLServer(jTDS)",
"server;databasename=dbname",
"Username" -> "uid",
"Password" -> "pwd"
];
sql = "SELECT * FROM table";
data = SQLExecute[conn, sql];
CloseSQLConnection[conn];
data
];
If you're talking about the communication from SQL to Mathematica, I managed it by creating an SQL Aggregate function, which is called by a stored procedure. It uses the Wolfram.NETLink.dll. I use Mathematica as the "calculation engine" for my SQL server. I did it the following way (Content of file C:\temp\mathematica\MathLinkCLR.cs):
/*
1) Compile DLL
a) cd "C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727"
b) csc.exe /target:library /out:c:\temp\mathematica\MathLinkCLR.dll /reference:c:\temp\mathematica\Wolfram.NETLink.dll c:\temp\mathematica\MathLinkCLR.cs
2) TSQL (MS SQL Server Management Studio)
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 ;
c) and d) issue warnings, but it still works
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.
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];
*/
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;
}
}
Sorry for my bad coding. I'm still new to this forum and need some more expertise in nice coding. I hope this is useful. You can add some more SQL procedures to store your Mathematica scripts, generate them, auto-execute them, etc.
$\begingroup$
$\endgroup$
Another variation to try (similar to akm's) :-
Needs["DatabaseLink`"]
connection =
OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)", "123.456.789.10:1010"],
Username -> "name", Password -> "apple", Catalog -> "DatabaseName"];
SQLTables[connection]
answered Dec 12, 2013 at 16:00