Group Abstract Group Abstract

Message Boards Message Boards

Inter operability of Mathematica with Microsoft Office and/or SQL

David Caple
Posted 4 years ago

I have data held on an MS SQL-Server database that I want to do probability calculations on ... what can I expect by way of smooth communication and conversely problems between the database and wanting to add value to the data using Mathematica? I can use SQL or Access/Excel VBA although for the number of records involved Excel will be stretched! BTW I have no idea what headings to put such a question under, I hope I have guessed wisely!
POSTED BY: David Caple
6 Replies
Sort By:
Updating Name
Updating Name
Posted 4 years ago

Hi Mike and thanks for your help. I guess it's not imperative which way round it is driven for the historical data, I only said for SQL Server to drive it because that data is held in an SQL Server database. The future data will be from visitors to the website that is linked to the SQL database.

If I use Mathematica to drive the process at the website stage for future calculations for visitors will the process be ...

Website gets input from visitor and passes it to Mathematica Mathematica performs the calculation and sends the result including the inputted data to SQL Server which saves the data and publishes it back to the website?

I guess I now need to work out how to get Mathematica to do it! :)

Thanks again.
POSTED BY: Updating Name
Mike Besso
Mike Besso
Posted 4 years ago

David:

I've come across similar scenarios before. If you want to use Mathematica, I think your best option will be to use DirectLink as Rohit suggested. Though, I think you would have to rely on Mathematica driving the process. Though, that should not be hard to do with a flow like this:

  • Mathematica, using direct link, extract the data from the database
  • Mathematica does the calculation
  • Mathematica, using direct link, writes the results back to the database

If you really do have to have the database drive the flow, then I would suggest looking to Python or R to do the calculation. The newer versions of MS SQL Server integrate with both Python and R.

One thing to remember is that you can use WolframScript along with Mathematica or the Wolfram Engine to create scheduled jobs that run without the Mathematica UI. Depending on your work flow, that could work as well.

Good luck.
POSTED BY: Mike Besso
David Caple
David Caple
Posted 4 years ago

Hi Rohit and thanks for your continued interest. By "direct link" I mean to have SQL Server and Mathematica link so that they directly pass data between them with no manual assistance. As I said in the OP I have c.150,000 records I need to perform a calculation on and therefore manually typing the data is not on!

What I would wish to do is to have SQL Server control the exercise because the resolved calculations will need to be held there. So in a perfect world I would instruct SQL Server to extract the data from the records, pass it to Mathematica to perform the calculations record by record, have Mathematica pass the resolved calculation back to SQL Server and then for SQL Server to insert the resolved calculation into the data table.

Once I have performed that on all archived records I then want to publish a website where visitors will be able to manually enter their own data into a webform and Mathematica perform it's calculation and print the result to the webpage.

The calculation is of probability. I currently have an Excel workbook that resolves the probability by way of a Monty Carlo Simulation but the processing time is too great to use that for 150,000 records so I would be looking to Mathematica to calculate the probability by way of a formula. Unfortunately I have been unable to hunt down the formula in an expanded form to enable me to code it but I know Mathematica has the necessary function. I did manage to find someone on here who said they could give me the formula but unfortunately before they could post it my post was taken down probably because it sought advice re the formula and not re the use of Mathematica. Therefore this post is solely to discover how to use Mathmatica within a website linked to an SQL Server database and not in anyway to obtain the formula (moderators please take note!).
POSTED BY: David Caple
Rohit Namjoshi
Rohit Namjoshi
Posted 4 years ago

Hi David,

Not sure what you mean by "direct link". With DatabaseLink you can connect to SQL Server using Microsoft's SQL Server JDBC driver. After the connection is established queries are passed to the server for execution and the results are passed back to Mathematica. WL is not involved in query execution.
POSTED BY: Rohit Namjoshi
David Caple
David Caple
Posted 4 years ago

Thank you Rohit, I will have a look ... it's just that I have a need to perform a probability calculation om 150,000 records so a direct link is imperative. :)
POSTED BY: David Caple
Rohit Namjoshi
Rohit Namjoshi
Posted 4 years ago

Hi David,

Have you tried DatabaseLink. If you are familiar with SQL I would use SQL Style queries rather than Wolfram Language Style queries. Documented here.
POSTED BY: Rohit Namjoshi
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard