Get Help With Your DatabaseLink

Dillon Tracy
Mili Vishwakarma

Wolfram Research Inc.

Outline

Installing and setting up a database engine

About DatabaseLink

Connecting to database from Mathematica

Create, Retrieve, Update and Delete operations

Creating procedures and functions

Creating time based applications

Working with ResultSets

Questions

Installing and setting up MySQL

Install Mathematica

Download MySQL Community Server - http://dev.mysql.com/downloads/

Install MySQL and set up a root user

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

Login as root, enter password when prompted to
mysql -u root -p
password:

Create a database that we will try connecting to from Mathematica. In MySQL prompt type:

Create Database dbname

where ‘dbname’ is the name you want to give to the database

Change the current working database to ‘dbname’

Use dbname

Connecting to database from Mathematica

Load DatabaseLink

Needs["DatabaseLink`"]

Open a connection to MySQL  - Use OpenSQLConnection function
We will use the JDBC driver that comes with Mathematica

conn=OpenSQLConnection[JDBC["MySQL(Connector/J)","localhost/TestDB"],"Username"-> "root","Password"-> "$Prompt"]

If you see a SQLConnection object in the output, it means that the connection was successful.

Run a test command to check DatabaseLink can query the database, lets list the tables in the database

SQLTables[conn]

Create a database

To create a dataabase in mysql use CREATE DATABASE command with SQLExecute function

SQLExecute[conn,"CREATE DATABASE TestDB;"]

Change the current working database to TestDB

SQLExecute[conn,"Use TestDB;"]

Create a table named testtable in TestDB database

SQLCreateTable[conn,SQLTable["testtable"],{SQLColumn["name","DataTypeName"->"VARCHAR","DataLength"-> 20 ],SQLColumn["value","DataTypeName"-> "Integer"]}]

The same can be done using SQLExecute function

SQLExecute[conn,"CREATE TABLE testtable (name VARCHAR(20), value INTEGER);"]

Inserting data into database

Create some data rows to be inserted into testtable

number=Range[4,10,1]

numberstr=ToString/@Range[4,10,1]

