Database Connectivity

With built-in database connectivity, Maple allows engineers and scientists to quickly develop and deploy powerful applications that combine large enterprise databases with the state-of-the-art analysis and visualization tools of Maple. You can easily query, create, and update your databases in Maple, without any detailed SQL knowledge. Now in Maple 18, database connectivity has been extended to also include native support for SQLite databases. SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world.

In the following example, we will use Maple's Database package to import population data from the G20 countries into a SQLite database, access the database, and produce population tables and charts.

 

Import Population Data from a .csv File into SQLite Database

> with(ArrayTools); -1
 
> with(Database[SQLite]); -1
 
> with(StringTools); -1


Import Data File

> csv := FileTools:-JoinPath([kernelopts('mapledir'),
 
> data := ImportMatrix(csv); -1


Create in Memory Database

The Open command opens a new database connection.

> db := Open(
 
SQLite database


Create Population Table

> sql := sprintf(
 
CREATE TABLE population (Date, USA, CHN, JPN, DEU, FRA, BRA, GBR, ITA, RUS, IND, CAN, AUS, ESP, MEX, KOR, IDN, TUR, SAU, ARG, ZAF)
CREATE TABLE population (Date, USA, CHN, JPN, DEU, FRA, BRA, GBR, ITA, RUS, IND, CAN, AUS, ESP, MEX, KOR, IDN, TUR, SAU, ARG, ZAF)
CREATE TABLE population (Date, USA, CHN, JPN, DEU, FRA, BRA, GBR, ITA, RUS, IND, CAN, AUS, ESP, MEX, KOR, IDN, TUR, SAU, ARG, ZAF)
 

The Execute command executes a SQL statement using the provided database connection. 

> Execute(db, sql)

 

Insert Data Into Population Table

> nrows := Size(data, 1); -1
 
> ncols := Size(data, 2); -1
 
> sql := sprintf(
 
INSERT INTO population VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
 

The Prepare command prepares a SQL statement for execution.

> stmt := Prepare(db, sql)
 
SQLite statement
 
> for i from 2 to nrows do for j to ncols do Bind(stmt, j, data[i][j]) end do; Step(stmt); Reset(stmt, 'clear' = true) end do; -1
 

The Finalize command finalizes the prepared statement.

> Finalize(stmt); -1

 

Read Data Back

> sql :=
 
SELECT * FROM population
> stmt := Prepare(db, sql)

SQLite statement
 

FetchAll returns all of the rows of the prepared statement.

> FetchAll(stmt)
 
Matrix(%id = 18446744074359045046)
 
> Finalize(stmt); -1

 

Plot Data for Population in Canada from 1975 to 1990

>
 
SELECT date, CAN FROM population WHERE date >= '1975-12-31' AND date <= '1990-12-31'
 
> stmt := Prepare(db, sql)
 
SQLite statement
 
> population := Matrix(1 .. 0, 1 .. 2); -1
 
> row := 1; -1
 
>


 
> Finalize(stmt); -1
 
> plots:-pointplot(population, color =
plots:-pointplot(population, color =
 
Plot_2d