(* Content-type: application/vnd.wolfram.mathematica *) (*** Wolfram Notebook File ***) (* http://www.wolfram.com/nb *) (* CreatedBy='Mathematica 9.0' *) (*CacheID: 234*) (* Internal cache information: NotebookFileLineBreakTest NotebookFileLineBreakTest NotebookDataPosition[ 157, 7] NotebookDataLength[ 170501, 3813] NotebookOptionsPosition[ 44779, 1352] NotebookOutlinePosition[ 161412, 3539] CellTagsIndexPosition[ 161332, 3534] WindowFrame->Normal*) (* Beginning of Notebook Content *) Notebook[{ Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell["Get Help With Your DatabaseLink", "Title", CellChangeTimes->{{3.448190122176695*^9, 3.44819012385317*^9}, { 3.485609127497636*^9, 3.485609133015955*^9}, {3.514308355088097*^9, 3.514308374696691*^9}, 3.5146594348020153`*^9, 3.5146601881617107`*^9, { 3.6071801166601562`*^9, 3.607180117693161*^9}, {3.622469062158454*^9, 3.6224690710224543`*^9}}], Cell["\<\ Dillon Tracy Mili Vishwakarma\ \>", "Subtitle", CellChangeTimes->{{3.485609136120798*^9, 3.4856091511532907`*^9}, { 3.4856091945334663`*^9, 3.485609199379443*^9}, {3.4951031489375*^9, 3.49510314984375*^9}, {3.495106455296875*^9, 3.495106455453125*^9}, { 3.5143083846926413`*^9, 3.514308395249558*^9}, 3.5443793532699003`*^9, { 3.622469088187454*^9, 3.6224691055324545`*^9}}], Cell["Wolfram Research Inc.", "Subsubtitle", CellChangeTimes->{ 3.483202458953512*^9, {3.495105345328125*^9, 3.495105347890625*^9}, { 3.49510644571875*^9, 3.495106448390625*^9}, {3.5143083980990458`*^9, 3.514308409442589*^9}, {3.622469111389454*^9, 3.6224691179594545`*^9}}] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Outline", "Section", CellChangeTimes->{ 3.483202458955147*^9, {3.514308340990994*^9, 3.514308352103572*^9}, { 3.6224691525584545`*^9, 3.622469154885454*^9}}], Cell[CellGroupData[{ Cell["Installing and setting up a database engine", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469219446454*^9}}], Cell["About DatabaseLink", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469231083454*^9}}], Cell["Connecting to database from Mathematica", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469241674454*^9}}], Cell["Create, Retrieve, Update and Delete operations", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469305000454*^9}}], Cell["Creating procedures and functions", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469321052454*^9}}], Cell["Creating time based applications", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469340573454*^9}}], Cell["Working with ResultSets", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469362004454*^9}}], Cell["Questions", "Item", CellChangeTimes->{{3.622469182622454*^9, 3.622469366328454*^9}}] }, Open ]] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Installing and setting up MySQL", "Section", CellChangeTimes->{ 3.483202458955147*^9, {3.51430857274755*^9, 3.514308578875259*^9}, { 3.6224694825884542`*^9, 3.622469493990454*^9}}], Cell[CellGroupData[{ Cell[TextData[{ " Install ", StyleBox["Mathematica", FontSlant->"Italic"], " " }], "ItemNumbered", CellChangeTimes->{{3.6200620403205214`*^9, 3.620062045817071*^9}, { 3.6229756723056164`*^9, 3.622975672777711*^9}}], Cell[TextData[{ "Download MySQL Community Server -", ButtonBox[" http://dev.mysql.com/downloads/", BaseStyle->"Hyperlink", ButtonData->{" http://dev.mysql.com/downloads/", None}] }], "ItemNumbered", CellChangeTimes->{{3.6200620536728563`*^9, 3.6200621229387817`*^9}, { 3.620062155654053*^9, 3.620062160853573*^9}, {3.6224695562064543`*^9, 3.622469570366454*^9}, {3.6224698835122156`*^9, 3.6224698835132155`*^9}, { 3.6229756769865522`*^9, 3.6229756778687286`*^9}}], Cell["Install MySQL and set up a root user", "ItemNumbered", CellChangeTimes->{{3.622469584045454*^9, 3.622469620370904*^9}, { 3.6229756806672883`*^9, 3.6229756813424234`*^9}}], Cell["\<\ If using Command line: Bring up the command line, change directory to MySQL installation directory cd C:\\Program Files\\MySQL\\MySQL Server 5.6\\bin Create a root user and password mysqladmin -u root password myrootpassword\ \>", "ItemNumbered", CellChangeTimes->{{3.622469626393506*^9, 3.6224697311499805`*^9}, { 3.6229756839979544`*^9, 3.622975685346224*^9}}], Cell["\<\ Login as root, enter password when prompted to mysql -u root -p password:\ \>", "ItemNumbered", CellChangeTimes->{{3.622469656877554*^9, 3.622469659455812*^9}, 3.6224697166945353`*^9, {3.622975701150384*^9, 3.6229757015124564`*^9}}], Cell[TextData[{ "Create a database that we will try connecting to from ", StyleBox["Mathematica", FontSlant->"Italic"], ". In MySQL prompt type:\n\nCreate Database dbname\n\nwhere \ \[OpenCurlyQuote]dbname\[CloseCurlyQuote] is the name you want to give to the \ database" }], "ItemNumbered", CellChangeTimes->{{3.6224704025531144`*^9, 3.622470490693928*^9}, { 3.6229757036958933`*^9, 3.6229757042990136`*^9}}], Cell["\<\ Change the current working database to \[OpenCurlyQuote]dbname\ \[CloseCurlyQuote] Use dbname\ \>", "ItemNumbered", CellChangeTimes->{{3.622470494958354*^9, 3.6224705191877766`*^9}, { 3.622975710942342*^9, 3.622975711225399*^9}}] }, Open ]] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Connecting to database from Mathematica", "Section", CellChangeTimes->{ 3.483202458955147*^9, {3.514308863196991*^9, 3.5143088633311243`*^9}, { 3.622469760792945*^9, 3.62246976855072*^9}}], Cell[TextData[{ "Load ", ButtonBox["DatabaseLink", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "Guides"}, "SQLDatabaseOperations.nb", CharacterEncoding -> "WindowsANSI"], None}] }], "ItemNumbered", CellChangeTimes->{{3.622469802522117*^9, 3.622469807688634*^9}, { 3.62247026889575*^9, 3.62247026889675*^9}, {3.6229757469575434`*^9, 3.622975747572667*^9}}], Cell[BoxData[ RowBox[{"Needs", "[", "\"\\"", "]"}]], "Input", CellChangeTimes->{{3.62246981165103*^9, 3.622469817934658*^9}}], Cell[TextData[{ "Open a connection to MySQL - Use ", ButtonBox["OpenSQLConnection", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "ReferencePages", "Symbols"}, "OpenSQLConnection.nb", CharacterEncoding -> "WindowsANSI"], None}], " function\nWe will use the JDBC driver that comes with Mathematica" }], "ItemNumbered", CellChangeTimes->{{3.6224698266215267`*^9, 3.6224698588427486`*^9}, { 3.622470340324892*^9, 3.6224703706119204`*^9}, {3.622975749924137*^9, 3.6229757504522424`*^9}}], Cell[BoxData[ RowBox[{"conn", "=", RowBox[{"OpenSQLConnection", "[", RowBox[{ RowBox[{"JDBC", "[", RowBox[{"\"\\"", ",", "\"\\""}], "]"}], ",", RowBox[{"\"\\"", "\[Rule]", " ", "\"\\""}], ",", RowBox[{"\"\\"", "\[Rule]", " ", "\"\<$Prompt\>\""}]}], "]"}]}]], "Input", CellChangeTimes->{{3.6200585205415645`*^9, 3.62005857010452*^9}, { 3.6200586589564047`*^9, 3.620058672923801*^9}, {3.620061288641116*^9, 3.620061299713116*^9}, {3.620061431714116*^9, 3.6200614373041162`*^9}, { 3.6200614741591163`*^9, 3.620061486293116*^9}, {3.622394234909955*^9, 3.6223942619298477`*^9}}], Cell["\<\ If you see a SQLConnection object in the output, it means that \ the connection was successful.\ \>", "Text", CellChangeTimes->{{3.6224734130753803`*^9, 3.6224734409251647`*^9}, { 3.622975808060762*^9, 3.622975813332816*^9}}], Cell[TextData[{ " Run a test command to check ", StyleBox["DatabaseLink", FontSlant->"Italic"], " can query the database, lets list the tables in the database" }], "ItemNumbered", CellChangeTimes->{{3.622473453601432*^9, 3.6224734798980618`*^9}, { 3.622975752502652*^9, 3.6229757527427006`*^9}}], Cell[BoxData[ RowBox[{"SQLTables", "[", "conn", "]"}]], "Input", CellChangeTimes->{{3.6224734844695187`*^9, 3.6224734897380457`*^9}}] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Create a database", "Section", CellChangeTimes->{ 3.483202458955147*^9, {3.514308863196991*^9, 3.5143088633311243`*^9}, { 3.6224791863259335`*^9, 3.6224792045167522`*^9}}], Cell[TextData[{ "To create a dataabase in mysql use CREATE DATABASE command with ", ButtonBox["SQLExecute", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "ReferencePages", "Symbols"}, "SQLExecute.nb", CharacterEncoding -> "WindowsANSI"], None}], " function " }], "ItemNumbered", CellChangeTimes->{{3.6201418198451405`*^9, 3.6201418472031407`*^9}, { 3.622479476383936*^9, 3.6224794828975873`*^9}, {3.622479525459843*^9, 3.6224795254618435`*^9}, {3.6229758242950077`*^9, 3.6229758249371367`*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input", CellChangeTimes->{{3.6201419893733363`*^9, 3.6201420050279016`*^9}}], Cell["Change the current working database to TestDB", "ItemNumbered", CellChangeTimes->{{3.622479236491949*^9, 3.622479238387139*^9}, { 3.6224792953608356`*^9, 3.622479305345834*^9}, {3.6229758280507593`*^9, 3.622975828644878*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input", CellChangeTimes->{{3.6224793155888577`*^9, 3.622479317379037*^9}}], Cell["Create a table named testtable in TestDB database ", "ItemNumbered", CellChangeTimes->{{3.622479323733672*^9, 3.62247933981328*^9}, { 3.622975830866322*^9, 3.6229758313414173`*^9}}], Cell[BoxData[ RowBox[{"SQLCreateTable", "[", RowBox[{"conn", ",", RowBox[{"SQLTable", "[", "\"\\"", "]"}], ",", RowBox[{"{", RowBox[{ RowBox[{"SQLColumn", "[", RowBox[{"\"\\"", ",", RowBox[{"\"\\"", "\[Rule]", "\"\\""}], ",", RowBox[{"\"\\"", "\[Rule]", " ", "20"}]}], " ", "]"}], ",", RowBox[{"SQLColumn", "[", RowBox[{"\"\\"", ",", RowBox[{"\"\\"", "\[Rule]", " ", "\"\\""}]}], "]"}]}], "}"}]}], "]"}]], "Input", CellChangeTimes->{{3.622482826510933*^9, 3.6224829711552057`*^9}, { 3.622569413976675*^9, 3.6225694161948967`*^9}, {3.622975132005543*^9, 3.622975134880543*^9}}], Cell[" The same can be done using SQLExecute function", "Text", CellChangeTimes->{{3.622482984593236*^9, 3.6224829997927947`*^9}, { 3.62297731422025*^9, 3.622977318462675*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{ "conn", ",", "\"\\""}], "]"}]], "Input", CellChangeTimes->{{3.6224792761369133`*^9, 3.6224792761379137`*^9}, { 3.622479346765976*^9, 3.6224793614774466`*^9}, {3.622569423115589*^9, 3.622569425564834*^9}}] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Inserting data into database", "Section", CellChangeTimes->{ 3.483202458955147*^9, {3.514308863196991*^9, 3.5143088633311243`*^9}, { 3.6224791863259335`*^9, 3.6224792045167522`*^9}, {3.6224794171630144`*^9, 3.622479426476946*^9}}], Cell["Create some data rows to be inserted into testtable", "ItemNumbered", CellChangeTimes->{{3.6224794357178698`*^9, 3.6224794492352214`*^9}, { 3.6224795379820952`*^9, 3.622479540971394*^9}, {3.622975857595667*^9, 3.62297585836082*^9}}], Cell[BoxData[ RowBox[{"number", "=", RowBox[{"Range", "[", RowBox[{"4", ",", "10", ",", "1"}], "]"}]}]], "Input"], Cell[BoxData[ RowBox[{"numberstr", "=", RowBox[{"ToString", "/@", RowBox[{"Range", "[", RowBox[{"4", ",", "10", ",", "1"}], "]"}]}]}]], "Input"], Cell[BoxData[ RowBox[{"name", "=", RowBox[{"Map", "[", RowBox[{ RowBox[{ RowBox[{"StringJoin", "[", RowBox[{"\"\\"", ",", "#"}], "]"}], "&"}], ",", "numberstr"}], "]"}]}]], "Input"], Cell[BoxData[ RowBox[{"data", "=", RowBox[{"MapThread", "[", RowBox[{"List", ",", RowBox[{"{", RowBox[{"name", ",", "number"}], "}"}]}], "]"}]}]], "Input"], Cell[TextData[{ "Use ", ButtonBox["SQLInsert", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "ReferencePages", "Symbols"}, "SQLInsert.nb", CharacterEncoding -> "WindowsANSI"], None}], " to insert the data into testtable" }], "ItemNumbered", CellChangeTimes->{{3.622479547953092*^9, 3.622479584768773*^9}, { 3.6229758610403557`*^9, 3.6229758614634404`*^9}}], Cell[BoxData[ RowBox[{"SQLInsert", "[", RowBox[{"conn", ",", "\"\\"", ",", RowBox[{"{", RowBox[{"\"\\"", ",", "\"\\""}], "}"}], ",", "data"}], "]"}]], "Input"] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Selecting data from database", "Section", CellChangeTimes->{ 3.483202458955147*^9, {3.514308863196991*^9, 3.5143088633311243`*^9}, { 3.6224791863259335`*^9, 3.6224792045167522`*^9}, {3.6224794171630144`*^9, 3.622479426476946*^9}, {3.6224797222025156`*^9, 3.622479728722167*^9}}], Cell[TextData[{ "Use ", ButtonBox["SQLSelect", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "ReferencePages", "Symbols"}, "SQLSelect.nb", CharacterEncoding -> "WindowsANSI"], None}], " function to select data from a table" }], "Text", CellChangeTimes->{{3.6224797852698216`*^9, 3.6224798063339276`*^9}, { 3.6224799096712604`*^9, 3.6224799096722603`*^9}}], Cell["Select all data from a table", "ItemNumbered", CellChangeTimes->{{3.6224798104413385`*^9, 3.6224798178670807`*^9}, { 3.6229758807883043`*^9, 3.622975881142375*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input"] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Updating a table", "Section", CellChangeTimes->{{3.6224824753989334`*^9, 3.622482478204933*^9}}], Cell[TextData[{ "Use ", ButtonBox["SQLUpdate", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "ReferencePages", "Symbols"}, "SQLUpdate.nb", CharacterEncoding -> "WindowsANSI"], None}], " to update a table in database" }], "Text", CellChangeTimes->{{3.6224824844369335`*^9, 3.622482537831933*^9}}], Cell["Update all rows with value less than 5", "ItemNumbered", CellChangeTimes->{{3.622482555902933*^9, 3.6224825650949335`*^9}}], Cell[BoxData[ RowBox[{"SQLUpdate", "[", RowBox[{"conn", ",", "\"\\"", ",", RowBox[{"{", RowBox[{"\"\\"", ",", "\"\\""}], "}"}], ",", RowBox[{"{", RowBox[{"\"\\"", ",", "11"}], "}"}], ",", RowBox[{ RowBox[{"SQLColumn", "[", "\"\\"", "]"}], "<", "5"}]}], "]"}]], "Input"], Cell["Check if the update was successful", "ItemNumbered", CellChangeTimes->{{3.622482581488933*^9, 3.622482592967933*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input"] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Getting information about the table and connection", "Section", CellChangeTimes->{{3.622483117339048*^9, 3.622483123554912*^9}, { 3.6224835577636704`*^9, 3.6224835613680305`*^9}}], Cell[CellGroupData[{ Cell["Connection information", "Subsection", CellChangeTimes->{{3.6224836085077443`*^9, 3.622483614177311*^9}}], Cell["Get all information about a table", "Item", CellChangeTimes->{{3.6224838593738284`*^9, 3.6224838658244734`*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"SQLConnectionInformation", "[", "conn", "]"}], ";"}]], "Input"], Cell["Check is a connection is open", "Item", CellChangeTimes->{{3.622483648055699*^9, 3.6224836558944826`*^9}}], Cell[BoxData[ RowBox[{"SQLConnectionOpenQ", "[", "conn", "]"}]], "Input"], Cell["Check if a connection is usable", "Item", CellChangeTimes->{{3.6224836837632694`*^9, 3.622483690683961*^9}}], Cell[BoxData[ RowBox[{"SQLConnectionUsableQ", "[", "conn", "]"}]], "Input"], Cell["Close a connection", "Item", CellChangeTimes->{{3.622483767617654*^9, 3.622483770714964*^9}}], Cell[BoxData[ RowBox[{"CloseSQLConnection", "[", "conn", "]"}]], "Input", CellChangeTimes->{{3.6224837772656183`*^9, 3.622483785268419*^9}}], Cell[TextData[ButtonBox["List of functions for getting connection information", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "Guides"}, "DatabaseConnectionsAndResources.nb", CharacterEncoding -> "WindowsANSI"], None}]], "Item", CellChangeTimes->{{3.6224837995188437`*^9, 3.6224838258714786`*^9}}] }, Closed]], Cell[CellGroupData[{ Cell["Table information", "Subsection", CellChangeTimes->{{3.622483833996291*^9, 3.6224838366865597`*^9}}], Cell["Get information on all tables in a database", "Item", CellChangeTimes->{{3.6224831664177675`*^9, 3.6224831738009815`*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"SQLTableInformation", "[", "conn", "]"}], "//", "TableForm"}]], "Input"], Cell["Get information on a specific table", "Item", CellChangeTimes->{{3.6224831945291977`*^9, 3.6224832183413396`*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"SQLTableInformation", "[", RowBox[{"conn", ",", "\"\\"", ",", RowBox[{"\"\\"", "\[Rule]", " ", "True"}]}], "]"}], "//", "TableForm"}]], "Input"], Cell["Get {table,column} pairs from a particular table", "Item", CellChangeTimes->{{3.6224832906409607`*^9, 3.622483307747671*^9}}], Cell[BoxData[ RowBox[{"SQLColumnNames", "[", RowBox[{"conn", ",", RowBox[{"SQLTable", "[", "\"\\"", "]"}]}], "]"}]], "Input"], Cell[TextData[Cell[BoxData[ FormBox[ ButtonBox[ RowBox[{ "List", " ", "of", " ", "functions", " ", "for", " ", "getting", " ", "information", " ", "on", " ", "tables", " ", "and", " ", "data"}], BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "SystemFiles", "Links", "DatabaseLink", "Documentation", "English", "Guides"}, "DatabaseInformation.nb", CharacterEncoding -> "WindowsANSI"], None}], TraditionalForm]]]], "Item",\ CellChangeTimes->{{3.6224834869205866`*^9, 3.622483546107505*^9}}] }, Closed]] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Creating procedures and functions", "Section", CellChangeTimes->{{3.6224839560174923`*^9, 3.6224839626181517`*^9}}], Cell[TextData[{ "No separate function in ", StyleBox["DatabaseLink", FontSlant->"Italic"], " to create a procedure and function, use SQLExecute" }], "Text", CellChangeTimes->{{3.6225435825396795`*^9, 3.6225436144145803`*^9}, { 3.6225443503843327`*^9, 3.622544353120606*^9}}], Cell["Use CREATE PROCEDURE command in SQLExecute directly", "Text", CellChangeTimes->{{3.6225443639326873`*^9, 3.6225443822945232`*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{ "conn", ",", "\"\\""}], "]"}]], "Input"], Cell["Call procedure \[OpenCurlyQuote]proc2\[CloseCurlyQuote]", "Text", CellChangeTimes->{{3.622975595894543*^9, 3.6229756043565435`*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input", CellChangeTimes->{{3.622975051418543*^9, 3.622975095875543*^9}, { 3.6229751736165433`*^9, 3.622975197492543*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input", CellChangeTimes->{{3.622975273505543*^9, 3.6229753165835433`*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input", CellChangeTimes->{{3.6229753560355434`*^9, 3.622975371908543*^9}, { 3.622975434277543*^9, 3.622975439914543*^9}}], Cell["Drop function and procedures", "Text", CellChangeTimes->{{3.6229755368965435`*^9, 3.622975543055543*^9}}], Cell[BoxData[ RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\\""}], "]"}]], "Input", CellChangeTimes->{{3.622975397387543*^9, 3.622975411536543*^9}, { 3.622975476850543*^9, 3.6229755137055435`*^9}}] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Creating timed based application", "Section", CellChangeTimes->{{3.6225649661852455`*^9, 3.622564974283246*^9}}], Cell[TextData[{ "This application uses ", ButtonBox["ScheduledTask", BaseStyle->"Hyperlink", ButtonData->{ FrontEnd`FileName[{ ParentDirectory[ ParentDirectory[ ParentDirectory[ ParentDirectory[]]]], "Program Files", "Wolfram Research", "Mathematica", "10.0.1", "Documentation", "English", "System", "ReferencePages", "Symbols"}, "ScheduledTask.nb", CharacterEncoding -> "WindowsANSI"], None}], " functionality in ", StyleBox["Mathematica", FontSlant->"Italic"], " to get data at particular times or for a time interval" }], "Text", CellChangeTimes->{{3.6225649796492457`*^9, 3.6225650210972457`*^9}, { 3.622565186295246*^9, 3.6225651862962456`*^9}}], Cell[CellGroupData[{ Cell["\<\ Create a table that contains 4 columns - Id, Name, Value, UpdateTime\ \>", "Subsection", CellChangeTimes->{{3.6225652037391033`*^9, 3.622565226520659*^9}, { 3.622565376565662*^9, 3.6225653772237935`*^9}}], Cell[CellGroupData[{ Cell["Create column header", "Subsubsection", CellChangeTimes->{{3.6225652515356607`*^9, 3.6225652603654265`*^9}}], Cell[BoxData[ RowBox[{"columnheader", "=", RowBox[{"{", RowBox[{ "\"\\"", ",", "\"\\"", ",", "\"\\"", ",", "\"\\""}], "}"}]}]], "Input"] }, Closed]], Cell[CellGroupData[{ Cell["List data type for each column", "Subsubsection", CellChangeTimes->{{3.6225652794142356`*^9, 3.622565287293811*^9}}], Cell[BoxData[ RowBox[{"datatype", "=", RowBox[{"{", RowBox[{ "\"\\"", ",", "\"\\"", ",", "\"\\"", ",", "\"\\""}], "}"}]}]], "Input"] }, Closed]], Cell[CellGroupData[{ Cell["Create a table of columns", "Subsubsection", CellChangeTimes->{{3.6225652995912704`*^9, 3.6225653058805275`*^9}}], Cell[BoxData[ RowBox[{"Cols", "=", RowBox[{"Table", "[", RowBox[{ RowBox[{"{", RowBox[{ RowBox[{"columnheader", "[", RowBox[{"[", "i", "]"}], "]"}], ",", RowBox[{"datatype", "[", RowBox[{"[", "i", "]"}], "]"}]}], "}"}], ",", RowBox[{"{", RowBox[{"i", ",", "1", ",", RowBox[{"Length", "@", "columnheader"}]}], "}"}]}], "]"}]}]], "Input"] }, Closed]], Cell[CellGroupData[{ Cell["\<\ Get a list of columns into a format accepted by SQLCreateTable\ \>", "Subsubsection", CellChangeTimes->{3.622565322579867*^9}], Cell[BoxData[ RowBox[{"colnsyntax", "=", RowBox[{ RowBox[{ RowBox[{"SQLColumn", "[", RowBox[{"#1", ",", RowBox[{"\"\\"", "\[Rule]", " ", "#2"}]}], "]"}], "&"}], "@@@", "Cols"}]}]], "Input"] }, Closed]], Cell[CellGroupData[{ Cell["\<\ Create a table, A return value of 0 indicates table was created successfully.\ \ \>", "Subsubsection", CellChangeTimes->{3.62256534069849*^9}], Cell[BoxData[ RowBox[{"SQLCreateTable", "[", RowBox[{"conn", ",", "\"\\"", ",", "colnsyntax"}], "]"}]], "Input", CellChangeTimes->{{3.622569689719247*^9, 3.622569692979573*^9}}] }, Closed]] }, Closed]], Cell[CellGroupData[{ Cell["Create some data to be inserted", "Subsection", CellChangeTimes->{{3.6225653888591204`*^9, 3.6225653979719424`*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"ids", "=", RowBox[{"Table", "[", RowBox[{"i", ",", RowBox[{"{", RowBox[{"i", ",", "1", ",", "10000"}], "}"}]}], "]"}]}], ";"}]], "Input",\ CellChangeTimes->{{3.566653054888865*^9, 3.5666530790887413`*^9}, { 3.6225700949977703`*^9, 3.6225700953368044`*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"names", "=", RowBox[{"Table", "[", RowBox[{ RowBox[{"\"\\"", "<>", RowBox[{"ToString", "[", "i", "]"}]}], ",", RowBox[{"{", RowBox[{"i", ",", "1", ",", "10000"}], "}"}]}], "]"}]}], ";"}]], "Input",\ CellChangeTimes->{{3.5666530870810375`*^9, 3.5666531588050933`*^9}, { 3.62257011970024*^9, 3.622570119829253*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"values", "=", RowBox[{"Flatten", "@", RowBox[{"RandomReal", "[", RowBox[{"20", ",", RowBox[{"{", RowBox[{"1", ",", "10000"}], "}"}]}], "]"}]}]}], ";"}]], "Input", CellChangeTimes->{{3.566653162930176*^9, 3.566653228038906*^9}, 3.566653321559625*^9, {3.566653363091625*^9, 3.566653365081625*^9}, 3.5666542629456778`*^9, {3.622570124230694*^9, 3.6225701244297132`*^9}}], Cell[BoxData[ RowBox[{"Length", "[", "values", "]"}]], "Input", CellChangeTimes->{{3.566653351800625*^9, 3.5666533572086253`*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"data", "=", RowBox[{"Table", "[", RowBox[{ RowBox[{"{", RowBox[{ RowBox[{"ids", "[", RowBox[{"[", "i", "]"}], "]"}], ",", RowBox[{"names", "[", RowBox[{"[", "i", "]"}], "]"}], ",", RowBox[{"values", "[", RowBox[{"[", "i", "]"}], "]"}], ",", RowBox[{"SQLDateTime", "[", RowBox[{"Date", "[", "]"}], "]"}]}], "}"}], ",", RowBox[{"{", RowBox[{"i", ",", "1", ",", "10000"}], "}"}]}], "]"}]}], ";"}]], "Input",\ CellChangeTimes->{{3.5666532377441006`*^9, 3.566653299256625*^9}, { 3.5666535722616253`*^9, 3.566653582855625*^9}, 3.566654272635334*^9, { 3.6225701301712875`*^9, 3.6225701303003006`*^9}}], Cell[BoxData[ RowBox[{"Length", "@", "data"}]], "Input", CellChangeTimes->{{3.5666553096040106`*^9, 3.5666553118790107`*^9}}] }, Closed]], Cell[CellGroupData[{ Cell["Insert data into table", "Subsection", CellChangeTimes->{{3.6225654279969463`*^9, 3.6225654330439553`*^9}}], Cell[BoxData[ RowBox[{"time", "=", RowBox[{"AbsoluteTiming", "[", RowBox[{"SQLInsert", "[", RowBox[{"conn", ",", "\"\\"", ",", "columnheader", ",", "data"}], "]"}], "]"}]}]], "Input", CellChangeTimes->{{3.622569710382313*^9, 3.6225697133916135`*^9}}] }, Closed]] }, Open ]] }, Open ]], Cell[CellGroupData[{ Cell["", "SlideShowNavigationBar", CellTags->"SlideShowHeader"], Cell[CellGroupData[{ Cell["Creating timed based application", "Section", CellChangeTimes->{{3.6225649661852455`*^9, 3.622564974283246*^9}}], Cell[CellGroupData[{ Cell["Create a task that runs once every n seconds upto m times", "Subsection", CellChangeTimes->{{3.622565540911384*^9, 3.62256554587788*^9}, { 3.6225657570429945`*^9, 3.6225657725625467`*^9}}], Cell[BoxData[ RowBox[{ RowBox[{"n", "=", "1"}], ";"}]], "Input", CellChangeTimes->{{3.5666555004810104`*^9, 3.566655509777011*^9}}], Cell[BoxData[ RowBox[{"task", ":=", RowBox[{"Flatten", "@", RowBox[{"SQLExecute", "[", RowBox[{"conn", ",", "\"\