Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I just started to work with a database through Mathematica. I have a large data set. I use an .mx file to store my data. Now I have to store the data in a database, I have installed Microsoft SQL server 2012 in my system. My problem is I don't know how I should store my big list of data in the database. My options seem to be

  1. store the mx file in database and retrieve it using SQL command in Mathematica

  2. create a column in a database table and insert an element from my data list into each row.

I tried the SQL route to insert the .mx file into the database, since I thought that would be better than inserting the list element-by-element.

insert into test select * from openrowset(bulk 'I:\MsSql\test.mx', single_blob) as test1 

I executed the above line in Microsoft SQL server 2012. It appeared to work, but when I tried to retrieve my data set from the database table through Mathematica, I got results that look like a big sublist containing some random numbers and characters.

{{282A546869732069732061......}}

I have googled it, but I couldn't find a useful answer. Please explain what I did wrong or refer to me to good material I can learn from.

share|improve this question
3  
it unfortunately is quite difficult to understand what exactly you are trying to do. It sounds as if you store the binary content of your .mx file in the database. That sounds like an abuse of the idea behind using a database but in certain circumstances could be reasonable. If you want to reimport such data to Mathematica you would at some place need something like a ImportString[x,"MX"] to convert the binary content back into a Mathematica expression. Depending on what your select statement is and how the data is stored in the database additional conversions might be necessary... –  Albert Retey May 6 '13 at 13:24
add comment

2 Answers

up vote 6 down vote accepted

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 how you do this.

Lets see some examples. Start by creating some sample data:

data = RandomInteger[100, {20000, 1}];

So data contains 20000 random integers. Lets assume that your database connection is in the variable conn and that there is a table myTable in your database with a single column which can hold integer numbers.

Here we see that a very fast operation repeated 20k times can take a long time:

Scan[SQLExecute[conn, "INSERT INTO myTable VALUES (`1`) ", {#}] &, 
  Flatten[data]] // Timing

{323.609754, Null}

Instead you can send all of the numbers at the same time to DatabaseLink for insertion which results in a much better performance:

SQLExecute[conn, "INSERT INTO myTable VALUES (`1`) ", data]; // Timing

{3.961870, Null}

We can also try to send the numbers in batches of 100 numbers

Scan[SQLExecute[conn, "INSERT INTO myTable VALUES (`1`) ", #] &, 
  Partition[data, 100, 100, 1, {}]] // Timing

{7.210615, Null}

And finally we can build a huge query string:

query = "INSERT INTO myTable VALUES " <> 
    StringReplace[
     StringTake[ToString@data, {2, -2}], {"{" -> "(", 
      "}" -> ")"}]; // Timing

{0.039831, Null}

And execute it at a blazing fast speed:

SQLExecute[conn, query]; // Timing

{0.025865, Null}

This last approach must have a downside. I guess it uses more RAM.

share|improve this answer
add comment

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" -> "myUsername", 
   "Password" -> "myPassword" ]

(*Insert Data*)
insertData[newData_]:=Module[{conn=openConn[],columnsName},
    columnsName={"col1","col2","col3"};
    SQLInsert[conn,"myBaseName",columnsName,newData];
    CloseSQLConnection[conn];
]

(*Select Data*)
topNData[n_]:=Module[{conn=openConn[], data, columnsName, sql,r},
    sql="Select top "<>ToString@n<>" * from myBaseName";
    r=SQLExecute[conn,sql];
    CloseSQLConnection[conn];
    r
]
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.