name=Map[StringJoin["Name",#]&,numberstr]

data=MapThread[List,{name,number}]

Use SQLInsert to insert the data into testtable

SQLInsert[conn,"testtable",{"name","value"},data]

Selecting data from database

Use SQLSelect function to select data from a table

Select all data from a table

SQLExecute[conn,"Select * from testtable;"]

Select data based on some criteria

SQLSelect[conn,"testtable",{"name","value"},SQLColumn["value"]<8]

The same selection can be done using SQLExecute function

SQLExecute[conn,"select name,value from testtable where value < 6;"]

Updating a table

Use SQLUpdate to update a table in database

Update all rows with value less than 5

SQLUpdate[conn,"testtable",{"name","value"},{"Name11",11},SQLColumn["value"]<5]

Check if the update was successful

SQLExecute[conn,"Select * from testtable;"]

Delete data from table

Use SQLDelete to delete rows from a table

Delete all rows with ‘value’ greater than 10

SQLDelete[conn,"testtable",SQLColumn["value"]>10]

Similar operation can also be done by using SQLExecute

SQLExecute[conn,"Delete from testtable where value < 6;"]

Check if the delete was successful

SQLExecute[conn,"Select * from testtable;"]

Getting information about the table and connection

Connection information

Get all information about a table

SQLConnectionInformation[conn];

Check is a connection is open

SQLConnectionOpenQ[conn]

Check if a connection is usable

SQLConnectionUsableQ[conn]

Close a connection

CloseSQLConnection[conn]

List of functions for getting connection information

Table information

Get information on all tables in a database

SQLTableInformation[conn]//TableForm

Get information on a specific table

SQLTableInformation[conn,"testtable","ShowColumnHeadings"-> True]//TableForm

Get {table,column} pairs from a particular table

SQLColumnNames[conn,SQLTable["testtable"]]

List of functions for getting information on tables and data

Creating procedures and functions

No separate function in DatabaseLink to create a procedure and function, use SQLExecute

Use CREATE PROCEDURE command in SQLExecute directly

SQLExecute[conn,"CREATE PROCEDURE proc2(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM TestDB.testtable;
END"]

Call procedure ‘proc2’

SQLExecute[conn,"call proc2(@param1);"]

SQLExecute[conn,"select @param1;"]

To create a function use CREATE FUNCTION command with SQLExecute

SQLExecute[conn,"CREATE FUNCTION func(b VARCHAR(20),a INT) returns INT
BEGIN
if(a<5) then
insert into testtable values(b,a);
end if;
return a;
end;"]

Call function ‘func’

SQLExecute[conn,"select func('new',2);"]

SQLExecute[conn,"select * from testtable;"]

SQLExecute[conn,"CREATE FUNCTION Calc( start_value INT )
RETURNS INT
BEGIN
   DECLARE sum INT;
   SET sum = 0;
   label1: LOOP
     SET sum = sum + start_value;
     IF sum < 3000 THEN
       ITERATE label1;
     END IF;
     LEAVE label1;
   END LOOP label1;
   RETURN sum;
END;"]

SQLExecute[conn,"select Calc(2995);"]

Drop function and procedures

SQLExecute[conn,"drop procedure proc2;"]

Creating timed based application

This application uses ScheduledTask functionality in Mathematica to get data at particular times or for a time interval

Create a table that contains 4 columns - Id, Name, Value, UpdateTime

Create column header

columnheader={"Id","Name","Value","UpdateTime"}

List data type for each column

datatype={"INTEGER","VARCHAR(50)","FLOAT","DATETIME"}

Create a table of columns

Cols=Table[{columnheader[[i]],datatype[[i]]},{i,1,Length@columnheader}]

Get a list of columns into a format accepted by SQLCreateTable

colnsyntax=SQLColumn[#1,"DataTypeName"-> #2]&@@@Cols

Create a table, A return value of 0 indicates table was created successfully.

SQLCreateTable[conn,"Test1",colnsyntax]

Create some data to be inserted

ids=Table[i,{i,1,10000}];

names=Table["Name"<>ToString[i],{i,1,10000}];

values=Flatten@RandomReal[20,{1,10000}];

Length[values]

data=Table[{ids[[i]],names[[i]],values[[i]],SQLDateTime[Date[]]},{i,1,10000}];

Length@data

Insert data into table

time=AbsoluteTiming[SQLInsert[conn,"Test1",columnheader,data]]

Creating timed based application

Create a task that runs once every n seconds upto m times

n=1;

task:=Flatten@SQLExecute[conn,"Select value from Test1 limit 10;"]

Run the task using RunScheduledTask

Below command makes the task run once every 5 seconds upto 5 times

RunScheduledTask[n+=4;task,{5,5}]

Check if the task is running

pdata=Dynamic[ListPlot[n task,Filling->Axis,PlotRange->{Automatic,{1,600}}]]

Create a task that starts at a particular time

RunScheduledTask[n+=50;task,{1},AbsoluteTime[{2013,6,13,09,01,0}]]

Remove scheduled tasks

RemoveScheduledTask[ScheduledTasks[]]

Working with Resultsets

Create a simple Resultset that gets a set of values in one iteration.

We will use ResultSet functionality from DatabaseLink package for this task.

Set the size of dataset to be fetched at once

n=10;

Open a ResultSet that selects ‘value’ from table Test1 using SQLResultSetOpen

rs2=SQLResultSetOpen[SQLSelect[conn,"Test1",{"value"}]]

Create an empty list to which data coming from Test1 will be appended

newlist={};

Create a task that reads through the ResultSet  using SQLResultSetRead ‘n’ rows at a time and populates the list ‘newlist’

Use SQLResultSetPosition to return the current cursor position of result set

Use SQLResultSetGoto to set the current position of cursor to pos

task2:={data1=SQLResultSetRead[rs2,n];
AppendTo[newlist,data1];
pos=SQLResultSetPosition[rs2];
SQLResultSetGoto[rs2,pos+1];}

Run the task once every 5 seconds upto 5 times

RunScheduledTask[task2,{5,5}]

Visualize the data coming in dynamically

data2=Dynamic@Flatten[data1]

DynamicModule[{},Dynamic@ListLinePlot[Flatten[data1],PlotMarkers->Automatic,Filling->Axis]]

Close ResultSet using SQLResultSetClose and remove scheduled task

SQLResultSetClose[rs2];

RemoveScheduledTask[ScheduledTasks[]]

Questions??