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.

Hello i am trying to create a notebook that automatically connects to a database on MSSQL server and would then query this to create a sort of personal report. I have connected to this database and run a few queries ok but i am having trouble with getting it to load when i open the notebook.

Needs["DatabaseLink`"];
Dynamic[
    conn=OpenSQLConnection["TSQL2012"]]

myOrder =SQLExecute[Dynamic[conn], "select custid, shipcountry from Sales.Orders where orderid = '10248'", "ShowColumnHeadings" ->True] //TableForm

I have been trying code such as the above but i think the Dynamic connection is trying to open to many connections to the database and then it crashes. Running the code above without the Dynamic elements in place works fine. is there a way to connect to DB when a notebook is opened and use this in SQLExecute statements?

share|improve this question
1  
You need to read the docs on Dynamic. Dynamic essentially dynamically updates pixels on your screen. It is not for dynamic -- open and close as needed -- connections to databases or other links. –  Mike Honeychurch Sep 26 '13 at 20:59
1  
To connect when the notebook is open the simple thing to do is include the Needs command and conn=OpenSQLConnection["TSQL2012"] in an initialization cell. –  Mike Honeychurch Sep 26 '13 at 21:03
    
Why are you using Dynamic? –  Murta Sep 27 '13 at 2:22

1 Answer 1

I wouldn't wrap Dynamic around the setup of your database connection. However if you wish to update your SQL query to reflect changes in the database you could use Dynamic with a nested Refresh to specify an update time interval for your query. The SQL functions are not tickish and in general it would be undesirable for them to update automatically. I make use of this type of functionality when constructing user interfaces. To try this code you will need to have installed the example database "demo" which comes with DatabaseLink.

Needs["DatabaseLink`"]; conn = OpenSQLConnection["demo"];
SQLCreateTable[conn, 
SQLTable["TEST3"], {SQLColumn["COL1", "DataTypeName" -> "INTEGER"], 
SQLColumn["COL2", "DataTypeName" -> "DOUBLE"]}];
SQLInsert[conn, "TEST3", {"COL1", "COL2"}, { {1, 23}, {2, 34}, {9, 9}}]; 

DynamicModule[{selection}, 
Column[{Dynamic[
ListPicker[Dynamic[selection], 
Refresh[SQLSelect[conn, "TEST3"], UpdateInterval -> 1]]], 
Dynamic[selection],     
Button["Delete", Dynamic[SQLExecute[conn, 
  "DELETE FROM TEST3 WHERE COL1 = `1`", {selection[[1]][[1]]} ]]],
Button["Drop Table", Dynamic[SQLDropTable[conn, "TEST3"]]]}]
]
share|improve this answer

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.