Introduction to Using the Database Package
Package Structure
Help Page Conventions
Structured Query Language
Using the Database Package
Storing Maple Objects
Examples
Database uses an object oriented design. As such, some commands in the Database package return modules. These modules export commands that comprise the majority of the Database package functionality.
Each module that is returned is an instance of a class. A class defines the commands that are exported by the module. A module of a class must export all the commands defined by the class.
The Database help pages use the following conventions.
- A package export is referred to using table notation, for example, Database[LoadDriver].
- An exported command of a returned module uses :- notation. The left-hand side of the :- is written using italic font, for example, Driver:-OpenConnection. The name on the left-hand side of the :- can be viewed as the class name, thus defining the exported commands of the module, or as a user-defined name for the returned module, thus providing an example calling sequence.
Structured Query Language (SQL) is a standard language that is used to manipulate data in databases. Without any SQL knowledge, you can use the Query Builder Maplet application to construct SQL queries. To query and update the database, use commands to execute SQL statements.
Although you do not need any SQL knowledge to use the Database package, a basic understanding of SQL helps you build queries. SQL tutorials are available on the Internet. Also, many books on SQL are available.
Database Assistants
The Database package provides two Assistants. The Connection Builder facilitates the creation of Connection objects. All the information needed to open a connection can be entered into the Assistant. It also provides methods to load and save this information to a file. The saved connections can then be reloaded with the Connection Builder or through the LoadConnection command. The Connection Builder Assistant can be opened using either of the following commands:
Database():
Database:-ConnectionBuilder():
The Query Builder Assistant is used to visually construct queries. This allows data from a database to be accessed without using SQL. The Query Builder has two views, the Simple view and the Advanced view. The Simple view can be used for basic queries and is a good starting place for users with little experience with database queries. The Advanced view can construct more complex queries using aliases, joins, column expressions, and more. The Query Builder Assistant can be started by using the following command:
Database:-QueryBuilder():
Database Commands
To connect to a database, you must load a Java Database Connectivity (JDBC) driver. For information on installing a JDBC driver, see JDBC. The JDBC driver is loaded using LoadDriver. This returns a Driver module which represents the loaded driver.
A Driver module exports the command OpenConnection, which can be used to create a connection to the database. A login name, a password, and the location of the database are all required to open a connection. OpenConnection returns a Connection module. After establishing a connection, you can save it by using the Save command, so that you can easily reopen it by using the LoadConnection command.
driver, connection := Database():
Once the connection has been opened, the database can be accessed. Simple queries and updates can be executed using ExecuteQuery and ExecuteUpdate. However, for more complex queries, use a Statement.
You can also use the Query Builder Maplet application to construct SQL queries.
There are two kinds of statements. The basic Statement allows a string of SQL to be passed to the database. The string can be a query, an update or a combination of the two. These statements can be created by calling CreateStatement. The other kind of statement is a Prepared Statement. A Prepared Statement acts like a function call and allows data to be inserted into the SQL string on execution. For example, a Prepared Statement representing an insert can be used multiple times with different arguments to insert multiple data items into the database. A Prepared Statement module can be created using CreatePreparedStatement.
Statement and Prepared Statement modules export a command, Execute, which sends a SQL statement to the database. For a Statement, Execute takes the SQL string to be executed. A Prepared Statement takes the values to be inserted into its statement. A statement may generate multiple results. A result is either an integer, representing the number of updates performed, or a Result module, representing an SQL table. The first result is returned by the call to Execute. Other results can be obtained by calling NextResult.
A Result module represents an SQL table containing the rows selected by a query. To access these results, the Result module maintains a cursor which indicates a row in the table. The cursor can be moved using various commands, including Next, Previous and GotoRow. Data from the current row is accessed using the GetData command.
Some SQL data types are not easily represented in Maple, or they may be very large and so it is more convenient to access them in smaller pieces. To represent these cases, a DataInterface module is used. The current version of the Database package does not support many of the complex SQL data types (see compatibility). Therefore, currently the DataInterface module is used only for streaming character or binary types.
The following commands may return a module: LoadDriver, OpenConnection, ExecuteQuery, CreateStatement, CreatePreparedStatement, Execute, NextResult and GetData
While Database attempts to be as platform and database independent as possible, there are still some compatibility issues. The issues page documents some known issues and workarounds for particular databases.
There is currently no direct support in Database for storing arbitrary Maple objects in a database. However, Maple objects can be stored by using sprintf to convert them to a string in ".m" format. These strings can then be stored in a database. To recover the Maple object, retrieve the string from the database and the convert it back to Maple using sscanf.
First create the driver module. This loads the JDBC driver and prepares to connect to the database. This returns a module, which you assign to the variable driver.
driver≔DatabaseLoadDriverdriver=com.database.Driver,classpath=/path/to/jar/jdbc3.jar
driver≔moduleoptionunload=Close;localhandle;exportOpenConnection,Close;end module
Using the module, driver, you can open a connection to the database. This returns another module that is assigned to connection.
connection≔driver:-OpenConnectionjdbc:database://localhost/Data,username,password
connection≔moduleoptionunload=Close;localhandle;exportExecuteQuery,ExecuteUpdate,CreateStatement,CreateCallableStatement,CreatePreparedStatement,Commit,Rollback,Close,SetOptions,GetOptions;end module
You can use the ExecuteQuery export of connection to begin accessing data from the database. Since the SQL statement is a query, you will receive a Result module.
result≔connection:-ExecuteQuerySELECT id,name FROM dataTable
result≔moduleoptionunload=Close;localhandle;exportNext,Previous,Last,First,GetRowNumber,GotoRow,GetRowCount,InsertRow,DeleteRow,UpdateRow,GetData,UpdateData,GetType,GetName,GetColumnCount,SetOptions,GetOptions,Close,ToMaple;end module
Step through the table, accessing the columns from each row.
whileresult:-Nextdoresult:-GetData1;result:-GetData2enddo
1
John
2
Jerry
3
Jane
4
Jennifer
Using a Prepared Statement, you can select individual rows based on the value in their id column without having to enter nearly identical SQL statements multiple times.
pstat≔connection:-CreatePreparedStatementSELECT id,name FROM dataTable WHERE id = ?
pstat≔moduleoptionunload=Close;localhandle;exportExecute,NextResult,Close,SetOptions,GetOptions;end module
The value given to Execute is inserted into the SQL statement so this selects the row with id = 1.
result≔pstat:-Execute1:result:-Next
true
result:-GetData1
result:-GetData2
The Prepared Statement can be reused with different data.
result≔pstat:-Execute2:result:-Next
See Also
Database
Database[compatibility]
Database[Connection]
Database[DataInterfaceStream]
Database[Driver]
Database[issues]
Database[JDBC]
Database[Result]
Database[Statement]
examples/DatabaseGrades
Download Help Document
