Tag Info

12

After much digging, I found this MathGroup archive detailing how to get Mathematica working with SQL servers protected by Windows authentication. It's repeated here mainly for posterity's sake. Download the jTDS files from here: http://sourceforge.net/projects/jtds/files/ Unzip and locate the ntlmauth.dll file in the appropriate folder (x64, x86, IA64). ...

9

I hope the following helps, it worked for me (I used it once, but then converted dates and thus got rid of those zero dates didn't use it anymore, so I hope it's somewhat stable...). What I did is specify the property when setting up the connection, i.e. if using the wizard to set up the connection, I added ?zeroDateTimeBehavior=convertToNull to the ...

7

DatabaseLink uses Java Database Connectivity (JDBC) internally. The behaviour you describe is a known, long-standing, and annoying bug in JDBC. The problem is that Java inappropriately attempts to apply daylight savings time adjustments to dates in the database -- even though such adjustments are likely to have taken place already. This bug occurs even if ...

6

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", ...

6

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 ...

6

I would rule out option #1 as it would be like working inside a spreadsheet but using only cell A1. For inserting large amounts of data I recommend you to just use DatabaseLink's SQLExecute. As your dataset is large, and you want to insert this as fast as possible please take into account that there are very large differences in performance depending on ...

6

For me, the best way to learn how to do it was in Wolfram DatabaseLink User Guide. You can download the PDF for free. Here is some Mathematica code example for insert and select from Microsoft SQL Server. (*Function for Connection String*) openConn[]:= OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)", "myIpNumber"], "Username" -> ...

5

I don't know sql and the formats for set1 and set2 are somewhat unclear to me so I could be completely missing the point but maybe you could do something like this for the Inner Join query intersect[set1_, set2_] := Module[{expCodes}, expCodes = Intersection[set2[[All, 1]], set1[[All,1]]]; Reap[Sow[#2, #1] & @@@ set1; Sow[#2, #1] & @@@ ...

4

If you just need to run a block of Mathematica code then this questions seems to be equivalent to, How do I run an operating system command/script from SQL? Using the operating system command line invocation MathKernel -script myFile.m you can call your Mathematica script by whichever mechanism your version of SQL supports for accessing OS commands. Your ...

2

I found out how to solve this problem in SQL Sever. I used PsExec together with xp_Cmdshell to do the job. You have to install the PsExec in Windows in the system32 directory of the server machine. You can find the installer here. After that, I created this procedure, that can execute a file in a remote server: CREATE PROCEDURE [dbo].[MAT_EXEC_SCRIPT] ...

2

You need to use JVMArguments <<JLink; InstallJava[]; ReinstallJava[JVMArguments -> "-Xmx512m"] Shameless copy From Wolfram ;-) Or directly... InstallJava[JVMArguments -> "-Xmx512m"] The options available depend on your JVM runtime. The standard way to get the non-standard options is (in the shell) java -X

2

You will do yourself a service by doing as much as you can in SQL. In this case you can use SELECT A, B, COUNT(*) FROM Table1 GROUP BY A,B with SQLExecute. After that you have to use GatherBy to group them by the first element to get the kind of format you're looking for. But it's pretty easy. So for this kind of query I would recommend asking/searching ...

1

My problem was solved by Wolfram support. Apparently there was some corruption in one or more Mathematica files. We solved the problem by deleting the following folders (Windows 7, 64 bit): C:\ProgramData\Mathematica C:\Users\mkaplan\AppData\Roaming\Mathematica When I then ran Mathematica, it recreated these folders and the error went away.

1

I wouldn't wrap Dynamic around the setup of your database connection. However if you wish to update your SQL query to reflect changes in the database you could use Dynamic with a nested Refresh to specify an update time interval for your query. The SQL functions are not tickish and in general it would be undesirable for them to update automatically. I make ...

1

This is more a workaround than an answer. Schema information for all tables is also available in a table. In the case of vertica, a query like the following would get all schemas: columns={"table_schema","table_name","column_name","data_type","data_type_length","is_nullable","column_default"}; SQLExecute[conn,"SELECT "<>StringJoin[Riffle[columns, ", ...

1