# Tag Info

12

As @MikeHoneychurch observes, the formatted form of an SQLConnection expression: SQLConnection["db", 3, "Open", "Catalog" -> "db", "ReadOnly" -> True] differs from its FullForm: SQLConnection[JDBC[...], JLinkObjectsvm1JavaObject18126325894086657, 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

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

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

Needs["DatabaseLink"] conn = DatabaseLinkOpenSQLConnection[ DatabaseLinkJDBC[ "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, ...

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

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

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

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

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

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

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

4

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

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

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

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

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

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

3

If you examine Information[SQLSelect] you'll see the various input patterns allowed for SQLSelect. It's quite long but the relevant parts are: DatabaseLinkSQLPrivatecolumns:_SQLColumn|{__SQLColumn} DatabaseLinkSQLPrivatecolumns:_String|{__String} DatabaseLinkSQLPrivatecolumns:{{_String,_String}..} Indeed, it allows either strings, lists of ...

3

When I have to connect frequently to a database, I prefer to create a pack with the connections strings. BeginPackage["myConn",{"DatabaseLink"}] myConn::usage="myConn[ip] with default 192.168.0.20"; Begin["Private`"] myConn[ip_:"192.168.0.20"] := OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)", ip], "Username" -> "my User", ...

2

The last paragraph here indicates that the .lck extension is assumed by the OS to be managed by the application (which in this case is Mathematica). The .log is also in that category. The solution is to specifically delete those files after CloseSQLConnection[tc] I use the CUD pattern when programming: Create, Use, and Dispose. Mainly for memory ...

2

Please refer to my answer here, it FINALLY works after struggling for almost a year. I hope this works for everyone else. I'd suggest completely ignoring the connection wizard and going the code route: How to connect Mathematica 9 to MS SQL 2012 using Windows 7 enterprise (64-bit)

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