updates/Maple18/Database - Help

Online Help

All Products    Maple    MapleSim


Home : Support : Online Help : updates/Maple18/Database

Database Updates

 

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. For more information about SQLite, refer to http://www.sqlite.org/

 

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

withArrayTools:

withDatabase[SQLite]:

withStringTools:

Import Data File

csvFileTools:-JoinPathkernelopts'datadir',SQLite,G20-Population.csv:

data:=ImportMatrixcsv:

Create in Memory Database

The Open command opens a new database connection.

db:=Open:memory:

db:=SQLite database,table( [( "main" ) = "" ] )

(1)

Create Population Table

sql:=sprintfCREATE TABLE population (%s),Joinconvertdata1,'list',,

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

(2)

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

Executedb,sql

Insert Data Into Population Table

nrows:=Sizedata,1:

ncols:=Sizedata,2:

sql:=sprintfINSERT INTO population VALUES (%s),Join?$ncols,,

sql:=INSERT INTO population VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

(3)

The Prepare command prepares a SQL statement for execution.

stmt:=Preparedb,sql

stmt:=SQLite statement,INSERT INTO population VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

(4)

forifrom2tonrowsdo   for j to ncols do     Bindstmt,j,dataij   end do;   Stepstmt;   Resetstmt,'clear'=trueend do:

The Finalize command finalizes the prepared statement.  

Finalizestmt:

Read Data Back

sqlSELECT ⋅ FROM population

sql:=SELECT * FROM population

(5)

stmt:=Preparedb,sql

stmt:=SQLite statement,SELECT * FROM population

(6)

FetchAll returns all of the rows of the prepared statement.

FetchAllstmt

53 x 21 MatrixData Type: anythingStorage: rectangularOrder: C_order

(7)

Finalizestmt:

Plot Data for Population in Canada from 1975 to 1990

sql:=SELECT date, CAN FROM population WHERE date >= '1975-12-31' AND date <= '1990-12-31'

sql:=SELECT date, CAN FROM population WHERE date >= '1975-12-31' AND date <= '1990-12-31'

(8)

stmt:=Preparedb&comma;sql

stmt:=SQLite statement&comma;SELECT date, CAN FROM population WHERE date >= '1975-12-31' AND date <= '1990-12-31'

(9)

population:=Matrix1..0&comma;1..2&colon;

row:=1&colon;

whileStepstmt&equals;RESULT_ROWdo   populationrow&comma;1:=sscanfFetchstmt&comma;0&comma;%d-%d-%d1&semi;   populationrow&comma;2:=Fetchstmt&comma;1&semi;row:=row&plus;1end do&colon;

Finalizestmt&colon;

plots:-pointplotpopulation&comma; color&equals;Red&comma; title&equals;Population of Canada: 1975 − 1990&comma; labels &equals; Year&comma;Population&comma; style&equals;line&comma; thickness&equals;3&comma; size&equals;800&comma;400

See Also

Database, Database[SQLite][Execute], Database[SQLite][Finalize], Database[SQLite][FetchAll], Database[SQLite][Open], Database[SQLite][Prepare]


Download Help Document

Was this information helpful?



Please add your Comment (Optional)
E-mail Address (Optional)
What is ? This question helps us to combat spam