Tag Info

Hot answers tagged

17

Here's a set of functions that allows to do this. The code uses many ideas found on this site and on other places on the web. It is a bit factorized already so it should be easily reusable. More on YQL and available tables here: https://developer.yahoo.com/yql https://github.com/yql Query test Edit: this API is great also and simple ...


13

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


12

Currently in Mathematica v10 you need to be careful joining datasets as the Key[] function doesn't work with a string key for a dataset (it does for an association) . See Taliesin's comment below. Personally I think the help is a bit misleading referring to SQL joins with associations when they are more like "joins" in a NOSQL database. Examples of ...


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

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


7

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


7

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


7

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


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


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


4

Apologies if I misunderstand as I am not familiar with SQL but this seems to match your sole example: i1 = {<|a -> 1, b -> X|>, <|a -> 1, c -> Y|>}; i2 = {<|a -> 2, b -> XX|>, <|a -> 2, c -> YY|>}; Join @@@ {i1, i2} {<|a -> 1, b -> X, c -> Y|>, <|a -> 2, b -> XX, c -> YY|>}


3

Here is one options: If[SQLExecute[con, "Select * from TABLE","MaxRows" -> 1] == {},True, False] If it's just a test, Using MaxRows you can prevent an unnecessary data load.


3

I don't know how to make JoinAcross yield the result you ask for, but you can get it from Merge. Merge[#, Union /* First] & /@ {{<|a -> 1, b -> X|>, <|a -> 1, c -> Y|>}, {<|a -> 2, b -> XX|>, <|a -> 2, c -> YY|>}} {<|a -> 1, b -> X, c -> Y|>, <|a -> 2, b -> XX, c -> ...


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

The Join[] is across two datasets and only two. To make it easier to visualize I'll make the sets longer. With 2x2 it's easy to confuse up-down with left-right. asc1 = {<|a -> 1, b -> X|>, <|a -> 2, b -> Y|>, <|a -> 4, b -> bb|>, <| a -> 8, b -> 3|>}; asc2 = {<|a -> 1, c -> X|>, <|a ...


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

I would suggest to use SQL directly. For Oracle databases the following module should work. Module[{dbConn, sqlAct, records}, "SELECT TABLE_NAME,COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME like'%your table name%'"// Set[sqlAct, #] &; OpenSQLConnection["your connection"] // Set[dbConn, #] &; SQLExecute[dbConn, sqlAct] // Set[records, #] &; ...


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


2

The following works as expected: Needs["DatabaseLink`"] conn = OpenSQLConnection["publisher"] (* SQLConnection["publisher", 10, "Open", "TransactionIsolationLevel" -> "ReadCommitted"] *) First, an example from the docs: SQLExecute[conn, "SELECT * FROM ROYSCHED WHERE ROYALTY >= .11 AND ROYALTY <= .12"] (* ...


1

After looking at all the comments I was able to come up the following code that allows me to connect and query the database: Needs["DatabaseLink`"]; conn1 = OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)", "ARC-WEB"], "UserName" -> "Mathematica", "Instance" -> "SKWSPATIAL", "database" -> "yyy", "Password" -> "xxx"] Thanks for all ...


1

Try this data = SQLSelect[conn, "All", {"col1", "col2"}, SQLMemberQ[Range@8, SQLColumn["col1"]] && SQLMemberQ[Range[21, 30], SQLColumn["col2"]]];


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

You can try changing the driver and, instead of the MSFT driver "Microsoft SQL Server(jTDS)", use the "jtds_sqlserver" driver. I'm using it to connect to my remote SQL Server and works fine.


1

Finally found the answer on how to connect MM to Actian vectorwise via SQL :) Needs["JLink"] AddToClassPath["C:\\Users\\Desktop\\iijdbc.jar"]; Needs["DatabaseLink"]; OpenSQLConnection[ JDBC["com.ingres.jdbc.IngresDriver", "jdbc:ingres://HOST:VW7/DATABASE;user=xxx;password=yyy"]] Feeling good! Pat


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

On MySQL, this is done with system 'MathKernel -script myFile.m', but it only works in Unix.



Only top voted, non community-wiki answers of a minimum length are eligible