Tag Info

Hot answers tagged

21

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


15

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


13

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


9

It appears that an operation equivalent to SQL GROUP BY is surprisingly awkward to express: a[GroupBy[Key["A"]] /* Values, <| "A" -> First /* "A", "B" -> Query[Total, "B"] |>] If the application will tolerate a resulting dataset that is non-rectangular in shape, then the result can be obtained with a more natural query (more natural for ...


9

After some useless hours in Google trying to understand jTDS, I discovered that DatabaseLink implementation is a open Package, and that SQLInsert implementation is in the file DatabaseLink/Kernel/SQL.m. Inside It you can check that SQLInsert implementation is just a special application of SQLExecute. And that SQLExecute has two new parameters in V10 called ...


8

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

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


6

Firstly, a bit about SQLite insert performances: http://stackoverflow.com/questions/1711631/how-do-i-improve-insert-per-second-performance-of-sqlite http://stackoverflow.com/questions/3852068/sqlite-insert-very-slow Both of these state the fact that SQLite wraps every insert statement with a transaction and that the run times can be up to 270x faster ...


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

Using SQLBeginTransaction works fast with the SQLite version 3.7.2 distributed with Mathematica 10 : Needs["DatabaseLink`"]; testdb = FileNameJoin[{$TemporaryDirectory, "test1.db"}]; If[ FileExistsQ[testdb], DeleteFile[testdb] ]; db = JDBC["SQLite", testdb]; conn = OpenSQLConnection[db]; SQLCreateTable[conn, SQLTable["TEST"], {SQLColumn["COL1", ...


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

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


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

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

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"] (* ...


2

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


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

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

To pass batch parameters to SQLExecute, we must specify the arguments as a single list-of-lists. Each sublist is expected to contain one value for each parameter placeholder. Thus, there is no need to split Matrix into its component columns using Matrix[[All,1]],Matrix[[All,2]]. It is enough to say: SQLExecute[con, "INSERT INTO VARIABLE (x,y) VALUES ...


2

Whilst valid Leonid's code has been superseded by new functionality in Mathematica 10. As well as Association take a look at the Dataset, Select and Query functions in the help. But basically try some of the following : data = {{"1", "Cat", "Tiddles"}, {"2", "Dog", "Spot"}}; cols = {"id", "animal", "name"}; (* turns a list of animals into an ...


1

First, try the standard JVMArguments trick, and if that doesn't work, you should use streaming via SQLResultSetOpen[]. Here's an example of this more sophisticated technique, which should enable you to interface with very large sql tables. Example First you can do a reality check using the GUI tool DatabaseExplorer[], making sure you've got a good ...


1

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



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