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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.

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

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

Export returns the filename to which your data was exported. Your code does insert that filename into your database, and you see that this actually does work. What you most probably want is to insert the file content, not the filename, into the database. For that you need a combination of ExportString, ToCharacterCode and SQLBinary. This is described, as ...

You appear to be facing a problem related to the configuration of MySQL itself. Have you take a look at the MySQL server configurations? It appears to be the value of the wait_timeout property which depending on the installer used defaults to something between 2 and 8 hours. Normally, the server configuration file is called my.cfg and it is located in the ...

Maybe sqlToDatelist:=# /. SQLDateTime[x_] :> DateList[x]& Or sqlToDatelist2:=# /. SQLDateTime[x_] :> x & Applied to {{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}], 176.5}} They both give ...

I did a little digging, and I do not believe you can access an MDB file using DatabaseLink` on anything but Windows. Looking at the available drivers Needs["DatabaseLink`"] JDBCDriverNames[] (* {"Microsoft Access(ODBC)", "hsqldb", "HSQL(Memory)", "HSQL(Server)", "HSQL(Server+TLS)", "HSQL(Standalone)", "HSQL(Webserver)", "HSQL(Webserver+TLS)", ...

It sounds like all you are looking for is a generic storage using MySQL. Then your translation of _String to TEXT makes sense, but I would consider _Real as a DOUBLE column, depending on your precision requirements. Additionally, I would probably separate the inner list: {_String, {_String, _Real}, _Real} into: {_String, _String, _Real, _Real} So ...

Here is database connection file to one of my databases. The file can be found on Linux systems in directory: /home/username/.Mathematica/DatabaseResources/mydatabse.m If you can't find mydatabase.m or DatabaseResources directory, try creating them (I assume that path should be similar on Windows?). Here is a config file: ...