Tag Info

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

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

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

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

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

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] & @@@ ...

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

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

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