Creating a Dynamic MSSQL connection



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.


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?

andrew k

Posted 2013-09-26T20:09:31.107

Reputation: 11

1You 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 – 2013-09-26T20:59:45.607

1To 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 – 2013-09-26T21:03:00.573

Why are you using Dynamic? – Murta – 2013-09-27T02:22:35.783



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"];
SQLTable["TEST3"], {SQLColumn["COL1", "DataTypeName" -> "INTEGER"], 
SQLColumn["COL2", "DataTypeName" -> "DOUBLE"]}];
SQLInsert[conn, "TEST3", {"COL1", "COL2"}, { {1, 23}, {2, 34}, {9, 9}}]; 

Refresh[SQLSelect[conn, "TEST3"], UpdateInterval -> 1]]], 
Button["Delete", Dynamic[SQLExecute[conn, 
  "DELETE FROM TEST3 WHERE COL1 = `1`", {selection[[1]][[1]]} ]]],
Button["Drop Table", Dynamic[SQLDropTable[conn, "TEST3"]]]}]


Posted 2013-09-26T20:09:31.107

Reputation: 41