Tag Info

Hot answers tagged

13

As @MikeHoneychurch observes, the formatted form of an SQLConnection expression: SQLConnection["db", 3, "Open", "Catalog" -> "db", "ReadOnly" -> True] differs from its FullForm: SQLConnection[JDBC[...], JLink`Objects`vm1`JavaObject18126325894086657, 1, ...] Pattern matching uses the FullForm. One way to work around this is to convert the ...


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


8

By default, SQL Server will return two result sets when it executes the exhibited SQL batch. The first result set contains the number of rows inserted by the INSERT statement. The second contains the rows from the SELECT statement. DatabaseLink will only report the first result set from a batch. However, you can tell SQL Server to avoid generating the ...


7

You might be facing the problem documented in Microsoft's knowledge base as KB942976. In a nutshell, the system call that enumerates DSNs on a 64-bit system will also list 32-bit DSNs -- even though those DSNs cannot be accessed from a 64-bit application. The knowledge base article states that there is no current resolution to this problem in the interest ...


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


7

The answer here is found in the comments above: The MySQL query itself takes 1.6-1.7 seconds, so the time spend in Mathematica on this query is negligible and can not realistically be optimized in this case.


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

Needs["DatabaseLink`"] conn = DatabaseLink`OpenSQLConnection[ DatabaseLink`JDBC[ "MySQL(Connector/J)", "localhost:3306/railfreight"], "Username" -> "", "Password" -> ""] (* SQLConnection[1, "Open", "Catalog" -> "railfreight", "TransactionIsolationLevel" -> "RepeatableRead"]*) But when you check out the FullForm (removed ...


7

You can decompress on the Mathematica side easily. Compressed MySQL reply has the following format: first four bytes are size of uncompressed data (lowest byte first) the rest is the string compressed with deflate algorithm (zlib library) Here is an example of a reply: {10, 0, 0, 0, 120, 156, 243, 72, 205, 201, 201, 87, 240, 170, 112, 82, 4, 0, 19, ...


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

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 was able to reproduce your behaviour on Mathematica V9 64-bit under Windows 7. Neither TimeConstrained nor $SQLTimeout would work. However, an explicit "Timeout" option worked for me: OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)", "187.111.111.111/mydb"] , "Username" -> "myUser" , "Password" -> "myPass" , "Timeout" -> 1 ] The ...


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

The internals of the DataDistribution are necessary for reconstructing it. I don't know much about database connectivity but Export does the right thing. Export["temp.txt", KernelMixtureDistribution[Range[10]]]; Since I chose a .txt file Import comes back with a string. Import["temp.txt"] // Head (*String*) In order to compute with it needs to be an ...


5

If you have full control over the MySQL database I think it lets you log every SQL statement from every client (query-log), which probably is the most simple way to get that information. You could also try to look at or even manipulate the sources, it looks like the relevant code is delivered as clear text in the following file in the Mathematica directory: ...


5

Adding a database index is very important when SELECTing data from a big table. Once you add the index, MySQL will take care of keeping it updated. The disadvantage of indices is that your database now takes more space in your HD and that your INSERTs are now slower. Note that you created a multiple-column index that speeds up you SELECTs when you lookup ...


5

The fastest way should be to use Part I think, particularly as your list gets larger. data={{SQLDateTime[{2005, 10, 3}], 188.88}, {SQLDateTime[{2005, 10, 4}], 184.53}, {SQLDateTime[{2005, 10, 5}], 176.5}, {SQLDateTime[{2005, 10, 6}], 172.}, {SQLDateTime[{2005, 10, 7}] . . . etc} For example data[[All, 1]] = data[[All, 1, 1]] leaves you with data ...


5

"DatabaseLink"` connections in Mathematica are done via Java and it is the Java virtual machine which actually holds the file locks. To get rid of those locks you can e.g. use: Needs["JLink"]; UninstallJava[]; After that you should be able to delete the files (you can do that from Mathematica, if so desired with DeleteFile). There might be ways to remove ...


5

Trigger an external event Edit Don't you dare to close this question! :) I've to say, that for the Java/MathPackage part of this answer I've used Workbench plugin within eclipse/Juno and again I've to agree with @halirutan that this is not a time saver at all. It took me more time to configure this etc. than to write the code. I am already a user of the ...


5

Two possible solutions are Use MySQL, SQLite or any other database that is not an in-memory database. HSQL keeps all its data in the memory at all times. I'm leaving this for future visitors since there is a version of HSQL that is built in that is in-memory, although that was not the case for the OP. Increase the Java heap size by using the same technique ...


4

In the past, years ago, I have had problems with the MySQL connection that were solved following Wolfram's tech support advice of updating the java connector to its latest version. This can be done by: Download the connector from MySQL web page: http://www.mysql.com/downloads/connector/j/ Install it at ...


4

Here is an example of using ProgressIndicator. I added Pause[] to simulate some blocking operation, and added Abort button. This is the pattern that I use when using this feature. It is simple and works for me. The idea is to pass the tracking symbol by reference. Manipulate[(*example using progress monitor in Manipulate*) Module[{result}, abort = ...


4

You can use "SortingColumns" in SQLSelect or simply use the SQL statement inside SQLExecute. In the second case you simply write the SQL statement in the string: SQLExecute[dbConn, "SELECT id, measurement, timestamp FROM myTable WHERE id IN (1, 2) ORDER BY timestamp"] And you are returned triplets with {id, measurement, timestamp}.


3

Lets do an exercise importing and exporting JPEG data. First let's create a tiny single pixel JPEG file: In[1]:= Export["~/Desktop/minitest.jpg", Image[{{0}}]] Now lets import the binary data that we have just created: In[2]:= Import["/Users/gdelfino/Desktop/minitest.jpg", "Binary"] Out[2]:= {255, 216, 255, 224, 0, 16, 74, 70, 73, 70, 0, 1, 1, 1, 0, 72, ...


3

Your binarydata seems to be a string with hexadecimal digits. Converting this using FromCharacterCode yields nonsense, of course. It can be solved in a few steps: 0) The string: imageString = "FFD8FFE000104A46494600010201006000600000FFEE000E41646F6265006400000\ 00001FFE1135D4578696600004D4D002A0000000800070132000200000014000000620\ ...


3

I'm not sure but don't think the problem is with your setup of the JDBC driver. There is reasonable documentation how you can make JDBC drivers available in the DatabaseLink documentation, which you can find here: JDBC Connections. It is basically as easy as putting the jar that you can download into the right directory (It might well be that what Stefan did ...


3

It looks like you have the answer by added indexes. Note you can check how MySql is satisfying your query using the keyword explain . This will tell you if there are any full table scan going on which will slow down the query enormously depending on the size of the tables. See here for more info.


3

I would convert your file first to CSV format. Then, there are a number of options for importing CSV files. If Import is too slow, you can try something similar to what is described here or here. The method described in the former link may require custom code, while the first method of the two described in the latter link is quite general and should work ...


3

You could use a trigger in your database which fires when data is added or changed in your table to execute a piece of code; I don't know sqlserver but I assume that a trigger can execute some c++ or .Net code. That in turn could pass the data into Mathematica through NetLink. Mathematica would have to be set up to listen for the data of course. Actually ...



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