1
$\begingroup$

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?

Improve this question
$\endgroup$
3
  • 1
    $\begingroup$ 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. $\endgroup$
    – Mike Honeychurch
    Commented Sep 26, 2013 at 20:59
  • 1
    $\begingroup$ 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. $\endgroup$
    – Mike Honeychurch
    Commented Sep 26, 2013 at 21:03
  • $\begingroup$ Why are you using Dynamic? $\endgroup$
    – Murta
    Commented Sep 27, 2013 at 2:22

1 Answer 1

Reset to default
1
$\begingroup$

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"]]]}]
]
Improve this answer
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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